GeoFile: используем данные OpenStreetMap и создаем базу данных PostgreSQL

Для тех, кто не в курсе что такое что такое OpenStreetMap  – рассказываю. Это некоммерческий веб-картографический проект, который создается силами участников сообщества. Это что-то типа Вики карты, которую может редактировать кто угодно. OpenStreetMap (OSM) является одним из наиболее признанных и популярных открытых источников данных для открытых карт. В отличие от Google здесь не тратится около 1 миллиарда долларов для поддержки своих карт в актуальном состоянии. Здесь все просто –  если магазин отсутствует на карте, его может добавить как владелец магазина, так и его посетитель. Что касается показа (рендеринга) карты, то любой человек или компания, принимающие участие в создании карты, свободен рендерить её как ему удобно. В итоге, содержимое карт OpenStreetMap   нейтрально и прозрачно.  Большинство веб-и мобильных приложений используют его из-за своей надежности, так как он постоянно обновляется большим количеством участников.

В этой статье я покажу вам как сохранять данные OSM в базу данных PostgreSQL. Кроме того, мы покажем вам некоторые функции данных OSM и способы запроса городских объектов, в данном примере рестораны.

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

Ну а сначала нам нужно получить данные OSM……

Импорт данных OSM

Для наших примеров мы будем использовать данные OSM из Сиэтла, штат Вашингтон.  На сайте OpenStreetMap вы можете отыскать Сиэтл в строке поиска в верхнем левом углу экрана. Как только Сиэтл появится на экране, экспортируйте карту.

Ну а поскольку OSM предоставляем нам карту не только города, нам нужно увеличить масштаб. Нужно увеличивать до тех пор, пока на экране не будет именно тот “кусок” карты, который вы непосредственно хотите экспортировать.

Как только убедились, что эта картинка – это то, что вам нужно, то смело нажимайте кнопку Export в верхней части окна.  Слева вам предложат несколько вариантов для экспорта.

Если вы нажмете на синюю кнопку Export,  то получите сообщение об ошибке в вашем браузере. Эта ошибка возникает только тогда,  когда мы пытаемся загрузить область, содержащую слишком много узлов (> 50000).Запустим команду CURL в терминале, чтобы увидеть описание ошибки:

curl -I “http://www.openstreetmap.org/api/0.6/map?bbox=-122.3819%2C47.5763%2C-122.2677%2C47.6267”

Error: You requested too many nodes (limit is 50000). Either request a smaller area, or use planet.osm

Если бы мы увеличили масштаб изображения одной определенной области Сиэтла (где поменьше узлов), то мы могли бы успешно экспортировать карту части города.  Однако  мы заинтересованы в экспорте карты всего города,  поэтому перейдите вперед и нажмите на ссылку Overpass API, которая дает нам больше узлов, и автоматически начнет загружать файл с названием map, который включает весь город.

Нам нужно будет добавить .osm к файлу карты, чтобы дальше импортировать его в PostgreSQL. Чтобы импортировать данные OSM, мы будем использовать инструмент командной строки osm2pgsql.

Osm2pgsql – это   инструмент командной строки, который преобразует данные OSM в формат, который можно сохранить в PostgreSQL. Если вы используете MacOS, вы можете скачать его с помощью установки Homebrew   brew install osm2pgsql. Для других операционных систем инструкции по установке можно найти на  Github.

После того, как osm2pgsql установлен, нам нужно создать базу данных. Когда мы войдем в систему, то создадим базу данных под названием osm:

CREATE DATABASE osm;

После этого подключайтесь к этой базе данных с помощью \c osm;.  Ну а потом уже устанавливайте расширения PostGIS и HStore:

CREATE EXTENSION postgis;

CREATE EXTENSION hstore;

 

Нам понадобится PostGIS, чтобы успешно импортировать данные OSM. Если вы не установите расширение PostGIS и попытаетесь импортировать данные, то вы сразу получите сообщение об ошибке.  Мы также установим расширение hstore, которое будет использоваться для хранения и извлечения нестандартизированных данных, которые не помещаются в столбец.  Эти данные вставляются в виде ключ – значение в столбце с именем tags.

После того как база данных и все расширения были настроены, мы можем использовать osm2pgsql прямо из терминала:

osm2pgsql -U admin -W -d osm -H aws-us-west-1-portal.3.dblayer.com -P 17124 –hstore –hstore-add-index map.osm

Сначала идет сама команда osm2pgsql  к которой добавляется -U с вашим именем админа базы данных,   а ключ   -W указывает, что вам нужна подсказка пароля. Затем добавьте -d и имя вашей базы данных (здесь osm). Параметр –H является именем хоста развертывания, а –P – номером порта. Мы добавили -hstore для создания столбцов для каждой таблицы, содержащей дополнительные нестандартизированные данные, а опция –hstore-add-index задает индексы для этих столбцов. Ну а в конце прописываем имя нашего файла, который мы скачали до этого.

После запуска команды введите пароль, после чего вы увидите, что наши данные обрабатываются в терминале и импортируются в нашу базу данных PostgreSQL – osm , создавая при этом  таблицы и индексы для столбцов первичного ключа и тегов. После того как данные обработаны и импортированы, войдите в систему PostgreSQL и подключитесь к базе данных.

Ну а теперь давайте посмотрим на таблицы, которые были созданы. Увидеть список таблиц можно используя команду \d:

List of relations
Schema | Name | Type | Owner
——–+——————–+——-+——-
public | geography_columns | view | admin
public | geometry_columns | view | admin
public | planet_osm_line | table | admin
public | planet_osm_point | table | admin
public | planet_osm_polygon | table | admin
public | planet_osm_roads | table | admin
public | raster_columns | view | admin
public | raster_overviews | view | admin
public | spatial_ref_sys | table | admin

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

Planet_osm_polygon содержит все данные многоугольника и мультиполигона.

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

Planet_osm_line содержит все пути.

Planet_osm_roads – это подмножество planet_osm_line для рендеринга на низких уровнях масштабирования.

Итак, теперь когда все наши данные были импортированы и созданы индексы, давайте посмотрим, как это выглядит и сделаем несколько запросов …

Запрос данных OSM

Загрузка данных из PostgreSQL  в программу картографирования GIS предоставит нам визуальное представление данных нашего OSM. А выглядит это примерно так:

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

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

Чтобы получить все рестораны из таблицы, SQL запрос  должен выглядеть следующим образом:

SELECT name, count(name) FROM planet_osm_point WHERE amenity = ‘restaurant’ GROUP BY name ORDER BY count DESC;

Результатом будет список из 644 ресторанов Сиэтла. Если мы немного улучшим наш запрос, то получим только те рестораны, которые имеют 3 или более филиалов:

SELECT name, count(name) as number FROM planet_osm_point WHERE amenity = ‘restaurant’ GROUP BY name HAVING count(name) >= 3 ORDER BY name ASC;

В результате этого запроса список будет намного меньше. Всего пять ресторанов с 3 филиалами.


name | number
——————-+——–
Blue Moon Burgers | 3
Cactus | 3
MOD Pizza | 3
Pho Than Brothers | 3
Via Tribunali | 3

Но просто названия ресторанов нам не очень помогут. Мы же ведь не знаем чего они там готовят. Вот здесь то нам и пригодятся наши данные в hstore. Такая нестандартная информация как “кухня”  сохраняется в колонке тегов, которую мы настроили при импорте наших данных в PostgreSQL. Чтобы узнать что готовят в каждом ресторане нужно сделать примерно такой запрос:

SELECT name, tags->‘cuisine’ as cuisine FROM planet_osm_point WHERE amenity = ‘restaurant’ AND name IN (‘Blue Moon Burgers’, ‘Cactus’, ‘MOD Pizza’, ‘Pho Than Brothers’, ‘Via Tribunali’) GROUP BY name, tags ORDER BY name ASC;

В результате мы получим ключ “кухня” для каждого ресторана из нашего списка:

name | cuisine
——————-+————
Blue Moon Burgers | burger
Blue Moon Burgers | burger
Blue Moon Burgers |
Cactus | mexican
Cactus | mexican
Cactus | mexican
MOD Pizza | pizza
MOD Pizza | pizza
MOD Pizza | pizza
Pho Than Brothers | vietnamese
Pho Than Brothers | vietnamese
Pho Than Brothers | vietnamese
Via Tribunali | pizza
Via Tribunali | pizza
Via Tribunali |

Ну давайте продолжим изучать рестораны города. Давайте узнаем о 10 самых популярных  “кухнях” города. Для этого мы использовали следующий SQL-запрос, который сгруппирует количество ресторанов, которые будут обслуживать один и тот же тип “кухни”:

SELECT DISTINCT tags->‘cuisine’ as cuisine, count(name) FROM planet_osm_point WHERE tags ? ‘cuisine’ GROUP BY cuisine ORDER BY count DESC LIMIT 10;

Это даст такие результаты:

cuisine | count
————-+——-
coffee_shop | 171
sandwich | 73
mexican | 65
pizza | 58
american | 42
italian | 37
vietnamese | 37
thai | 34
chinese | 33
japanese | 32

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

Чтобы сделать эти данные более интересными, мы могли бы посмотреть, какие кафешки доминируют в Сиэтле. Чтобы узнать какие  10 кофеен лучшие в городе, мы  запустим следующий SQL-запрос:

SELECT name, count(name)FROM planet_osm_point WHERE tags->‘cuisine’ IN (‘coffee_shop‘) GROUP BY name ORDER BY count DESC LIMIT 10;

Мы просто сгруппируем имена всех кафе, у которых ключ “кухня” – это «coffee_shop». Результат будет выглядеть так:

name | count
—————————-+——-
Starbucks | 47
Tully’s Coffee | 11
Cherry Street Coffee House | 7
Uptown Espresso | 5
Caffe Ladro | 5
Storyville Coffee | 3
Voxx Coffee | 2
Stumptown Coffee | 2
Specialty’s | 2
Caffe Vita | 2

Ну конечно же (ожидаемо…) Starbucks побеждает ))  Но удивляет, что в городе аж целых 95 различных кофеен.