Перейти к содержанию

Геометрический тип данных#

Материалы к практическому занятию#

В директории Student создайте папку Группа_ФИО. Разархивируйте скаченные материалы

Презентация к практике

Архив данных

Загрузка данных#

  1. Скачайте Архив данных и разархивируйте файлы.
  2. Откройте pgdmin
  3. Откройте панель "Query tool"
  4. Перенесите файл в панель и выполните код.
  5. Повторите для всех файлов

https://ratcatcher.ru/media/bd/pr/pr4/Практика_4.gif

Описание данных#

Данные для данного практикума включают четыре шейп-файла для города Нью-Йорк и одну атрибутивную таблицу с социодемографическими переменными. Мы загрузим наши шейп-файлы как таблицы PostGIS и добавим социодемографические данные позже в ходе занятия.

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

Для изучения структуры таблиц в pgAdmin нажмите правой кнопкой мыши на выбранной таблице и выберите Properties. Вы найдете краткое описание свойств таблицы, включая список атрибутов в разделе Columns.

Таблица nyc_census_blocks#

Блок переписи населений — это самая мелкая географическая единица, для которой собираются данные переписи. Все более крупные географические единицы (группы блоков, тракты, метрополии, округа и т.д.) могут быть сформированы объединением блоков. В данной таблице к блокам прикреплены демографические данные.

https://ratcatcher.ru/media/bd/pr/pr4/nyc_census_blocks.png

Количество записей: 38 794

Атрибут Описание
blkid Уникальный 15-значный код блока переписи (например: 360050001009000)
popn_total Общее количество людей в блоке переписи
popn_white Количество людей, идентифицирующих себя как "Белые"
popn_black Количество людей, идентифицирующих себя как "Темнокожие"
popn_nativ Количество людей, идентифицирующих себя как "Коренные американцы"
popn_asian Количество людей, идентифицирующих себя как "Азиаты"
popn_other Количество людей, относящихся к другим категориям
boroname Название района Нью-Йорка (Манхэттен, Бронкс, Бруклин, Статен-Айленд, Куинс)
geom Полигональная граница блока

Таблица nyc_neighborhoods#

Нью-Йорк имеет богатую историю районных названий и границ. Районы являются социальными конструкциями, не всегда совпадающими с административными границами.

Количество записей: 129

https://ratcatcher.ru/media/bd/pr/pr4/nyc_neighborhoods.png

Атрибут Описание
name Название района
boroname Название района Нью-Йорка
geom Полигональная граница района

Таблица nyc_streets#

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

Количество записей: 19 091

Атрибуты:

name — название улицы.
oneway — является ли улица односторонней («yes» = да, «» = нет).
type — тип дороги (primary, secondary, residential, motorway).
geom — центральная линия улицы.

https://ratcatcher.ru/media/bd/pr/pr4/nyc_streets.png

Атрибут Описание
name Название улицы
oneway Является ли улица односторонней («yes» = да, «» = нет)
type Тип дороги (primary, secondary, residential, motorway)
geom Центральная линия улицы

Таблица nyc_subway_stations#

Станции метро соединяют поверхностный мир с подземной сетью метро. Расположение станций определяет доступность для разных категорий населения.

Количество записей: 491

https://ratcatcher.ru/media/bd/pr/pr4/nyc_subway_stations.png

Атрибут Описание
name Название станции
borough Название района Нью-Йорка
routes Линии метро, проходящие через станцию
transfers Линии, на которые можно пересесть на этой станции
express Является ли станция остановкой для экспресс-поездов («express» = да, «» = нет)
geom Точечное местоположение станции

Таблица nyc_census_sociodata#

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

Атрибут Описание
tractid Уникальный 11-значный код переписного участка
transit_total Общее количество работников в участке
transit_private Количество работников, использующих частный транспорт
transit_public Количество работников, использующих общественный транспорт
transit_walk Количество работников, ходящих пешком
transit_other Количество работников, использующих другие виды транспорта
transit_none Количество работников, работающих из дома
transit_time_mins Общее количество минут, проведенных в пути
family_count Количество семей в участке
family_income_median Медианный доход семьи
family_income_mean Средний доход семьи
edu_total Общее количество людей с образовательной историей
edu_no_highschool_dipl Количество людей без диплома средней школы
edu_highschool_dipl Количество людей с дипломом средней школы
edu_college_dipl Количество людей с дипломом колледжа
edu_graduate_dipl Количество людей с дипломом магистра

Геометрические типы данных#

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

Основные геометрические типы данных в PostGIS:

POINT (Точка)

Точка — это геометрический объект, представляющий одно местоположение в пространстве с заданными координатами. Он используется для хранения местоположений объектов, таких как GPS-координаты зданий, деревьев, остановок транспорта и т.д.

LINESTRING (Линия)

Линия — это набор связанных точек, образующих линейный объект. Этот тип данных полезен для представления дорог, рек, железнодорожных путей и других линейных объектов, которые имеют протяженность.

POLYGON (Полигон)

Полигон — это замкнутая линия, которая описывает площадь на карте. Он используется для представления объектов с четко выраженными границами, таких как земельные участки, озера, территории зданий и т.д.

MULTIPOINT (Множественная точка)

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

MULTILINESTRING (Множественная линия)

Множественная линия представляет собой несколько линий, которые объединены в один объект. Это полезно для описания сложных линейных систем, таких как многополосные дороги или сети водоснабжения.

MULTIPOLYGON (Множественный полигон)

Этот тип данных объединяет несколько полигонов в один объект. Он используется для описания территорий, которые состоят из нескольких отдельных участков, например, административные районы или зоны с разными правовыми границами.

GEOMETRYCOLLECTION (Коллекция геометрий)

Коллекция геометрий — это набор различных типов геометрических объектов (точек, линий, полигонов), хранимых как один объект. Это гибкий тип данных, который позволяет хранить в одном поле разные геометрии, такие как комбинации точек, линий и полигонов.

https://ratcatcher.ru/media/bd/pr/pr4/postgis_geomery.png

Вставьте этот пример SQL-кода в окно редактора SQL pgAdmin (удалив любой текст, который может быть там по умолчанию), а затем выполните.

Tip

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES
('Point', 'POINT(0 0)'),
('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;

В приведенном выше примере создается таблица (геометрии), затем вставляются пять геометрических объектов: точка, линия, многоугольник, многоугольник с отверстием и коллекция. Наконец, вставленные строки выбираются и отображаются на панели вывода.

Метаданные к геометрии#

В соответствии со спецификацией Simple Features for SQL (SFSQL), PostGIS предоставляет две важные таблицы, которые используются для отслеживания и составления отчетов о типах геометрических данных, доступных в базе данных.

  1. spatial_ref_sys

Эта таблица содержит информацию обо всех системах пространственной привязки (SRID), известных базе данных. В PostGIS она используется для хранения и идентификации пространственных референционных систем, применяемых к геометрическим данным. Каждая запись в этой таблице описывает систему координат, включая ее SRID, текстовое описание и параметры, которые определяют преобразования координат из одной системы в другую. Более подробное описание работы с этой таблицей будет рассмотрено позже.

  1. geometry_columns

Хотя это фактически представление, а не таблица, geometry_columns играет ключевую роль в отслеживании геометрических объектов в базе данных. В ней содержится информация о всех "объектах" — таблицах или представлениях, которые содержат геометрические атрибуты. Для каждого объекта представление geometry_columns предоставляет следующие сведения:

  • Имя таблицы, содержащей геометрические данные.
  • Название столбца, в котором хранятся геометрические данные.
  • Тип геометрии (точка, линия, полигон и т.д.).
  • SRID, указывающий на систему пространственной привязки, использованную для геометрии.

https://ratcatcher.ru/media/bd/pr/pr4/table01.png

Давайте взглянем на таблицу geometry_columns в нашей базе данных. Вставьте эту команду в инструмент запроса, как и раньше:

Tip

    SELECT * FROM geometry_columns;

https://ratcatcher.ru/media/bd/pr/pr4/start08.png

Tip

'f_table_catalog', 'f_table_schema' и 'f_table_name' предоставляют полное имя таблицы объектов, содержащей заданную геометрию. Поскольку PostgreSQL не использует каталоги, 'f_table_catalog', как правило, будет пустым.

'f_geometry_column' – это имя столбца, который содержит geometry - для таблиц объектов с несколькими столбцами geometry для каждого будет одна запись.

coord_dimension и srid определяют размер геометрии (2-, 3- или 4-мерный) и идентификатор пространственной системы отсчета, который ссылается на таблицу 'spatial_ref_sys' соответственно.

Запрашивая эту таблицу, ГИС-клиенты и библиотеки могут определить, чего ожидать при получении данных, и могут выполнять любое необходимое проектирование, обработку или рендеринг без необходимости проверки каждой геометрии.

Tip

    ALTER TABLE nyc_neighborhoods
    ALTER COLUMN wkb_geometry
    TYPE Geometry(MultiPolygon, 26918)
    USING ST_SetSRID(wkb_geometry, 26918);

Спецификация Simple Features for SQL (SFSQL)#

Спецификация Simple Features for SQL (SFSQL), которая изначально была стандартом для разработки PostGIS, определяет, как объекты реального мира могут быть представлены в пространственной базе данных. Чтобы описать объект из реального мира, его форма и размеры преобразуются в цифровую форму с фиксированным разрешением, что позволяет создать достаточно точное представление объекта. Первоначально SFSQL работал только с 2-мерными объектами, но PostGIS расширил возможности до 3- и 4-мерных представлений. Более того, стандарт SQL-Multimedia Part 3 (SQL/MM) официально определил способы представления таких данных.

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

Основные функции для работы с метаданными геометрии:

  • ST_GeometryType(geometry) возвращает тип геометрии.
  • ST_NDims(geometry) возвращает количество измерений геометрии.
  • ST_SRID(geometry) возвращает идентификатор системы пространственной привязки геометрии (SRID).

Tip

SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom)
FROM geometries;
name st_geometrytype st_ndims st_srid
Point ST_Point 2 0
Polygon ST_Polygon 2 0
PolygonWithHole ST_Polygon 2 0
Collection ST_GeometryCollection 2 0
Linestring ST_LineString 2 0

Точка#

Точка — это геометрический объект, представляющий одно местоположение на поверхности Земли. Она описывается одной координатой, которая может иметь 2, 3 или 4 измерения. Точки используются для представления объектов, когда точные размеры и форма не важны на целевом масштабе. Например, на карте мира города можно обозначать точками, тогда как на более детализированной карте, такой как карта штата, города могут быть показаны полигонами.

Пример запроса для получения координат точки в текстовом формате:

Tip

SELECT ST_AsText(geom)
FROM geometries
WHERE name = 'Point';

Результат: POINT(0 0)

Некоторые функции для работы с точками:
- ST_X(geometry) возвращает координату X (долгота).
- ST_Y(geometry) возвращает координату Y (широта).

Пример запроса для чтения координат:

Tip

SELECT ST_X(geom), ST_Y(geom)
FROM geometries
WHERE name = 'Point';

Пример использования данных с координатами точек: таблица с нью-йоркскими станциями метро nyc_subway_stations, где каждая станция представлена точкой.

Tip

SELECT name, ST_AsText(wkb_geometry)
FROM nyc_subway_stations
LIMIT 1;

Линия#

Линия представляет путь между местоположениями и состоит из упорядоченной последовательности двух или более точек. Линии используются для представления таких объектов, как дороги или реки. Линия считается замкнутой, если она начинается и заканчивается в одной и той же точке. Линия является простой, если она не пересекает саму себя, за исключением случаев, когда замыкается в начальной и конечной точках.

Пример запроса для отображения линии:

Tip

SELECT ST_AsText(geom)
FROM geometries
WHERE name = 'Linestring';

Результат: LINESTRING(0 0, 1 1, 2 1, 2 2)

Некоторые функции для работы с линиями:

  • ST_Length(geometry) возвращает длину линии.
  • ST_StartPoint(geometry) возвращает первую точку линии.
  • ST_EndPoint(geometry) возвращает последнюю точку линии.
  • ST_NPoints(geometry) возвращает количество точек в линии.

Пример запроса для расчета длины линии::

Tip

SELECT ST_Length(geom)
FROM geometries
WHERE name = 'Linestring';

Полигон#

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

Полигоны используются для представления объектов, для которых важны их размер и форма. Например, границы города, парки, контуры зданий или водоемов обычно представляются полигонами, если масштаб карты достаточно высок для отображения их площади. В некоторых случаях дороги и реки также могут быть представлены полигонами.

Пример запроса, который вернет геометрию полигона в текстовом формате:

Tip

SELECT ST_AsText(geom)
FROM geometries
WHERE name LIKE 'Polygon%';

Tip

Результат:
POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))
POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))

Первый полигон содержит только одно кольцо, а второй — внутреннюю "дыру". Большинство графических систем поддерживают концепцию "полигона", но геоинформационные системы (ГИС) уникальны тем, что позволяют полигонам содержать явные внутренние дыры.

Некоторые функции для работы с линиями:

  • ST_Length(geometry) возвращает длину линии.
  • ST_StartPoint(geometry) возвращает первую точку линии.
  • ST_EndPoint(geometry) возвращает последнюю точку линии.
  • ST_NPoints(geometry) возвращает количество точек в линии.

Пример запроса для вычисления площади полигонов:

Tip

SELECT name, ST_Area(geom)
FROM geometries
WHERE name LIKE 'Polygon%';
name st_area
Polygon 1
PolygonWithHole 99

Обратите внимание, что полигон с дырой имеет площадь, равную площади внешней оболочки (квадрат 10x10) минус площадь дыры (квадрат 1x1).

Ввод и вывод геометрических данных#

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

Well-known text (WKT)

  • ST_GeomFromText(text, srid) returns geometry

  • ST_AsText(geometry) returns text

  • ST_AsEWKT(geometry) returns text

Well-known binary (WKB)

  • ST_GeomFromWKB(bytea) returns geometry

  • ST_AsBinary(geometry) returns bytea

  • ST_AsEWKB(geometry) returns bytea

Geographic Mark-up Language (GML)

  • ST_GeomFromGML(text) returns geometry

  • ST_AsGML(geometry) returns text

Keyhole Mark-up Language (KML)

  • ST_GeomFromKML(text) returns geometry

  • ST_AsKML(geometry) returns text

GeoJSON

  • ST_AsGeoJSON(geometry) returns text

Scalable Vector Graphics (SVG)

  • ST_AsSVG(geometry) returns text

Пример вывода геометрии в GEOJSON

Tip

SELECT 
ST_AsGeoJSON(ST_GeometryFromText('LINESTRING(0 0,1 0)'));

Вывод: "{""type"":""LineString"",""coordinates"":[[0,0],[1,0]]}"

Практика#

Эти функции будут полезны для выполнения упражнений:
- sum(expression) — агрегатная функция для возврата суммы по набору записей.
- count(expression) — агрегатная функция для возврата количества записей в наборе.
- ST_GeometryType(geometry) — возвращает тип геометрии.
- ST_SRID(geometry) — возвращает идентификатор системы пространственной привязки (SRID) для геометрии.
- ST_X(point) — возвращает координату X (долгота) для точки.
- ST_Y(point) — возвращает координату Y (широта) для точки.
- ST_Length(linestring) — возвращает длину линии (linestring).
- ST_StartPoint(geometry) — возвращает первую точку линии.
- ST_EndPoint(geometry) — возвращает последнюю точку линии.
- ST_NPoints(geometry) — возвращает количество точек в линии.
- ST_Area(geometry) — возвращает площадь полигона.

Таблицы, с которыми мы работаем:

  • nyc_census_blocks
    (Поля: blkid, popn_total, boroname, wkb_geometry)

  • nyc_streets(Поля: name, type, wkb_geometry)

  • nyc_subway_stations(Поля: name, wkb_geometry)

  • nyc_neighborhoods(Поля: name, boroname, wkb_geometry)

Какова площадь района Уэст-Виллидж?

Ответ: 1044614.5296486

Каков геометрический тип "Pelham St’? Длина?

Ответ: ST_MultiLineString, 50.323

Как записана станция метро "Брод-Стрит" в GeoJSON?

Ответ: {"type":"Point",
"crs":{"type":"name","properties":{"name":"EPSG:26918"}},
"coordinates":[583571.905921312,4506714.341192182]}

Какова общая протяженность улиц (в километрах) в Нью-Йорке?

Ответ: 10418.9047172

Какая станция метро самая западная?

Результат: Tottenville

Какова протяженность улиц в Нью-Йорке в разбивке по типам?

Результат:

type length
residential 8629870.33786606
motorway 403622.478126363
tertiary 360394.879051303
motorway_link 294261.419479668
secondary 276264.303897926
unclassified 166936.371604458
primary 135034.233017947
footway 71798.4878378096
service 28337.635038596
trunk 20353.5819826076
cycleway 8863.75144825929
pedestrian 4867.05032825026
construction 4803.08162103562
residential; motorway_link 3661.57506293745
trunk_link 3202.18981240201
primary_link 2492.57457083536
living_street 1894.63905457332
primary; residential; motorway_link; residential 1367.76576941335
undefined 380.53861910346
steps 282.745221342127
motorway_link; residential 215.07778911517