Partager via

SQL 2008 Spatial Samples, Part 2 of 9 - Background on Spatial Types & Well Known Text (WKT)

  • Article

This post covers all the Methods to enter data in WKT, WKB & XML(GML) as well as functions to view the in Human Readable form. It also covers MakeValid, STIsValid & STSrid.

 

Summary of Methods to convert Geometric Formats

The following table shows all the Methods create or import a spatial object from; Well Known Text (WKT), Well Known Binary (WKB) or Geographic Markup Language (GML) formats.

  GML (XML) Well Known Text WKT Well Known Binary
Point   .STPointFromText() .STPointFromWKB()
MultiPoint   .STMPointFromText() .STMPointFromWKB()
Line   .STLineFromText() .STLineFromWKB()
MultiLine   .STMLineFromText() .STMLineFromWKB()
Polygon   .STPolyFromText() .STPolyFromWKB()
MultiPolygon   .STMPolyFromText() .STMPolyFromWKB()
Geometry .GeomFromGml() .STGeomFromText() .STGeomFromWKB()
GeometryCollection   .STGeomCollFromText() .STGeomCollFromWKB()

The following table shows all the Methods to export a spatial object into WKT, WKB or Geographic Markup Language (GML) format.

Display as ... GML (XML) Well Known Text WKT Well Known Binary
    .STAsText() .STAsBinary()
  .AsGml() .AsTextZM()  
    .ToString()  

 

Overview - Background on “What” you are loading

This post & the next give you a background in what Spatial Data is & the methods available to Import/Export it.

  • Basic Geometry Items (Points, Lines etc)
  • Methods to display Well Known Text (WKT)
  • Tips on writing WKT
  • Methods to Load Spatial Data
    • WKT, WKB & GML
    • SQL Builder API  (my next post)

Basic Geometry Items

Everything is created from 3 core types; Point, Linestring & Polygon.
GeometryTypes

Spatial objects can comprise of multiple groups of the same object included in one  object. These are the 3 "Multi-" Shapes. They could also be a combination of any & all of the shapetypes, including Multi-shape types. These are called GeometryCollections.

They can be converted to a human readable form called Well Known Text (WKT). I've listed all 7 types in the table below.
WKT Examples 

Tip: Valid WKT Syntax (4 Forms)

There are 4 different syntax variations which you can use to assign Well-Known-Text to a spatial type.

Note: There is no performance difference between them. Other then a type check they all use the same base code. The number on the end is a Spatial Reference ID (SRID) discussed further in this post.

1.  Precise Spatial method

This is OGS compliant, lets you set the SRID & does syntax checking to ensure that you only load the spatial type it is designed to handle. ie: STPolyFromText only handles WKT that defines a polygon.  See the table at the start of this post for a list of the methods & the specific geographic objects they handle.

Advantage: By preventing you from accidentally loading the wrong spatial object into a table. It may reduce errors your users might experience.

 SET @g = GEOMETRY::STPolyFromText('POLYGON( (0 0, 30 0, 30 30, 0 30, 0 0)) ',4326);

2.  Generic Spatial method

This is OGS compliant, lets you set the SRID & doesn't enforce a specific geometric type. Loads anything that can be in a GeometryCollection.

Advantage: Easier to write generic code that can load any spatial object. Also handy if you are just experimenting with code & want to quickly try different inputs.

 SET @g = GEOMETRY::STGeomFromText('POLYGON( (0 0, 30 0, 30 30, 0 30, 0 0)) ',4326);

3. Short Format

This is a side effect of the routines being developed as a SQL CLR Type. They need to have a default method to initialise them & so use the STGeomFromText.

Advantage: Less to type. Less clutter to read.

Disadvantage: You can't specify the SRID. It uses the default; Geometry =0, Geography=4326. Of course you can change it later eg: SET @g.STSrid = 4120

 SET @g = 'POLYGON( (0 0, 30 0, 30 30, 0 30, 0 0) )';

4. Short Format - CLR form

This is also side effect of the routines being developed as a SQL CLR Type. They need to support a Parse Method.

Advantage: Can't think of one.

Disadvantage: You can't specify the SRID. It uses the default; Geometry =0, Geography=4326.

 SET @g = GEOMETRY::Parse('POLYGON( (0 0, 30 0, 30 30, 0 30, 0 0) )'); 

Sample of complete WKT TSQL Query

 -- ===< Creating a Single Polygon using WKT >===
DECLARE @g GEOGRAPHY;
SET @g = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 30 0, 30 30, 0 30, 0 0)) ',4236);
SELECT @g as 'Single Polygon';

 -- ===< Using data in GML format >===
DECLARE @x xml; 
SET @x = '<LineString xmlns="https://www.opengis.net/gml"> <posList>100 100 20 180 180 180</posList> </LineString>';
 
DECLARE @g geometry;
SET @g = geometry::GeomFromGml(@x, 4326);
SELECT @g.ToString() as 'Imported GML';


 See other posts for more examples: Point, MultiPoint, Line, MultiLineString, MultiPolygon, etc

Tip: In SSMS if you give a column an alias you can see its name better in the Spatial Results tab's dropdown list. If you have more then one Spatial column in your result list you will need to use the dropdown list to show the other columns

 

Tip: WKT Syntax - watch those parentheses!

  • Put Points & Lines in Single Parentheses

    • Eg: LINESTRING ( 10 12, 20 12 )

  • Polygons have double Parentheses

    • Eg: POLYGON (( x y, ....., x y ))

    • This is because polygons can have internal “holes”

      • Eg: POLYGON ( ( <outline> ), ( <internal hole1> ) , ..., ( <holeN> ) )

  • The last Point in a Polygon must be the same as the 1st point

    • Eg: POLYGON(( x y, ....., x y))

    • In RTM release only the X & Y values of the points are checked, the Z & M values can be different. This may change in a future release. So best keep Z & M identical too.

  • The "Multi" & GeometryCollections require their own "additional" Parentheses

    • MULTIPOINT ( (0 0), (30 0) )

      • You don't have to put Parentheses around each individual point in a Multipoint object. But it does improve readability.

Tip: WKT Syntax - dots & double dots

  • When you use a method of a Declared variable use "." (a period or full stop)
 -- Sample WKT, Use '.' to call a method on a Variable.
DECLARE @Ln3 AS GEOMETRY = 'LINESTRING(1 5, 5 2)'
SELECT @Ln3.STBuffer(0.2)
SELECT @Ln3.STBuffer(0.2).STAsText() -- Methods called on other methods

  • When you use a method on the Class use "::" (double colon)
 -- Sample WKT, Use '::' to call a method on the Class.
DECLARE @Ln3 AS GEOMETRY = GEOMETRY::STGeomFromText('LINESTRING(1 5, 5 2)', 4326);

 

Tip: Displaying WKT in 2, 3, & 4 Dimensions

All points can be 4 Dimensional (X, Y, Z & M(Measure). The Open Geospatial Consortium spec describes the function names with ST... . This stands for SpatioTemporal, clearly the 4th parameter M was for the Temporal or Time based property. However I've not seen any mention of any Time Dependant aspect of these functions. So you could use it's value represent whatever you want. eg: Perhaps the number of other points in a 5Km radius to this point. Or maybe the Data Mining Cluster this point belongs to.

There are 3 different functions to display the Spatial Types in human readable form (Well Known Text)

  • SELECT @g.AsTextZM()

    • Displays all 4 Dimensions. If they aren't specified or NULL, they aren't displayed.
    • MS Extension, Not OGC Compliant

  • SELECT @g.ToString()

    • Same output as .AsTextZM()
    • Not OGC compliant
    • Exists as due to .NET inheritance from the Base class

  • SELECT @g.STAsText()

    • Display only the X & Y values
    • Is OGC Compliant

 -- ==< Sample showing the 3 WKT display functions, And how Z & M values aren’t returned unless you specifically ask>==

DECLARE @g as Geometry = 'POINT( 4 4 4 5 )';

SELECT '.STAsText' as Method, @g.STAsText() as WKT

UNION ALL SELECT '.AsTextZM', @g.AsTextZM()

UNION ALL SELECT '.ToString', @g.ToString();

go

 Results:

Method WKT
.STAsText POINT (4 4)
.AsTextZM POINT (4 4 4 5)
.ToString POINT (4 4 4 5)

Other samples for you to run. Look at the results WKT column to set how it affects the output. 

 -- ==< Samples showing how to Store & Retrieve 3D (Z axis) & 4D (Measure) values >==
DECLARE @p2D as Geometry = 'POINT( 1 1 )';
DECLARE @p3D as Geometry = 'POINT( 3 3 4 )';
DECLARE @p4D as Geometry = 'POINT( 4 4 4 5 )';
-- AsTextZM doesn't display dimensions that aren't used
SELECT @p2D.STBuffer(0.2) as Geo, @p2D.AsTextZM() as [WKT 4D]
UNION ALL SELECT @p3D.STBuffer(0.2), @p3D.AsTextZM()
UNION ALL SELECT @p4D.STBuffer(0.2), @p4D.AsTextZM()
go

-- ==< 3D & 4D also works for Lines & Polygons >===
DECLARE @L2D as Geometry = 'LINESTRING( 2 2, 9 0 )';
DECLARE @L3D as Geometry = 'LINESTRING( 3 3 3, 9 0 3 )';
DECLARE @L4D as Geometry = 'LINESTRING( 4 4 4 4, 9 0 4 4 )';

SELECT @L2D as Geo, @L2D.AsTextZM() as [WKT 4D]
UNION ALL SELECT @L3D, @L3D.AsTextZM() 
UNION ALL SELECT @L4D, @L4D.AsTextZM()

-- ==< Polygon Example >===
DECLARE @g as GEOMETRY = 'POLYGON( (0 0 9, 30 0 9, 30 30 9, 0 30 9, 0 0 9) )';    
SELECT @g as geo, @g.AsTextZM() as [WKT 3D]
go

-- ==< Valid MultiPoint String Syntax >==
DECLARE @g as GEOMETRY = 'MULTIPOINT(12 18, 15 15, 10 15)'
DECLARE @Br as GEOMETRY = 'MULTIPOINT( (22 18), (25 15), (20 15))'
DECLARE @3D as GEOMETRY = 'MULTIPOINT( (12 8 10), (15 5 5), (10 5 7))'
DECLARE @4D as GEOMETRY = 'MULTIPOINT( (22 8 10 78), (25 5 5 43), (20 5 7 27))'

-- Display only 2D --
SELECT @g.STBuffer(0.2) as Geo, @g.STAsText() as WKT, 'X,Y Points without Bracketing pairs' as Comment
UNION ALL 
SELECT @Br.STBuffer(0.2),@Br.STAsText(), 'X,Y Points with Bracketing pairs - Preferred
UNION ALL 
SELECT @3D.STBuffer(0.2),@3D.STAsText(), 'Points that store a Z Dimension'
UNION ALL 
SELECT @4D.STBuffer(0.2),@4D.STAsText(), 'Points that store Z & M(measure) Dimensions'

-- Display only all you can --
SELECT @g.STBuffer(0.2) as Geo, @g.AsTextZM() as WKT, 'X,Y Points without Bracketing pairs' as Comment
UNION ALL 
SELECT @Br.STBuffer(0.2),@Br.AsTextZM(), 'X,Y Points with Bracketing pairs - Preferred
UNION ALL 
SELECT @3D.STBuffer(0.2),@3D.AsTextZM(), 'Points that store a Z Dimension'
UNION ALL 
SELECT @4D.STBuffer(0.2),@4D.AsTextZM(), 'Points that store Z & M(measure) Dimensions'
go

What about Well Known Binary & GML?

From the "How do I use the method?" perspective, WKT & WKB are identical. Substitute WKB for Text in the method name & away you go. WKB is a more efficient way to store & transfer shapes from one system to another. The disadvantage is that both app's need to be compliant with the Open Geospatial Specification (OGS) in order to be successful.

   Most Web Based applications are designed around an XML based communication. For those, the OpenGIS Geography Markup Language (GML) Encoding Standard may serve you better.

For more info on WKB, WKT & the OGS Standard for SQL v1.1 look at OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option

 

What is a Valid Shape?

SQL lets you store & retrieve any combination of points to make any geometry you like. But if you do create some convoluted shape which twists & turns over itself, it is often impossible to determine what you've described. So most of the methods will fail. A polygon that crosses over itself is really a multipolygon containing polygons which just touch each other. A Line which draws over itself is really a multilinestring.

Key Points

-

A Shape is valid where it does NOT cross over itself

  
  

  -   
    
    Be diligent, when you create shapes, ensure that you are forming them correctly, especially geography shapes which do not have a MakeValid Method.
  • You can only perform calculations on Valid Shapes.

Method .STIsValid()

  • Test for a valid shape. (as described above)
  • Works with Geometry objects only. It is not a method of Geography Objects.
  • Companion method of .MakeValid Method.
  • Returns Boolean: 0 = NotValid, 1 = Valid

Note: Invalid shapes are a common cause of .NET Framework exceptions when working with Geometry types in .NET code. They result in the error message.

Msg 6522, Level 16, State 1, Line 2

A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":

System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid.

Use MakeValid to convert the instance to a valid instance.

Note that MakeValid may cause the points of a geometry instance to shift slightly.

Example use: Alter Geometry shapes so they "make sense"

Method .MakeValid()

  • Adjust the definition of a shape to make it valid.

    • Note: MakeValid may cause the points of a geometry instance to shift slightly.

  • Works with Geometry objects only. It is not a method of Geography Objects.

  • Companion method of .STIsValid Method.

Example use: Alter Geometry shapes so they "make sense"

 --< Sample: Test if a Shape or Line traced back over itself - not Valid >--
DECLARE @g GEOMETRY = 'LINESTRING(0 9, 1 8, 2 6, 2 2, 1 1, 3 1, 2 2, 2 6, 3 8, 4 9)'
SELECT @g, @g.ToString() as 'Invalid String'
      ,@g.STIsValid() as 'STIsValid'
      ,CASE @g.STIsValid() 
            WHEN 0 THEN 'Not Valid' 
            WHEN 1 THEN 'Valid' 
       END As 'Is Valid?'

SET @g = @g.MakeValid()    -- Make it valid
SELECT @g, @g.ToString() as 'Valid String'
    ,@g.STIsValid() as 'STIsValid'
    ,CASE @g.STIsValid() WHEN 0 THEN 'Not Valid' WHEN 1 THEN 'Valid' END As 'Is Valid?'

MakeValid   MadeValid

Left: Invalid Line Shape: LINESTRING (0 9, 1 8, 2 6, 2 2, 1 1, 3 1, 2 2, 2 6, 3 8, 4 9)

Right : Valid Line Shape:–MULTILINESTRING ((0 9, 1 8, 2 6, 3 8, 4 9) ,(2 6, 2 2), (1 1, 3 1, 2 2, 1 1))

 

What is a SRID (Spatial Reference ID)?

The spatial reference identification system is defined by the European Petroleum Survey Group (EPSG) standard, which is a set of standards developed for cartography, surveying, and geodetic data storage. This standard is owned by the Oil and Gas Producers (OGP) Surveying and Positioning Committee.

  • Every spatial object has a Spatial Reference ID (SRID)

    • The SRID defines the coordinate system and datum

    • Each object can have a different SRID, but usually doesn’t (difficult to work with)

    • Operation between objects require them to have the same SRID

  • To see what SRIDs are supported :

    • -- < List of Spatial Reference Systems supported in SQL 2008 >--

      SELECT * FROM SYS.SPATIAL_REFERENCE_SYSTEMS

  • Most common SRID for Geography is 4326 (also referred to as WGS84)

  • The Default SRID for geometry is 0

For a more background on SRID's see: Wikipedia - SRID article I posted with the assistance of Ed Katibah

Property .STSrid

  • Read or Change the SRID of a spatial object.
    • Think before changing the SRID of an existing object, understand what it means, as it is typically not a good idea.

 

More info

For more info see SQL Server 2008 Books Online Geography Spatial Type & Geometry Spatial Type

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 Server Spatial,Spatial,TSQL,.NET,SRID,MakeValid

Comments