postgis查询地址(带有osm数据) [英] postgis query for addresses (with osm data)

问题描述

我想使用openstreetmap中的数据查询到Postgis数据库的地址，检查数据库中是否存在该地址，如果存在，请获取坐标.使用渗透从.pbf文件填充数据库.这是数据库 http://pastebin.com/Yigjt77f 的架构.我有城市名称，街道名称和街道编号形式的地址.对我来说最重要的是这张桌子:

CREATE TABLE node_tags (
    node_id BIGINT NOT NULL,
    k text NOT NULL,
    v text NOT NULL
);

k列采用标签形式，我感兴趣的是:addr:housenumber，addr:street，addr:city，而v是对应的值.首先，我要搜索的是城市名称是否与数据库中的城市名称相匹配，而不是搜索结果集中的街道名称和门牌号.问题是我不知道如何进行仅查询一次即可获得此结果的SQL查询.我只能先询问城市名称，获取所有与我的城市匹配的node_id并将其保存在Java程序中，然后再进行查询，询问街道的每个找到的(与我的城市匹配)id_number(从我的Java程序中列出)，等等.这种方式确实很慢，因为要获取更详细的信息(城市比街道比数字更重要)，我必须进行越来越多的查询，而我还要检查很多地址.一旦有了匹配的node_id，我就可以轻松找到坐标，这不是问题.

此表的示例:

    node_id | k | v <br>
    123 | addr:housenumber | 50
    123 | addr:street | Kingsway
    123 | addr:city | London
    123 | (some other stuff) | .....
    100 | addr:housenumber | 121
    100 | addr:street | Edmund St
    100 | addr:city | London

我希望我能清楚地说明我的问题.

解决方案

这并不像您想象的那么容易. OSM中的地址是分层的，就像在现实世界中一样.并非OSM中的所有元素都附有完整的地址.有些仅具有addr:housenumber，并且仅属于最近的街道.有些具有addr:housenumber和addr:street但没有addr:city，因为它们只是属于最近的城市.或者将它们包含在边界关系中，该关系指定相应的城市.有时，除了addr:housenumber以外，还只是由addr:interpolation键描述的地址插值.有关更多信息，请参见地址密钥Wiki页面.

OSM Wiki中的 Karlsruhe架构页介绍了许多有关OSM中地址的信息.它还提到了 associatedStreet 关系，这些关系有时用于对房屋编号及其对应的街道进行分组. /p>

如您所见，数据库中的单个查询可能不够用.如果您需要一些启发，可以看看OSM的地址搜索引擎名词.但是请注意，为了优化地址查询，Nominatim使用了与常规数据库方案不同的数据库方案.您还可以查看许多路由应用程序之一，它们都必须进行地址查找.

I want to make queries for addresses to postgis database with data from openstreetmap, check if such address exist in database and if so, get coordinates. Database was filled from .pbf file using osmosis. This is schema for the database http://pastebin.com/Yigjt77f. I have addresses in form of city name, street name and number of street. The most important for me is this table:

CREATE TABLE node_tags (
    node_id BIGINT NOT NULL,
    k text NOT NULL,
    v text NOT NULL
);

k column is in form of tags, one that I'm interested are: addr:housenumber, addr:street, addr:city and v is corresponding value. First I'm searching if name of city matches one in database, than in results set I'm searching for street and than for house number. The problem is that I don't know how to make SQL query that will get this result with asking only once. I can ask first only for city name, get all node_id that match my city and save them in java program, than make queries asking for each found(matching my city) id_number (list from my java program) for the street, and so on. This way is really slow, because asking for more detailed information (city than street than number) I have to make more and more queries and what is more I have to check a lot of addresses. Once I have matching node_id I can easily find coordinates, so that's not a problem.

Example of this table:

    node_id | k | v <br>
    123 | addr:housenumber | 50
    123 | addr:street | Kingsway
    123 | addr:city | London
    123 | (some other stuff) | .....
    100 | addr:housenumber | 121
    100 | addr:street | Edmund St
    100 | addr:city | London

I hope I explained clearly what is my problem.

解决方案

This is not as easy as you might think. Addresses in OSM are hierarchically, like in the real world. Not all elements in OSM have a full address attached. Some only have addr:housenumber and simply belong to the nearest street. Some have addr:housenumber and addr:street but no addr:city because they simply belong to the nearest city. Or they are enclosed by a boundary relation which specifies the corresponding city. And instead of addr:housenumber there are sometimes also just address interpolations described by the addr:interpolation key. See the addr key wiki page for more information.

The Karlsruhe Schema page in the OSM wiki explains a lot about addresses in OSM. It also mentions associatedStreet relations which are sometimes used to group house numbers and their corresponding streets.

As you can see a single query in the database probably won't suffice. If you need some inspiration you can take a look at OSM's address search engine Nominatim. But note that Nominatim uses a different data base scheme than the usual one in order to optimize address queries. You can also take a look at one of the many routing applications which all have to do address lookups.

这篇关于postgis查询地址(带有osm数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！