5

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 ?

Alophind
  • 2,707
  • 5
  • 40
  • 75
  • 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. http://stackoverflow.com/questions/8988453/how-to-get-the-current-viewport-of-the-map-out-of-openlayers-as-geometry-boundi – 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

1 Answers1

5

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.

Glorfindel
  • 1,096
  • 2
  • 9
  • 14
Dave Lewis
  • 84
  • 3