Spatial SQL -> PostGIS. Extract vertices from polygon boundary

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


-- create num_seq tally table with numeric sequence from 1 to 1000
DROP TABLE IF EXISTS num_seq;

SELECT a.generate_series AS n INTO num_seq
FROM (select * from generate_series(1,1000)) AS a
ORDER BY a.generate_series;

CREATE UNIQUE INDEX idx_num_seq ON num_seq (n);
-- ------------------------------------------------------------------

-- extract coordinates:

SELECT a.id, b.n,
  st_x(st_pointn(st_exteriorring(a.geom),b.n)) AS x_long,
  st_y(st_pointn(st_exteriorring(a.geom),b.n)) AS y_lat
FROM milano_reg AS a, public.num_seq AS b
WHERE b.n <= st_npoints(st_exteriorring(a.geom))
ORDER BY a.userid, b.n;




sqlexamples.info