Über den Bahnübergangsvergleich DB Netz vs. OpenStreetMap

Datenquellen und Lizenz

Auf dieser Karte sind die Ergebnisse eines Vergleichs der Bahnübergänge aus OpenStreetMap mit den Daten aus dem Open-Data-Portal der Deutschen Bahn zu sehen. Die OpenStreetMap-Daten sind vom 14. Dezember 2017, die DB-Daten vom Januar 2017.

Die Resultate dieses Vergleichs unterliegen ebenso wie die verwendeten OpenStreetMap-Daten den Bedingungen der Open Database License 1.0. Die Daten der Deutschen Bahn unterliegen den Bedingungen der CC-BY 4.0

Ablauf

Import der OpenStreetMap-Daten in eine PostgreSQL-Datenbank:

Das verwendete spezielle Stylefile für osm2pgsql:

# OsmType  Tag          DataType     Flags
node,way   admin_level  text         linear
node,way   area         text         polygon # hard coded support for area=1/yes => polygon is in osm2pgsql
node,way   barrier      text         linear
node,way   bicycle      text         linear
node,way   bridge       text         linear
node,way   boundary     text         linear
node,way   construction text         linear
node,way   foot         text         linear
node,way   highway      text         linear
node,way   horse        text         linear
node,way   layer        text         linear
node,way   motorcar     text         linear
node,way   oneway       text         linear
node,way   operator     text         linear
node,way   public_transport text     polygon
node,way   railway      text         linear
node,way   service      text         linear
node,way   surface      text         linear
way        tracktype    text         linear
node,way   tunnel       text         linear
node,way   water        text         polygon
node,way   waterway     text         polygon
node,way   z_order      int4         linear # This is calculated during import
way        way_area     real         linear # This is calculated during import

Shapefiles mit Bahnübergängen und Strecken der DB importieren:

Bahnübergänge an DB-Strecken ermitteln:

SELECT p.osm_id AS osm_id, p.railway AS railway, p.tags AS tags, p.way AS geom
  INTO osm_bahnuebergaenge
  FROM planet_osm_point AS p, strecken_db AS s
  WHERE ST_DWithin(s.geom, p.way, 0.0002) AND p.railway IN ('level_crossing', 'crossing'); -- 1.7 Sek. 23810 INSERT

CREATE INDEX osm_bahnuebergaenge_geom_idx ON osm_bahnuebergaenge USING GIST(geom);

CREATE INDEX planet_osm_line_highway ON planet_osm_line USING GIST(way)
  WHERE highway IS NOT NULL AND tags->'access' IN ('private', 'no') AND osm_id > 0; -- 9 Sekunden
        

Dienstwege und nicht öffentliche Bahnübergänge (größtenteils Dienstwege in Bahnhöfen) löschen:

DELETE FROM osm_bahnuebergaenge AS o
  WHERE o.osm_id IN (
    SELECT a.osm_id AS osm_id
      FROM osm_bahnuebergaenge AS a, planet_osm_line AS p
      WHERE a.geom && a.geom AND ST_Covers(p.way, a.geom) AND p.highway IS NOT NULL AND p.tags->'access' IN ('private', 'no') AND p.osm_id > 0
  ); -- 2 Sekunden, 1094 DELETE

Bahnübergänge ausgeben, die nur in OSM sind

SELECT b.osm_id AS osm_id, b.railway AS railway, b.tags AS tags, b.geom AS geom
  INTO bahnuebergaenge_nur_osm
  FROM osm_bahnuebergaenge AS b
  WHERE b.osm_id NOT IN (
    SELECT c.osm_id AS osm_id
    FROM osm_bahnuebergaenge AS c
    JOIN crossings_db AS d
    ON (ST_DWithin(c.geom, d.geom, 0.0003))
  ); -- 0,3 Sekunden 2137 INSERT

Tabelle für Bahnsteige:

CREATE TABLE platforms (osm_id bigint, railway text, geom geometry(Geometry, 4326));
INSERT INTO platforms (osm_id, railway, geom)
  SELECT osm_id, railway, way
    FROM planet_osm_polygon
    WHERE railway = 'platform'; -- 1 Sekunde, 12157 Insert
INSERT INTO platforms (osm_id, railway, geom)
  SELECT osm_id, railway, way
    FROM planet_osm_line
    WHERE railway = 'platform'; -- 11 Sekungen, 7617 Insert
CREATE INDEX platforms_geom_idx ON platforms USING gist(geom);
        

Bahnübergänge in Bahnsteignähe entfernen, da das i.d.R. Reisendenübergänge sind und diese in den DB-Daten fehlen:

DELETE FROM bahnuebergaenge_nur_osm AS b
  WHERE b.osm_id IN (
    SELECT b.osm_id
      FROM bahnuebergaenge_nur_osm AS b
      JOIN platforms AS p
      ON (ST_DWithin(b.geom, p.geom, 0.0004))
    ); -- 1103 DELETE, < 1 Sekunde
        

Bahnübergänge, die in OSM nicht ordentlich erfasst sind

In OSM gibt es zahlreiche Bahnübergänge, bei denen das Tag railway=level_crossing oder railway=crossing fehlt und die nicht an Stellen liegen, wo Straßen/Wege und Gleise sich kreuzen.

SELECT *
    INTO highways
    FROM planet_osm_line
    WHERE highway IS NOT NULL AND osm_id > 0; -- 12 Sekunden
SELECT *
  INTO railways
  FROM planet_osm_line
  WHERE railway = 'rail' AND osm_id > 0; -- 2 Sekunden
CREATE INDEX highway_geom_idx ON highways USING GIST(way); -- 139 Sekunden
CREATE INDEX railway_geom_idx ON railways USING GIST(way); -- 1 Sekunde
UPDATE highways
  SET layer = '0'
  WHERE layer NOT SIMILAR TO '-?[0-9]+' OR layer IS NULL; -- 211 Sekunden
UPDATE railways
  SET layer = '0'
  WHERE layer NOT SIMILAR TO '-?[0-9]+' OR layer IS NULL; -- 3 Sekunden
ALTER TABLE highways ALTER COLUMN layer TYPE int USING layer::int; -- 164 Sekunden
ALTER TABLE railways ALTER COLUMN layer TYPE int USING layer::int; -- 2 Sekunden
SELECT DISTINCT ON (geom) osm_id, geom, highway, tags
  INTO crossings_by_intersection
  FROM (
    SELECT DISTINCT ON (l.osm_id) l.osm_id AS osm_id, ST_Intersection(l.way, r.way) AS geom, l.highway AS highway, l.tags AS tags
      FROM highways AS l
      JOIN railways AS r
      ON (l.way && r.way AND ST_Intersects(l.way, r.way) AND l.layer = r.layer)
  ) AS f; -- 37 Sekunden, 76964 INSERT
CREATE INDEX crossings_by_intersection_geom_idx ON crossings_by_intersection USING GIST(geom); -- < 1 Sekunde
ALTER TABLE crossings_by_intersection ADD COLUMN pkey serial primary key;
SELECT *
  INTO all_crossings
  FROM planet_osm_point
  WHERE railway IN ('level_crossing', 'crossing');
CREATE INDEX all_crossings_geom_idx ON all_crossings USING gist(way);
DELETE FROM crossings_by_intersection AS c
  WHERE pkey IN (
    SELECT d.pkey AS pkey
      FROM crossings_by_intersection AS d
      JOIN all_crossings AS o
      ON (ST_DWithin(o.way, d.geom, 0.00001))
    )
    OR ST_GeometryType(geom) IN ('ST_LineString', 'ST_MultiLineString'); -- 12974 DELETE 0,4 Sekunden

Und nun nur noch die, die an DB-Infrastruktur liegen:

SELECT a.osm_id AS osm_id, a.highway AS highway, a.geom AS geom
  INTO crossings_by_intersection_near_db
  FROM crossings_by_intersection AS a, strecken_db AS s
  WHERE ST_DWithin(a.geom, s.geom, 0.0001);

Bahnübergänge suchen, die nur die DB kennt

SELECT d.strecke_nr AS vzg_nr, d.richtung AS richtung, d.km_i AS km_i, d.km_l AS km_l, d.bezeichnun AS bezeichnung, d.techn_sich AS technische_sicherung, d.strassenar AS strassenart, d.geom AS geom
  INTO bahnuebergaenge_nur_db
  FROM crossings_db AS d
  WHERE d.gid NOT IN (
    SELECT e.gid AS gid
      FROM crossings_db AS e
      JOIN osm_bahnuebergaenge AS o
      ON (ST_DWithin(o.geom, e.geom, 0.0005))
  ); -- 1450 INSERT, 0,3 Sekunden
ALTER TABLE bahnuebergaenge_nur_db ADD COLUMN pkey serial primary key;
CREATE INDEX bahnuebergaenge_nur_db_geom_idx ON bahnuebergaenge_nur_db USING GIST(geom);
CREATE INDEX crossings_by_intersection_near_db_geom_idx ON crossings_by_intersection_near_db USING GIST(geom);
DELETE FROM bahnuebergaenge_nur_db
  WHERE pkey IN (
    SELECT b.pkey AS pkey FROM bahnuebergaenge_nur_db AS b, crossings_by_intersection_near_db AS c WHERE ST_DWithin(b.geom, c.geom, 0.0005)
  ); -- 220 DELETE