## Top 5 Recent Articles

##### ARTICLES CATEGORIES

- Biography (1)
- Blog (27)
- Changes (1)
- Customers (1)
- Education (2)
- General Software (21)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Spatial (2)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (61)
- PostGIS (20)
- Press Releases (1)
- Source code (6)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (17)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- XML (5)

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

name | dim |
---|---|

Point | 0 |

LineString | 1 |

Polygon | 2 |

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;

pDim |
---|

2 |

3 |

4 |

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

mpDim |
---|

3 |

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;

lDim |
---|

2 |

3 |

4 |

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;

mlDim |
---|

2 |

3 |

4 |

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;

dPoly |
---|

2 |

3 |

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;

dMPoly |
---|

2 |

3 |

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;

dGC |
---|

3 |

**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;

dGeogPoly |
---|

2 |

I hope this function is useful to somebody.

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