GreenPlum. PostGIS

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

В Рамках задачи работы с 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 позволяет манипулировать большими объемами картографических и географических данных, а его регулярное обновление дает большие перспективы для дальнейшего применения.