3 ms·
By all means, consider PostGIS. If it works, great. If not, consider it as the source of truth and then an export to Elastic/some other search engine for search
by toast0 5y ago
By all means, consider PostGIS. If it works, great. If not, consider it as the source of truth and then an export to Elastic/some other search engine for searching.
One strategy is to make the DB the source of truth, and only put geo info and an id and maybe expiration time into the search engine. Partitioning into several indexes will help with your expiration issue. Have one index for 'permanent' objects, then one index for objects that expire at X:00, another at X:01, X:02, etc. Query multiple indexes and join on the client, then query the database. After the expiration window, you can just drop the index (plus whatever on the source of truth). If you can partition similarly on the source of truth db, that helps there too.
Alternatively, you may want to consider just using in memory structures, plus something to persist permanent objects.
Break up the map into reasonable sized rectangles, that can have small lists in them. Take the user's viewable box and determine which of your storage rectangles match, then go through all the lists and refilter.
If your boxes are all the same size, it's easy to find the matching ones, but popular boxes may have too many objects for performance. OTOH, resizing boxes sounds complex.