Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question
your looking for the midpoint/centrepoint of each road? - the centroid of a u-shaped road would be outside the geometry, correct? – Mapperz Dec 17 '12 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 Dec 17 '12 at 20:26
So your looking for the Bounding Box of each feature.… – Mapperz Dec 17 '12 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 Dec 17 '12 at 20:46

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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