Partager via

SQL 2008 Spatial Samples, Part 3 of 9 - SQL Builder API

  • Article

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

Comments