OpenStreetMap Forum

Nils_Lux

Member

Registered: 2008-06-11

Posts: 18

Extract relations from planet_osm_rels (osm2pgsql)

Hi,
I'm working on a map for which I need some relations extracted from the OSM database. I use osm2pgsql with slim mode to get the additional three tables.

I find the relations in the table planet_osm_lines, but unfortunately more than one row per relation. That causes some trouble later on to give these relations certain properties.
In the table planet_osm_rels I have only one row per relation (what is fine), but the tags are stored as text arrays, and so it is difficult to select certain tag-value pairs. I tried it with ANY, but I can select only _key_ or _value_ but not the pair. The SQL  "SELECT * FROM planet_osm_rels WHERE 'key' = ANY (tags) AND 'value' = ANY (tags)" gives me all rows where 'key' and 'value' are keys OR values BUT NOT as a pair.

Has anyone tried to select key/value pairs from this table using the tags column?

Any help would be appreciated.

Regards
Nils

sly

Member

Registered: 2008-07-15

Posts: 75

Re: Extract relations from planet_osm_rels (osm2pgsql)

(In my answer, I suppose you are interested in type=road relations)

Keep using the planet_osm_lines table which is much suited for queries than the planet_osm_rels which is mainly for diff updates of your database.

But, as you have noticed, relations which forms long lines are cut into pieces in the planet_osm_lines. To avoid that, you have to hack osm2pgsql a bit.

In the file output-pgsql.c, search for the comment :
// Split long linear ways after around 1 degree or 100km (polygons not effected)

change the next lines which talks about the split_at variable :
split_at = 1;
and
split_at = 100 * 1000;

and change the values to something huge (I've put 40000000) and then your relations are no more splited

Last edited by sly (2011-02-15 01:19:39)

Nils_Lux

Member

Registered: 2008-06-11

Posts: 18

Re: Extract relations from planet_osm_rels (osm2pgsql)

Thx for your help, sly!

I just amended output-pgsql.c as you proposed:

    // Split long linear ways after around 1 degree or 100km (polygons not effected)
    if (Options->projection == PROJ_LATLONG)
        split_at = 40000000; /* 1; nivi 7.2.2011 */
    else
        split_at = 40000000; /* 100 * 1000; nivi 7.2.2011 */

but I have still the relations split into several parts :-(

I get an error during make, to be honest (but I remember having got this error during my first compilation as well...):

make[2]: *** No rule to make target `debian/osm2pgsql.1', needed by `all-am'.  Stop.

Now I try to get around the problem by using an aggregate function querying the planet_line table: ST_Collect. That works for me.

Regards
Nils