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

`ST_AsText` delivers a WKT representation, which is ISO standardized and thus this format will not change. So the best and by far fastest way is to simply strip the parentheses from the WKT string — The Coprolal, Oct 01 '18 at 13:11

Tom-db · Accepted Answer · 2015-10-23T10:21:52.640

17

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;

edited Oct 23 '15 at 10:21

answered Oct 23 '15 at 10:06

Tom-db

6,528
3
30
44

Thank you, that did it just fine :) – Harry Geo Oct 23 '15 at 10:18

score 12 · Answer 2 · answered Sep 16 '19 at 02:41

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.

score 3 · Answer 3 · answered Jul 24 '17 at 12:44

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')

answered Jul 24 '17 at 12:44

The Coprolal

896
8
8

score 2 · Answer 4 · edited Feb 11 '16 at 15:52

2

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.

edited Feb 11 '16 at 15:52

Alig

314
4
17

answered Oct 23 '15 at 10:28

geoandri

2,360
2
15
28

PostGIS extract coordinates from POLYGON

Query

Output

4 Answers4