## Top 5 Recent Articles

##### ARTICLES CATEGORIES

- Biography (1)
- Blog (24)
- Changes (1)
- Customers (1)
- Education (2)
- General Software (20)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Spatial (2)
- Networking and Routing (including Optimization) (2)
- Oracle Spatial (55)
- PostGIS (19)
- Source code (5)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (8)
- SQL Server Spatial (General) (13)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- XML (4)

# 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.

## Documentation

- MySQL Spatial General Function Documentation
- Oracle Spatial Exporter Package Documentation
- Oracle Spatial Object Function Documentation
- Oracle Spatial Object Function Documentation (Multi Page Version)
- PostGIS pl/pgSQL Function Documentation
- SC4O Oracle Java Topology Suite (Stored Procedures) Package Documentation
- SQL Server Spatial General TSQL Function Documentation
- SQL Server Spatial LRS TSQL Function Documentation