SQL 2008 Spatial ordering of objects

Question

I have a dataset containing the start and end points (both OSGB36 and corresponding WGS84 coordinates) of sections of a road network. I am able to import this data in SQL and create geometry and geography data types for the start and end points and for the entire object (LINESTRING etc).

Viewing the coordinates in SQL query window enables me to see the spatial results for this coordinates and viewing the geometry of the entire object displays the entire road network visually in the spatial results.

However, if I was wishing to return the road objects as a linear representation - in other words as a standard result set and order them on geographic position e.g. north to south, east to west etc - what would be the best method. I am currently assuming that calculating a distance from the start point would be the best approach - but I would appreciate some Spatial SQL 2008 expertise.

Answer 1

You could get the bounding box of each shape w/ .STEnvelope(), and then sort by one of the corners using .STPointN().STX and .STY. That wouldn't be too expensive.