Reverse coordinates in PostgreSQL polygon column

0
votes

I have a PostgreSQL 11 table containing a POLYGON column, with currently about 100 rows, and each polygon with between 3 and about 15 corner points. It now turns out that I mistakenly created my data with reversed X and Y coordinates. Therefore I now would like to swap the coordinates to rectify this mistake.

Minimal working example:

CREATE TABLE geom_example (shape POLYGON);
INSERT INTO geom_example (shape) VALUES ('(1, 2), (5, 2), (1, 3)');

I would like to reverse the coordinates so that

(1, 2), (5, 2), (1, 3)

becomes:

(2, 1), (2, 5), (3, 1)

for all the rows of the table.

Is there an easy way to accomplish this?

1
postgresqlpolygon

1 Answers

1
votes

Use ST_SwapOrdinates, which is made just for that:

SELECT ST_AsText(
          ST_SwapOrdinates(
             'POLYGON((1 2,5 2,1 3,1 2))'::geometry,
             'xy'
          )
       );

         st_astext          
════════════════════════════
 POLYGON((2 1,2 5,3 1,2 1))
(1 row)