SQL 2008 Spatial Samples, Part 8 of 9 - Conditional Methods
This post covers the following methods: STDisjoint, STIntersects, Filter, STTouches, STWithin, STContains, STOverlaps, STCrosses, STRelate, & STEquals
One day these examples may find their way to Books Online. For now they can live here.
Overview of Conditional Methods
You will find that these Methods exceptionally handy to find or filter out similar spatial shapes. They are extremely efficient when a suitable spatial index exists.
I'm not saying these methods can ONLY be used in a WHERE clause. It is often handy to combine them with a CASE clause in the select list. However I wanted to group sets of Methods together & this seemed an appropriate name.
These methods Return a boolean: 1 (true) or 0 (false).
Tip: They take advantage of Spatial Index to retrieve relevant rows quickly. Especially if you ask for what you want, so searching for Equality works well, but filtering on not equals, often means the index will not help you. Most methods have a opposite ie: STDisjoint is the opposite of STIntersects. So instead of using "not equals" change to the reciprocal method.
The following code sample lets you explore the GIS Methods. Just Cut n Paste into SQL Server Management Studio (SSMS) 2008 (or higher).
Note 1: If your data has POINTs then the SSMS Spatial viewer disables the ability to display labels. While this is nice if you have a few polygons with names, covers in a lot of points, is it inconvenient here. So comment out the last 3 Union Alls if you want show the labels of your shapes & lines. You can still hover over them to see ToolTip labels on points.
Note 2: Usually you would not have the @S0 shape in the set of shapes you are querying. So sometimes @S0 is in the results where normally it wouldn’t be. I've left it in so you can see the relationship with it & the object you are comparing against it.
-- ===< Samples: TSQL Spatial Conditional Methods as a filter >===
DECLARE @s0 AS GEOMETRY = 'POLYGON((4 4, 7 4, 7 7, 4 7 ,4 4))' -- Main Shape
DECLARE @s1 AS GEOMETRY = 'POLYGON((5 4.2, 6 4.2, 6 5, 5 5, 5 4.2))' -- Inside Shape
DECLARE @s2 AS GEOMETRY = 'POLYGON((5 3, 6 3, 6 4, 5 4, 5 3))' -- Part Edge in Common
DECLARE @s3 AS GEOMETRY = 'POLYGON((7.2 4, 8 4, 8 5, 7.2 5, 7.2 4))' -- Outside Shape
DECLARE @t1 AS GEOMETRY = 'POLYGON((3 4, 4 4, 4 3, 3 4))' -- 2 Corners in Common
DECLARE @t2 AS GEOMETRY = 'POLYGON((3 5, 4 5.5, 3 6, 3 5))' -- Corner on an Edge
DECLARE @t3 AS GEOMETRY = 'POLYGON((6.5 5, 7.5 5.5, 6.5 6, 6.5 5))' -- Overlap
DECLARE @L1 AS GEOMETRY = 'LINESTRING(5 6.5, 6.5 6.5)' -- Line in Shape
DECLARE @L2 AS GEOMETRY = 'LINESTRING(4.5 3, 4.5 7.5)' -- Line Thru Shape
DECLARE @L3 AS GEOMETRY = 'LINESTRING(3 6.5, 4.2 6.5)' -- Line cross Edge of Shape
DECLARE @P1 AS GEOMETRY = 'POINT(6.5 4.5)' -- Point in Shape
DECLARE @P2 AS GEOMETRY = 'POINT(4 5)' -- Point on Edge
DECLARE @P3 AS GEOMETRY = 'POINT(6.5 3.5)' -- Point off Shape
SELECT * -- Make all variables appear as the rows of one table
FROM (
SELECT @s0 AS 'Geo', 'S0' as 'Labels'
UNION ALL SELECT @s1, 'S1'
UNION ALL SELECT @s2, 'S2'
UNION ALL SELECT @s3, 'S3'
UNION ALL SELECT @t1, 'T1'
UNION ALL SELECT @t2, 'T2'
UNION ALL SELECT @T3, 'T3'
UNION ALL SELECT @L1, 'L1'
UNION ALL SELECT @L2, 'L2'
UNION ALL SELECT @L3, 'L3'
-- As Labels don't display in SSMS when you have Points in output.
-- Either Comment out the next 3 lines if you want to see the labels in SSMS.
-- or put a buffer around them so they display as polygons.
UNION ALL SELECT @P1.STBuffer(0.1), 'P1'
UNION ALL SELECT @P2.STBuffer(0.1), 'P2'
UNION ALL SELECT @P3.STBuffer(0.1), 'P3'
) AS A
--Uncomment ONE of the lines below to test the effect of each different where clause
-- WHERE A.Geo.STTouches(@s0) = 1
-- WHERE A.Geo.STWithin(@s0) = 1
-- WHERE A.Geo.STCrosses(@s0) = 1
-- WHERE A.Geo.STContains(@S1) = 1
-- WHERE A.Geo.STOverlaps(@s0) = 1
-- WHERE A.Geo.STIntersects(@s0) = 1
-- WHERE A.Geo.STDisjoint(@s0) = 1
When you run the code all the objects display as shown in this diagram.
Use this as a baseline when we look at the results of the different filters below.
Technorati Tags: SQL Server Spatial,SQL Server,SQL Server 2008
Method .STDisjoint()
- Spatial Objects DO NOT touch each other.
- Works with both Geometry & Geography objects
- Opposite method of .STIntersects & .Filter Methods.
Example use: Show all customers who live outside our standard delivery area
WHERE A.Geo.STDisjoint(@s0) = 1
Yellow highlights all objects which are Disjoint from Shape S0
Method .STIntersects()
- Spatial Objects DO touch each other.
- Works with both Geometry & Geography objects
- Opposite of .STDisjoint Method.
- Similar to .Filter
- To use spatial index search on WHERE geo1.STIntersects ( geo2 ) = 1
Example use: List cancer patients who live within 1km of a high tension power line.
WHERE A.Geo.STIntersects(@s0) = 1
Yellow highlights all objects which Intersect with Shape S0
Method .Filter()
Spatial Objects DO touch each other.
Works with both Geometry & Geography objects
Opposite of .STDisjoint Method.
Similar to .STIntersects() but :-
- Less accurate sometimes returns false positive results. Eg: Says they intersect when they do not.
- often much faster if a relevant spatial index exists.
So, Great for getting results to display not so good for accurate analysis. Eg: Find all shapes in this rectangle (my window). If you get back a few extra shapes that are outside the rectangle then they will be clipped when you try to draw them outside the window.
Example use: Show cancer patients who live within 1km of a high tension power line.
-- ===< Sample: Using FILTER to rapidly get relevant results >==== USE someDB -- This will give you an error, reminding you not to create it in Master :-) go -- ==< Create a Table with a spatial Index >=== CREATE TABLE dbo.GeoTable (id INT PRIMARY KEY, geom GEOGRAPHY); INSERT INTO GeoTable VALUES (0, GEOGRAPHY::Point(45, -120, 4326)), (1, GEOGRAPHY::Point(45, -120.1, 4326)), (2, GEOGRAPHY::Point(45, -120.2, 4326)), (3, GEOGRAPHY::Point(45, -120.3, 4326)), (4, GEOGRAPHY::Point(45, -120.4, 4326)); CREATE SPATIAL INDEX GeoTable_idx ON dbo.GeoTable(geom); -- ==< Example: The Filter Query >== -- Remember to look in the SSMS Spatial Results tab for see the relationships here SELECT id FROM dbo.GeoTable WHERE geom.Filter(GEOGRAPHY::Parse('POLYGON((-120.2 44.9, -119.9 44.9, -119.9 45.1, -120.1 45.1, -120.2 44.9))')) = 1; -- Show the Geo Data -- SELECT geom.STBuffer(2000) AS Geom, Cast(id AS VARCHAR(4)) AS 'Labels' FROM dbo.GeoTable UNION ALL SELECT GEOGRAPHY::Parse('POLYGON((-120.2 44.9, -119.9 44.9, -119.9 45.1, -120.1 45.1, -120.2 44.9))') AS SD, 'Poly'; go DROP TABLE dbo.GeoTable go
Method .STTouches(Geometry object)
The edges of the Spatial Objects touch but their interiors don’t overlap each other. For points this means they are on the border of an object.
Works with Geometry objects only. It is not a method of Geography Objects.
To use spatial index search on WHERE geo1.STTouches ( geo2 ) = 1
Example use: List all suburbs next to Suburb X. Part of a larger query to find homes for sale in or near this suburb.
WHERE A.Geo.STTouches(@s0) = 1
Yellow highlights all objects which touch Shape S0
Method .STWithin()
- This Spatial Object is completely contained within another.
- Works with Geometry objects only. It is not a method of Geography Objects.
- Sister method of .STContains
- To use spatial index search on WHERE geo1.STWithin ( geo2 ) = 1
Example use: Find all homes within 3km of the Bush Fire zone. Would also need STBuffer to expand the Bush Fire Zone shape by 3km.
WHERE A.Geo.STWithin(@s0) = 1
Yellow highlights all objects which are completely inside Shape S0
Method .STContains()
This Spatial Object is completely surrounded by another.
Works with Geometry objects only. It is not a method of Geography Objects.
Sister method of .STWithin, but asks the question from the other perspective.
To use spatial index search on WHERE geo1.STContains ( geo2 ) = 1
Example use: Find the polygon (postcode) that the mouse cursor is currently hovering over. You'd need to convert the mouse value into a point, & see what shape contains that point.
I've combined the display of 3 queries into one diagram. ie:-
WHERE L1. STContains(@S0) = 1
OR S1. STContains(@S0) = 1
OR P1. STContains(@S0) = 1
Yellow highlights all objects which are contained by Shape S0
Note: This query is different, all others use the Geo Column & compare it to the shape S0. These queries use the STContains method on the shape we are interested in & compare it to shape S0
Method .STOverlaps()
True when this Shapes partly lie on other shapes OR lines cross.
Works with Geometry objects only. It is not a method of Geography Objects.
Sister method of .STCrosses, but only works when objects are the same type: ie Shapes with shapes, Lines with lines.
To use spatial index search on WHERE geo1.STOverlaps ( geo2 ) = 1
Example use: Show the Sales Territories where it is not clear who "owns" a new customer.
WHERE A.Geo.STOverlaps(@s0) = 1
Yellow highlights all Polygons which lie over the border of Shape S0
Method .STCrosses()
True when a Line overlaps a Polygon OR A Point lies on a line. ie: Something of a lesser Dimension (1D - Line or 0D - Point)
Works with Geometry objects only. It is not a method of Geography Objects.
Sister method of .STCrosses, but only works when objects are different dimensions. So
Why not T3? Because the intersection is still a 2D Object, use STOverlaps() for this.
WHERE A.Geo.STCrosses(@s0) = 1
Yellow highlights all Lines & Points which lie over the border of Shape S0
-- Sample 2: Show a Conditional method being used in a Select list --- -- Also show STCrosses() working with lines & Points DECLARE @L as GEOMETRY = GEOMETRY::STGeomFromText('LINESTRING(1 0, 5 0)',0); DECLARE @Pon as GEOMETRY = GEOMETRY::STGeomFromText('POINT(2 0)',0); DECLARE @Pend as GEOMETRY = GEOMETRY::STGeomFromText('POINT(5 0)',0); DECLARE @Poff as GEOMETRY = GEOMETRY::STGeomFromText('POINT(3 1)',0); -- Test STCrosses SELECT geo, WKT, Labels, CASE @L.STCrosses(geo) WHEN 0 THEN 'STCrosses is FALSE' WHEN 1 THEN 'STCrosses is TRUE' END AS STCrosses FROM ( SELECT @L as geo, @L.ToString() as WKT, 'L1' as 'Labels' UNION ALL SELECT @Pon.STBuffer(0.1), @Pon.ToString(), 'Point on Line' UNION ALL SELECT @Pend.STBuffer(0.1), @Pend.ToString(), 'Point on End of Line' UNION ALL SELECT @Poff.STBuffer(0.1), @Poff.ToString(), 'Point not on Line' ) AS TResults:
WKT
Labels
STCrosses
LINESTRING (1 0, 5 0) Line1 STCrosses is FALSE POINT (2 0) Point on Line1 STCrosses is TRUE POINT (5 0) Point on End of Line1 STCrosses is TRUE POINT (3 1) Point not on Line1 STCrosses is FALSE
Map showing the 3 Points & 1 Linestring
Method .STRelate( ,Intersection Matrix)
This Spatial Object has whatever relationship you describe in the Intersection Matrix.
Works with Geometry objects only. It is not a method of Geography Objects.
It is a superset of all the other methods described in this blog & easily deserves a post dedicated to it. It could easily replace all the “Conditional methods” in this blog eg: .STDisjoint(), STTouches() etc
As there are already good articles available on the web, I will provide a superficial coverage here, for more detail I suggest you look at the following. ESRI's Developer Help: Understanding Spatial Relations
What is an Intersection Matrix?
The 2nd Parameter that you provide is an Intersection Matrix.
An Intersection Matrix short for a Dimensionally Extended 9 Intersection Model (DE-9IM). This is a 3 * 3 grid that represents the possible intersections between 2 shapes. Each shape is divided into 3 parts; Interior, Boundary & Exterior & results in a grid below.
1. All possible intersections between 2 shapes; Shape "a" & Shape "b"
Interior (b) Boundary (b) Exterior (b) Interior (a) I(a) ? I(b) I(a) ? B(b) I(a) ? E(b) Boundary (a) B(a) ? I(b) B(a) ? B(b) B(a) ? E(b) Exterior (a) E(a) ? I(b) E(a) ? B(b) E(a) ? E(b)
2. Each of the 9 possible intersections can have one of 6 possible values.
Value Comments Maximum Dimensionality T An intersection must exist dim = 0, 1, or 2 F An intersection must not exist dim = -1 * It does not matter if an intersection exists or not dim = -1, 0, 1, or 2 0 An intersection must exist and its maximum dimension must be 0 dim = 0 1 An intersection must exist and its maximum dimension must be 1 dim = 1 2 An intersection must exist and its maximum dimension must be 2 dim = 2 For example, the intersection matrix for the STWithin method would look like:-
b Interior (b) Boundary (b) Exterior (b) Interior (a) T * F a Boundary (a) * * F Exterior (a) * * * ie: A must be inside B, But A's Interior & Boundary can not be external to B.
This is then mapped to 9 characters representing <top line><Middle><bottom Line>. Which in this case is "T*F**F***".
Experiment by changing the value in the code below, or combine it into the main code set at the top of this blog.
-- Create Shapes DECLARE @Main as GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((10 10, 20 20, 10 30, 0 20, 10 10))',0); DECLARE @S_In as GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((4 20, 16 20, 10 26, 4 20))',0); DECLARE @S_On as GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((12 12, 12 18, 18 18, 18 12, 12 12))',0); DECLARE @S_Out as GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 10, 8 10, 0 18, 0 10))',0); -- Intersection Matrix DECLARE @Matrix as CHAR(9) = 'T*F**F***' --'FF*FF****' -- Use STRelate in a query -- SELECT geom, Labels, CASE geom.STRelate(@Main,@Matrix) WHEN 0 THEN 'FALSE' WHEN 1 THEN 'TRUE' END AS [STRelate] FROM ( SELECT @Main as geom, 'Main' as 'Labels' UNION ALL SELECT @S_In, 'S_In' UNION ALL SELECT @S_On, 'S_On' UNION ALL SELECT @S_Out, 'S_Out' ) As Tab1;
Method .STEquals()
Spatial Objects represent the same set of points as each other.
Works with both Geometry & Geography objects
To use spatial index search on WHERE geo1.STEquals ( geo2 ) = 1
Be careful with this Method it is not as straightforward as you may first think. The following are all considered equal.
At its most trivial, two polygons may be comprised of the same points but have a different starting point.
- eg: A triangle may have the same 3 points. But if you start at a different point you will have the same triangle but the points will appear in a different order. Clearly that same logic applies to more complex shapes
A little less obvious. One shape can have more points than the other. So long as the extra points are still on the boundary of the other.
- eg: LineString(10 10, 10 20) equals LineString(10 10, 10 15, 10 20)
A more subtle issue is in GeometryCollections, this function seem to ignore anything in the interior. So if the external polygons are the same then it doesn't seem to matter if there are extra lines, points, polygons contained within each one.
More info
For more info see SQL Server 2008 Books Online Geometry Methods Supported by Spatial Indexes
Please give feedback. Is this series of articles useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.
Technorati Tags: SQL Spatial,SQL Server Spatial,Spatial,Geography,Geometry,TSQL,.NET