Fork me on GitHub

Хранение географических координат в Базах Данных

Полезные ссылки:

Установка PostGIS

The following will install postgresql 9.6, PostGIS 2.3, PGAdmin4, pgRouting 2.3 and additional supplied modules including the adminpack extension:

sudo apt-get install postgresql-9.6
sudo apt-get install postgresql-9.6-postgis-2.3 postgresql-contrib-9.6 postgresql-9.6-postgis-scripts
#to get the commandline tools shp2pgsql, raster2pgsql you need to do this
sudo apt-get install postgis

Возникла задача хранить в БД географические координаты объекта:

  • 1 вариант. Данные-координаты (8 переменных) с формы преобразуются функцией в запись типа 45-23-56N 34-12-44W и записываются в 1 поле coordinates varchar(20) основной таблицы.
  • 2 вариант. Данные-координаты (8 переменных) с формы преобразуются функцией в 2 десятичных числа вида 14.5469645673 и –23.65798543556 и записываются в 2 поля в основной таблице: latitude dec(2,10) и longitude dec(3,10)
  • 3 вариант. Создается дополнительная таблица coordinates и данные с формы (8 переменных) записываются каждая в свое поле.Чтобы указать положение точки на поверхности Земли можно воспользоваться:Широтой(latitude) — идет с севера на юг. 0 — экватор. Изменяется от -90 до 90 градусов.Долготой(longitude) — идет с запада на восток. 0 — нулевой меридиан(Гринвич). Изменяется от -180 до 180 градусов.

Нужно обратить внимание что x — это долгота, y — широта(Google Maps, Яндекс.Карты и все остальные сервисы указывают долготу первой).

Географические координаты можно перевести в пространственные — просто точка (x,y,z). Кому интересно более подробно можно посмотреть википедию.Количество знаков после запятой определяет точность.

PostGIS

PostGIS добавляет поддержку для географических объектов в PostgreSQL. По сути PostGIS позволяет использовать PostgreSQL в качестве бэкэнда пространственной базы данных для геоинформационных систем (ГИС), так же, как ESRI SDE или пространственного расширения Oracle. PostGIS соответствует OpenGIS «Простые особенности. Спецификация для SQL» и был сертифицирован.

Установка и использование

Для начала инициализируем расширение в базе данных:

CREATE EXTENSION postgis;

При создании пространственной базы данных автоматически создаются таблица метаданных spatial_ref_sys и представления geometry_columns, geography_columns, raster_columns и raster_overviews. Они создаются в соответствии со спецификацией «Open Geospatial Consortium Simple Features for SQL specification», выпущенной OGC и описывающей стандартные типы объектов ГИС, функции для манипуляции ими и набор таблиц метаданных. Таблица spatial_ref_sys содержит числовые идентификаторы и текстовые описания систем координат, используемых в пространственной базе данных. Одним из полей этой таблицы является поле SRID — уникальный идентификатор, однозначно определяющий систему координат. SRID представляет из себя числовой код, которому соответствует некоторая система координат. Например, распространенный код EPSG 4326 соответствует географической системе координат WGS84. Более подробную информацию по таблицами метаданных можно найти в руководстве по PostGIS.

Теперь, имея пространственную базу данных, можно создать несколько пространственных таблиц. Для начала создадим обычную таблицу базы данных, чтобы хранить данные о городе. Эта таблица будет содержать три поля: числовой идентификатор, название города и колонка геометрии, содержащую данные о местоположении городов:

CREATE TABLE cities ( id int4 primary key, name varchar(50), the_geom geometry(POINT,4326) );

the_geom поле указывает PostGIS, какой тип геометрии имеет каждый из объектов (точки, линии, полигоны и т.п.), какая размерность (т.к. возможны и 3-4 измерения — POINTZ, POINTM, POINTZM) и какая система координат. Для данных по городам мы будем использовать систему координат EPSG:4326. Чтобы добавить данные геометрии в соответствующую колонку, используется функция PostGIS ST_GeomFromText, чтобы сконвертировать координаты и идентификатор референсной системы из текстового формата:

# INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');

# INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');

# INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

Все самые обычные операторы SQL могут быть использованы для выбора данных из таблицы PostGIS:

SELECT * FROM cities;

id |      name       |                      the_geom ----+-----------------+---------------------------------------------------- 1 | London, England | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940 2 | London, Ontario | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540 3 | East London,SA  | 0101000020E610000040AB064060E93B4059FAD005F58140C0 (3 rows)

Это возвращает нам бессмысленные значения координат в шестнадцатеричной системе. Если вы хотите увидеть вашу геометрию в текстовом формате WKT, используйте функцию ST_AsText(the_geom) или ST_AsEwkt(the_geom). Вы также можете использовать функции ST_X(the_geom), ST_Y(the_geom), чтобы получить числовые значения координат:

SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;

id |          st_astext           |               st_asewkt                |    st_x     |   st_y ----+------------------------------+----------------------------------------+-------------+----------- 1 | POINT(-0.1257 51.508)        | SRID=4326;POINT(-0.1257 51.508)        |     -0.1257 |    51.508 2 | POINT(-81.233 42.983)        | SRID=4326;POINT(-81.233 42.983)        |     -81.233 |    42.983 3 | POINT(27.91162491 -33.01529) | SRID=4326;POINT(27.91162491 -33.01529) | 27.91162491 | -33.01529 (3 rows)

Большинство таких функций начинаются с ST (пространственный тип) и описаны в документации PostGIS. Теперь ответим на практический вопрос: на каком расстоянии в метрах друг от другах находятся три города с названием Лондон, учитывая сферичность земли?

SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

name       |      name       | st_distance_sphere -----------------+-----------------+-------------------- London, Ontario | London, England |   5875787.03777356 East London,SA  | London, England |   9789680.59961472 East London,SA  | London, Ontario |   13892208.6782928 (3 rows)

Этот запрос возвращает расстояние в метрах между каждой парой городов. Обратите внимание как часть WHERE предотвращает нас от получения расстояния от города до самого себя (расстояние всегда будет равно нулю) и расстояния в обратном порядке (расстояние от Лондона, Англия до Лондона, Онтарио будет таким же как от Лондона, Онтарио до Лондона, Англия). Также можем рассчитать расстояния на сфере, используя различные функции и указывая называния сфероида, параметры главных полуосей и коэффициента обратного сжатия:

# SELECT p1.name,p2.name,ST_Distance_Spheroid(

#         p1.the_geom,p2.the_geom, 'SPHEROID["GRS_1980",6378137,298.257222]'

#         )

#        FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

name       |      name       | st_distance_spheroid -----------------+-----------------+---------------------- London, Ontario | London, England |     5892413.63999153 East London,SA  | London, England |     9756842.65715046 East London,SA  | London, Ontario |     13884149.4143795 (3 rows)

Заключение

В данной главе мы рассмотрели как начать работать с PostGIS. Более подробно о использовании расширения можно ознакомиться через официальную документацию.

social