1

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.