In PostGIS, how do I swap the lat/log of invalid points, like MySQL ST_SwapXY?

Question

MySQL has a function ST_SwapXY which swaps the x and y values (long, and lat). A function, like ST_SetSRID, that's useful if you import data incorrectly.

Accepts an argument in internal geometry format, swaps the X and Y values of each coordinate pair within the geometry, and returns the result.

How do I do this in PostGIS?

Answer 1

With PostGIS you would use the accessors ST_X() and ST_Y(), and recreate the point with a constructor like ST_MakePoint(x,y) for example:

SELECT ST_AsText(geom) AS old, ST_AsText(newgeom) AS new
FROM ( VALUES (ST_MakePoint(0,1)) ) AS t(geom)
CROSS JOIN LATERAL
  ST_SetSRID(  ST_MakePoint(ST_Y(geom),ST_X(geom)), ST_SRID(geom)  )
AS t2(newgeom);
    old     |    new     
------------+------------
 POINT(0 1) | POINT(1 0)
(1 row)

You can create a simple function _ST_SwapXY to accomplish this on points.

CREATE FUNCTION _ST_SwapXY(geom geometry(point))
RETURNS geometry(point) AS $$
  SELECT ST_SetSRID(  ST_MakePoint(ST_Y(geom),ST_X(geom)), ST_SRID(geom)  );
$$ LANGUAGE sql
IMMUTABLE;

CREATE FUNCTION _ST_SwapXY(geog geography(point))
RETURNS geography(point) AS $$
  SELECT ST_SetSRID(  ST_MakePoint(ST_Y(geog::geometry),ST_X(geog::geometry)), ST_SRID(geog)  )::geography;
$$ LANGUAGE sql
IMMUTABLE;

Testing it over a geography and geometry object (EWKT is Extended Well-known Text (WKT) to include the SRID.)

SELECT ST_AsEWKT(pt) AS orig,
  ST_AsEWKT(  _ST_SwapXY(pt::geometry)   ) AS geom,
  ST_AsEWKT(  _ST_SwapXY(pt::geography)  ) AS geog
FROM ( VALUES
  (ST_MakePoint(0,1)),
  (ST_SetSRID(ST_MakePoint(0,1),4326))
) AS t(pt);
         orig         |         geom         |         geog         
----------------------+----------------------+----------------------
 POINT(0 1)           | POINT(1 0)           | SRID=4326;POINT(1 0)
 SRID=4326;POINT(0 1) | SRID=4326;POINT(1 0) | SRID=4326;POINT(1 0)

Note: MySQL's ST_SwapXY() is substantially more powerful covering LINESTRINGS and POLYGON too, but hopefully your structures are not internally that screwed up. If they are you'll have to recursively do this.