在Postgres中使用OSM在带SQL的Postgis中使用OSM检索给定的(经纬度，长经)最近的道路

本文介绍了在Postgres中使用OSM在带SQL的Postgis中使用OSM检索给定的(经纬度，长经)最近的道路的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

给出一个集合(纬度，经度)，我试图使用"max_speed"找到最大速度，并使用"Highway"找到街道类型.

Given a set (lat, long) I am trying to find the maximum speed using "max_speed" and street type using "highway".

我已按如下方式加载了我的数据库(Postgres和Postgis):

I have loaded my database (Postgres and Postgis) as follows:

$ osm2pgsql -c -d gis --slim -C 50000 /var/lib/postgresql/data/germany-latest.osm.pbf

我能找到的最接近的相关问题是如何使用osm-postgis查询特定经度/纬度附近的所有商店?.我接受了查询，并插入了在谷歌地图上找到的慕尼黑市中心的(经纬度)(因为该帖子也与慕尼黑市中心有关，并且我有德国的地图).结果变成空的.

The closest related question I could find was How to query all shops around a certain longitude/latitude using osm-postgis?. I have taken the query, and plugged in a (lat, long) that I found in google maps for the city center of Munich (as the post was also related to city center Munich and I have the map for Germany). The result turns up empty.

gis=# SELECT name, shop FROM planet_osm_point WHERE ST_DWithin(way ,ST_SetSrid(ST_Point(48.137969, 11.573829), 900913), 100); name | shop ------+------ (0 rows)

同样，当直接查看Planet_osm_nodes节点时，我最终没有任何结果:

Also when looking into the planet_osm_nodes, which contains (lat, long) pairs directly, I end up with no results:

gis=# SELECT * FROM planet_osm_nodes WHERE ((lat BETWEEN 470000000 AND 490000000) AND (lon BETWEEN 100000000 AND 120000000)) LIMIT 10; id | lat | lon | tags ----+-----+-----+------ (0 rows)

我已验证数据在数据库中:

I verified the data is in my database:

gis=# SELECT COUNT(*) FROM planet_osm_point; count --------- 9924531 (1 row)

和

gis=# SELECT COUNT(*) FROM planet_osm_nodes; count ----------- 288597897 (1 row)

所以理想情况下，我的问题是 问:如何在给定的一组(纬度，经度)下找到最大速度"和高速公路" 或者，我的问题是: 问:如何从另一个堆栈溢出帖子中获取查询?

So ideally, my question would be Q: How can I find the "max speed" and "highway" given a set (lat, lon) alternatively, my questions is: Q: How do I get the query from the other stack overflow post to work?

我的最佳猜测是，我需要以某种方式转换(纬度，经度)，或者由于任何原因我只是拥有错误的数据.

My best guess is that I need to transform my (lat, lon) in some way, or that I simply have the wrong data for whatever reason.

根据要求添加示例数据:

added sample data as requested:

gis=# SELECT * FROM planet_osm_point LIMIT 1; osm_id | access | addr:housename | addr:housenumber | addr:interpolation | admin_level | aerialway | aeroway | amenity | area | barrier | bicycle | brand | bridge | boundary | building | capital | construction | covered | culvert | cutting | denomination | disused | ele | embankment | foot | generator:source | harbour | highway | historic | horse | intermittent | junction | landuse | layer | leisure | lock | man_made | military | motorcar | name | natural | off ice | oneway | operator | place | poi | population | power | power_source | public_transport | railway | ref | religion | route | service | shop | sport | surface | toll | tourism | tower:type | tunnel | water | waterway | wetland | wi dth | wood | z_order | way -----------+--------+----------------+------------------+--------------------+-------------+-----------+---------+---------+------+---------+---------+-------+--------+----------+----------+---------+--------------+---------+---------+ ---------+--------------+---------+-----+------------+------+------------------+---------+----------+----------+-------+--------------+----------+---------+-------+---------+------+----------+----------+----------+------+---------+---- ----+--------+----------+-------+-----+------------+-------+--------------+------------------+---------+-----+----------+-------+---------+------+-------+---------+------+---------+------------+--------+-------+----------+---------+--- ----+------+---------+---------------------------------------------------- 304070863 | | | | | | | | | | | | | | | | | | | | | | | | | | | | crossing | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 010100002031BF0D0048E17A94F19F2941CDCCCCDCC60D5741 (1 row)

和

gis=# SELECT * FROM planet_osm_nodes LIMIT 1; id | lat | lon | tags --------+-----------+----------+------ 234100 | 666501948 | 80442755 | (1 row)

提到了"SRID"，因此我从另一个表中添加了示例数据:

Edit 2: There was a mention regarding "SRID", so I added example data from another table:

gis=# SELECT * FROM spatial_ref_sys LIMIT 1; srid | auth_name | auth_srid | srtext | proj4text ------+-----------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------ 3819 | EPSG | 3819 | GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PR IMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]] | +proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs (1 row)

推荐答案

PostGIS中的几何具有(纬度长)不同的顺序，首先是经度，然后是纬度. 另外，如果要将点从一个SRID转换为另一点，请使用st_transfrom()而不是ST_SetSrid. ST_Transform会将数据从一个坐标系可靠地转换到另一个坐标系.

Geometry in PostGIS has a different ordering of (lat long) first is going longitude then latitude. Also if you want to transform a point from one SRID to another use st_transfrom(), not ST_SetSrid. ST_Transform relly transform your data from one coordinates system to another.

select st_astext(st_transform(ST_SetSrid(ST_Point(11.573829,48.137969), 4326),900913))

ST_SetSrid-只需更改对象的SRID.

ST_SetSrid - just change SRID for the object.

select st_astext((ST_SetSrid(ST_Point(11.573829,48.137969),900913)

因此，您必须以这种方式更改SQL

So, you have to change your SQL that way

SELECT name, shop FROM planet_osm_point WHERE ST_DWithin(way,st_transform(ST_SetSrid(ST_Point(11.573829,48.137969), 4326),900913), 100);