May 26, 2014 at 12:29 pm
Hi Everyone
I have two tables, Signs and Water. Each of these tables has tens of Thousands of spatial records. What I would like to do find is how many signs are within 100 meters of the water.
So I need to add a buffer of 100 meters to the signs spatial position and see if they intersect with the waters spatial area. I am new to the Spatial part of SQL Server so I have been having issues getting the query to work. Any help would be great. Thanks.
Signs table spatial is Geometry
Water table spatial is Geography.
Thanks.
May 26, 2014 at 9:27 pm
Hi Johnny
Unfortunately I think you are going to have some issues here. By there nature Geometry and Geography don't really play nice together. Geometry is designed for a cartesian world (think flat plan) and Geography is designed for a spherical world (tennis ball?).
If you could post a small sample of each geometry/geography I may be able to help you get a result.
Use the following queries to get out the geometries/geographies in a format I can quickly look at
SELECT TOP 5 GeometryColumn.STSrid, GeometryColumn.ToString() FROM Signs;
SELECT TOP 5 GeometryColumn.STSrid, GeometryColumn.ToString() FROM Water;
May 26, 2014 at 10:06 pm
This should get you started
😎
/* convert geometry to geography */
DECLARE @GEO1 GEOMETRY = geometry::STGeomFromText('POINT (-122.358 47.652)',0);
DECLARE @GEOG GEOGRAPHY = geography::Point(@GEO1.STY, @GEO1.STX, 4326)
/* Geography shape/poligon */
DECLARE @g geography = geography::STMPolyFromText('MULTIPOLYGON(((-122.358 47.653, -122.348 47.649, -122.358 47.658, -122.358 47.653)), ((-122.341 47.656, -122.341 47.661, -122.351 47.661, -122.341 47.656)))', 4326);
/* Get distance in meters */
SELECT @g.STDistance(@GEOG)
Results
Distance
----------------------
95.6798532700461
May 27, 2014 at 1:33 am
Eirikur Eiriksson (5/26/2014)
This should get you started😎
/* convert geometry to geography */DECLARE @GEO1 GEOMETRY = geometry::STGeomFromText('POINT (-122.358 47.652)',0);
DECLARE @GEOG GEOGRAPHY = geography::Point(@GEO1.STY, @GEO1.STX, 4326)
/* Geography shape/poligon */
DECLARE @g geography = geography::STMPolyFromText('MULTIPOLYGON(((-122.358 47.653, -122.348 47.649, -122.358 47.658, -122.358 47.653)), ((-122.341 47.656, -122.341 47.661, -122.351 47.661, -122.341 47.656)))', 4326);
/* Get distance in meters */
SELECT @g.STDistance(@GEOG)
Results
Distance----------------------
95.6798532700461
That will do the trick when working singularly, but performance will be an issue scaling it out to tables with 10's of thousands of rows.
I was heading down the path of suggesting that the geometry column in the signs table gets converted to a geography ... assuming that it is in Lat/Long and not a projected coordinate system. Once that is done, then a query could done that takes advantage of the spatial indexing.
Something along the lines of a join on a buffer. Picking which spatial objects to buffer will require determining which will provide the best performance.
SELECT
SignID, WaterID, WaterGeography.STDistance(SignLocation)
FROM Signs s
INNER JOIN Water w ON WaterGeography.STIntersects(SignLocation.STBuffer(100)) = 1
I haven't tested the above to make sure the indexes are used, it can sometimes be difficult to get the optimizer to use them:-). I'll try it out tomorrow on some real data to make sure.
There may also be a way to use the nearest neighbor query structure.
May 27, 2014 at 7:16 am
Here is what I have tried. Setting GEOMETRY to GEOGRAPHY, then a inner join, I have also tried with a union with the same error results.
DECLARE @sign GEOGRAPHY = (SELECT GEOGRAPHY::STGeomFromText(v_well.GEOMETRY.STAsText(),4326)
FROM [dbo].[v_sign])
DECLARE @GEO GEOGRAPHY = (select Geography from dbo.water)
select *
from v_sign b
inner join REFERENCE.dbo.water a
on b.ID <> a.OID
and (@sign.STIntersects(@GEO.STBuffer(1000)) = 1)
This returns 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'
May 27, 2014 at 10:00 am
Johnny H (5/27/2014)
Here is what I have tried. Setting GEOMETRY to GEOGRAPHY, then a inner join, I have also tried with a union with the same error results.DECLARE @sign GEOGRAPHY = (SELECT GEOGRAPHY::STGeomFromText(v_well.GEOMETRY.STAsText(),4326)
FROM [dbo].[v_sign])
DECLARE @GEO GEOGRAPHY = (select Geography from dbo.water)
select *
from v_sign b
inner join REFERENCE.dbo.water a
on b.ID <> a.OID
and (@sign.STIntersects(@GEO.STBuffer(1000)) = 1)
This returns 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'
The parameter in STBuffer is the distance in meters, this means that the function will build another geography instance, normally more complex (in number of points) than the original. To avoid this extra work, you are probably better off using something like this:
😎
select *
from v_sign b
CROSS APPLY REFERENCE.dbo.water a
WHERE @sign.STDistance(@GEO) < 100;
May 27, 2014 at 1:24 pm
Hi
I've had a look at a few options and for the data the I'm testing on, this appears to be the best solution (for SQL 2012 +). It is a variation of the nearest neighbour query structure.
Note: There are some strict rules to make this work:
1. You must have a spatial index present on one of the columns (sort of goes without saying)
2. The TOP statement can't be a percentage (I haven't put one in)
3. The WHERE clause must contain a STDistance expression
4. The STDistance part of the WHERE clause can't be optional (part of an OR)
5. STDistance calls that result in NULL must be filtered out
6. The STDistance expression must appear first in the ORDER BY clause and be ascending. The ORDER BY clause has to be used
-- Assuming that both spatial objects are Geographies with the same SRID
-- You will need to change the SHAPE column name to the column names you are using
-- This query on my machine took 4 mins to return 82427 rows from 872000 lines and 166000 points
SELECT *
FROM v_sign p, -- Is this a view?
REFERENCE.dbo.water l
WHERE l.SHAPE.STDistance(p.SHAPE) <= 100 and
l.SHAPE.STDistance(p.SHAPE) is not null
ORDER BY l.SHAPE.STDistance(p.SHAPE);
-- To convert the signs to a Geography in the query
-- Things to note. This will mean that only the spatial index on the water table will be used.
-- There will be an overhead converting to Geography
SELECT *
FROM REFERENCE.dbo.water l,
(SELECT other columns, Geography::STGeomFromText(Shape.STAsText(),4326) SHAPE FROM v_sign) p
WHERE l.SHAPE.STDistance(p.SHAPE) <= 100 and
l.SHAPE.STDistance(p.SHAPE) is not null
ORDER BY l.SHAPE.STDistance(p.SHAPE);
If the Geometry in v_signs is in Lat/Long coordinates then it should really be using Geography.
May 27, 2014 at 1:38 pm
mickyT (5/27/2014)
HiI've had a look at a few options and for the data the I'm testing on, this appears to be the best solution (for SQL 2012 +). It is a variation of the nearest neighbour query structure.
Note: There are some strict rules to make this work:
1. You must have a spatial index present on one of the columns (sort of goes without saying)
2. The TOP statement can't be a percentage (I haven't put one in)
3. The WHERE clause must contain a STDistance expression
4. The STDistance part of the WHERE clause can't be optional (part of an OR)
5. STDistance calls that result in NULL must be filtered out
6. The STDistance expression must appear first in the ORDER BY clause and be ascending. The ORDER BY clause has to be used
-- Assuming that both spatial objects are Geographies with the same SRID-- You will need to change the SHAPE column name to the column names you are using
-- This query on my machine took 4 mins to return 82427 rows from 872000 lines and 166000 points
SELECT *
FROM v_sign p, -- Is this a view?
REFERENCE.dbo.water l
WHERE l.SHAPE.STDistance(p.SHAPE) <= 100 and
l.SHAPE.STDistance(p.SHAPE) is not null
ORDER BY l.SHAPE.STDistance(p.SHAPE);
-- To convert the signs to a Geography in the query
-- Things to note. This will mean that only the spatial index on the water table will be used.
-- There will be an overhead converting to Geography
SELECT *
FROM REFERENCE.dbo.water l,
(SELECT other columns, Geography::STGeomFromText(Shape.STAsText(),4326) SHAPE FROM v_sign) p
WHERE l.SHAPE.STDistance(p.SHAPE) <= 100 and
l.SHAPE.STDistance(p.SHAPE) is not null
ORDER BY l.SHAPE.STDistance(p.SHAPE);
If the Geometry in v_signs is in Lat/Long coordinates then it should really be using Geography.
Thank you for this, didn't have time to go to this extent, appreciate it. Would be nice to see a write-up on it!
😎
May 27, 2014 at 1:45 pm
Hi Everyone
The STDistance will not work well because it will cause too many calculation. I am going to look into the Nearest Neighbor method.
May 27, 2014 at 2:00 pm
Johnny H (5/27/2014)
Hi EveryoneThe STDistance will not work well because it will cause too many calculation. I am going to look into the Nearest Neighbor method.
The trick is to make sure the spatial index is used, otherwise you will end up with a cross join. On my test data that would have been 144,752,000,000 expensive STDistance calculations. I would hate to imagine how long that would have taken:w00t:
I was interested to see the v_ prefix to the signs table. Does this mean it is a view? If so, does it build the geometry on the fly?
May 28, 2014 at 10:27 am
Johnny H (5/27/2014)
Here is what I have tried. Setting GEOMETRY to GEOGRAPHY, then a inner join, I have also tried with a union with the same error results.DECLARE @sign GEOGRAPHY = (SELECT GEOGRAPHY::STGeomFromText(v_well.GEOMETRY.STAsText(),4326)
FROM [dbo].[v_sign])
DECLARE @GEO GEOGRAPHY = (select Geography from dbo.water)
select *
from v_sign b
inner join REFERENCE.dbo.water a
on b.ID <> a.OID
and (@sign.STIntersects(@GEO.STBuffer(1000)) = 1)
This returns 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'
This error occurred because you are trying to set a variable (which can hold only a scalar value) to the result of a query that returns multiple rows. That's just a basic T-SQL mistake that has nothing to do with the spatial aspect of your requirement.
I haven't tested this solution, but it should work pretty well with a spatial index on the dbo.water table:
SELECT 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)
Jason Wolfkill
May 28, 2014 at 2:58 pm
Thank Jason this increased the speed 10 fold
SELECT 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)
May 28, 2014 at 3:11 pm
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.
May 28, 2014 at 9:22 pm
Quick thought, if the "signs" are points then I would have thought that the STContains function would be more appropriate.
😎
May 29, 2014 at 8:09 am
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!
Jason Wolfkill
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply