SQL 2008 Spatial Samples, Part 9 of 9 - Handy but Obvious Methods
This post covers the following methods: STArea, STLength, STDistance, STIsEmpty, STIsClosed, STIsSimple, STIsRing.
Overview of Handy but obvious Methods
The following methods are self evident. I’ve included here for the sake of completeness. You do need to be aware of them, They are great for calculating & displaying attributes associated with your maps.
Method .STArea()
- Area of a Polygon.
- Total area of a MultiPolygon.
- The Area for Lines & Points = 0.
- Exceptionally useful with Geography types as you can quickly calculate land area without the huge distortion usually associated with Flat Earth.
- Note: even Geography is not perfect, the ellipsoidal approximation does have some error. It is good if you want the area "as the crow flies" or have a fairly flat flood plain but doesn't allow for the steep sides of hills & valleys. If you are spraying fertilizer & need to know the actual surface area of the land in square meters, you'd need to allow for the vertical changes.
- Works with both Geometry & Geography objects
Example use: Show Population Density. ie: People per square mile.
-- =====< Sample: Area example >================= DECLARE @g GEOMETRY; SET @g = GEOMETRY::STGeomFromText('MULTIPOLYGON (((5 5, 8 5, 8 8, 5 8, 5 5)), ((0 0, 3 0, 3 3, 0 3, 0 0), (0.25 0.25, 0.25 0.75, 0.75 0.75 , 0.75 0.25, 0.25 0.25), (1 1, 1 2, 2 2, 2 1, 1 1)))', 0); SELECT @g.STArea() AS [Area], @g.STAsText() AS WKT, @g AS Geo go
The display of the MultiPolygon, Area = 16.75
Method .STLength()
- Length of a Line &/or Perimeter of a Polygon.
- Total length of all shapes in a GeometryCollection.
- Length of a Point = 0.
- Works with both Geometry & Geography objects
- Example use:
What is the driving distance from A to B. ie: Find the distance (length) of a line.
- SQL 2008 doesn't have functions to help you decide on what is the shortest route. For this release, Use MapPoint or Virtual Earth Web service for that type of spatial calculation.
Handy to improve performance by filtering out objects whose length is 0. ie: Use with STReduce to filter out Objects whose size has been Reduced to 0.
Could use it to find points. (as Lines & Polygons have non-zero length)
Good for finding the perimeter of a shape.
Method .STDistance()
- Distance between the closest parts of 2 Spatial Objects.
- This is FANTASTIC as a filter, but make sure you ask for items "Less Than" or Less Than or Equal To" a distance. "Greater than" will not use the spatial index.
- Works with both Geometry & Geography objects
Example use: List all Points (Students) within walking distance 3km of this point (the School)
As the August 08 release of Books Online states that Geography object can only calculate the distance if at least one of the objects is a point, I've included copious examples to show this is incorrect. Both Geometry & Geography can calculate the distance between any combination of spatial objects. (To be fair to the documentation team this was a limitation at one point while the product was in development. BOL will be corrected in the Dec 08 release)
-- ====< Geometry Distance function example >=============== DECLARE @g GEOMETRY; SET @g = GEOMETRY::STGeomFromText('MULTIPOLYGON( (( 5 5, 8 5, 8 8, 5 8,5 5)) ,((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2) ,(0.25 0.25, 0.75 0.25, 0.75 0.75, 0.25 0.75, 0.25 0.25)) )', 0); DECLARE @L1 AS GEOMETRY = 'LINESTRING(0 4, 8 3 )' DECLARE @P1 AS GEOMETRY = 'POINT(2 6)' -- Distance Polygon to Line SELECT 'MultiPolygon to Line' AS Comment, @g.STDistance(@L1) AS 'Shortest Distance' ,@g.STAsText()AS WKT, @g AS GEOMETRY UNION ALL -- Distance Line to Point SELECT 'Line to Point', @L1.STDistance(@P1), @L1.STAsText(), @L1 UNION ALL -- Distance Point to Polygon SELECT 'Point to Polygon', @P1.STDistance(@g), @P1.STAsText(), @P1.STBuffer(0.1) go-- ====< Geography Distance function example >=========================== -- Note: Books Online states that Geography STDistance is limited & -- can only show distance if at least one of the objects is a Point. This is false. -- As can be seen below it works with any combination of objects. -- This Error will be corrected in the next release of Books Online. DECLARE @g GEOGRAPHY; DECLARE @S1 GEOGRAPHY; DECLARE @L1 GEOGRAPHY; DECLARE @L2 GEOGRAPHY; DECLARE @P1 GEOGRAPHY; DECLARE @P2 GEOGRAPHY; --SET @g = GEOGRAPHY::STGeomFromText('MULTIPOLYGON( (( 5 5, 8 5, 8 8, 5 8,5 5)) -- ,((0 0, 3 0, 3 3, 0 3, 0 0), (2 2, 2 1, 1 1, 1 2, 2 2) -- ,(0.25 0.25, 0.25 0.75, 0.75 0.75, 0.75 0.25, 0.25 0.25)) )', 4326) -- Exterior --SET @g = GEOGRAPHY::STGeomFromText('POLYGON( (2 2, 2 1, 1 1, 1 2, 2 2) )', 4326) -- Interior (Clockwise points) --SET @g = GEOGRAPHY::STGeomFromText('POLYGON( (2 2, 1 2, 1 1, 2 1, 2 2) )', 4326) -- Exterior (AntiClockwise: "Look to the Left" of the direction you are drawing) SET @g = GEOGRAPHY::STGeomFromText('POLYGON( (0 0, 3 0, 3 3, 0 3, 0 0) ,(2 2, 2 1, 1 1, 1 2, 2 2), (0.25 0.25, 0.25 0.75 ,0.75 0.75, 0.75 0.25, 0.25 0.25))', 4326) -- Exterior SET @S1 = GEOGRAPHY::STGeomFromText('POLYGON(( 5 5, 8 5, 8 8, 5 8,5 5))', 4326) -- Exterior SET @L1 = GEOGRAPHY::STGeomFromText('LINESTRING(0 4, 8 3 )', 4326); SET @L2 = GEOGRAPHY::STGeomFromText('LINESTRING(0 6, 2 6 )', 4326); SET @P1 = GEOGRAPHY::STGeomFromText('POINT(2 6)', 4326); SET @P2 = GEOGRAPHY::STGeomFromText('POINT(1 7)', 4326); -- Distance Polygon to Line SELECT 'MultiPolygon to Line' as Comment, @g.STDistance(@L1) as 'Shortest Distance', @g.STAsText()as WKT, @g as Geo UNION ALL -- Distance Line to Point SELECT 'Line to Point', @L1.STDistance(@P1), @L1.STAsText(), @L1 UNION ALL --Distance Point to Polygon SELECT 'Point to Polygon', @P1.STDistance(@g), @P1.STAsText(), @P1.STBuffer(20000) UNION ALL --Distance Polygon to Polygon SELECT 'Polygon to Polygon', @S1.STDistance(@g), @S1.STAsText(), @S1 UNION ALL --Distance Point to Point SELECT 'Point to Point', @P2.STDistance(@P1), @P2.STAsText(), @P2.STBuffer(20000) UNION ALL --Distance Line to Line SELECT 'Line to Line', @L2.STDistance(@L1), @L2.STAsText(), @L2 go
Method .STIsEmpty()
Returns: 1 (true) if the Spatial Object contains no value. (obvious huh?).
- It is 0 (false) if the object is NULL or contains some points.
Note: EMPTY is different to NULL.
Works with both Geometry & Geography objects
-- < Empty means no points, different to NULL >== DECLARE @temp TABLE (Shape VARCHAR(10), [geom] GEOMETRY); INSERT INTO @temp VALUES ('Nothing', NULL) ,('Im Empty', 'POLYGON EMPTY') ,('Point', 'POINT(3 3)') ,('LineString', 'LINESTRING(0 0, 3 3)') ,('Polygon', 'POLYGON((0 0, 3 0, 0 3, 0 0))'); SELECT Shape, [geom].STDimension() as [STDimension] FROM @temp WHERE geom.STIsEmpty() = 1;
Method .STIsClosed()
- Returns: 1 (true) if the Start & End points of a Line the same
- Polygons are always closed.
- Points are always open.
- GeometryCollections are Closed if all their shapes are closed. eg: No Points & no open lines.
- Works with both Geometry & Geography objects
Method .STIsSimple()
Returns: 1 (true) if the Object doesn’t cross over itself
- Ie: No figures overlap each other. They can touch.
Works with Geometry objects only. It is not a method of Geography Objects.
Method .STIsRing()
Returns: 1 (true) if the spatial object is :-
- (A) a Line, and
- (B) Simple and
- (C) Closed
- Ie: A line that forms a closed shape & doesn’t cross itself.
Works with Geometry objects only. It is not a method of Geography Objects.
Handy if you want to covert the Line into a Polygon, can quickly determine if it is suitable as the perimeter.
Can also be used to filter out both Points & Polygons.
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