spatial database vs spatial datastructure?

Question

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)

Answer 1

Not answer but only way to make long comment about test that OP made.

Test data Finnish OSM routing table, 379293 lines (allmoust 400k lines) OP had 300k lines. Test machine was highend desktop i7 + 8G ram , database on normal hardisk, database postgresql 9.2 , non default conf. (Table size 118Mb , Index Size 44Mb. Shared memory 2G)

select count(*) FROM hh_2po_4pgr as h, hh_2po_4pgr as h2 WHERE h.id != h2.id 
AND ST_Intersects(h.geom_way,h2.geom_way)IS TRUE 

= ( i add time later, but it is slow ) (Compares all geoms to another geom in database)

select count(*) FROM hh_2po_4pgr as h, hh_2po_4pgr as h2 WHERE h.id != h2.id 
AND ST_DWithin(h.geom_way, h2.geom_way ,0.0001 )AND ST_Intersects(h.geom_way,h2.geom_way)IS TRUE 

= 2.3 min (ST_DWithin limits Intersected data to 0.0001 degrees (at equator 1.1132 m ) more about decimal degrees

select count(*) FROM hh_2po_4pgr as h, hh_2po_4pgr as h2 WHERE h.id != h2.id 
AND h.geom_way && h2.geom_way AND ST_Intersects(h.geom_way,h2.geom_way)IS TRUE

= 1.9 min h.geom_way && h2.geom_way limits data using intersecting bounding boxes

Conclusion : Your database test probably did not work as you intended, or your hardware is completely different than my or MS SQL is alot slower than postgresql

And yes i know , these test are not comparable. But i have feeling that OP results are not correct.

Answer : my opinion is that prober spatial database with indexes is more than sufficient for job.

Answer 2

I have created two testcases using java and one MS SQL server database Table with 366634 rows with two columns: uid (varchar 255), geom (Geometry). The data access layer is implemented using Hibernate Spatial in order to have a bulkloading mechanism.

The first testcase bulkloads the data into an RTree model (JTS) and then performs a search for each geometry that is encountered. So basically every item is searched for in the tree.

The second test testcase also bulkloads all the spatial data in memory first, but then performs a spatial database query for each geometry. So basically every item is searched for in the database using an STIntersects()-function.

Now comes the interesting part: which one is faster? I ran each testcase ten times, and then calculated the average time from the results.

Database-only-approach: The query time for 1000 elements is on average 2.3s. So after extrapolating the results in order to have an approximate idea of how long it takes to do 366634 elements, we come to a result of 14,1min.

Rtree-and-Database-approach: Bulkloading and insertion into the RTree took on average 6.2s. Search time for all the elements is on average 0.7s. So in total: 6.2s + 0.7s = 6.8s.

Conclusion: 6.8s < 14.1min. Using an Rtree is 120 times faster initially! And subsequent searches are 1180 times faster!

ps: the above testdata used (Multi)Lines that were converted to bounding boxes using their internal envelope. I have also used other testdata using Point(x,y)-data, and the results were even more disadvantageous for the Database-only approach. (1146 times slower initially and 9803 subsequently)