PostGIS PostgreSQL Queries – Effective Methods for OSM Data Handling

openstreetmappostgispostgresqlquery

I'm newbie to PostGIS and am looking for some SQL examples for better understanding.

I didn't understand how do I manipulate the tables, how objects relate one to another. An example:

There is this page: https://www.openstreetmap.org/relation/297514 – a city and its boundaries in strong orange.

enter image description here

I'm trying to do the same on my OSM server.

I found the city (the Relation ID) with the next query, because the cities I am interested have a known code/id list called IBGE:GEOCODIGO :

SELECT id,tags FROM planet_osm_rels WHERE 'IBGE:GEOCODIGO' = ANY(tags) AND '4106902' = ANY(tags) ;

Now, correct me if I'm wrong, I think there is some way to SELECT the polygon that represents the ways(?) around the city, I mean its boundaries, to finally draw this polygon with LeafLet on my OSM server.

Some code examples or a documents to understand how planet_osm data is organized and where are the "foreing_keys" to the objects I want:

  • SELECT polygon (administrative boundaries) FROM city
  • SELECT djikstra/ways between two points (not a line, but a road path)

this sort of thing

It seems easy to learn how to manipulate geographical objects (I found examples on PostgreSQL), but how to do it with planet_osm ?

Best Answer

The ids of the elements that compose the relation should be in the "parts" column of the planet_osm_rels as an array. The ids can be nodes of planet_osm_nodes, ways of planet_osm_ways, or polygon of planet_osm_polygon, and you will have to gather them by selecting them with the id (or osm_id) column of those tables. For example for polygons, that would be something like that:

WITH wanted_rel AS (
    SELECT 
            id, parts,tags 
        FROM planet_osm_rels 
        WHERE 'IBGE:GEOCODIGO' = ANY(tags) AND '4106902' = ANY(tags)
)
SELECT 
        id, geom
    FROM planet_osm_polygon pop
    INNER JOIN wanted_rel wr
    ON pop.osm_id IN wr.parts;

But manipulating imports of osm2pgsql can be a little difficult because it's not really made for analysis, just display. I suggest you to use for example imposm to import the wanted data in postgis, so you will have directly nodes, linestring and polygon as geom in a coherent table, and you can select fields that will have a given columns (instead of always having to use hstore). Plus some fields like speed, oneway, or boolean and numeric fields in general have a specific treatment on the import to clean the field and correct error when possible.

If relation are what you want, you should use imposm3.

Also, for path between points this is something completly different, and you will need to use specialized tools otherwise the computation time will quickly explode if you try to do it yourself. You should at tools like pgrouting for something in postgis, or an external tool like osrm or graphhopper that works with OSM data. You also have other paying services of course, if you don't want to do it yourself.

Related Question