/ Tutorial

Besseres Geocoding mit Open Street Map

Nach meinem Post von gestern habe ich mich sofort an die Arbeit für meinen Mini-Geocoder auf Github gemacht und eine kleine, aber feine API entwickelt. Nur mit einem war ich nicht zufrieden: Der Performance.

Die Antwortzeiten waren so schlecht, wie ich sie sonst nur von ellenlangen Mega-SQL-Queries kenne, die meist irgendwelche Reports erstellen und immer zwischen 02:00 und 05:00 Uhr laufen, damit sie im laufenden Betrieb nicht die Datenbank lahmlegen.

Folgende Anwortzeiten habe ich gemessen (Schnitt aus 10x die selbe Abfrage hintereinander)

  • Geocoding: 756 Millisekunden
  • Reverse: 2340 Millisekunden

Es gab sogar Ausreißer auf fast 2500 ms. Also habe ich mir zuerst einmal für das Reverse Geocoding einen View erstellt, in der Hoffnung Postgres würde das View in irgendeiner Weise cachen.

Fehlanzeige, mit dem View verschlechterten sich die Zeiten sogar auf durchschnittlich 2414 ms. Also blieb mir nichts anderes übrig, als eine Tabelle aus meinem Select Statement zu machen, die ich dann jedes Mal, wenn ich irgendwelche Daten ändere neu generieren muss.

Folgende Anforderungen habe ich mir an die Tabelle gestellt:

  • Der Volltextindex für das Geocoden soll vorbereitet werden
  • Beim Aufruf sollen am Besten keine oder nur sehr wenige Funktionen oder Subqueries aufgerufen werden
  • Alles, was vorberechnet werden kann, soll vorberechnet werden.

Das hat mich zu folgendem DDL Statement geführt:

CREATE TABLE geocode_optimized
AS SELECT
     w.tags -> 'addr:street'                            AS street,
     w.tags -> 'addr:housenumber'                       AS housenumber,
     w.tags -> 'addr:postcode'                          AS postcode,
     w.tags -> 'addr:city'                              AS city,
     w.tags -> 'addr:country'                           AS country,
     AVG(ST_X(n.geom))                                  AS longitude,
     AVG(ST_Y(n.geom))                                  AS latitude,
     to_tsvector(concat_ws(' ', w.tags -> 'addr:street',
                           w.tags -> 'addr:housenumber',
                           w.tags -> 'addr:postcode',
                           w.tags -> 'addr:city',
                           w.tags -> 'addr:country'
                 ))                                     AS full_text,
     st_makepoint(AVG(ST_X(n.geom)), AVG(ST_Y(n.geom))) AS point
   FROM ways w
     INNER JOIN way_nodes wn ON w.id = wn.way_id
     INNER JOIN nodes n ON n.id = wn.node_id
   WHERE exist(w.tags, 'addr:housenumber') AND exist(w.tags, 'addr:street')
   GROUP BY housenumber, street, postcode, city, country;

CREATE INDEX idx_geocode_full_text ON geocode_optimized USING GIN (full_text);

Die Abfragen werden jetzt nicht mehr über die Subquery sondern über die optimierte Tabelle gemacht:

Geocode

SELECT
  street,
  housenumber,
  postcode,
  city,
  country,
  longitude,
  latitude
FROM geocode_optimized
WHERE full_text @@ plainto_tsquery('Hauptplatz Graz') 

Reverse

SELECT
  street,
  housenumber,
  postcode,
  city,
  country,
  longitude,
  latitude,
  CAST (st_distance_sphere(point, 
    st_makepoint(lon,lat)) AS FLOAT) as distance
FROM geocode_optimized 
ORDER BY distance

Antwortzeiten

Und siehe da: Geocoding braucht im Durchschnitt nur mehr Sagenhafte 17 ms (45x schneller) und Reverse Geocoding 127 ms (18x schneller). Das hat sich doch gelohnt!

Hier ein Diagramm, das uns die Veränderung zeigt.