I assume that using Rtree to store points seems wrong. Although such a structure is indicated for storing spatial data, after some research, which I just found out, it is best suited for storing nonzero regions of a region (since R refers to a region or rectangle on behalf of). Creating a simple table with a good index should provide better performance for updating and retrieving data. Consider my example below:
CREATE TABLE locations (id, latitude, longitude); CREATE INDEX idx_locations ON locations (latitude, longitude);
preferable
CREATE VIRTUAL TABLE locations USING rtree( id, minLatitude, maxLatitude, minLongitude, maxLongitude);
if you only plan on repeating minLatitude over maxLatitude and minLongitude over maxLongitude for each row to represent points, not rectangles. Although the latter approach will work as expected, Rtrees are suitable for areas of the index rectangle, and using them to store points is a misuse with worse performance. Prefer the index of the connection, as described above.
Further reading: http://www.deepdyve.com/lp/acm/r-trees-a-dynamic-index-structure-for-spatial-searching-ZH0iLI4kb0?key=acm
ClΓ©ssio mendes
source share