SQL 2008 Spatial Samples, Part 3 of 9 - SQL Builder API
This post covers the SqlGeographyBuilder & SqlGeometryBuilder Classes & their methods.
Overview of Methods to make your own Spatial Objects using the Builder API.
SQL provides a Builder API that permit you to easily write your own Spatial Data Importing utilities. It is also handy manipulating your shapes in a way that is not supported out of the box, perhaps writing your own CLR (Common Language Runtime) extension to SQL. Also handy for generating test data (especially points), reverse geo-coding & similar applications.
The methods for Geography & Geometry are identical. ( Except that they target a different datatype & have a name change that reflects that, which I've highlighted in bold. )
Geometry Geography Classes .SqlGeometryBuilder .SqlGeographyBuilder Methods .SetSrid() .SetSrid() .BeginGeometry() / .EndGeometry() .BeginGeography() / .EndGeography() .BeginFigure() / .EndFigure() .BeginFigure() / .EndFigure() .AddLine() .AddLine() Return .ConstructedGeometry .ConstructedGeography
Table of methods covered in this post. Fortunately the methods & classes are fairly self explanatory. So I hope you can just cut n paste the samples into your VB.NET code & run them. For C# put a ";" on the end of each line. ;-). NB: Don't forget to add a reference to Microsoft.SqlServer.Types. As mentioned in an earlier post - Learning SQL Spatial
Main Steps
1. Decide what spatial type you wish to create (Geometry / Geography) & declare an instance of the appropriate "Builder"
Dim gb As New SqlGeographyBuilder
2. Set the Spatial Reference ID (SRID). This must be the first method you call.
gb.SetSrid(4326)
3. Create Geographies. Start with BeginGeography & indicate the type of geometry you are creating (point, line, polygon) then close with matching EndGeography.
4. Create one or more shapes inside it with a BeginFigure ... EndFigure pair. Leaving these out will create an EMPTY Geography. BeginFigure also sets the StartPoint for the shape.
5. For lines & polygons, use AddLine to additional points with lines connecting them.
6. When you are done use the ConstructedGeography method to pass the shape to a SQLGeometry or SQLGeography variable.
Below are a few code samples showing how to create each of the different shapes.
Sample 1a: Building a Geography Point
This example shows declaring the SqlGeographyBuilder, setting the SRID to WGS 84, Creating a Geography Pair & adding one Figure pair to specify the point.
Note: For the Geography datatype, the order of Latitude & Longitude coordinates are swapped. Effectively this makes the co-ordinate (Y,X). It is consistent with the way maritime sailors have been readn' maps for centuries, "so get used to it, ya scurvy dog!". Yes, it is opposite to the WKT format, see output below.
Dim gb As New SqlGeographyBuilder
gb.SetSrid(4326) 'Must set First
gb.BeginGeography(OpenGisGeographyType.Point)
gb.BeginFigure(-33, 151)
gb.EndFigure()
gb.EndGeography()
Dim geo As New SqlGeography
geo = gb.ConstructedGeography
geo.ToString()
The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString()
"POINT (151 -33)"
Sample 1b: Building a Geometry Point
For Geometry objects all "geography" methods change to "Geometry" except the OpenGisGeographyType enumeration.
Dim gb As New SqlGeometryBuilder
gb.SetSrid(4326) 'Must set First
gb.BeginGeometry(OpenGisGeographyType.Point)
gb.BeginFigure(-33, 151)
gb.EndFigure()
gb.EndGeometry()
Dim geo As New
SqlGeometry geo = gb.
ConstructedGeometry geo.ToString()
Sample 2: Building a Polygon
This example extends the one above by adding lines to create a polygon.
Dim g As New SqlGeographyBuilder
g.SetSrid(4326) '<= Must set First
g.BeginGeography(OpenGisGeographyType.Polygon)
g.BeginFigure( -33, 151) ‘Note: Lat, Long format
g.AddLine(-31, 152)
g.AddLine(-30, 152)
g.AddLine( -33, 151) ‘Note: Last Point same as First
g.EndFigure()
g.EndGeography()
Dim geo As New SqlGeography
geo = g.ConstructedGeography
geo.ToString()
The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString()
"POLYGON ((151 -33, 152 -31, 152 -30, 151 -33))"
Sample 3: Building a Complex Polygon
This example extends the one above by adding extra figures to the geometry
Dim g As New SqlGeographyBuilder
g.SetSrid(4326) '<= Must set First
g.BeginGeography(OpenGisGeographyType.Polygon)
' Exterior shape
g.BeginFigure(-33, 151) 'Note: Lat, Long format
g.AddLine(-33, 154)
g.AddLine(-30, 154)
g.AddLine(-33, 151) 'Note: Last Point same as First
g.EndFigure()
' Interior "Enclosed" shape
g.BeginFigure(-32.5, 152)
g.AddLine(-31, 153.5)
g.AddLine(-32.5, 153)
g.AddLine(-32.5, 152)
g.EndFigure()
g.EndGeography()
Dim geo As New SqlGeography
geo = g.ConstructedGeography
geo.ToString()
The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString()
"POLYGON ((151 -33, 154 -33, 154 -30, 151 -33), (152 -32.5, 153.5 -31, 153 -32.5, 152 -32.5))"
Sample 4: Building a MultiPoint shape
Note: BeginGeography for Point nested inside BeginGeography for Multipoint.
Dim b As New SqlGeographyBuilder
b.SetSrid(4326) 'Must set 1st
b.BeginGeography(OpenGisGeographyType.MultiPoint)
b.BeginGeography(OpenGisGeographyType.Point)
b.BeginFigure(-33, 151)
b.EndFigure()
b.EndGeography()b.BeginGeography(OpenGisGeographyType.Point)
b.BeginFigure(-33, 155)
b.EndFigure()
b.EndGeography()b.BeginGeography(OpenGisGeographyType.Point)
b.BeginFigure(-32, 153)
b.EndFigure()
b.EndGeography()b.EndGeography()
Dim geo As New SqlGeography
geo = b.ConstructedGeography
geo.ToString()The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString()
"MULTIPOINT ((151 -33), (155 -33), (153 -32))"
Sample 5: Building a MultiPolygon
Two polygons where one is not contained inside the other, nor do they overlap each other.
Dim b As New SqlGeographyBuilder
b.SetSrid(4326) 'Must set 1st
b.BeginGeography(OpenGisGeographyType.MultiPolygon)b.BeginGeography(OpenGisGeographyType.Polygon)
b.BeginFigure(-33, 151)
b.AddLine(-31, 152)
b.AddLine(-30, 152)
b.AddLine(-33, 151)
b.EndFigure()
b.EndGeography()b.BeginGeography(OpenGisGeographyType.Polygon)
b.BeginFigure(-33, 155)
b.AddLine(-31, 156)
b.AddLine(-30, 156)
b.AddLine(-33, 155)
b.EndFigure()
b.EndGeography()
b.EndGeography()Dim geo As New SqlGeography
geo = b.ConstructedGeography
geo.ToString()
The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString
"MULTIPOLYGON (((151 -33, 152 -31, 152 -30, 151 -33)), ((155 -33, 156 -31, 156 -30, 155 -33)))“
Sample 6: Building a Building a GeometryCollection
OpenGisGeographyType has the 7 types; Point, LineString, Polygon & their Multi variants, use them in whatever combination you desire.
Dim b As New SqlGeographyBuilder
b.SetSrid(4326) 'Must set 1st
b.BeginGeography(OpenGisGeographyType.GeometryCollection)b.BeginGeography(OpenGisGeographyType.LineString)
b.BeginFigure(-33, 151)
b.AddLine(-31, 152)
b.AddLine(-30, 152)
b.AddLine(-33, 151)
b.EndFigure()
b.EndGeography()b.BeginGeography(OpenGisGeographyType.Polygon)
b.BeginFigure(-33, 155)
b.AddLine(-31, 156)
b.AddLine(-30, 156)
b.AddLine(-33, 155)
b.EndFigure()
b.EndGeography()b.BeginGeography(OpenGisGeographyType.Point)
b.BeginFigure(-32, 153)
b.EndFigure()
b.EndGeography()
b.EndGeography()Dim geo As New SqlGeography
geo = b.ConstructedGeography
geo.ToString()
The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString
GEOMETRYCOLLECTION (LINESTRING (151 -33, 152 -31, 152 -30, 151 -33), POLYGON ((155 -33, 156 -31, 156 -30, 155 -33)), POINT (153 -32))
More info
For more info see SQL Server 2008 Books Online. Might have to wait for the December 08 Update to Books Online as I've not found any mention of these Classes in the Aug 08 release.
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 Spatial,SQL Server,TSQL,.NET,SQLGeometryBuilder,SQLGeographyBuilder,Geometry,Geography