SQL 2008 Spatial Sample, Part 4 of 9 - Methods for Drawing Spatial Shapes
This post covers the following methods: STDimension, STGeometryType, STNumGeometries, STGeometryN, STNumPoints, STPointN, STNumInteriorRing / NumRings, STInteriorRingN / RingN, STExteriorRing, STX / Long, STY / Lat, Z & M (measure).
Note: Unless otherwise stated. All code samples are designed to be Cut n Pasted directly into SQL Server Management Studio (SSMS) & run.
Overview of Rendering methods.
Clearly these methods aren't limited to rendering. Anytime you need to really understand the internal detail about a shape, or convert shapes from one format to another you will find these handy. On first glance drawing is pretty straightforward. Especially if you are creating your own test data & have minimal experience with spatial data. You create a series of simple polygons, loop around their points & everything is good. The purpose of this post is to ensure you don't miss half the details. It takes a little more thought to handle polygons with interior holes, "Multi"-shapes & GeometryCollection objects correctly.
Below I've described the key methods you will require & provided an outline on how they relate to each other.
Overview of Methods used for Exploring Shapes
This table shows all the methods that assist in understanding the data that "makes" the shape. While the methods for Geography & Geometry are similar, there are some differences which I've highlighted in bold.
Geometry Geography Shapes / Object .STNumGeometries() .STNumGeometries() Shape index .STGeometryN() .STGeometryN() Type of Shape .STDimension() .STDimension() .STGeometryType() .STGeometryType() Rings / Polygon .STNumInteriorRing() .NumRings() .STExteriorRing() Ring index .STInteriorRingN() .RingN() Points / Shape .STNumPoints() .STNumPoints() Point index .STPointN() .STPointN() 4D Point values X .STX .Long Y .STY .Lat Z .Z .Z M (Measure) .M .M
Drawing Shapes.
Often you will know what sort of object you are dealing with as your table only contains either Points, Lines or Polygons. But if you are dealing with GeometryCollection objects or are writing code to handle anything then the following 2 methods are vital.
Method .STDimension()
Returns an integer to indicate the dimensionality of the spatial object.
- 2 = 2D for Polygon
- 1 = 1D for Line
- 0 = 0D for Point
- -1 = Empty
Works with both Geometry & Geography objects
Sister method of .STGeometryType Method.
Syntax: geo1.STDimension()
Example use: Handy in Case/Select statements to call different rendering routines.
--< Show STDimension, Returns 2=2D for Polygon, 1=1D for Line & 0 = 0D for Point >-- DECLARE @temp TABLE (Shape VARCHAR(10), [geom] GEOGRAPHY); INSERT INTO @temp VALUES ('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;Results:
Shape STDimension Empty -1 Point 0 LineString 1 Polygon 2
Method .STGeometryType()
Returns name of Spatial Object Type.
- eg: Point, Multipoint etc
Unlikely that you'll use this a lot. But it it is more precise in describing exactly what the geometric object is.
Works with both Geometry & Geography objects
Sister method of .STDimension Method.
Syntax: geo1.STGeometryType()
Example use: Handy for grouping the spatial objects if you need to know the difference between Single & Multi variations.
--< Show STGeometryType, Returns Name of object >-- -- Note: It has the same name even with Geography datatypes. ie: Not STGeographyType -- DECLARE @temp TABLE (Shape VARCHAR(10), [geom] GEOGRAPHY); INSERT INTO @temp VALUES ('Point', GEOGRAPHY::STGeomFromText('POINT(3 3)', 4326)) ,('M-Point', GEOGRAPHY::STGeomFromText('MULTIPOINT( (4 4), (5 5) )', 4326)) ,('Line', GEOGRAPHY::STGeomFromText('LINESTRING(0 5, 3 8)', 4326)) ,('M-Line', GEOGRAPHY::STGeomFromText('MULTILINESTRING( (0 0, 3 3), (0 3, 3 0) )', 4326)) ,('Polygon', GEOGRAPHY::STGeomFromText('POLYGON((0 0, 3 0, 0 3, 0 0))', 4326)) ,('M-Poly', GEOGRAPHY::STGeomFromText('MULTIPOLYGON( ((0 0, 3 0, 0 3, 0 0)), ((5 0, 8 0, 5 3, 5 0)) )', 4326)) ,('GeomCol', GEOGRAPHY::STGeomFromText('GEOMETRYCOLLECTION( POLYGON((0 0, 3 0, 0 3, 0 0)), POINT(5 5) )', 4326)); SELECT Shape, [geom].STDimension() as [STDimension], [geom].STGeometryType() AS [STGeometryType], [geom].STAsText() AS WKT FROM @temp;Results:
Shape STDimension STGeometryType WKT Point 0 Point POINT (3 3) M-Point 0 MultiPoint MULTIPOINT ((4 4), (5 5)) Line 1 LineString LINESTRING (0 5, 3 8) M-Line 1 MultiLineString MULTILINESTRING ((0 0, 3 3), (0 3, 3 0)) Polygon 2 Polygon POLYGON ((0 0, 3 0, 0 3, 0 0)) M-Poly 2 MultiPolygon MULTIPOLYGON (((0 0, 3 0, 0 3, 0 0)), ((5 0, 8 0, 5 3, 5 0))) GeomCol 2 GeometryCollection GEOMETRYCOLLECTION (POLYGON ((0 0, 3 0, 0 3, 0 0)), POINT (5 5))
Drawing Simple Polygons
The VB.NET code below shows the relationships between the main methods you will need. It is straightforward, just looping thru all points the shape contains & processing or drawing them (somehow)
Options for Rendering the shapes
Unless you are using the Dundas .NET Map Control you should ignore the references to Shape & ShapeSegment. I left them in the sample code to prompt you that you will probably need to create your own class or similar to render it.
-
- Windows Presentation Framework (WPF) is great for this. It is really simple to create a really slick UI. But GDI+ also works if you aren't running .NET v3.5. I plan on writing on this in a future post.
- DirectX & unmanaged C++ has the potential to give you the best performance, but it requires much more work to match the look of the WPF & if you aren't great at threading & design you may run much slower.
- I found the Dundas Map for .NET lets you avoid all the map rendering work completely. So unless you are a specialist Spatial S/W developer attempting to create your own distinct look I'd suggest you look at Dundas Map or Virtual Earth.
Steps to understand a simple Polygon
-
Use STNumPoints to determine how many points comprise the shape. Perhaps use the value to initialise an array or similar collection.
Iterate thru each point
- For Geometry types use properties: STX, STY, Z & M
- For Geography types use properties: Long, Lat, Z & M
As mentioned in other posts, the SQL2008 Spatial functions ignore Z & M in their calculations. But if you want to create 3D or even 4D (time) based drawings, this is where you'd start.
Note: Points & Geometries collections are 1-Based, not 0-Based as most .NET Collections.
Private Sub makeGeomSegment(ByRef myShape As Shape, ByVal geom As SqlGeography)
'--< Segment >--
Dim newSegment(0) As ShapeSegment
newSegment(0) = New ShapeSegment
newSegment(0).Type = SegmentType.Polygon
newSegment(0).Length = geom.STNumPoints
'--< Points >--
Dim ptsShape(geom.STNumPoints - 1) As MapPoint ' Points start at 1 not 0
For cntPT As Integer = 1 To geom.STNumPoints
ptsShape(cntPT - 1).X = geom.STPointN(cntPT).Long 'or .STX
ptsShape(cntPT - 1).Y = geom.STPointN(cntPT).Lat 'or .STY
Next
'- Update Shape
myShape.AddSegments(ptsShape, newSegment)
End Sub
Short description of the relevant methods
Method .STPointN()
- STPointN acts as an index into the "N"th Point in a shape. Where "N" is a positive integer.
- Works with both Geometry & Geography objects.
- Assists you to enumerate thru all the points in a shape.
Method .STNumPoints()
- Number of Points in a shape.
- Works with both Geometry & Geography objects.
- Handy to initialise arrays & to terminate loops.
Method .STStartPoint()
- Returns the 1st point in a shape.
- STStartPoint() is the equivalent of STPointN (1)
- Works with both Geometry & Geography objects.
- Not exciting but useful for drawing a "start" icon on a path.
Method .STEndPoint()
- Returns the last point in a shape. In a Polygon, Closed Line or Point is it identical to STStartPoint.
- geo.STEndPoint() is the equivalent of geo.STPointN( geo.STNumPoints() ).
- Works with both Geometry & Geography objects.
- Not exciting but useful for drawing a "finish" icon on a path.
Drawing Complex Polygons
By complex we mean:-
- It has holes in it
- It is comprised of multiple similar objects, like the 2 polygons that form the North & South shapes for the Islands of New Zealand.
I view GeometryCollection as a extension of this. They are often comprised of multiple objects that could be anything, multiple Lines, Polygons & Points. So if you write your code to handle these, everything else is a subset.
Drawing Geometry Polygons
Method .STExteriorRing()
A Geometry Object that defines the outside of a Geometry polygon.
Works with Geometry objects only. It is not a method of Geography Objects.
- Geography equivalent is RingN()
Syntax: geo1.STExteriorRing()
Example use: Pass the result to a routine that iterate thru the points it contains & draw a shape.
Note: The Sample code adds STBuffer(0.05) to the STExteriorRing() just to make it easier to see. You wouldn't do this in your rendering code.
-- ==< Sample: Outer ring of a polygon >== DECLARE @h AS GEOMETRY SET @h= geometry::STGeomFromText('LINESTRING(67 36,68 37,69 38,73 38,75 37,76 35, 75 33,74 32,71 32,69 33,58 39,55 40,52 40,50 39, 48 36,48 34,50 31,52 30,56 30,60 31,62 33)', 0); DECLARE @b AS GEOMETRY = @h.STBuffer(1.5) -- Make the line a Polygon SELECT @b as 'Geo', 'Line' AS 'Labels',@b.STAsText() as 'Text' UNION ALL SELECT @b.STExteriorRing().STBuffer(0.05), 'STExteriorRing()', @b.STExteriorRing().STAsText()
Method .STInteriorRingN(n)
A Geometry Object that defines one of the “holes” in a Geometry polygon.
Works with Geometry objects only. It is not a method of Geography Objects.
- Geography equivalent is RingN()
Syntax: geo1.STInteriorRingN()
-- ==< Sample: Inner ring 1 of a polygon >== DECLARE @h AS GEOMETRY SET @h=geometry::STGeomFromText('LINESTRING(67 36,68 37,69 38,73 38,75 37,76 35, 75 33,74 32,71 32,69 33,58 39,55 40,52 40,50 39, 48 36,48 34,50 31,52 30,56 30,60 31,62 33)',0); DECLARE @b AS GEOMETRY= @h.STBuffer(1.5) -- Make the line a Polygon SELECT @b as'Geo', 'Line' AS 'Labels', @b.STAsText() as 'Text' UNION ALL SELECT @b.STInteriorRingN(1).STBuffer(0.05),'STInteriorRingN(1)',@b.STInteriorRingN(1).STAsText()
Algorithm for Drawing Complex Geometry Polygons
The VB.NET code below shows the relationships between the main methods you will need.
Protect yourself from any invalid data.
- I do this with MakeValid or you may prefer to raise an error.
Use STNumGeometries to check if this geometry object is a MultiPolygon or a GeometryCollection. While I've not included this in the code sample below, if it is a GeometryCollection you may what to use STDimension & logic to handle Lines & points in addition to the polygons shown below.
Use STGeomertyN as an index to loop thru each polygon in the collection.
For each polygon
- Use STNumInteriorRing to check if there are any internal shapes.
- If there are, use STExteriorRing to Process the outer Polygon
- Then use STInteriorRingN to loop thru all the interior polygons.
- Remember indicate to your drawing routines that you need it to mask out the "holes" formed by the InteriorRing polygons. (below I used shapeComplexPolygon for that task)
Note: As STExteriorRing & STinteriorRingN both return a simple polygon, you can then pass their output to your routine that loops thru their points & draws them.
Drawing Geography Polygons
Method .STRingN(n)
A Geography Object that defines the boundaries of a Geography.
- Where n = 1 to number of interior shapes.
Geography Objects are cleaner to work with. Just loop thru & no External/Internal stuff to think about.
Works with Geography objects only. It is not a method of Geometry Objects.
- Geometry equivalent is STInteriorRingN() & STExteriorRing()
Syntax: geo1.STRingN(ring_number)
-- ==< Sample: Show all Rings of a Complex Geography Polygon >== DECLARE @h AS GEOGRAPHY SET @h= GEOGRAPHY::STGeomFromText('LINESTRING(67 36,68 37,69 38,73 38,75 37,76 35, 75 33,74 32,71 32,69 33,58 39,55 40,52 40,50 39, 48 36,48 34,50 31,52 30,56 30,60 31,62 33)', 4236); DECLARE @b AS GEOGRAPHY = @h.STBuffer(100000) -- Make the line a Polygon SELECT @h as 'Geo', 'Line' AS 'Labels',@h.STAsText() as 'Text' UNION ALL SELECT @b as 'Geo', 'Buffer(100000)',@b.STAsText() -- NB: Much larger value as in Metres UNION ALL SELECT @b.RingN(1).STBuffer(8000), 'RingN(1)', @b.RingN(1).STAsText() -- Exterior Ring UNION ALL SELECT @b.RingN(2).STBuffer(8000), 'RingN(2)', @b.RingN(2).STAsText() -- Interior Ring go
Warning: The direction of the points that form the polygon path is important.
Anti-Clockwise fills in the centre.
Clockwise fills the entire world except the centre.
This good for creating "holes" in polygons.
Also great for causing errors stating your polygon is more than half the world.
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.
Tip: I use the phrase “Look to Left”. If you imagine you are walking in the direction the line is being drawn, then left is side of the line that will be filled.
Algorithm for Drawing Complex Geography Polygons
The VB.NET code below shows the relationships between the main methods you will need. It is very similar to the Geometry processing.
Protect yourself from any invalid data.
- Unfortunately there is no MakeValid for geography objects. It is also harder to create invalid shapes. Still, you need to catch the error & fix it yourself. The most common exception is the points create a wrong ring direction. For more info on how to fix it see Working with invalid data & the SQL2008 Geography Datatype
Use STNumGeometries to check if this geometry object is a MultiPolygon or a GeometryCollection. While I've not included this in the code sample below, if it is a GeometryCollection you may what to use STDimension & logic to handle Lines & points in addition to the polygons shown below.
Use STGeomertyN as an index to loop thru each polygon in the collection.
For each polygon
- Use NumRings to check if there are any internal shapes.
- Then RingN to loop thru all the polygons, both Exterior & any Interior
- Remember indicate to your drawing routines that you need it to mask out the "holes" formed by the InteriorRing polygons. (below I used shapeComplexPolygon for that task)
Note: As RingN returns a simple polygon, you can then pass the output to your routine that loops thru their points & draws them.
More info
For more info see SQL Server 2008 Books Online OGC Methods on Geometry Instances
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: Spatial,SQL Server 2008,SQL Server Spatial,TSQL,.NET,Geometry,Geography,Polygon