Spatial SQL -> MS-SQL 2008. Extract vertices from polyline in spatial query

STNumPoints() method returns number of polyline vertices while STPointN() method extracts actual point in given position. In the beneath query we combine this methods aiming to retrieve all relevant vertices from milano_str and milano_reg layers. The range of vertix positions in every polyline object starts from 1 and ends with STNumPoints(). milano_reg table stores polygons, so we first have to get it's external boundary (polyline) using RingN() method.

USE milano
GO

/*
  num_seq is a tally table that stores numeric sequence from 1 to 10000
*/

select a.id, a.g from dbo.milano_str AS a

  UNION ALL

select a.id, a.g.STPointN(b.n).STBuffer(20)
from dbo.milano_str AS a, dbo.num_seq AS b
where b.n BETWEEN 1 AND a.g.STNumPoints()

  UNION ALL

select a.id, a.g from dbo.milano_reg AS a

  UNION ALL

select a.id, a.g.RingN(1).STPointN(b.n).STBuffer(20)
from dbo.milano_reg AS a, dbo.num_seq AS b
where b.n BETWEEN 1 AND a.g.STNumPoints();


Spatial results will look like this:




sqlexamples.info