3

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

2 Answers 2

4

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

2
  • 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
  • 3
    I would strongly recommend an upgrade!
    – atlefren
    Mar 11, 2014 at 14:32
1

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:

enter image description here

and this is how the LineStrings looks:

enter image description here

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:

enter image description here


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:

enter image description here

  • Step 2: Install the Swap XY plugin.

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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