I want to extract the coordinates of the vertices of a polygon, and then create a point on each of the vertices.
After several searches on the internet with possible solutions to this question, I found this code and I adapted to my data. Obtaining the vertices of a polygon, extracting its coordinates, assigning an identifier in clockwise order, generating points on the vertices and eliminate the "5" point that is equal to the first(vertex where the polygon closes):
--transform coordinate system with newgeom as ( select gid, --geographic identifier st_transform(geom, 4326) as geom from aviation.runways ), --extracting the coordinates of the vertices of the polygon into points coord as ( SELECT gid, ST_dumppoints(geom) as dump FROM newgeom as foo GROUP BY gid, geom), g as ( select gid as gid_original, (coord.dump).path AS part, --get out ring polygon (coord.dump).path as vertex, --get point (vertex) (coord.dump).geom as geom from coord ) select --creating new gid for the subsequent elimination of the redundant "5" vertex row_number() over () as gid, gid_original, vertex, st_x(g.geom) as long, --set x coord of the vertex st_y(g.geom) as lat, --set y coord of the vertex g.geom::geometry('POINT', 4326) as geom, --re-assigning geometry st_astext(geom)as type_geometry from g where (g.gid_original, g.part, g.vertex) NOT IN (select g.gid_original, g.part, max(g.vertex) as max --showing just the vertex not include in the maximum list vertex (i.e last one) from g group by g.gid_original, g.part);
Does anyone know a more efficient way, a simpler code to achieve this?