0

I have a PostgreSQL table that has a geometry type column, in which different simple polygons (possibly intersecting) are stored. The polygons are are all areas within a city. I receive an input of a point (latitude-longitude pair) and need to find the list of polygons that contain the given point. What I have currently:

  • Unclustered GiST index defined on the polygon column.
  • Use ST_Contains(@param_Point, table.Polygon) on the whole table.

It is quite slow, so I am looking for a more performant in-memory alternative. I have the following ideas:

  1. Maintain dictionary of polygons in Redis, keyed by their geohash. Polygons with same geohash would be saved as a list. When I receive the point, calculate its geohash and trim to a desired level. Then search in the Redis map and keep trimming the point's geohash until I find the first result (or enough results).
  2. Have a trie of geohashes loaded from the database. Update the trie periodically or by receiving update events. Calculate the point's geohash, search in the trie until I find enough results. I prefer this because the map may have long lists for a geohash, given the nature of the polygons.
  3. Any other approaches?

I have read about libraries like GeoTrie and Polygon Geohasher but can't seem to integrate them with the database and the above ideas.

Any cues or starting points, please?

Abubakar Mehmood
  • 938
  • 1
  • 10
  • 19

1 Answers1

0

Have you tried using ST_Within? Not sure if it meets your criteria but I believe it is meant to be faster than st_contains

Slumdog
  • 470
  • 2
  • 4
  • Not yet, but I can try and revert with results. However, an event-based system which maintains the whole polygon data hierarchically in Redis would be ideal. – Abubakar Mehmood Mar 09 '20 at 11:13