3

I am trying to load latitude and longitude from a CSV file to PostgreSQL database table using a database function. The db function has the following code to transform the lng/lat to the proper point data: "ST_Transform(ST_SetSRID(ST_MakePoint(longitude,latitude),4326),2272)".

The database table has a field called 'location' and is configured as : geometry(Point,2272).

It was working fine until I had to reinstall the database, and subsequently reinstalled PostGIS / GDAL / proj / etc.

Now, the db function generates a point data that is somewhere in Antartica. I compared the data by running

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(-77.622917,40.67931),4326),2272); and got '0101000020E0080000487BB57A23933E41987B010FC2ED1D41'.

Whereas if I run the same query in my test server, I get the correct result as: '0101000020E0080000397BB57A23933E41987B010FC2ED1D41'

It's just a difference of 2 digits ( bolded ). How can I rectify this ?

dev server:
POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 3.1.2, released 2020/07/07" LIBXML="2.9.2" LIBJSON="0.15" LIBPROTOBUF="1.3.3" TOPOLOGY RASTER

test server:
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.0-CAPI-1.11.0 673b9939" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.1, released 2018/06/22" LIBXML="2.9.1" LIBJSON="0.12" LIBPROTOBUF="1.3.0" TOPOLOGY RASTER

Improve this question
9
  • I had a similar experience and I fix it by uploading the latitude and longitude as a string and then create another point geometry column(GEOM) from latitude and longitude columns. I think you can transform your point geometry during the update column script.
    – Praveen Kumar
    Commented Sep 3, 2020 at 5:38
  • 3
    If I run those two hex strings through ST_AsText(ST_GeomFromEWKB(E'\\x0101...')) I get the same value, down to a few nanometers (I think — I got lost counting the decimals).
    – Ture Pålsson
    Commented Sep 3, 2020 at 6:02
  • 1
    Being in Antarctica means probably that latitude and longitude has been flipped. But as pointed above, the two EWKB presentations do not make that flip. Do you use the same upgraded GDAL and Proj versions in other places of your workflow?
    – user30184
    Commented Sep 3, 2020 at 8:18
  • @PraveenKumar - tried creating a geometry column, and updated it with a point geometry from lat/lng. Then used ST_Transform in the update script. Did not help. It is generating the same values as before.
    – mdutt
    Commented Sep 3, 2020 at 15:02
  • @user30184 - made sure that the long and lat are not flipped. No, I am not using the upgraded GDAL and Proj versions in any other places.
    – mdutt
    Commented Sep 3, 2020 at 15:04

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.