将空间数据插入MySQL数据库

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.

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.

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)'));

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!