2

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 ?

| improve this question | | | | |
0

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.

| improve this answer | | | | |
  • Thanks. When I installed an OSM server with mod_tile, importing PBF with osm2pgsql , I thought that I would have all features in openstreemaps.org (find city by name and draw its polygon, calculate directions...). But one day ago I realized that another setup is responsible for city/address to geographical object resolution (Verbatim). So, it is not a all in one solution, it is all fragmented, right? – monovox Dec 13 '19 at 11:42
  • I just noticed that openstreetmaps.org has options to find directions: graphopper and osrm. It's gonna be a longer way ! – monovox Dec 13 '19 at 13:32
  • Yes, it's different tools behind. The tool to get the address should be Nominatim. You can take a look at the different tools usually used with OSM there: wiki.openstreetmap.org/wiki/Develop – robin loche Dec 16 '19 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.