Creating Spatial Data

(Presented 2014 by Nathanael Beisiegel - Ch04.pdf)

SQL provides methods to create data in several formats:

• Well-Known Text (WKT)
• Well-known Binary (WKB)
• Geography Markup Language (GML)
• Programatically

  • SqlGeometryBuilder class

  • SqlGeographyBuilder class

Well-Known Text

Advantages:

• Simple format
• Human readable

Disadvantage:

• loss of precision due to rounding of text-based representation of floating point coordinate values

• SQL must parse All WKT into it's own internal binary format. Parsing takes additional time making this method slower than binary creation methods.

Methods of instantiating Spatial data from WKT for either geometry or geography

--T-SQL
SELECT geography::STPointFromText('POINT(153 -27.5)', 4326);

SELECT geometry::STLineFromText('LINESTRING(300500 600150, 310200 602500)', 27700);

SELECT geography::STGeomFromText('POINT(153 -27.5)', 4326),
       geometry::STGeomFromText('LINESTRING(300500 600150, 310200 602500)', 27700);

//C#
SqlGeography Point = SqlGeography.STPointFromText(new SqlChars("POINT(153 -27.5)"),4326);

You can also use the Parse() method which does not require an SRID since it defaults to 4326 (WGS84) for geography or 0 for geometry. Assignment statements use Parse by default e.g.:

DECLARE @Delhi geography = 'POINT(77.25 28.5)';
--Equivalent to:
DECLARE @Delhi geography = geography::Parse('POINT(77.25 28.5)';
--Equivalent to:
DECLARE @Delhi geography = geography::STGeomFromText('POINT(77.25 28.5)', 4326);

Or in C#
SqlGeography Delhi = SqlGeography.Parse("POINT(77.25 28.5)");

Retreiving WKT representation of an Instance

STAsText() --retrieves only 2D coordinates.

AsTextZM() --includes z and m coordinate values defined by the geometry

//In C# ToString() acts like AsTextZM()

Try it:

DECLARE @Point geometry = geometry::STPointFromText('POINT(14 9 7)', 0);
SELECT @Point.STAsText() AS STAsText, @Point.AsTextZM() AS AsTextZM, @Point.ToString() AS ToString;

Creating Spatial Data from Well-Known Binary (WKB)

Binary Format: The WKB hexidecimal string, 0x00000000014001F5C28F5C28F6402524DD2F1A9FBE, represents the following information

||0x Hexadecimal notation identifier ||00 Byte order marker. 0×00 indicates little-endian byte order ||00000001 This geometry is a Point, denoted as type 1 ||4001F5C28F5C28F6 x-coordinate (10.572) ||402524DD2F1A9FBE y-coordinate (2.245)

Try some examples:

SELECT geometry::STPointFromWKB(0x00000000014001F5C28F5C28F6402524DD2F1A9FBE, 2099);
--OR
SELECT geometry::STGeomFromWKB(0x00000000014001F5C28F5C28F6402524DD2F1A9FBE, 2099);

--Using what we have learned so far:
DECLARE @g geometry = geometry::STPointFromText('POINT(14 9 7)', 0);
SELECT @g.STAsBinary();

Geometry Markup Language

GML suffers my personal disdain because it doesn't support z or m coordinates! Want to see the XML? call AsGml() method.

Advantages:

• GML is text-based, making it relatively easy to examine and understand the information contained within.

• The explicit structure of a GML document mirrors the structure of a geometry itself; a GML <Polygon> contains an <exterior> element, which specifies a <LinearRing> containing an array of coordinates in a <posList>, for example. This makes it easy to understand the structure of a complex geometry by examining the structure of the associated GML representation.

• GML is very verbose, explicitly stating all values within specific elements.

Disadvantages:

• It is very verbose! Although both WKT and GML are text-based formats, the GML representation of a geometry requires substantially more space than the equivalent WKT representation
• Because GML is text-based, it too suffers from precision issues caused by rounding of binary floating-point values.
• Widely used in practice, but SQL Server doesn't support the whole standard, so you may not be able to import it all.

Try it (Note you must use the given namespace or SQL Server won't recognize it):

DECLARE @gml xml = '<Point xmlns="http://www.opengis.net/gml"><pos>47.6 -122.3</pos></Point>';
SELECT geography::GeomFromGml(@gml, 4269);

DECLARE @polygon geography = 'POLYGON((-4 50, 2 50, 2 60, -4 60, -4 50))';
SELECT @polygon.AsGml();

Spatial Data from Dynamically Generated WKT

Try it:

CREATE TABLE GPSLog (
  Latitude float,
  Longitude float,
  LogTime datetime
);
INSERT INTO GPSLog VALUES
  (51.868, -1.198, '2011-06-02T13:47:00'),
  (51.857, -1.182, '2011-06-02T13:48:00'),
  (51.848, -1.167, '2011-06-02T13:49:00'),
  (51.841, -1.143, '2011-06-02T13:50:00'),
  (51.832, -1.124, '2011-06-02T13:51:00');

SELECT geography::STGeomFromText('POINT(' + CAST(Longitude AS varchar(32)) + ' ' + CAST(Latitude AS varchar(32)) + ')',4326)
FROM GPSLog;
--Or a bit easier:
SELECT geography::Point(Latitude, Longitude, 4326) 
FROM GPSLog;

Try making a LineString instead:

-- Declare an empty nvarchar to hold our constructed WKT string
DECLARE @WKT nvarchar(max) = '';

-- Build up the comma-delimited list of coordinate pairs
SELECT @WKT = @WKT + CAST(Latitude AS varchar(32)) + ' ' + CAST(Longitude AS varchar(32)) + ','
FROM GPSLog
ORDER BY LogTime;

-- Remove the final trailing comma
SET @WKT = LEFT(@WKT, LEN(@WKT) - 1);

-- Append the LINESTRING keyword and enclose the coordinate list in brackets
SET @WKT = 'LINESTRING(' + @WKT + ')';

-- Pass the constructed WKT to the static method
SELECT geography::STGeomFromText(@WKT, 4326);

Ok, its possible. But do we really want to create data this way? Probably not!

Creating Spatial Data Using the Builder Classes

Library: SqlServer.Types.dll

Classes: SqlGeometryBuilder and SqlGeographyBuilder --> SqlGeometry and SqlGeography

Try it:

using Microsoft.SqlServer.Types;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SpatialDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlGeometryBuilder gb = new SqlGeometryBuilder();
            gb.SetSrid(27700);
            gb.BeginGeometry(OpenGisGeometryType.Point);
            gb.BeginFigure(300500, 600200);
            gb.EndFigure();
            gb.EndGeometry();

            SqlGeometry Point = gb.ConstructedGeometry;

            Console.WriteLine(Point.ToString());

            gb = new SqlGeometryBuilder();

            gb.SetSrid(0);

            gb.BeginGeometry(OpenGisGeometryType.Polygon);

            //Exterior Ring
            gb.BeginFigure(0,0);
            gb.AddLine(10, 0);
            gb.AddLine(10, 20);
            gb.AddLine(0, 20);
            gb.AddLine(0, 0);
            gb.EndFigure();

            // Interior ring
            gb.BeginFigure(3, 3);
            gb.AddLine(7, 3);
            gb.AddLine(5, 17);
            gb.AddLine(3, 3);
            gb.EndFigure();

            gb.EndGeometry();

            SqlGeometry Polygon = gb.ConstructedGeometry;

            Console.WriteLine(Polygon.ToString());
        }
    }
}

Note: To construct a Polygon geometry using the SqlGeometryBuilder class, each ring requires a separate call to the BeginFigure() method. The exterior ring is the first to be created, and every subsequent figure defines an interior ring.