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