PostGIS extract coordinates from POLYGON

Question

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

Answer 1

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;

Answer 2

Cast the GeoJson geometry to json then extract the coordinates:

SELECT 
  ST_AsGeoJSON(geom) :: json->'coordinates' AS coordinates
FROM
  your_table;

See the Examples section in the ST_AsGeoJSON docs as well as the JSON functions reference for PostgreSQL.

Answer 3

Trimming is the easiest way to go:

select btrim(st_astext(coverage_area), 'POLYGON()') from some_table;

If you need more flexibility also covering linestrings, points, and similar:

select regexp_replace(st_astext(coverage_area), '[A-Z()]', '', 'g')

Answer 4

You can also use postgresql string functions to simplify the query.

SELECT substring(left(St_astext(coverage_area),-2),10) FROM tablename;

This will remove "POLYGON((" at the beginning and "))" at the end.