MySQL 将纬度/经度文本列移动到“点”类型列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5756232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Moving lat/lon text columns into a 'point' type column
提问by cannyboy
I've got a table in my MySQL database called house.
我的 MySQL 数据库中有一个表，名为house.
Within the housetable, there are a couple of text columns called latitudeand longitude.
在house表中，有几个文本列，称为latitude和longitude。
I've added a new column called coords, of type point- http://dev.mysql.com/doc/refman/5.0/en/gis-class-point.html
我添加了一个名为coords, 类型的新列point- http://dev.mysql.com/doc/refman/5.0/en/gis-class-point.html
How would I move the latitudeand longitudevalues into the new coordscolumn?
我如何将latitude和longitude值移动到新coords列中？
回答by Quassnoi
Assuming you want a SPATIALindex on this column:
假设您想要SPATIAL此列上的索引：
ALTER TABLE mytable ADD coords Point;
UPDATE mytable
SET coords = Point(lon, lat);
ALTER TABLE mytable MODIFY coords POINT NOT NULL;
CREATE SPATIAL INDEX sx_mytable_coords ON mytable(coords);
If you don't, you can omit the last two steps.
如果不这样做，则可以省略最后两个步骤。
Update:
更新：
In earlier versions of MySQL, you would need to populate Pointcolumns using WKT:
在 的早期版本中MySQL，您需要Point使用WKT以下方法填充列：
UPDATE mytable
SET coords = GeomFromText(CONCAT('POINT (', lon, ' ', lat, ')'))
回答by lemonpro
MySQL Version 5.5.8
MySQL 版本 5.5.8
My latitude and longitude are of type float. To update existing rows...
我的纬度和经度是浮动类型。要更新现有行...
UPDATE table_name SET coord = POINT(longitude_field, latitude_field);
Something to consider, if you are collecting data and need to save the latitude and longitude separately, in their respective columns, I suggest adding a trigger to your table
需要考虑的事情，如果您正在收集数据并需要在各自的列中分别保存纬度和经度，我建议在您的表中添加一个触发器
CREATE DEFINER=`username`@`localhost` TRIGGER `table_name`.`create_point_geom`
BEFORE INSERT ON database_name.table_name FOR EACH ROW
BEGIN
SET NEW.coord = POINT(NEW.longitude, NEW.latitude);
END;
I collect geo-tagged social media data and I use this method to add geometry to my tables.
我收集带有地理标记的社交媒体数据，并使用此方法将几何图形添加到我的表格中。
回答by Joseph Lust
Concisely:
简而言之：
UPDATE myTable SET coords = GeometryFromText( CONCAT( 'POINT(', lon, ' ', lat, ')' ) );
Note that answer from Quassnoi is in error since the proper input format is POINT(X Y), or in terms of earth POINT(lon lat).
请注意，来自 Quassnoi 的答案是错误的，因为正确的输入格式是 POINT(XY)，或者就地球 POINT(lon lat) 而言。
Note you can show points via the X() and Y() functions like the following example:
请注意，您可以通过 X() 和 Y() 函数显示点，如下例所示：
SELECT X( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS x, Y( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS y;
回答by PixelKraft
FINALLY! I was able to fix these errors:
最后！我能够修复这些错误：
#3037 - Invalid GIS data provided to function st_geometryfromtext.
#1416 - Cannot get geometry object from data you send to the GEOMETRY field
By doing a custom SQL query, where I pointed the lat and long points. In my case, the SQL string that did it was:
通过执行自定义 SQL 查询，我指出了经纬度点。就我而言，执行此操作的 SQL 字符串是：
UPDATE wp_wpgmza SET latlng = GeometryFromText( CONCAT( 'POINT(', 38.5775167, ' ', -121.4868583, ')' ) ) WHERE id = 63;
