PostGIS Retrieves 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 to get the coordinates using a query like

Query

ST_AsText(coverage_area) as coverage_area But the conclusion he returns is not in a form convenient for me.

Exit

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 result 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 looked for the PostGIS documentation, and the only thing I found was ST_AsGeoJSONthat didn't help me either ... Has anyone else encountered this problem?

Thanks.

Note : I know that I can create a regular expression rule and break the parentheses, but I would like to avoid this and find a way to return the “pure” coordinate pairs

Answer 1

st_dumppoints, ST_x ST_y, . array_agg array_to_string

:

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

postgresql , .

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

"POLYGON ((" "))" .

Answer 3

- :

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

, , ..:

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