Search code examples
postgresqlpostgis

PostGIS extract coordinates from POLYGON

I'm trying to extract latitude / longitude values from a polygon column in PostgreSQL that I created using the PostGIS extension, I tried get the coordinates using a query like this

Query

ST_AsText(coverage_area) as coverage_area But the output it returns it's not in a convenient form for me to use.

Output

POLYGON((37.9615819622 23.7216281890869,37.9617173039801 23.7193965911865,37.9633413851658 23.717679977417,37.964559422483 23.7147617340087,37.9644240860015 23.7116718292236,37.9615819622 23.7216281890869))

I need the output to be like this:

37.9615819622 23.7216281890869,37.9617173039801 23.7193965911865,37.9633413851658 23.717679977417,37.964559422483 23.7147617340087,37.9644240860015 23.7116718292236, 37.9615819622 23.7216281890869

I also searched the PostGIS documentation and the only thing I found was the ST_AsGeoJSON that also didn't help me too... Has anyone else encountered this problem?

Thank you.

Note: I know I can create a regex rule and strip down the parentheses but I would like to avoid that and find a way to return "clean" pairs of coordinates

Solution

  • Use the function st_dumppoints to extract all points of a geometry and the functions ST_x and ST_y to extract the coordinates of the points. Then use array_agg and array_to_string for build a row with all coordinates

    Try this query:

    SELECT array_to_string(array_agg, ',') FROM 
(SELECT array_agg( ST_x(geom)||' '||ST_y(geom))  FROM 
    (SELECT (ST_dumppoints(coverage_area)).geom FROM your_table
    ) AS foo_1
) AS foo_2;