Inserting spatial data into MySQL database

Question

I tried to insert data into point() datatype column from PHP this way:

INSERT INTO table (coordinates) VALUES ("48.20 14.80");

or

INSERT INTO table (coordinates) VALUES ("POINT(48.20 14.80)");

And then I applied triggers before every insert or update:

BEGIN
SET @lat = SUBSTRING(NEW.coordinates, 1, LOCATE(' ', NEW.coordinates));
SET @lng = SUBSTRING(NEW.coordinates, LOCATE(' ', NEW.coordinates) + 1);
SET @coor = PointFromWKB(POINT(@lat, @lng));
SET NEW.coordinates = @coor;
END

or

BEGIN
SET NEW.coordinates = GEOMFROMTEXT(NEW.coordinates);
END

But it returns:

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field

Does anyone of you know what's the problem? Thanks.

check datatype of coordinates – PHP Ninja Feb 07 '17 at 12:34 — PHP Ninja, Feb 07 '17 at 12:34

Danilo Bustos · Answer 1 · 2017-02-07T13:47:43.230

3

try this:

1-

   BEGIN
    SET @lat = ST_X(NEW.coordinates);
    SET @lng = ST_Y(NEW.coordinates);
    SET t.coordinates = GEOMFROMTEXT(CONCAT( 'POINT(', @lat, ' ', @lng, ')' ) ) ... --update point table
    END

2-

 INSERT INTO table (coordinates) VALUES (ST_GeomFromText('POINT(48.20 14.80)'));

edited Feb 07 '17 at 13:47

answered Feb 07 '17 at 13:13

Danilo Bustos

1,083
1
7
9

It works, but only when I add the data manually. I can' figure out how to send "raw" data (the app is based on a framework), that should be executed as a command (ST_GeomFromText(value) in INSERT). So I added immediately after BEGIN this line: SET NEW.coordinates = ST_GeomFromText(NEW.coordinates). And that, of course, doesn't work. – KRiSTiN Feb 07 '17 at 13:57
Does not work because wait `value` should be `text`. – Danilo Bustos Feb 07 '17 at 14:06
May I contact you somehow? I have few more questions about that problem. – KRiSTiN Feb 07 '17 at 14:36
Yes, I completed them. – KRiSTiN Feb 07 '17 at 15:15

score 0 · Answer 2 · answered Feb 07 '17 at 12:33

0

Your best bet is

split the colum coordinate into two colums e.g coord_lat and coord_long and before inserting data into table split the coordinate value into two variables.

OR 2. Change coordinate column data type to text.

answered Feb 07 '17 at 12:33

Qammar Feroz

708
8
21

Good solution, but I forgot to mention I would like to have a spatial index applied on that column. – KRiSTiN Feb 07 '17 at 13:11

score 0 · Answer 3 · answered Sep 07 '17 at 09:06

It is kinda late but i created this solution to the problem

$cord= $long." ".$lat;

And on insert i used this. Note i am using the codeigniter active record to insert data

$this->db->set("coord",'geomfromtext("POINT("'."'$cord'".'")")',false);
$this->db->insert("gisdata");

Hope it helps. Cheers!

Inserting spatial data into MySQL database

3 Answers3