创建MySQL空间列-使用lat long的Point Data类型，而无需使用Alter表 [英] Create MySQL spatial column - Point Data type with lat long without using Alter table

问题描述

如何使用纬度&创建带有"Point"数据类型的MySQL空间列?使用CREATE语句的经度. (不使用Alter)

How can I create a MySQL spatial column with "Point" Data type using latitude & longitude using a CREATE statement. (Without using Alter)

我是否还需要存储纬度和经度，或者可以将两个值都插入Point(x，y)字段并摆脱纬度/经度列?到目前为止，我所阅读的示例都保留了lat long字段.

Do I need to store latitude and longitude as well or can I insert both values into the Point(x,y) field and get rid of the latitude/ longitude columns? The examples that I have read till now retain the lat long fields.

请分享一些例子.

推荐答案

point字段内部存储了纬度和经度数据，如果需要，可以很容易地检索它们.假设您的点字段为名称pt，以下查询将提供此信息.

The point field has both the latitude and longitude data stored inside it and they can be retrieved quite easily if required. Assuming your point field is name pt, the following query gives this information.

SELECT ST_Y(pt), ST_X(pt) FROM my_spatial_table;

这和做的完全一样

SELECT Y(pt), X(pt) FROM my_spatial_table;

因为 X和ST_X 是别名.简而言之，您只需要点字段即可.

since X and ST_X are aliases. So in short you only need the point field.

您可以如下添加pt字段:

You can add your pt field as follows:

ALTER TABLE my_table ADD COLUMN GEOMETRY;

然后，您可以按如下所示从现有的纬度和经度列中移动数据:

Then you can move the data from the existing latitude, and longitude columns as follows:

UPDATE my_table SET pt = PointFromText(CONCAT('POINT(',longitude,' ',latitude,')'))

有关此的更多详细信息，请参见: https://stackoverflow.com/a/7135890/267540
http://dev.mysql.com/doc/refman/5.7/en/populating-spatial-columns.html

For more details on this please see: https://stackoverflow.com/a/7135890/267540
http://dev.mysql.com/doc/refman/5.7/en/populating-spatial-columns.html

这篇关于创建MySQL空间列-使用lat long的Point Data类型，而无需使用Alter表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！