Extract relations from planet_osm_rels (osm2pgsql)

Nils_V · 2011-02-06T19:25:12.000Z

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 · 2011-02-07T14:49:32.000Z

(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

Nils_V · 2011-02-14T18:36:24.000Z

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