Search code examples
sqlpostgresqlgispostgis

Any way to recover data from GPS coordinates that were inserted as LAT/LONG as geography data type in PostGIS?

I inserted all of my data points into a geography field as 'POINT(43.870683 -112.359383)', that is, latitude first and then longitude. Now, when I try to extract the coordinates with ST_X and ST_Y, the X coordinate appears correct, but the Y coordinate is putting me way out in the atlantic ocean. I'm assuming PostGIS left the latitude alone, and tried to reason about the longitude. The points I get back out from that insertion are:

   st_x    |    st_y
-----------+------------
 43.870683 | -67.640617

Is there any way to recover the proper coordinates?

Edit: version info

            postgis_version
---------------------------------------
 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

PostgreSQL 13.3

                        gps
----------------------------------------------------
 0101000020E61000007217618A72EF4540BF1072DEFFE850C0

gps | geography(Point,4326)

insert query (now i know this was the WRONG order, can I recover data somehow?)

insert into buildings (gps) values('POINT(43.870683 -112.359383)')

select ST_X(gps::geometry), ST_Y(gps::geometry) from buildings


   st_x    |    st_y
-----------+------------
 43.870683 | -67.640617
              ^^^^^^^^^ very wrong, not what I input
Solution

  • You winding up with -67.640617 initially confused me, how was that possible? Well I can derive that exact value (I am sure it is not exactly how it got that and probably is not a general formula). Consider latitude (Y coordinate) is -90 <= latitude <= 90 with negative values meaning south of the equator yet continuing in the same line of movement after -90 you begin moving north for what remains. The value you have can be derived as: Note: 180 from equator -> south pole -> equator = 180 degrees of latitude traversed.

    with on_earth (gps) as
     ( values ( POINT(43.870683, -112.359383)) )
select ST_Y(gps::geometry) "Latitude"
     , sign( ST_Y(gps::geometry) ) * (180 - abs( ST_Y(gps::geometry))) "Derived Latitude"        
  from on_earth;

    Now this begs 2 questions:

    1. How are you inserting the values: direct SQL or thru some interface (ORM/API);
    2. Did the transformation occur during input or on the output.

    Running direct SQL with Postgres 14.1 and PostGIS 3.1.4 I did not get the -67.640617 for latitude after inserting the -112.359383.

    Except #2 none of the above actually address your question so much as trying to understand the results. If the answer the #2 is the transformation occurred during input and the -67 is the value stored then your will just have to reload your data. Not the answer you want, but what you would be facing. If however that is an display output transformation then recovery is a simple update statement.
    Postgres (and AFAIK all other database) you can use old data values until at least statement complication. You can reverse your columns just by selecting the columns in reverse reverse; ie select ST_Y for X and ST_X for Y. So try:

    update building  
   set gps = point( st_y(gps::geometry)    -- move Y value to X
                  , st_x(gps::geometry)    -- move X value to Y
                 );

    It worked in my environment, beploy is my full test sequence. I would normally create a fiddle but neither db<>fiddle nor SQL Fiddle support PostGIS.

    create table building(id   integer generated always as identity
                     , gps point
                     );
                         
insert into building(gps) values (POINT(43.870683, -112.359383));

select * from building; 
/* Result
*  id | gps                    
*  1  | (43.870683,-112.359383)
*/

update building  
   set gps = point( st_y(gps::geometry)    -- move Y value to X
                  , st_x(gps::geometry)    -- move X value to Y
                 );


select * from building; 
/* Result
*  id | gps                     
*  1  | (-112.359383,43.870683) 
*/

    If the transformation of the data value occurred on input (insert) this will not restore the actual value. But it would seem you have nothing to lose with a possible big gain. Worth a try!