使用MySQL'POINT'和PHP通过表单插入纬度和经度点 [英] Using MySQL 'POINT' and PHP to insert Latitude and Longitude points through a form

问题描述

我正在制作一个基于Web的应用程序，该应用程序允许用户根据其输入来创建路线.用户向Google地图添加航点，并获取经度和纬度点，并将其复制到网站上的表格中，然后使用PHP将数据发送到MySQL数据库.

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.

我在将lat和lon点添加到我的MySQL数据库时遇到了麻烦.我正在使用数据类型"POINT".但是，当我将点发送到数据库时，出现错误无法从发送到GEOMETRY字段的数据中获取几何对象".

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

我花了很多天研究这个错误.我尝试添加空间索引，但是MySQL不允许我这样做.我尝试过更改表，以便将该行更改为update table set point_latlon = POINT(lat lon)，但是它告诉我未定义lat，因此找不到解决方案.

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.

这是我的桌子:

CREATE TABLE user_input (
  point_id not null primary key auto_increment,
  point_desc varchar(40),
  point_LatLon not null point
);

这是我的插入语句:

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

推荐答案

您需要类似

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

其中传递给GeomFromText的参数是一个字符串，例如

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

POINT(45.1234 123.4567)

我发现，如果我有两个数字参数，其中?是那些lat和lon参数的占位符，则这种构造效果很好. CONCAT函数将所需的文本字符串拼凑在一起.

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(', ?, ' ', ?, ')') ) 

碰巧，您无法在5.7.4之前的MySQL版本中的InnoDB表中创建空间索引(目前尚不可用).如果需要空间索引，则需要对表使用MyISAM访问方法.这有点麻烦.

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.

这是有关为此目的使用地理空间的文章. http://www.plumislandmedia.net/mysql/using- mysqls-geospatial-extension-location-finder/

Here's a writeup on using geospatial for this purpose. http://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/

这篇关于使用MySQL'POINT'和PHP通过表单插入纬度和经度点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！