Convert coordinates from EPSG 4326 to EPSG 28992 in PostGIS

Question

I imported a CSV file with coordinates into a PostGIS table. This table contains a column "latitude" and a column "longitude". The coordinates in these columns are in EPSG:4326. What is the best way to convert these coordinates to EPSG:28992? Is it possible to do that directly, or should I convert these coordinates to geometry first?

Answer 1

Once you have loaded the CSV into postgresql, you can use postgis to build the geometry temporarily, transform it, and extract the lat/lon like this:

select
    schnum --school #'s from my schools table
    , lon --original 4326
    , lat --original 4326
    , ST_X(ST_Transform(st_setsrid(st_makepoint(lon, lat), 4326), 102654)) as new_lon
    , ST_y(ST_Transform(st_setsrid(st_makepoint(lon, lat), 4326), 102654)) as new_lat
from
    csv

You can then export to a new CSV.