I am building an automated system that has to process several hundred spatial queries in the form of a polygon every day. The queries have to return both spatial and nonspatial data from different database tables. Each table consists of over 500 000 records.
What would be a better approach when performance is a top priority? I) execute a spatial query every time i need (spatial) data? II) first bulk-load all data in-memory into spatial datastructure, and then perform those same spatial queries onto the spatial datastructures?
If the latter is the better approach, which kind of datastructure would be recommended? A few possible datastructures could be:
- PR-trees
- tries
- quadtrees
- R-trees Notes: It's safe to assume that there will be no data changes during the processing. The spatial data is 2-dimensional (points, linestrings, polygons)