PostGis вопросы по запросам к БД

defond · 2014-02-24T14:17:00.000Z

Добрый день.

Установил PostGis и залил данные ОСМ (использовал инструкцию с Gis-Lab). Пытался освоить документацию по PostGis, но знаний английского не хватает.

Поэтому прошу помощи - нужно показать один-два запроса к БД, которые позволят вытянуть данные для дальнейшей обработки.

Допустим - нужно выдернуть из БД координаты всех аэродромов.
Посчитать расстояние между двумя любыми аэродромами.

Буду очень благодарен, если кто-то согласиться помочь.

Пытался освоить вот эту инструкцию.

Пользовал пример 2 (Массачусетс), но видимо недопонимаю логику использования.

Свой пример:


SELECT ST_Distance(
			ST_Transform(ST_GeomFromText('POINT(45.0488000 38.9725000)',4326),26986),
			ST_Transform(ST_GeomFromText('LINESTRING(45.0488000 38.9725000, 43.4311100 39.9236100)', 4326),26986)
		);

где,


Координаты Краснодара (45.0488000 38.9725000)

Координаты Адлера (43.4311100 39.9236100)

Эти координаты получал вручную, из сторонних источников, а не при помощи запроса к БД.

Модератору. ГГугл поиск по форуму мне не помог. Если я что-то упустил, буду благодарен, если перенаправите на нужный топик, а эту тему закроете.

С уважением.

defond · 2014-02-24T16:03:33.000Z

Видимо ни кто PostGis не пользует?

Буду рад любой помощи.

П.С.: Запрос по аэродромам пусть Вас не смущает. Я бывший летчик, мне проще решать близкие мне задачи.

dudka · 2014-02-24T16:19:44.000Z

Документация по PostGIS на русском: http://gis-lab.info/docs/postgis/manual/

По какой инструкции с gis-lab импортировались данные?

Alexandr_Zeinalov · 2014-02-24T16:24:17.000Z

26986 ориентирована на окрестности штата Массачусетс. Нужна система координат, подходящая под нужный регион. Или как самое простое решение использовать ST_Distance_Spheroid.

Извлекать аэропорты можно примерно так:

SELECT ST_X(way) AS lon,ST_Y(way) AS lat,aeroway,name FROM prefix_point WHERE aeroway IN (‘airport’,‘aerodrome’)
UNION
SELECT ST_X(ST_Centroid(way)) AS lon,ST_Y(ST_Centroid(way)) AS lat,aeroway,name FROM prefix_polygon WHERE aeroway IN (‘airport’,‘aerodrome’)

defond · 2014-02-24T16:25:18.000Z

С Gis-Lab. Сейчас попробую ссылку найти. Нашел в районе страницы с инструкцией по установке.

Фактически просто скачал файл RU-KDA-140222 в архиве. Распаковал и загрузил в Post-Gis.

За инструкцию - спасибо огромное. Буду читать.

defond · 2014-02-24T16:29:01.000Z

Использовал инструкцию по установке БД вот эту:

http://gis-lab.info/qa/postgis-install.html#02

Файлы shp просто загрузил при помощи загрузчика.

Alexandr_Zeinalov · 2014-02-24T16:31:01.000Z

По поводу датума под RU-KDA http://gis-lab.info/forum/viewtopic.php?f=1&t=9090

defond · 2014-02-24T16:39:33.000Z

Alexandr Zeinalov:

26986 ориентирована на окрестности штата Массачусетс. Нужна система координат, подходящая под нужный регион. Или как самое простое решение использовать ST_Distance_Spheroid.

Извлекать аэропорты можно примерно так:

Понял. Спасибо огромное.

Буду разбираться.

defond · 2014-02-24T16:42:14.000Z

Alexandr Zeinalov:

26986 ориентирована на окрестности штата Массачусетс. Нужна система координат, подходящая под нужный регион. Или как самое простое решение использовать ST_Distance_Spheroid.

Извлекать аэропорты можно примерно так:

SELECT ST_X(way) AS lon,ST_Y(way) AS lat,aeroway,name FROM prefix_point WHERE aeroway IN (‘airport’,‘aerodrome’)
UNION
SELECT ST_X(ST_Centroid(way)) AS lon,ST_Y(ST_Centroid(way)) AS lat,aeroway,name FROM prefix_polygon WHERE aeroway IN (‘airport’,‘aerodrome’)


ОШИБКА:  отношение "prefix_point" не существует
LINE 1: ..._X(way) AS lon,ST_Y(way) AS lat,aeroway,name FROM prefix_poi...
                                                             ^
********** Ошибка **********

ОШИБКА: отношение "prefix_point" не существует
SQL-состояние: 42P01
Символ: 60

dkiselev · 2014-02-24T18:12:16.000Z

Что делать с тем набором данных который вы загрузили - не знаю. Если загрузить вот так (не забудьте только загрузить с геометрией для линий)
http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage_0.41#–write-pgsql_.28–wp.29
то

select ST_X(ST_Centroid(w.linestring))  lon, select ST_Y(ST_Centroid(w.linestring))  lat 
from ways w 
where w.tags -> 'aeroway' in ('airport','aerodrome')

Еще можно overpass api использовать чтобы точки получить.

Alexandr_Zeinalov · 2014-02-24T18:16:09.000Z

Образец запроса был для схемы osm2pgsql. Вместо prefix нужно было написать используемый префикс (ключ -p, по умолчанию planet_osm).

=> SELECT 'point' AS type,ST_X(way) AS lon,ST_Y(way) AS lat,aeroway,name FROM mosobl_point
WHERE aeroway IN ('airport','aerodrome')    
UNION
SELECT 'polygon' AS type,ST_X(ST_Centroid(way)) AS lon,ST_Y(ST_Centroid(way)) AS lat,aeroway,name FROM mosobl_polygon
WHERE aeroway IN ('airport','aerodrome') LIMIT 5;

  type   |       lon        |       lat        |  aeroway  |             name             
---------+------------------+------------------+-----------+------------------------------
 point   |       4096530.14 |       7578600.86 | aerodrome | Планерная Площадка "Шевлино"
 polygon | 4249710.01476296 | 7526438.96354953 | aerodrome | 
 polygon | 4148116.61488309 |  7336950.8655616 | aerodrome | аэродром Дракино
 point   |        4259510.6 |       7376169.63 | aerodrome | Аэродром Заячья Поляна
 point   |       4030636.09 |       7459563.77 | aerodrome | Аэродром Аэроград-Можайский
(5 строк)

(Координаты в srid 900913, чтобы были нормальные, надо вместо way писать ST_Transform(way,4326)).

Как именно устроен результат загрузки шейпов я не знаю. В psql есть команда \d которой можно анализировать струтуру таблиц.

Вот что получилось у меня из orbview3_catalog.shp:

=> \d ov3cat
                                  Таблица "public.ov3cat"
  Колонка   |          Тип           |                     Модификаторы                     
------------+------------------------+------------------------------------------------------
 gid        | integer                | NOT NULL DEFAULT nextval('ov3cat_gid_seq'::regclass)
 entity_id  | double precision       | 
 usgs_id    | character varying(50)  | 
 acq_date   | character varying(12)  | 
 cloud      | double precision       | 
 nw_corner  | character varying(50)  | 
 ne_corner  | character varying(50)  | 
 sw_corner  | character varying(50)  | 
 se_corner  | character varying(50)  | 
 center     | character varying(50)  | 
 platform   | character varying(12)  | 
 sensor     | character varying(20)  | 
 sun_elev   | double precision       | 
 sun_azimut | double precision       | 
 vendor     | character varying(20)  | 
 vendor_id  | character varying(50)  | 
 map_proj   | character varying(20)  | 
 proc_level | character varying(100) | 
 datum      | character varying(20)  | 
 format     | character varying(20)  | 
 pixel_x    | double precision       | 
 pixel_y    | double precision       | 
 date_enter | character varying(12)  | 
 view_name  | character varying(20)  | 
 browse     | character varying(150) | 
 the_geom   | geometry               | 
Индексы:
    "ov3cat_pkey" PRIMARY KEY, btree (gid)
Ограничения-проверки:
    "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)

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

defond · 2014-02-25T04:16:56.000Z

Alexandr Zeinalov:

…

Спасибо огромное!

Буду разбираться и осваиваться. Другим путем, но тоже пришел к выводу, что неверно выстроил структуру БД, тоже буду разбираться.

defond · 2014-02-25T11:55:02.000Z

Ситуация с ОСМ для виндузятников просто катастрофическая.

Инструкций нет. Нормального мануала по установке osm2pgsql под винду нет. Самого osm2pgsql нет, ну или я не нашел. Под Линуксоподобные системы/ядра - валом. Вот нет в жизни справедливости!

В общем я osm2pgsql качнул. Есть exe-шник, написанный на питоне, который запускает консоль, которая моментально закрывается.

Может кто помочь поставить под Вин7(32) osm2pgsql?

defond · 2014-02-25T11:56:15.000Z

Прошу не предлагать ставить линук. По ряду причин, поставить его я не могу. Хочу, но не могу.

fndoder · 2014-02-25T12:31:13.000Z

Ставить виртуальную машину с Линуксом тогда.
Вин7(32) тут неподходящий инструмент.

Vitalts · 2014-02-25T12:35:23.000Z

А на кой он вам? Osmosis прекрастно справляется

Alexandr_Zeinalov · 2014-02-25T13:51:25.000Z

Vitalts:

А на кой он вам? Osmosis прекрастно справляется

А мультиполигоны?

defond · 2014-02-25T14:37:12.000Z

fndoder:

Ставить виртуальную машину с Линуксом тогда.
Вин7(32) тут неподходящий инструмент.

Чего тогда ставить? Убунту или Федору?

В чем легче работать с PostGis и ОСМ?

gryphon · 2014-02-25T14:56:31.000Z

defond:

fndoder:

Ставить виртуальную машину с Линуксом тогда.
Вин7(32) тут неподходящий инструмент.

Чего тогда ставить? Убунту или Федору?

В чем легче работать с PostGis и ОСМ?

Полагаю, без разницы. К плюсам убунты можно отнести наличие ppa репозитариев с актуальной версией postgis

Vitalts · 2014-02-25T15:24:00.000Z

А мультиполигоны?

А что с ними? osm2pgsql сразу геометрию мультиполигона в базу пишет? Я просто с ним не сталкивался…