[OSM-talk] Bridge relation on way going under?

[Lennard]

On 28-5-2011 18:46, Tobias Knerr wrote:

> With a relation, these calculations would not be necessary.

The people that come up with these types of relations seem to forget 
that spatial data is what OSM is all about.


For instance, using the osm2pgsql schema:

http://www.openstreetmap.org/browse/way/13884500

"Which roads/railways/waterways does this bridge cross?"

select osm_id,highway,railway,waterway,name
  from (
   select l1.osm_id,l1.highway,l1.railway,l1.waterway,l1.name,
     case when l1.layer ~ E'^-?[[:digit:]]+(\.[[:digit:]]+)?$'
     then cast (l1.layer as float) else 0 end as crossing_layer,
     case when l2.layer ~ E'^-?[[:digit:]]+(\.[[:digit:]]+)?$'
     then cast (l2.layer as float) else 0 end as bridge_layer
     from planet_osm_line l1, planet_osm_line l2
     where ST_Crosses(l2.way, l1.way)
       and l2.osm_id = 13884500
       and (l1.highway is not null or l1.waterway is not null
            or l1.railway is not null)
  ) as foo
  where crossing_layer < bridge_layer;


   osm_id   |   highway    | railway | waterway |           name
-----------+--------------+---------+----------+--------------------------
   41050723 |              | rail    |          |
   98667698 |              | rail    |          |
   25933220 | unclassified |         |          | Ferdinand Perdieusstraat
   71890307 | path         |         |          | Brampad
    9923332 |              | rail    |          | Lijn 36
  107068083 | residential  |         |          | Brandweg
   53085949 | cycleway     |         |          |
   25806811 | path         |         |          | Tunnelstraat
   22903417 | unclassified |         |          | Brandenstraat
(9 rows)

Time: 7.328 ms


-- 
Lennard