Swapping coordinates in plain PostgreSQL polygon data

Question

I have a PostgreSQL 11 table with a plain PostgreSQL Polygon column for storing geographic areas, like this (simplified):

CREATE TABLE areas
(
    name    TEXT NOT NULL,
    bounds  POLYGON NOT NULL
);

I then inserted > 100 rows of manually entered data into this table, with coordinates in the order latitude, longitude (which is the common order in navigation), such as in this example:

INSERT INTO areas (name, bounds) VALUES (
    'Hamburg Harbour',
    '((53.543420, 9.875492), (53.486682, 9.885482), (53.467172, 9.990250), (53.519363, 9.993678),
     (53.521112, 10.011104), (53.533893, 10.018592), (53.544478, 9.965988))'
);

I have now received some other areas with the coordinates in the PostgreSQL Polygon data type's default order x, y (i.e. longitude, latitude). Consequently I decided to swap the coordinates of all my existing areas to be consistent with the newly received ones, as well as with the intended order of the PostgreSQL Polygon data type. A search revealed that the PostGIS ST_SwapOrdinates() function can accomplish this, like thus:

SELECT ST_AsText(ST_SwapOrdinates(bounds::geometry, 'xy'))
FROM areas
WHERE name = 'Hamburg Harbour';

outputting:

POLYGON((9.875492 53.54342,9.885482 53.486682,9.99025 53.467172,9.993678 53.519363,10.011104 53.521112,10.018592 53.533893,9.965988 53.544478,9.875492 53.54342))

So far so good. However, I want to store the swapped coordinates back into a table like the one above, with a plain PostgreSQL Polygon type column. The ST_SwapOrdinates() function returns PostGIS geometry type data, the ST_AsText() function simply converts this into a text representation of the same. Neither of these is of PostgreSQL Polygon data type. Having tried various ways to convert the result back into a plain PostgreSQL Polygon type have been unsuccessful. In a somewhat related post, but converting in the opposite direction, the solution suggests using regexp_replace() to construct a string that conforms to the expected format. I can't believe that there is no simpler way to get a PostGIS geometry converted to PostgreSQL Polygon. Have I missed something obvious?

Edited:

Thanks to @JGH, the answer is simple (I was quite certain that what @JGH suggests is among the many things I had already tried, but apparently not!), namely casting from PostGIS type geometry to PostgreSQL type polygon.

The following creates a new table with swapped coordinates:

CREATE TABLE areas_swapped AS
    SELECT name, ST_SwapOrdinates(bounds::GEOMETRY, 'xy')::POLYGON
    FROM areas;

JGH JGH · Accepted Answer · 2021-07-22T18:27:29

You can just cast to polygon:

 select 'POLYGON((9.875492 53.54342,9.885482 53.486682,9.99025 53.467172,9.993678 53.519363,10.011104 53.521112,10.018592 53.533893,9.965988 53.544478
        ,9.875492 53.54342))'::geometry::polygon;
                                                                                 polygon                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ((9.875492,53.54342),(9.885482,53.486682),(9.99025,53.467172),(9.993678,53.519363),(10.011104,53.521112),(10.018592,53.533893),(9.965988,53.544478),(9.875492,53.54342))
(1 row)

Swapping coordinates in plain PostgreSQL polygon data

1 Answers