STAsEWKT

STAsEWKT — Implements an method to create Extended Well Known Text (EWKT) strings from the input @p_geometry.

Introduction/Description

The Open Geospatial Consortium’s, Simple Features Specifications (SFS) 1.1 and 1.2, as well as the SQL/MM standard, define a Well Known Text (WKT) format for use in interchanging or processing geometry data.

These specifications are 2D in nature: they only describe geometry data with X and Y ordinates as follows:

• POINT(1 1)
• LINESTRING(0 0,1 1)
• POLYGON((0 0,10 0,10 10,0 10,0 0))

etc

If a geometry object has Z or M ordinates, the current specification does not define a method for describing such data.

There is a need for WKT to support Z and M ordinates, as well as the SRID of the geometry object.

Various vendors and open source projects have defined and implemented their own extensions to the base 2D WKT, for example PostGIS.

However, these implementations, while using a common approach, are not exactly the same.

We give all these implementations the generic name of Extended Well Known Text (EWKT).

The following examples follow PostGIS’s EWKT implementation. Note the encoding of the geometry’s SRID values, as well as Z/M ordinates:

(POINT is used as a generic placeholder)

• POINT Z (x y z)
• POINT M (x y m)
• POINT ZM (x y z m)
• POINT EMPTY
• SRID=28346; POINT (x y)V/li>
• SRID=28346; POINT Z (x y z)
• SRID=28346; POINT M (x y m)
• SRID=28346; POINT ZM (x y z m)
• SRID=28346; POINT EMPTY

I have written a function, STAsEWKT() (see below), for SQL Server Spatial that extends or wraps its current WKT functionality (it supports Z and M but not SRID encoding) to that it will import PostGIS style EWKT (or other vendor EWKT such as POINT Z (x y z). It also handles situations like POINT M (x y m) where SQL Server encodes such an object as POINT(x y NULL m).

Where a geometry is 2D, the function exports by using the https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/astextzm-geometry-data-type?view=sql-server-2017 or https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stastext-geometry-data-type?view=sql-server-2017 functions directly.

Function Specification

Function [$(owner)].[STAsEWKT] (
  @p_geometry geometry
)
Returns varchar(max)

Parameters

@p_geometry (geometry) -- Any valid or empty geometry object.

Result

This function returns an EWKT string describing @p_geometry with size varchar(max)

Examples

select [$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2)',0));
GO

----------
POINT(1 2)

(1 row affected)

select [$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2)',2274));
GO

--------------------
SRID=2274;POINT(1 2)

(1 row affected)

select [$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 3)',0));
GO

-------------
POINTZ(1 2 3)

(1 row affected)

select [$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 3)',2274));
GO

-----------------------
SRID=2274;POINTZ(1 2 3)

(1 row affected)

select [$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 NULL 3)',2274));
GO

-----------------------
SRID=2274;POINTM(1 2 3)

(1 row affected)

select [$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 1.2 3)',2274));
GO

----------------------------
SRID=2274;POINTZM(1 2 1.2 3)

(1 row affected)

Conclusion

This function was not that difficult to write and, along with STGeomFromEWKT() should provide use tools for importing and exporting geometry data to and from many external systems, from FGDB, qGIS, PostGIS, GeoPackage, Spatialite etc.

This function is shipped with my package of SQL Server Spatial TSQL functions. It is available via donation on this website.

I hope this function is of use to someone out there.