Is there a way to get centroid of geographic object in MSSQL?

Question

I have a table of OSM geographic roads in MSSQL 2012.

I want to create a new column with the centroid of each road (So I can set center on OpenLayers map for each road).

How can I do it ?

Answer 1

I think what you want to do is this - to get the closest point on the road to the centre of the road's bounding box? If so, then this will do the trick:

declare @road geometry
declare @point geometry

select @road = geom from Roads where id = 12345
select @point = geom.STEnvelope().STCentroid() from Roads where id = 12345

select @point.ShortestLineTo(@road).STPointN(2).STAsText()

The ShortestLineTo method is new in MSSQL 2012, and there's some discussion of using it in this way here.