SpatiaLite: A Spatial Extension to SQLite
What are the general solutions to handling spatial data and in particular, 'nearness' in huge databases?Last time I needed something along those lines, I implemented geohash [1] in the application level ontop of a time-series database. I assume there is a better, more sound way to achieve that, as geohash doesn't handle boundaries well.---For the uninitiated, geo-hash uses a space filling curve multiple times at different precision to map quadrants into strings. So imagine a map, top left is North America, bottom right Australia, we can name the quadrants 0,1,2,3, we then take each quadrant and again use a z filling curve to split it to more quadrants (well, quadrant of a quadrant) and keep the name we gave it, we repreat the process up to desired precision, finally, we take the all the names of quadrants with the same order and concatenate them, to create a string that repretents the position. Strings with the same prefix are in the same quadrant and depending on the length of the prefix, the quadrant they are in is either bigger or smaller, longer prefix, smaller quadrant.We found that geohash, even at the application level, provided much faster queries than searching for coordinates within range on two fields.---[1] https://en.wikipedia.org/wiki/Geohash