I want to extract the coordinates of the vertices of a polygon, and then create a point on each of the vertices.

Sample of Polygon geometry table (multipolygon): polygon feature representation of airport worldwide runways

Wished output: point feature representation of the polygon vertex


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 (
        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 (
        ST_dumppoints(geom) as dump
        FROM newgeom as foo
        GROUP BY gid, geom),
    g as (
        gid as gid_original,
        (coord.dump).path[1] AS part, --get out ring polygon
        (coord.dump).path[3] as vertex, --get point (vertex)
        (coord.dump).geom as geom
        from coord
        --creating new gid for the subsequent elimination of the redundant "5" vertex
        row_number() over () as gid,
        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
        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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.