Перемещение текстовых столбцов lat / lon в столбец типа "точка"

#mysql #latitude-longitude #point

#mysql #широта-долгота #точка

Вопрос:

У меня есть таблица в моей базе данных MySQL под названием house .

В house таблице есть пара текстовых столбцов, называемых latitude и longitude .

Я добавил новый столбец с именем coords , типа point — http://dev.mysql.com/doc/refman/5.0/en/gis-class-point.html

Как бы мне переместить значения latitude и longitude в новый coords столбец?

Ответ №1:

Предполагая, что вам нужен 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);

Если вы этого не сделаете, вы можете пропустить последние два шага.

Обновить:

В более ранних версиях MySQL вам нужно было бы заполнять Point столбцы с помощью WKT :

 UPDATE  mytable
SET     coords = GeomFromText(CONCAT('POINT (', lon, ' ', lat, ')'))

1. Вторая команда, похоже, не переводит значения в координаты. Я получаю эту ошибку: не удается получить объект geometry из данных, которые вы отправляете в поле GEOMETRY

2. @cannyboy: какую версию MySQL вы используете? Может lat и lon быть NULL ?

3. MySQL 5.0.45. текстовые столбцы широты и долготы не равны нулю.

4. @Quassnoi У вас неправильный порядок. Это POINT(long, lat) . Смотрите dev.mysql.com/doc/refman/5.7/en/gis-class-point.html . GeomFromText также является избыточным в последних версиях MySQL.

5. @aroth Это имеет значение, по крайней мере, исходя из моего личного опыта. Работа с такими функциями, как ST_Contains и ST_Distance , привела к нечетным результатам, когда я сохранил точечные данные как lat, long .

Ответ №2:

Версия MySQL 5.5.8

Мои широта и долгота имеют тип float. Для обновления существующих строк…

 UPDATE table_name SET coord = POINT(longitude_field, latitude_field);

Что следует учитывать, если вы собираете данные и вам нужно сохранить широту и долготу отдельно, в соответствующих столбцах, я предлагаю добавить триггер в вашу таблицу

 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;

Я собираю данные социальных сетей с географическими тегами и использую этот метод для добавления геометрии в свои таблицы.

Ответ №3:

Кратко:

 UPDATE myTable SET coords = GeometryFromText( CONCAT( 'POINT(', lon, ' ', lat, ')' ) );

Обратите внимание, что ответ от Quassnoi содержит ошибку, поскольку правильный формат ввода — ТОЧКА (X Y), или в терминах ТОЧКИ земли (lon lat).

Обратите внимание, что вы можете отображать точки с помощью функций X () и Y (), подобных следующему примеру:

 SELECT X( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS x, Y( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS y;

Ответ №4:

НАКОНЕЦ-ТО! Я смог исправить эти ошибки:

 #3037 - Invalid GIS data provided to function st_geometryfromtext.
#1416 - Cannot get geometry object from data you send to the GEOMETRY field

Выполнив пользовательский SQL-запрос, в котором я указал точки lat и long. В моем случае строка SQL, которая это сделала, была:

 UPDATE wp_wpgmza SET latlng = GeometryFromText( CONCAT( 'POINT(', 38.5775167, ' ', -121.4868583, ')' ) ) WHERE id = 63;