Obtaining Centroid from Polygon in SQL Server 2008 R2

Question

I have several spatial layers created in QGIS that are held as spatial data in SQL Server 2008 R2. What I want to do is to find the centroid (OS Easting and Northing) of the polygon from the geometry. But I want to do this in a view that I have of the data. Each table I want to do this on, has a Geometry field. I've looked at examples of using ST.Centroid, but none of them seem to use a geometry field from a table.

Help on this will be invaluable.

Answer 1

In SQL Server 2008 R2, you should be able to use spatial function: STCentroid

If you have a geometry column (i.e. called geom) stored in the myTable, you can do the following:

select geom.STCentroid() from myTable

To create a view:

CREATE VIEW dbo.View1
  AS
    SELECT     geom, geom.STCentroid() AS CentroidPoint, geom.STCentroid().STAsText() AS Centroid
    FROM       dbo.myTable
GO