mysql 插入 point,使用MySQL'POINT'和PHP通过表单插入纬度和经度点

I'm making a web based application that allows users to create routes based on their input. The user adds waypoints to google maps and takes the latitude and longitude points and copies it into a table on the website that then sends the data to a MySQL database using PHP.

I'm having trouble with adding the lat and lon points to my MySQL database. I'm using the datatype 'POINT'. However, when I send the point to the database I get the error "Cannot get geometry object from data you send to the GEOMETRY field".

I have spent a good few days researching this error. I have tried adding a spatial index but MySQL won't allow me to. I have tried altering the table for so that that row is changed to update table set point_latlon = POINT(lat lon) but it tells me that lat isn't defined and I couldn't find a solution to it.

I truly am stuck with how to fix this error and have exhausted all my resources.

This is my table:

CREATE TABLE user_input (

point_id not null primary key auto_increment,

point_desc varchar(40),

point_LatLon not null point

);

And this is my insert statement:

$sql = "INSERT INTO user_input (point_desc, point_latLon) VALUES ('$_GET[waypoint_in_Desc]', 'GeomFromText($_GET[waypoint_in_LatLon])') ;";

This is my first time posting so apologies if I've left anything out or not formatted my post correctly. Thanks in advance.

解决方案

You need something like

SET point_LatLon = GeomFromText('POINT(45.1234 123.4567)')

in which the parameter you pass to GeomFromText is a character string like

POINT(45.1234 123.4567)

I have found that this sort of construct works well if I have two numeric parameters, where ? are the placeholders for those lat and lon parameters. The CONCAT function pieces together the required text string.

GeomFromText( CONCAT('POINT(', ?, ' ', ?, ')') )

It happens that you can't create a spatial index in an InnoDB table in versions of MySQL prior to version 5.7.4 (which is not yet generally available). If you want a spatial index, you'll need to use the MyISAM access method for your table. This is a bit of a nuisance.