Udostępnij za pośrednictwem

SQL 2008 Spatial Samples, Part 9 of 9 - Handy but Obvious Methods

  • Artykuł

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

Area

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

Comments

  • Anonymous
    November 30, 2008
    Comprehensive is not the word! How long have you been doing GIS? ;-)

  • Anonymous
    December 02, 2008
    The comment has been removed

  • Anonymous
    June 01, 2009
    This is an excellent tutorial, thanks for posting it! One thing that might help improve it would be some discussion of how spatial indexes are used.  It would be great if there were a spatial dataset akin to the Northwind or AdventureWorks we could use as a standard reference.  That way you could illustrate alternative approaches to performing spatial joins between large tables.  I noticed improvement by hinting the spatial index, but maybe there's something I could configure in the index?  

  • Anonymous
    April 23, 2013
    The comment has been removed

  • Anonymous
    August 09, 2015
    I need distance in miles of two GEOMetry, not two GEOGraphy.  What are the fastest solutions for this situation?