SQL – How to Identify Geometry Types

by David Crowther

Question:

How can I identify the Geometry Type of records in a SQL table?

Answer:

A SQL spatial table can contain records with different geometry types and indeed depending on the application that you are using to view the data, the type of geometry can be very important. In order to identify the geometry type for records in a SQL table you can use the SQL window in SQL Server Management Studio.

To View the list of records in your SQL Table simply choose to Select from the table.

SELECT  *  FROM [LCC_GEOSTORE].[dbo].[lcc_planning_apps]

This will list all records and show all fields, including the geometry field. However we cannot determine the geometry type of each feature using the raw geometry values.

If you now edit the SQL query we can show reveal the Well Known Text (WKT) for each record using the STAsText function.

Select ogr_geometry.STAsText( ) as GeometryType from [dbo].[lcc_planning_apps]

Each record in the SQL Table will now be listed with their geometry type defined as Well Known Text and the pairs of coordinates that create the geometry listed in brackets.

Editing the query to add extra columns e.g. requestor and progress, then allow you to add extra information to each record.

Select requestor, progress, ogr_geometry.STAsText( ) as GeometryType from [dbo].[lcc_planning_apps]

Finally, by adding a DISTINCT clause we can show the Unique Geometry records. This can be useful to find where you have duplicate Points, Lines or Polygons.

Select Distinct ogr_geometry.STAsText( ) as GeometryType from [dbo].[lcc_planning_apps]