# Switch coordinates in linestring

I made a silly mistake: when creating linestrings in my PostGis database i inverted the lon and lat values. Which isn't a problem for postgis itself, but for my visualization and intersections. ;)

Of course i only recorgnized this after importing lots of stuff. Instead of simply re-running the import i'm curios if there is a way i could swap the X/Y-Coordinates of every point of a linestring?

e.g.:

``````LINESTRING(lat1 lon1, lat2 lon2, lat3 lon3); // what i got
SomeMagicMethod();
LINESTRING(lon1 lat1, lon2 lat2, lon3 lat3); // what i want
``````

Take a look at ST_FlipCoordinates (PostGIs 2.0 and up). That function was created to help people in your situation.

• Thanks for pointing this out. Looks like it's exactly what i need...but i'm stuck on PostGis 1.5... Mar 11, 2014 at 14:08
• I would strongly recommend an upgrade! Mar 11, 2014 at 14:32

Solution 1:

Because you are stuck on PostGIS 1.5, you can look at this example about switching the X/Y coordinates for every point of a LineString:

Presuming this is the PostGIS table:

and this is how the LineStrings looks:

you can run the code below to flip the coordinates:

``````DO \$\$
DECLARE
r1 RECORD;
r2 RECORD;
rowNo int;
tStatement text;
tPoint varchar;
tNewLinestring varchar;
BEGIN
tStatement := '';
FOR r1 IN SELECT id_0 FROM "my_lines" LOOP
rowNo := 0;
tPoint := '';
tNewLinestring := '';
FOR r2 IN SELECT id_0, (ST_DumpPoints(geom)).geom FROM "my_lines" WHERE id_0 = r1.id_0 LOOP
rowNo := rowNo + 1;
tPoint := ST_Y(ST_AsText(r2.geom)) || ' ' || ST_X(ST_AsText(r2.geom)) || ',';
tNewLinestring := tNewLinestring || tPoint;
END LOOP;
tNewLinestring := '''' || 'LINESTRING(' || trim(trailing ',' from tNewLinestring) || ')' || '''';
tStatement := 'UPDATE "my_lines" ' || 'SET "geom" = ST_GeomFromText('
|| tNewLinestring || ', 4326) WHERE id_0 = ' || r1.id_0;
-- RAISE NOTICE 'statement2: %', tStatement;
EXECUTE tStatement;
END LOOP;
RETURN;
END\$\$;
``````

to obtain the following result:

Solution 2:

Bellow is a quick and dirt solution to solve the coordinates switching. (This time, using no code!)

• Step 1: Add your PostGIS table as a layer into QGIS:

• Step 2: Install the Swap XY plugin.

• Step 3: Press the Swap XY button in the plugin toolbar:

• Step 4: Import your shapefile back to PostGIS.
• Thanks for this snippet. But i finally solved it with a small perl-script i hacked together ;) Mar 18, 2014 at 12:36