Всем привет! Рассмотрим расширение для баз данных, которое добавляет поддержку географических объектов, а значит появляется возможность выполнять запросы местоположения

В Рамках задачи работы с ClickStream, у нас имелись координаты действий пользователей в определенный момент времени и возникла идея отобразить их на карте. Для этого нам поможет расширение PostGIS.

PostGIS – расширение для географических и пространственных данных, при помощи которого можно:

обрабатывать точки, кривые, фигуры;
получать статистику по ним;
строить карты или находить маршруты;
сохранять полученные данные в таблицу;
преобразовывать географические и/или пространственные данные в друг друга;
индексировать и обрабатывать полученные данные.

Библиотека включает в себя:

геометрические типы;
вычислительные и аналитические функции;
GIST индексы;
инструменты импорта/экспорта;
GDAL - Geospatial Data Abstraction Library;
параллельную обработку.

Для использования расширения можно:

в коммерческой версии скачивается и устанавливается package;
или это расширение можно собрать их исходников и установить самостоятельно.

Для примера создадим таблицу, где одним из типов данных будет geometry, в качестве IDE будем использовать DBeaver.

Полученные геометрические данные можно обрабатывать на сегментах, частями ускоряя обработку массива. Можно вставить:

полигон (POLYGON);
линию (LINESTRING), прямую или ломаную;
ломаную линию, из которой будет создан полигон (MULTIPOINTS)

CREATE TABLE geom_test (gid int4, geom geometry, name varchar(25) ); 
INSERT INTO geom_test ( gid, geom, name ) VALUES (1, 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square');
 INSERT INTO geom_test ( gid, geom, name ) VALUES (2, 'LINESTRING((1 1 1,5 5 5,7 7 5))', '3D Line'); 
INSERT INTO geom_test ( gid, geom, name ) VALUES (3, 'MULTIPOINT((3 4,8 9))', '2D Aggregate Point'); 
INSERT INTO geom_test ( gid, geom, name ) VALUES (1, ST_Polygon(ST)GeomFromText('LINESTRING(75.15 29.53,77 30,77.6 29.5 75.15 29.53)'), 4326, '3D Poly');

Можно выбрать из таблицы, какие типы данных содержатся в каком поле с каким именем

SELECT geometrytype (geom), NAME FROM geom_test;

Можно выбрать все содержимое таблицы и получить наглядное представление об информации, которая хранится в геометрическом поле.

SELECT * FROM geom_test;

Еще одной возможностью DBeaver является его способность отобразить объект на карте и посмотреть, что это такое.

SELECT ST_Polygon( ST_GeomFromText('LINESTRING(75.15 29.53,77 30,77.6 29.5, 75.15 29.53)'), 4326 );

Можно поставить точку для произвольных координат и посмотреть ее местонахождение на реальной карте.

Расширение PostGIS оно также работает в PostgreSQL. При необходимости анализа большого объема информации она складывается в Greenplum и обрабатывается параллельно на всех сегментах.

PostGIS поддерживает создание GiST, что позволяет ускорить обработку географических запросов и проще выполнять операции поиска-вхождения или принадлежности к одной и той же зоне разных объектов. Такой географический GiST индекс будет представлять собой дерево, в котором будут последовательно отображаться все объекты.

Геометрические поля можно добавлять к уже существующим таблицам. Для этого применяется специфический оператор AddGeometryColumn

DROP TABLE geotest;
CREATE TABLE geotest (id INT4, name VARCHAR(32) ); 
SELECT AddGeometryColumn ('geotest','geopoint', 4326,'POINT',2);
После создания таблицы, можно добавить к ней колонку с геометрическими данными, которые можно дополнять и смотреть их на карте.
INSERT INTO geotest (id, name, geopoint) VALUES (1, 'Olympia', ST_GeometryFromText('POINT(-122.90 46.97)', 4326));
 INSERT INTO geotest (id, name, geopoint) VALUES (2, 'Renton', ST_GeometryFromText('POINT(-122.90 47.50)', 4326)); 

SELECT name, ST_AsText(geopoint) 
FROM geotest;

SELECT geopoint FROM geotest WHERE name = 'Olympia'

Geohash

Для удобной работы с координатами можно использовать geohash. Геохэши позволяют из любой точки создать набор символов, который с некоторой точность задает нашу точку.

Их преимущество заключается в том, что:

можно явно задавать точность;
каждый геохэш более высокого порядка входит в геохэш более низкого порядка, поэтому легко производить операции включения/исключения;
использование геохешей экономит место, так как их хранить проще, чем координаты.

Например, зададим точку и посмотрим, какой геохэш будет ей соответствовать:

SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326));

Здесь ST_SetSRID - устанавливает SRID для геометрии в определенное целочисленное значение. SRID (spatial referencing system identifier) - уникальный идентификатор, однозначно определяющий систему координат, например, используемый нами код 4326 соответствует географической системе координат WGS84.

Уменьшим длину геохеша, например, до 5. Видим, что полученное значение входит в полученный ранее более точный геохэш:

SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326),5);

Подобными образом можно явно задавать точность геохэша. Например, геохэш в 6 символов дает погрешность ≈ 600 м.

Идея алгоритма состоит в том, что широта и долгота кодируется в число, которое затем кодируется в base-32.

Карта разбивается на 32 квадрата и нумеруется от 1 до z.

Подробно алгоритм построения можно почитать на википедии.

Его идея похожа на арифметическое кодирование.

Как видим из примера:

Хешированная координата указывает на квадрат C, что находится в Северной Америке.

Таким образом, расширение PostGIS позволяет манипулировать большими объемами картографических и географических данных, а его регулярное обновление дает большие перспективы для дальнейшего применения.

GreenPlum. PostGIS