Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
STGeomFromEWKT
STGeomFromEWKT — Function that implements an import method for Extended Well Known Text including EWKT with SRID, Z and M ordinates..
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 these implementations the generic name of Extended Well Known Text (EWKT).
The following examples follow PostGIS’s Extended Well Known Text 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, STGeomFromEWKT() (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).
The function imports value 2D WKT by direct pass through to the geometry::STGeomFromText() constructor.
The function returns a valid geometry object if the input is valid.
Function Specification
CREATE FUNCTION [dbo].[STGeomFromEWKT] ( @p_ewkt varchar(max) ) RETURNS geometry
Parameters
@p_ewkt - varchar(max) - Any WKT or EWKT
Result
Returns a valid geometry object if the input EWKT is correctly described.
Notes
A full description of the structure of a EWKT string is available in the PostGIS documentation.
Examples
select [dbo].[STGeomFromEWKT]('POINT EMPTY').AsTextZM() as geom;
geom
-----------
POINT EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('MULTIPOINT EMPTY').AsTextZM() as geom;
geom
----------------
MULTIPOINT EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('LINESTRING EMPTY').AsTextZM() as geom;
geom
----------------
LINESTRING EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('CIRCULARSTRING EMPTY').AsTextZM() as geom;
geom
--------------------
CIRCULARSTRING EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('MULTILINESTRING EMPTY').AsTextZM() as geom;
geom
---------------------
MULTILINESTRING EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('POLYGON EMPTY').AsTextZM() as geom;
geom
-------------
POLYGON EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('MULTIPOLYGON EMPTY').AsTextZM() as geom;
geom
------------------
MULTIPOLYGON EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('COMPOUNDCURVE EMPTY').AsTextZM() as geom;
geom
-------------------
COMPOUNDCURVE EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION EMPTY').AsTextZM() as geom;
geom
------------------------
GEOMETRYCOLLECTION EMPTY
(1 row affected)
select [dbo].[STGeomFromEWKT]('POINT(1 2)').AsTextZM() as geom;
geom
-----------
POINT (1 2)
(1 row affected)
select [dbo].[STGeomFromEWKT]('POINTZ(1 2 3)').AsTextZM() as geom;
geom
-------------
POINT (1 2 3)
(1 row affected)
select [dbo].[STGeomFromEWKT]('POINTM(1 2 3)').AsTextZM() as geom;
geom
------------------
POINT (1 2 NULL 3)
(1 row affected)
select [dbo].[STGeomFromEWKT]('POINTZM(1 2 3 4)').AsTextZM() as geom;
geom
---------------
POINT (1 2 3 4)
(1 row affected)
select [dbo].[STGeomFromEWKT]('LineString (1 2,4 5,3 4,4 6,5 7,6 7)').AsTextZM() as geom;
geom
-----------------------------------------
LINESTRING (1 2, 4 5, 3 4, 4 6, 5 7, 6 7)
(1 row affected)
select [dbo].[STGeomFromEWKT]('SRID=2274;LINESTRING (1 2,4 5,3 4,4 6,5 7,6 7)').AsTextZM() as geom;
geom
-----------------------------------------
LINESTRING (1 2, 4 5, 3 4, 4 6, 5 7, 6 7)
(1 row affected)
select [dbo].[STGeomFromEWKT]('SRID=2274;LINESTRINGZ (1 2 3,3 4 5,4 6 6,5 7 7,6 7 8)').AsTextZM() as geom;
geom
----------------------------------------------
LINESTRING (1 2 3, 3 4 5, 4 6 6, 5 7 7, 6 7 8)
(1 row affected)
select [dbo].[STGeomFromEWKT]('SRID=2274;LINESTRINGM (1 2 3,3 4 5,4 6 6,5 7 7,6 7 8)').AsTextZM() as geom;
geom
-----------------------------------------------------------------------
LINESTRING (1 2 NULL 3, 3 4 NULL 5, 4 6 NULL 6, 5 7 NULL 7, 6 7 NULL 8)
(1 row affected)
select [dbo].[STGeomFromEWKT]('MULTILINESTRING ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------
MULTILINESTRING ((1 2 3, 4 5 6, 3 4 5), (4 5 6, 5 6 7, 5 6 7))
(1 row affected)
select [dbo].[STGeomFromEWKT]('MULTILINESTRING Z ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------
MULTILINESTRING ((1 2 3, 4 5 6, 3 4 5), (4 5 6, 5 6 7, 5 6 7))
(1 row affected)
select [dbo].[STGeomFromEWKT]('MULTILINESTRING M ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------------------------
MULTILINESTRING ((1 2 NULL 3, 4 5 NULL 6, 3 4 NULL 5), (4 5 NULL 6, 5 6 NULL 7, 5 6 NULL 7))
(1 row affected)
select [dbo].[STGeomFromEWKT]('SRID=2287;MULTILINESTRING ZM ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------------------------
MULTILINESTRING ((1 2 NULL 3, 4 5 NULL 6, 3 4 NULL 5), (4 5 NULL 6, 5 6 NULL 7, 5 6 NULL 7))
(1 row affected)
select [dbo].[STGeomFromEWKT]('POLYGON((0 0 1,10 0 1,10 10 1,0 10 1,0 0 1))').AsTextZM() as geom;
geom
-------------------------------------------------
POLYGON ((0 0 1, 10 0 1, 10 10 1, 0 10 1, 0 0 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('POLYGONZ((0 0 1,10 0 1,10 10 1,0 10 1,0 0 1))').AsTextZM() as geom;
geom
-------------------------------------------------
POLYGON ((0 0 1, 10 0 1, 10 10 1, 0 10 1, 0 0 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('POLYGONM((0 0 1,10 0 1,10 10 1,0 10 1,0 0 1))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------
POLYGON ((0 0 NULL 1, 10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1, 0 0 NULL 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('POLYGONZM((0 0 NULL 1, 10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1, 0 0 NULL 1))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------
POLYGON ((0 0 NULL 1, 10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1, 0 0 NULL 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION (POINT(0 0 1), LINESTRING(10 0 1, 10 10 1, 0 10 1))').AsTextZM() as geom;
geom
------------------------------------------------------------------------
GEOMETRYCOLLECTION (POINT (0 0 1), LINESTRING (10 0 1, 10 10 1, 0 10 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION Z(POINT(0 0 1), LINESTRING(10 0 1, 10 10 1, 0 10 1))').AsTextZM() as geom;
geom
------------------------------------------------------------------------
GEOMETRYCOLLECTION (POINT (0 0 1), LINESTRING (10 0 1, 10 10 1, 0 10 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION M(POINT(0 0 1), LINESTRING(10 0 1, 10 10 1, 0 10 1))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION (POINT (0 0 NULL 1), LINESTRING (10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1))
(1 row affected)
select [dbo].[STGeomFromEWKT]('COMPOUNDCURVE M((2173369.79254475 259887.575230554 2600,2173381.122467 259911.320734575 2626.3106),CIRCULARSTRING (2173381.122467 259911.320734575 2626.3106,2173433.84355779 259955.557426129 0,2173501.82006501 259944.806018785 2768.24))').AsTextZM() as geom
geom
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMPOUNDCURVE ((2173369.79254475 259887.575230554 NULL 2600, 2173381.122467 259911.320734575 NULL 2626.3106), CIRCULARSTRING (2173381.122467 259911.320734575 NULL 2626.3106, 2173433.84355779 259955.557426129 NULL 0, 2173501.82006501 259944.806018785 NULL 2768.24))
(1 row affected)
Conclusion
The function is shipped with the SQL Server package of TSQL functions available in the shop of this website.
I hope this function is of use to someone.