This post covers the SqlGeographyBuilder & SqlGeometryBuilder Classes & their methods.
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
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.
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.
This example shows declaring the SqlGeographyBuilder, setting the SRID to WGS 84, Creating a Geography Pair & adding one Figure pair to specify the point.
gb.SetSrid(4326) 'Must set First gb.BeginGeography(OpenGisGeographyType.Point) gb.BeginFigure(-33, 151) gb.EndFigure() gb.EndGeography()
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)"
geo = gb.ConstructedGeography
geo.ToString()
The line below shows what is displayed in the Visual Studio debugger window.
>? geo.ToString()
"POINT (151 -33)"
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()
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))"
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()
geo = g.ConstructedGeography
>? geo.ToString() "POLYGON ((151 -33, 152 -31, 152 -30, 151 -33))"
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))"
>? geo.ToString() "POLYGON ((151 -33, 154 -33, 154 -30, 151 -33), (152 -32.5, 153.5 -31, 153 -32.5, 152 -32.5))"
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))"
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()
>? geo.ToString() "MULTIPOINT ((151 -33), (155 -33), (153 -32))"
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()
>? geo.ToString "MULTIPOLYGON (((151 -33, 152 -31, 152 -30, 151 -33)), ((155 -33, 156 -31, 156 -30, 155 -33)))“
>? geo.ToString
"MULTIPOLYGON (((151 -33, 152 -31, 152 -30, 151 -33)), ((155 -33, 156 -31, 156 -30, 155 -33)))“
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.
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()
>? geo.ToString GEOMETRYCOLLECTION (LINESTRING (151 -33, 152 -31, 152 -30, 151 -33), POLYGON ((155 -33, 156 -31, 156 -30, 155 -33)), POINT (153 -32))
GEOMETRYCOLLECTION (LINESTRING (151 -33, 152 -31, 152 -30, 151 -33), POLYGON ((155 -33, 156 -31, 156 -30, 155 -33)), POINT (153 -32))
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.
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.