База данных Countries (PostGIS): пространственные таблицы и SQL-задачи

Countries — база PostGIS для изучения пространственного SQL: страны и столицы мира, а также слои Нью-Йорка с переписными кварталами, районами, улицами и станциями метро. На SQLtest.online с ней можно работать прямо в браузере: решать задачи с автоматической проверкой и писать свои запросы в песочнице, ничего не устанавливая.

• 7 пространственных таблиц
• 246 стран
• 491 станция метро
• 11 SQL-задачи

Что такое Countries

PostGIS — расширение PostgreSQL, которое добавляет геометрические типы и сотни пространственных функций: расстояния, площади, пересечения, преобразования координат. Эта база позволяет попробовать их на понятных данных.

Таблицы Нью-Йорка взяты из известного практикума PostGIS «Introduction to PostGIS», а таблицы мира содержат границы стран и столицы. Вместе они охватывают точки, линии и полигоны в двух системах координат.

Из чего состоит база

Таблицы делятся на две группы.

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

Сколько данных в таблицах:

Структура таблиц

Нажмите на таблицу, чтобы увидеть её столбцы, пример строки и ключи.

Примеры запросов

Эти запросы показывают, как связаны данные. Скопируйте любой и запустите в песочнице.

Столица внутри своей страны — координаты точки через ST_X / ST_Y и пространственная проверка ST_Contains:

SELECT c.name AS capital, co.name AS country,
       round(ST_Y(c.location)::numeric, 2) AS lat,
       round(ST_X(c.location)::numeric, 2) AS lon,
       ST_Contains(co.border, c.location) AS inside_border
FROM capitals c
JOIN countries co ON co.id = c.country_id
ORDER BY c.name
LIMIT 3;

Станции метро и их SRID — слои Нью-Йорка используют проекцию 26918:

SELECT s.name AS station, s.borough, s.routes, ST_SRID(s.geom) AS srid
FROM nyc_subway_stations s
ORDER BY s.gid
LIMIT 3;

SQL-задачи по темам

Задач по PostGIS на этой базе: 11 — расстояния, площади, длины, преобразование в текст и JSON и пространственные соединения. Решение проверяется автоматически на настоящем PostgreSQL с PostGIS. Число справа — количество задач в теме, цветные метки — диапазон сложности.

• Геопространственные данные (PostGIS) 11

С чего начать

Первые задачи по базе Countries:

1. Извлечь геометрию как текст
2. Извлечь геометрию как JSON
3. Расстояние между городами
4. Площадь страны
5. Станции метро Манхэттена
6. Вычислить площадь микрорайона
7. Площадь микрорайона
8. Средняя площадь района
9. Длина улиц Нью-Йорка
10. Станции "Little Italy"

Все задачи по Countries →