Embed simple reverse geocoder in PostGIS database

[spwoodcock]

Problem

• Currently we rely on Nominatim (hosted by OSM).
• This is fine, but it's an external API we rely on causing two issues:
  • Rate limiting if we call it too many times successively.
  • When we run backend or e2e tests we create projects, which generate the location strings via Nominatim. If we have many workflows running the tests, this can spam Nominatim a bit (local tests + CI tests).
• We don't need super precise geocoding - only city and country.

Solution

• First get a dataset for cities of the world over 1000 population. This could be via OSM, but there are already curated datasets such as this.
  • Clean the data / do some basic checking
  • Extract the coordinate field into lat & lon fields.
• Create a table called geocoding with a computed/generated geom field (takes lat/lon and automatically generates a PostGIS geom):

  CREATE TABLE geocoding (
      city varchar,
      country varchar,
      geom geometry(Point,4326) GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(lon, lat), 4326)) STORED,
      voronoi geometry(Polygon,4326)
  );

• Insert CSV data into the table efficiently using psql \copy.
• Create voronoi polygons based on the points - ideally we get a mosaic of the entire world, built from adjoining city boundaries.
  
• Create a geospatial index on the voronois for performance.
• Now we have our voronois we can have a pre-canned query to get the city/country for a given geometry centroid (point).

The final query could be something like (untested):

SELECT 
    g.city, 
    g.country
FROM 
    geocoding g
WHERE 
    ST_Contains(g.voronoi, ST_GeomFromText('POINT(-72.48496 -13.92862)', 4326));

Additional Considerations

• Would love if anyone in the community wants to look into this!
• Once we have a curated dataset of voronois this table can be exported in a format for easy import in a Postgres init script, so we have it available when users run FMTM.

[Sujanadh]

I think it would be best if we create our own package or separate repo for the geocoding so it will be efficient to use , scale it and benefits of using it in other projects too like Tasking Manager.

[spwoodcock]

Good point, this would be a great pypi package!

Taking a quick browse there is already a similar implementation! https://github.com/thampiman/reverse-geocoder

Perhaps we could take this code and update it (or it might even work as it is, despite being so old).

Looks like they bundle a small C++ util? Not sure about this approach. Would love to leverage PostGIS.

[spwoodcock]

Obviously this is a quite common issue to solve.

I love it when I write out an idea and somebody on the internet has already solved the task, to your exact spec 😂
https://github.com/richardpenman/reverse_geocode

We should probably just use that package 👍

[spwoodcock]

On second thought perhaps we just use that as inspiration - I'm not too keen on the implementation!

The package downloads the JSON data and loads it into memory for the query - far from ideal.

We could write an alternative backed by PostGIS and consider a similar k-d tree index like this? (needs more research):

CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] );

https://www.crunchydata.com/blog/the-many-spatial-indexes-of-postgis