Как из russia-latest.osm.pbf достать список всех городов России?

Communities Россия (Russia)
1

Я скачал с этого Geofabrik Download Server сайта файл russia-latest.osm.pbf.
Затем установил postgreSQL и плагин postgis. Скачал osm2pgsql и выполнил команду

osm2pgsql -d osm -U postgres -W -O flex -r pbf -S flex-config/generic.lua russia-latest.osm.pbf

Оно там импортировалось. Далее я захожу pgAdmin и открываю Query tool. Помогите пожалуйста с SQL запросом, который достанет список всех городов, где population > 4000;

2

Я не думаю что так просто получится извлечь требуемую информацию. Но как направление поиска могу предложить взять один город. Например, Нововоронеж - Node: ‪Нововоронеж‬ (‪2616628792‬) | OpenStreetMap

В нём есть поля official_status == ru:город и population == 31560.

Чтобы “поиграться” с административно-территориальным делением России можно открыть Россия - валидатор НП и АТД (ОСМ) и посмотреть устройство региона который интересн или понятен.

3

Ну по сути это то, что мне нужно. Проверил ещё пару городов, значения есть.

Я планировал достать все города и затем все улицы в них… А в чём сложность? То, что я находил, там говорят, что запрос должен выглядеть как-то так: SELECT * FROM planet_osm_points WHERE place = 'city' AND population::int > 4000, однако у меня нет поля planet_osm_points…

4

Это не поле а таблица. Раскладка данных в БД зависит от выбранной схемы, вы выбрали схему generic.lua, там для точек используется таблица под названием points а теги записываются в поле tags в формате jsonb.

Далее, города в OSM могут быть обозначены по разному, это не только place=city, но и так же и place=town (или даже place=village, для совсем мелких городов) - https://wiki.openstreetmap.org/wiki/RU:Key:place

И вы определитесь, вам нужны города именно имеющие официальный статус “город” или просто любые населённые пункты с достаточным кол-вом жителей. Если первое - это значит надо использовать фильтр по тегу official_status, как предложил fserges.

5

да, оговорился. Я перезакачал pbf без flex. Теперь planet_osm_points появился. По поводу city и town вы абсолютно правы, мне нужно всё, что имеет более 4тыс. Данный запрос

SELECT * FROM planet_osm_points WHERE place = 'city' AND population::int > 4000

теперь выполняется, однако такого города как Анапа или Туапсе там нет. Они в базе хранятся как town. Однако если я меняю ‘city’ на ‘town’, то у меня возникает ошибка:

ERROR: ОШИБКА:  неверный синтаксис для типа integer: "38 364"
CONTEXT:  параллельный исполнитель
SQL state: 22P02

По всей видимости там криво записаны строки. Есть к примеру строка, где population = “100-200”. Как можно такие записи либо пропустить, либо в другую таблицу вывести, как исключения, чтобы просто глазами отсмотреть и отсеять?

как по нему фильтровать? Я так понимаю, что это не поле

6

Это уже обычными средствами SQL. В PostgreSQL это обычно делается либо regexp-ами, либо сделать специальную функцию проверки и использовать её:

7

Это тег в исходных данных. Чтобы оно превратилось в поле БД - надо подправить файлы стилей и скрипты импорта. Либо импортировать с поддержкой hstore, тогда все теги будут записаны в поле tags, соответственно можно будет использовать его в запросе, типа такого:

SELECT * FROM planet_osm_points WHERE tags -> 'official_status' = 'ru:город'
8

да, у меня как раз первый импорт таким был.

9

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

SELECT * FROM planet_osm_point WHERE place = 'city' AND population ~ '^[\d]+$' AND population::int > 4000;

Также нужно будет сделать запрос, где place = ‘town’, потому что в city не все города есть

10

Так просто исправить place = 'city' на (place = 'city' OR place = 'town') и будут все вместе.

1 Like