• Blog posts

    Bio

    by David Crowther

    SQL_Server_DC_01.png

    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.

    SQL_Server_DC_02.jpg

    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.

    SQL_Server_DC_03.jpg

    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.

    SQL_Server_DC_04.jpg

    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]

    SQL_Server_DC_05.jpg

    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]

    SQL_Server_DC_06.jpg