SQL 2008 Spatial Samples, Part 8 of 9 - Conditional Methods

發行項
10/24/2008

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 T

 Results:

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

Comments

Anonymous
February 23, 2009
An excellent article on the subject matter.
Anonymous
June 11, 2009
ヒマだょ…誰かかまってぉ…会って遊んだりできる人募集！とりあえずメール下さい☆　uau-love@docomo.ne.jp
Anonymous
June 12, 2009
話題の小向美奈子ストリップを隠し撮り！入念なボディチェックをすり抜けて超小型カメラで撮影した神動画がアップ中！期間限定配信の衝撃的映像を見逃すな
Anonymous
June 12, 2009
話題の小向美奈子ストリップを隠し撮り！入念なボディチェックをすり抜けて超小型カメラで撮影した神動画がアップ中！期間限定配信の衝撃的映像を見逃すな
Anonymous
June 13, 2009
カワイイ子ほど家出してみたくなるようです。家出掲示板でそのような子と出会ってみませんか？彼女たちは夕食をおごってあげるだけでお礼にHなご奉仕をしてくれちゃったりします
Anonymous
June 14, 2009
あなたは右脳派？もしくは左脳派？隠されたあなたの性格分析が3分で出来ちゃう診断サイトの決定版！合コンや話のネタにも使える右脳左脳チェッカーを試してみよう
Anonymous
June 15, 2009
The comment has been removed
Anonymous
June 16, 2009
セレブ達は一般の人達とは接する機会もなく、その出会う唯一の場所が「逆援助倶楽部」です。男性はお金、女性はSEXを要求する場合が多いようです。これは女性に圧倒的な財力があるから成り立つことの出来る関係ではないでしょうか？
Anonymous
June 17, 2009
貴方のオ○ニーライフのお手伝い、救援部でHな見せたがり女性からエロ写メ、ムービーをゲットしよう！近所の女の子なら実際に合ってHな事ができちゃうかも！？夏に向けて開放的になっている女の子と遊んじゃおう
Anonymous
June 18, 2009
まったぁ〜りしたデートがしたいです☆結構いつでもヒマしてます♪　m-g-j@docomo.ne.jp　年齢と名前くらいは入れてくれるとメール返信しやすいかも…
Anonymous
February 05, 2014
Thank you very much for your efforts Dave. The illustrations really help in understanding this topic. Outstanding job!
Anonymous
February 04, 2015
Thank you very much You solve a big problem

共用方式為