[GIS] How to generate 3d WKT FROM A Sql Server geometry or geography types

sql serverwell-known-text

If I have the point POINT (-75.45454 25.12345 1024 ) in a geometry or geography column in SQL Server, but render it with STAsText() I lose the Z dimension.

SELECT geography::STGeomFromText('POINT (-75.45454 25.12345 1024 )', 4326).STAsText() AS point

POINT (-75.45454 25.12345)

How do I get the full WKT? There is nothing listed in OGC Methods on Geography Instances.

Best Answer

The answer is AsTextZM (), which is listed on the Extended Methods on Geography.

WITH geo AS (
    SELECT geography::STGeomFromText('POINT (-75.45454 25.12345 1024 )', 4326) AS point)
SELECT 
    geo.point.AsTextZM() AS WTKFull,
    geo.point.STAsText() AS WTK2d
FROM geo

Related Solutions

[GIS] Detecting invalid WKT in text column in SQL Server

Here's a stored procedure that will do it, no CLR needed, unfortunately it can't be a function because it uses TRY... CATCH...

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestWKT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestWKT]

GO
------------------------------------------
CREATE PROCEDURE TestWKT(
    @wkt varchar(max),
    @srid int) 
AS
BEGIN

SET NOCOUNT ON;

DECLARE @valid bit;

BEGIN TRY
    SET @valid = geometry::STGeomFromText(@wkt, @srid).STIsValid();
END TRY
BEGIN CATCH
    SET @valid = 0
END CATCH

SELECT @valid;

END
------------------------------------------------
GO

exec TestWKT 'garbage', 4326

exec TestWKT 'POINT(-84 32)', 4326

[GIS] Storing SRID:4326 data as Geometry or Geography

If you do not need to measure distances over the spheroid then geometry suits well. The PostGIS manual contains understandable info about this http://postgis.net/docs/manual-2.0/using_postgis_dbmanagement.html#PostGIS_Geography and it is valid also for MSSQL.

Best Answer

Related Solutions

[GIS] Detecting invalid WKT in text column in SQL Server

[GIS] Storing SRID:4326 data as Geometry or Geography

Related Question