0

I have a .txt file with some x, y coordinates that I assume are in EPSG:25831. I want to transform these coordinates to EPSG:4326 in Decimal Degrees format. Example of one coordinate:

x= 539746,19
y= 4743262,723

I know that I can run this for transforming the coordinates:

ST_Transform(ST_SetSRID(ST_MakePoint(geocoded_testX.lon_gc, geocoded_testX.lat_gc),25831),4326))

But I don't know how to export the table in .txt or .csv the with the x, y columns in Decimal Degrees format.

Any ideas?

1
  • 2
    or you could import the CSV file into QGIS, and then save it as a CSV with a new projection
    – Ian Turton
    Jul 6, 2021 at 7:52

2 Answers 2

4

In one line, you can get x and y values and copy to CSV

\COPY (
SELECT *,
       ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(geocoded_testX.lon_gc, geocoded_testX.lat_gc), 25831),4326))) AS x,
       ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(geocoded_testX.lon_gc, geocoded_testX.lat_gc), 25831),4326))) as y
FROM geocoded_testX
) TO 'file.csv' CSV HEADER;

For recipe to import/export CSV in PostgreSQL https://gist.github.com/nepsilon/f2937fe10fe8b0efc0cc

I also used \COPY instead of COPY as I use psql. If you use COPY, path will have to be changed from TO 'file.csv' to TO '/your/absolute/path/file.csv'

1
  • 1
    Note that the server side COPY can ever only access locations that are owned by the OS PostgreSQL administrator account (i.e. postgres, which is not equal to the DB role, but set at installation time).
    – geozelot
    Jul 7, 2021 at 7:28
2

In the end I did it directly in postgres reprojecting geom , creating two new columns and then exporting on .csv using this sql lines:

ALTER TABLE <schema>.<table>
 ALTER COLUMN geom TYPE geometry(point, 4326)
  USING ST_Transform(ST_SetSRID(geom, 3857), 4326)

ALTER TABLE <schema>.<table>
 ADD COLUMN "x_4326" float,
 ADD COLUMN "y_4326" float 

UPDATE <schema>.<table> SET "x_4326" = ST_x(geom)
UPDATE <schema>.<table> SET "y_4326" = ST_y(geom)

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.