Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index:

CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN]
(
[Coord]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

T_POLYGON has:

CREATE SPATIAL INDEX [T_POLYGON_COORD] ON [dbo].[T_POLYGON]
(
[COORD]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY];

A query to find the intersection of T_PIN and T_POLYGON takes more than 45 minutes to execute:

SELECT COUNT(*)
FROM T_PIN 
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1;

The result is 4,438,318 rows.

How can I accelerate this query?

share|improve this question

This question has an open bounty worth +50 reputation from Paul White ending in 6 days.

This question has not received enough attention.

migrated from stackoverflow.com Dec 6 '13 at 10:27

This question came from our site for professional and enthusiast programmers.

    
Have you tried using `T_POLYGON.Coord.STIntersects(T_PIN.COORD)=1'? –  travis Jan 21 at 21:15
    
I would be interested to see your query plan. I work on Postgres, but have to run similar queries, but on significantly larger data sets. I have come up with a technique that gets my worst ones down to about 2 days (which unfortunately involves scripting), but I'd be interested to see your query plan first. –  John Barça 12 hours ago
    
Multiplying the polygons in my two tables together, I have a number that is 7000 times more, in terms of the number of potential intersections, than you have in your combination, so I think in that light my two days looks quite good. However, without seeing a query plan and knowing something about the average number of points per polygon, it will be hard to come up with concrete solutions. –  John Barça 10 hours ago

2 Answers 2

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item.

Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first. Matches against these simplified polygons could then be run through the primary filter to get the final results.

1) Add a new geography and geometry column to the [dbo].[T_POLYGON] table:

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeom geometry;
ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog geography;

2) Create the bounding box polygons (this involves an initial conversion to geometry to take advantage of STEnvelope()):

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeom = geometry::STGeomFromWKB(
    COORD.STAsBinary(), COORD.STSrid).STEnvelope();

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeog = geography::STGeomFromWKB(
    SimplePolysGeom.STAsBinary(), SimplePolysGeom.STSrid);

3) Create a spatial index on the simplified geography column

4) Get the intersections against this simplified geography column, then filter again on the matching geography data types. Roughly, something like this:

;WITH cte AS
(
   SELECT pinID, polygonID FROM T_PIN INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.SimplePolysGeog ) = 1
)
SELECT COUNT(*)
FROM T_PIN 
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1
    AND T_PIN.pinID IN (SELECT pinID FROM cte)
    AND T_POLYGON.polygonID IN (SELECT polygonID FROM cte)

EDIT: you can replace (1) and (2) with this computed, persisted column. credit to Paul White for the suggestion.

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS  ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),[COORD].[STSrid]).STEnvelope().STAsBinary(),(4326))) PERSISTED
share|improve this answer
    
Yeah, that is more or less what I was getting at. The issue I have found when spatially "joining" two sets of tables with a wide coverage area is that the optimizer often goes for two full table scans and loads of point in polygon tests. –  John Barça 12 hours ago
    
@PaulWhite, corrected. Thank you. –  g2server 11 hours ago

Queries like this often take a long time because of the complexity of the polygons. I've seen complex coastlines (for example) take ages to test points that are near their boundaries, having to zoom many levels to find whether a point is inside or outside.

...so you could try .Reduce()'ing the polygons, to see if that helps.

And for more about that function, look at http://msdn.microsoft.com/en-us/library/cc627410.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.