SpatialDB Advisor

Getting the number of coordinate dimensions of a geometry/geography object in SQL Server 2008

The OGC standard has an STDimension() method defined on geometry/geography objects and this is implemented in SQL Server 2009. This function returns the inherent (or topological) dimensionality of an object: for a point this is 0; for a linestring this is 1; and for a polygon this is 2.

SELECT [name], [geom].STDimension() as [dim]
  FROM (  select 'Point' as name, geometry::STGeomFromText('POINT(3 3)', 0) as geom
union all select 'LineString' as name, geometry::STGeomFromText('LINESTRING(0 0, 3 3)', 0) as geom
union all select 'Polygon' as name, geometry::STGeomFromText('POLYGON((0 0, 3 0, 0 3, 0 0))', 0) as geom) as a;

But what if you wanted to know the coordinate dimensionality of an object? That is whether an object is:

• 2D ie has only X,Y ordinates; or
• 3D ie has X,Y and Z ordinates defined; or
• 4D ie has X,Y,Z and M ordinates defined?

The OGC standard cannot help here and no such function has been implemented in SQL Server.

Now, in Oracle, a method called GET_DIMS() is defined on the sdo_geometry object that returns the coordinate dimensionality of an object and, in PostGIS, a function called ST_NDims() is defined. What can we do for SQL Server 2008 Spatial?

Here is a function called NDims that implements what we are after.

USE [GISDB]  -- Change to your database
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Function [dbo].[NDims]( @p_geom geometry )
  Returns Int
As
BEGIN
    Declare
      @v_ndims Int;
    Begin
        SELECT @v_ndims = ( 2 + 
                          case when c.point.Z IS NULL then 0 else 1 end + 
                          case when c.point.M IS NULL then 0 else 1 end )
          FROM (SELECT geometry::STGeomFromText(b.PointText,0) as point
                  FROM (select Top 1
                               case when geom.STGeometryType() = 'Point'           then geom.AsTextZM()
                                    when geom.STGeometryType() = 'MultiPoint'      then geom.STGeometryN(1).AsTextZM()
                                    when geom.STGeometryType() = 'LineString'      then geom.STPointN(1).AsTextZM()
                                    when geom.STGeometryType() = 'MultiLineString' then geom.STGeometryN(1).STPointN(1).AsTextZM()
                                    when geom.STGeometryType() = 'Polygon'         then geom.STExteriorRing().STPointN(1).AsTextZM()
                                    when geom.STGeometryType() = 'MultiPolygon'    then geom.STGeometryN(1).STExteriorRing().STPointN(1).AsTextZM()
                                 end as pointText
                          from (select case when @p_geom.STGeometryType() = 'GeometryCollection' 
                                            then @p_geom.STGeometryN(1) 
                                            else @p_geom 
                                         end as geom ) as a
                ) as b
          ) as c;
         RETURN @v_ndims;
    END;
END
GO

Note that the function is defined only on the geometry object type. The function can be called for geography object types via the use of my toGeometry/toGeography functions.

Testing

Testing this function with a variety of data returns the following:

          select dbo.ndims(geometry::STGeomFromText('POINT(0 1)',0)) as pDim
union all select dbo.ndims(geometry::STGeomFromText('POINT(0 1 2)',0)) as pDim
union all select dbo.ndims(geometry::STGeomFromText('POINT(0 1 2 3)',0)) as pDim;

select dbo.ndims(geometry::STGeomFromText('MULTIPOINT((0 0 0), (1 1 1), (2 2 2), (3 3 3))',0)) as mpDim;

          select dbo.ndims(geometry::STGeomFromText('LINESTRING(0 0, 1 1)',0)) as lDim
union all select dbo.ndims(geometry::STGeomFromText('LINESTRING(0 0 0, 1 1 1, 2 2 2, 3 3 3)',0)) as lDim
union all select dbo.ndims(geometry::STGeomFromText('LINESTRING(0 0 0 0, 1 1 1 1, 2 2 2 2, 3 3 3 3)',0)) as lDim;

          select dbo.ndims(geometry::STGeomFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))',0)) as mlDim
union all select dbo.ndims(geometry::STGeomFromText('MULTILINESTRING((0 0 0,1 1 1,2 2 2),(3 3 3,4 4 4,5 5 5))',0)) as mlDim
union all select dbo.ndims(geometry::STGeomFromText('MULTILINESTRING((0 0 0 0,1 1 1 1,2 2 2 2),(3 3 3 3,4 4 4 4,5 5 5 5))',0)) as mlDim;

          select dbo.ndims(geometry::STGeomFromText('POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))',0)) as dPoly
union all select dbo.ndims(geometry::STGeomFromText('POLYGON ((0 0 0, 1 0 0, 1 1 0, 0 1 0, 0 0 0))',0)) as dPoly;

          select dbo.ndims(geometry::STGeomFromText('MULTIPOLYGON (((20 20, 40 20, 40 40, 20 40, 20 20)), ((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 4 6, 6 6, 6 4, 4 4)))',0)) as dMPoly
union all select dbo.ndims(geometry::STGeomFromText('MULTIPOLYGON (((20 20 0, 40 20 0, 40 40 0, 20 40 0, 20 20 0)), ((0 0 0, 10 0 0, 10 10 0, 0 10 0, 0 0 0), (4 4 0, 4 6 0, 6 6 0, 6 4 0, 4 4 0)))',0)) as dMPoly;

select dbo.ndims(geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(2 3 4),LINESTRING(2 3 4,3 4 5),POLYGON ((0 0 0, 1 0 0, 1 1 0, 0 1 0, 0 0 0)))',0)) as dGC;

Geography

If we have a geography object we can still find its coordinate dimensionality via use of the toGeometry/toGeography functions.

select dbo.NDims(
         dbo.toGeometry(geography::STGeomFromText('POLYGON ((148.0 -44.0, 148.0 -43.0, 147.0 -43.0, 147.0 -44.0, 148.0 -44.0), (147.4 -43.6, 147.2 -43.6, 147.2 -43.2, 147.4 -43.2, 147.4 -43.6))',4326),
                        0)) as dGeogPoly;

I hope this function is useful to somebody.