将空间数据插入MySQL数据库

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.

Answer 1

Your best bet is

1. 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.