6

I have about 6 million rows with geography spatial features - they are all 1000 meter radius hexagons all over the US. There are a couple hundred thousand that would have been NULL, which I updated to "the north pole" so they could be indexed but not show up in my queries (on recommendation from a blog). I'm trying to find the closest polygons to a point.

I have tried many combos of spatial indexes to no avail. I run the sp_help_spatial_geography_index and the primary_filter_efficiency is always really low, no more than 15% and usually 3% or less. The odd one is that when it's 15%, if I run it a second time it drops to around 3%.

I have literally been trying for months to index this dataset and I don't know what to do. I've basically just reverted to using all points and use 4-point Lat/Long boxes to search for data instead, but it's kinda slow (2 seconds) - I would have thought Spatial would be faster, but it's 50 times slower.

EDIT - Using SQL 2008 R2. More history: First my data was just all points, and I would make a single radius around the point I was searching to see what points intersected, but that was really slow. I figured spatial indexes on only points aren't very helpful. So I had a revelation - what if I flipped the problem around - so then I put radius's around ALL the other points and indexed those instead. Then I ask the server to give me all the polygons that intersect the one point. Unfortunately, that did not help, same performance.

EDIT2 - Show Query using hints: For this 1 sample. the Policies date filter returns about 90,000 rows instantly. Including the Spatial filter takes anywhere from 500-1200 milliseconds depending on which type of index I use. I need to run this query on about 3,000 cases a day and no matter how much I've tweaked, the whole process still takes about 2 hours to run (All points do not query the same. Some other points take 10+ seconds).

DECLARE @g geography = geography::Point(40.731778, -73.77563, 4326)
DECLARE @DateDue DATETIME = '1/23/2014'
DECLARE @DateReceived DATETIME = '1/16/2014'

SELECT p.IdNumber, g.Radius1000Meters
FROM Policies p  WITH(INDEX(IX_Policies_DateReceived_DateDue)) 
JOIN GisTable g WITH(INDEX(SPATIAL_GisTable_HHHM16)) ON g.IdNumber = p.IdNumber
WHERE p.DateDue >= @DateReceived AND p.DateReceived <= @DateDue     
    AND g.Radius1000Meters.STIntersects(@g) = 1
  • 5
    what kind of database are you using? (postgres, oracle, spatialite, etc..) This kind of thing could be db-dependent. – matt wilkie Jan 31 '14 at 16:55
  • How are you formulating the query? – BradHards Feb 1 '14 at 0:05
  • Thanks for looking Brad - I've added a sample query and some more info. – David Storfer Feb 2 '14 at 19:38
  • It still vexes me that I cannot get my spatial index to have a primary filter efficiency any higher than 25%, but maybe I will create a process to brute force itself through hundreds of combinations of spatial indexes (high, medium, low - with different cells per object) like this guy suggests - esdm.co.uk/… – David Storfer Feb 2 '14 at 22:18
  • Out of curiosity, have you explored the same approach using Postgres/PostGIS? It would be interesting to see how it compares. – djq Feb 2 '14 at 22:41
4

Spatial fragmentation can make a random spatial distribution of data perform poorly, no matter how you tune the spatial index. Try duplicating the table by exporting the features by county id or some other attribute that clusters the data (or if you have to, by a systematic search grid that partitions the data by at least in ten tiles in each dimension), then rebuilding the index. If the data had been fragmented, you might see a significant improvement in spatial search efficiency.

Note: Optimizing for spatial search could have an impact on non-spatial queries; if your queries are compounnd in nature (e.g., time window and spatial envelope), then reorgainizing to split the difference (spatially, by month) can be a compromise, as can keeping two copies of the table, with differing clustered indexes, and queries directed to the table which is most friendly to the constraints.

  • Also wondering if it's similar to this guy's issue - where he says this "We think the problem is that our data is fairly sparse with high-density regions, so the static-grid-size approach isn't helpful. If we set the grids to high, the index is too specific in sparse areas, but if we set it to low, the index is useless in high-density areas.". stackoverflow.com/questions/3470146/… – David Storfer Jan 31 '14 at 20:36
  • Oh I see what you are saying -- so if the data is in a certain physical order in the table, in relation to the primary key -- like all the NY polygons are in a clump together -- then the index will have an easier time with them? I will try this. I wonder how this will handle inserts - it seems like I'd have to rebuild the table periodically to maintain that organization as I add new records. Might even need to be daily as I'm adding many thousands per day all over the U.S. – David Storfer Jan 31 '14 at 21:23
  • It's not the index that has the trouble, per se, it's fetching the rows out of the primary table in random order that in effect becomes a full table scan (or worse, multiple passes, if the cache is smaller than the table). – Vince Jan 31 '14 at 22:19
  • Thank you for the continuing advice, Vince. That is a good thought, but the PK was a Seek in the execution plan both before and after I re-organized the data by County ID. After re-organizing the data into "spatial cluster" it also didn't help the spatial index, which is oddly worse, down to 1.9% primary filter efficiency. These are really odd results. I've done some SQL Spatial before - a few years ago with some public geographic data (i.e. wildfire risk, damaging winds, etc) and never had this much trouble, but those were much fewer records (yet larger polygons). – David Storfer Jan 31 '14 at 23:16
  • 1
    Okay, after re-working the query and carefully examining execution plans, I was finally able to get the spatially clustered data to run at ~100ms, which is so much better! I tried the same query on my original 2 tables (not organized by countyID) and they took 10 seconds and 3 minutes. So -- Thank you so much Vince for getting me on the right track!! Maybe focusing on sp_help_spatial_geography_index isn't always the best approach? – David Storfer Feb 2 '14 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.