PostGIS – главный помощник при анализе геопространственных данных
Использование функций PostGIS с примерами в базах данных PostgreSQL и Greenplum
Что такое PostGIS
PostGIS – это специальное расширение пространственных баз данных PostgreSQL , включая Greenplum, массивно-параллельный PostgreSQL для аналитики. PostGIS добавляет поддержку географических объектов, без проблем работает с векторными и растровыми геопространственными данными и позволяет выполнять запросы местоположения с помощью единого набора SQL-функций.
Хотя подробную информацию обо всех функциях PostGIS можно легко найти в документации PostGIS, эта статья поможет Вам лучше понять то, как именно функции PostGIS используются в реальной жизни, а также познакомит Вас со сценариями использования с учетом местоположения и примерами запросов, связанных с геометрией.
Набор данных
В приведенных ниже примерах будут использоваться данные, полученные из Natural Earth,общедоступного набора картографических данных, представленных в различных масштабах: 1:10 м, 1:50 м и т. д.
Natural Earth Vector поставляется в формате ESRI шейп-файла, который является стандартом для всех векторных геоданных. Кодировка символов - UTF-8. Все данные Natural Earth используют географическую систему координат (проекцию) и точку отсчета WGS84.
В примерах, приведенных ниже, мы использовали сразу несколько различных шейп-файлов из Natural Earth для получения информации о странах, штатах, населенных пунктах, озерах, реках и аэропортах. В каждом шейп-файле столбец geom содержит информацию о геометрии, а name - название подразделения.
Примеры
• Найти аэропорты в Великобритании
Функция PostGIS ST_WithIn(geometry A, geometry B) возвращает true, если геометрия /география A полностью находится внутри геометрии/географии B.
SELECT airports.geom
, airports.name_en , airports.abbrev FROM public.ne_10m_airports airports , public.ne_10m_admin_0_countries countries WHERE ST_WithIn(airports.geom, countries.geom) AND countries.name = 'United Kingdom';
• Найти реки, начинающиеся или находящиеся в США
Функция PostGIS ST_Intersects(geometry A, geometry B) возвращает true, если 2 геометрии /географии A, B пересекаются в 2D пространстве (то есть имеют как минимум 1 общую точку).
SELECT rivers.name
, rivers.name_en , rivers.geom FROM public.ne_10m_admin_0_countries AS countries, public.ne_10m_rivers_lake_centerlines AS rivers WHERE countries.name = 'United States of America' AND rivers.featurecla = 'River' AND rivers.name != '' AND ST_Intersects(countries.geom, rivers.geom);
• Топ 10 густонаселенных европейских столиц, через которые протекает река
Функция PostGIS ST_Buffer (geometry A, float radius_of_distance) возвращает геометрию, которая представляет все точки, чье расстояние до геометрии больше или равно этому самому расстоянию
Функция PostGIS ST_Union(geometry set A) объединяет геометрии для получения результирующей геометрии без наложения.
WITH ranked AS ( SELECT places.gid AS pgid , rivers.gid AS rgid , DENSE_RANK () OVER (ORDER BY places.pop_max DESC) AS rank FROM public.ne_10m_populated_places_simple AS places, public.ne_10m_rivers_lake_centerlines AS rivers WHERE places.adm0cap = 1 AND rivers.name_en != '' AND ST_Within(places.geom, ( SELECT ST_Union(country.geom) FROM public.ne_10m_admin_0_countries country WHERE continent='Europe')) AND ST_Intersects(ST_Buffer(places.geom, 0.05), rivers.geom) GROUP BY places.gid, rivers.gid, places.pop_max) SELECT pl.name, ranked.rank, pl.geom FROM ranked, public.ne_10m_populated_places_simple AS pl WHERE ranked.rank <= 10 AND pl.gid = ranked.pgid UNION ALL SELECT rr.name, ranked.rank, rr.geom FROM ranked, public.ne_10m_rivers_lake_centerlines AS rr WHERE ranked.rank <= 10 AND rr.gid = ranked.rgid;
Давайте разберемся, что к чему:
- ST_UNION использует геометрию разных стран для создания нового, объединенного результата - геометрии континентов для всех европейских стран;
SELECT ST_Union(country.geom) FROM public.ne_10m_admin_0_countries country WHERE continent='Europe'
- ST_WITHIN возвращает все места (города), геометрия которых полностью совпадает с геометрией континента;
ST_Within(places.geom, ( SELECT ST_Union(country.geom) FROM public.ne_10m_admin_0_countries country WHERE continent='Europe'))
- ST_Buffer вычисляет «расширенную» геометрию места (города), охватывающую все точки на расстоянии радиуса 0,05 единиц от исходного места (города) - чтобы понять единицы измерения по EPSG:900913 - Google Maps Global (Spherical) Mercator, который является SRID шейп-файлов Natural Earth, см. https://epsg.io/900913);
ST_Buffer(places.geom, 0.05)
- ST_Intersects проверяет, есть ли у двух геометрий, «расширенных» мест (городов) и рек, хотя бы одна общая точка в 2D-пространстве:
ST_Intersects(ST_Buffer(places.geom, 0.05), rivers.geom)
• Расположите Испанию, Фрпнцию, Италию, Грецию, Турцию, Кипр и Морокко в порядке убывания в зависимости от длины их береговой линии
ST_Intersection(geometry A, geometry B) возвращает геометрию, представляющую общую часть геометрий A и B.
SELECT name
, continent , DENSE_RANK() OVER (ORDER BY coastline_length DESC) AS rank , coastline_length , geom FROM ( SELECT name , continent , ST_UNION(geom) AS geom , ST_LENGTH(ST_UNION(geom)) AS coastline_length FROM ( SELECT cntr."name" , cntr.continent , (ST_Dump(ST_Intersection(cntr.geom, cst.geom))).geom geom FROM ne_10m_admin_0_countries cntr , ne_10m_coastline cst WHERE ST_INTERSECTS(cst.geom, cntr.geom) AND name IN ('Spain', 'France', 'Italy', 'Greece', 'Turkey', 'Cyprus', 'Morocco') ) clipped WHERE ST_DIMENSION(clipped.geom) = 1 GROUP BY name, continent ) foo ORDER BY coastline_length DESC;
Давайте разберемся, что к чему:
- ST_INTERSECTS возвращает геометрии береговой линии и страны, которые пересекаются в пространстве;
ST_INTERSECTS(ne_10m_coastline.geom, ne_10m_admin_0_countries.geom)
- ST_INTERSECTION возвращает новую геометрию, представляющую общую часть геометрий береговой линии и страны, а ST_DUMP возвращает набор компонентов этой геометрии;
(ST_DUMP(ST_INTERSECTION(ne_10m_coastline.geom,ne_10m_admin_0_countries.geom))).geom
- ST_UNION возвращает геометрию, представляющую собой объединение наборов точек предыдущей геометрии или геометрию 'границы страны по береговой ';
ST_UNION(geom)
- И, наконец, ST_LENTH подсчитывает длину геометрии ‘границы страны по береговой линии';
ST_LENGTH(ST_UNION(geom))
