May 29, 2014 at 8:16 am
Johnny H (5/28/2014)
Thank Jason this increased the speed 10 foldSELECT s.ID
FROM v_sign s
WHERE EXISTS (SELECT 1 FROM dbo.water w WHERE (geography::STGeomFromText(s.geometry.STAsText(), 4326).STBuffer(100).STIntersects(w.geography) = 1)
The speed increase most likely comes from the advantages of the EXISTS condition over a JOIN in this situation, which apply whether you're dealing with spatial data or not. When you have two tables A and B and you need to test whether a row in Table A has a corresponding row in Table B but don't actually need to return any data from Table B, the EXISTS condition will almost always be faster than joining Table B (assuming that the necessary indexes exist to enable efficient seeking or range-scanning on Table B). This is because SQL Server can prove the EXISTS condition TRUE as soon as it finds any one corresponding row in Table B, whereas a JOIN requires SQL Server to find ALL corresponding rows in Table B. As you might guess, the EXISTS can really outperform a JOIN where there is a one-to-many relation between Table A and Table B - the EXISTS test can stop as soon as it finds a single corresponding row in Table B while the JOIN must plug on to complete the join of ALL rows.
Jason Wolfkill
May 29, 2014 at 8:29 am
mickyT (5/28/2014)
Johnny H (5/28/2014)
Thank Jason this increased the speed 10 foldSELECT s.ID
FROM v_sign s
WHERE EXISTS (SELECT 1 FROM dbo.water w WHERE (geography::STGeomFromText(s.geometry.STAsText(), 4326).STBuffer(100).STIntersects(w.geography) = 1)
Are you able to post up some DDL to show us the structure of your tables and the spatial indexes. This has peeked my interest and I have been doing a bit of testing of various options.
Also are you in a position to change the datatype for the Signs table? You could potentially get an even better increase in performance.
I'd be interested in seeing whether this condition performs better than the STBuffer() approach:
WHERE (geography::STGeomFromText(s.geometry.STAsText(), 4326).STDistance(w.geography) <= 100
Certainly adding a geography type column to the sign table would help things along by eliminating the need for the type conversion. A good spatial index on the dbo.water table should also make a noticeable difference.
Since any query that requires the type conversion on the values from the sign table will most likely require a table scan (discounting the effect of any conditions in the view definition if v_signs is actually a view), indexing the signs table probably won't make much difference. However, if the OP can add the geography type column to the sign table, a spatial index on that table may help, too.
Jason Wolfkill
May 29, 2014 at 8:38 am
wolfkillj (5/29/2014)
Eirikur Eiriksson (5/28/2014)
Quick thought, if the "signs" are points then I would have thought that the STContains function would be more appropriate.😎
Except that the requirement was to determine which signs are within 100 meters of water. The water polygons won't necessarily contain the sign points!
Implied is using the STBuffer(100)
😎
May 29, 2014 at 8:44 am
Eirikur Eiriksson (5/29/2014)
wolfkillj (5/29/2014)
Eirikur Eiriksson (5/28/2014)
Quick thought, if the "signs" are points then I would have thought that the STContains function would be more appropriate.😎
Except that the requirement was to determine which signs are within 100 meters of water. The water polygons won't necessarily contain the sign points!
Implied is using the STBuffer(100)
😎
Still, the water polygon won't necessarily contain the 200m diameter polygon created by buffering the sign point by 100 meters (and almost certainly will not, if the assumption that most signs are on dry land is true) - if any part of the 200m diameter polygon intersects the water polygon, the sign is within 100m of the water.
😉
Jason Wolfkill
May 29, 2014 at 9:33 am
wolfkillj (5/29/2014)
Eirikur Eiriksson (5/29/2014)
wolfkillj (5/29/2014)
Eirikur Eiriksson (5/28/2014)
Quick thought, if the "signs" are points then I would have thought that the STContains function would be more appropriate.😎
Except that the requirement was to determine which signs are within 100 meters of water. The water polygons won't necessarily contain the sign points!
Implied is using the STBuffer(100)
😎
Still, the water polygon won't necessarily contain the 200m diameter polygon created by buffering the sign point by 100 meters (and almost certainly will not, if the assumption that most signs are on dry land is true) - if any part of the 200m diameter polygon intersects the water polygon, the sign is within 100m of the water.
😉
😀 he he, I was thinking the other way around, buffered water containing the sign.
😎
May 29, 2014 at 1:42 pm
Hi All
I've generally found the STContains is no better performing than STIntersects and in some cases worse as it needs to verify that an object in completely enclosed.
I have also had a look at the conversion of the Geometry to a Geography and Geography::Point(g.geom.STY, g.geom.STX, 4326) should perform better than the text conversion. On my system it was 6 secs vs 4 secs on the following
DECLARE @gscrap geography
DECLARE @d datetime = getdate()
SELECT @gscrap = Geography::STGeomFromText(g.geom.STAsText(), 4326)
FROM (SELECT TOP 100000 N From Tally) t CROSS APPLY (SELECT Geometry::Point(t.N / 10000.0, -t.N / 10000.0, 0) geom) g
PRINT CONCAT('Text conversion ',DATEDIFF(ms,@d,GETDATE()))
SET @d = GETDATE()
SELECT @gscrap = Geography::Point(g.geom.STY, g.geom.STX, 4326)
FROM (SELECT TOP 100000 N From Tally) t CROSS APPLY (SELECT Geometry::Point(t.N / 10000.0, -t.N / 10000.0, 0) geom) g
PRINT CONCAT('Point conversion : ',DATEDIFF(ms,@d,GETDATE()))
I have set up a test set to try the various options and the results in seconds are. These are based on not having to do a conversion and spatial indexes on both tables. There is also more points than lines or polygons.
NN on Line : 75
NN on Poly : 45
Point Buffer on Line : 187
Point Buffer on Poly : 162
Point on Line Buffer : 52
Point on Poly Buffer : 30
Point on Line Buffer (contains) : 49
Point on Poly Buffer (contains) : 30
Point Buffer on Line (exists) : 191
Point Buffer on Poly (exists) : 184
Point to Line Distance (exists) : 288
Point to Poly Distance (exists) : 277
While testing I found that one of the most significant impacts on performance is how you build your spatial indexes. All I can say is AUTO_GRID didn't perform the best.
The build script and queries are in the attachments if you are interested.
May 29, 2014 at 1:57 pm
I have found that one should generally (as much as I dislike generalization) avoid AUTO_GRID.
😎
June 2, 2014 at 9:46 am
Eirikur Eiriksson (5/29/2014)
I have found that one should generally (as much as I dislike generalization) avoid AUTO_GRID.😎
Would you care to elaborate why?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2014 at 9:33 pm
The Dixie Flatline (6/2/2014)
Eirikur Eiriksson (5/29/2014)
I have found that one should generally (as much as I dislike generalization) avoid AUTO_GRID.😎
Would you care to elaborate why?
Using the automatic settings for the spatial index's grid resolution may produce acceptable results but that's just like tossing a coin. Tuning the resolution involves dropping and recreating the index while varying the resolution, which cannot be done if the resolution is "automatic".
😎
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply