[OSM-talk] Finding relation members in osm2pgsql PostGIS database?

[Ture Pålsson]

2010/6/21 Phil! Gold <phil_g at pobox.com>:
> I've got a PostGIS database created and maintained with osm2pgsql.  For
> some of the Mapnik rendering I'm doing, I'd like to see whether ways
> belong to relations.  (Specifically, whether a highway=* way is a member
> of a route=road relation.)  I've been able to look in the planet_osm_rels
> table for relation membership, but the members are stored in an array, and
> searching those arrays for membership, even on a bbox-restricted subset,
> is really slow.  Is there any way to do this faster?  If not, I suppose

I was playing around with representing ways as arrays of node id:s the
other day, and I got seemingly decent performance, at least doing
array intersection tests (using '&&', stuff like "what other ways
share nodes with this way"),  by building a GIN index on the array
column. I was just playing around with it interactively though, so it
might still be too slow for heavy use. Just mentioning it in case you
have not tried. :-)

  -- Ture