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 ?

  • your looking for the midpoint/centrepoint of each road? - the centroid of a u-shaped road would be outside the geometry, correct?
    – Mapperz
    Commented Dec 17, 2012 at 20:13
  • I'm a programmer , not GIS person , So my choice of words might be wrong. I'm looking to get a lat/lon that I can center the map on. preferred the middle of the road.
    – Alophind
    Commented Dec 17, 2012 at 20:26
  • So your looking for the Bounding Box of each feature. stackoverflow.com/questions/8988453/…
    – Mapperz
    Commented Dec 17, 2012 at 20:37
  • I probably lost you. I have sql DB with geographic objects. I want to get a lat lon column for each object so I can center the map on it (it doesn't have to show the entire road or zoom to extent) I just need coords to center map on .
    – Alophind
    Commented Dec 17, 2012 at 20:46

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.