SQL 2008 Spatial Samples, Part 2 of 9 - Background on Spatial Types & Well Known Text (WKT)
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.
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.
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?'
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 :
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