Creating Spatial Data

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)