Transform Natural Earth data to 27700

Question

I'd like to know how (if possible) to transform the shapefiles I've downloaded from the natural earth datasets which are in EPSG:4326 to BNG EPSG:27700.

So far I've loaded the shapefiles I need into a Postgresql database through the Postgis shapefile loader. Normally I would have the data stored as separate lat/lng columns and run a query such as..

UPDATE table_name 
    SET geom_column = ST_SetSRID(ST_MakePoint(wgslng,wgslat),27700);

This time however the files are not supplied with lat/lng only a geom column in 4326. I'm using the populated places, coastlines, land, ocean and urban areas shp files.

Is there a method that would allow me to transform the 4326 geom column to 27700? I need the data in BNG so as I can measure distances in metres.

Answer 1

Select ST_Transform(geom, 27700) AS newGEOM FROM TABLENAME

Will get you the transformed geometry called "newGEOM". Using this, you could add other commands such as ST_X() for getting the X-Coordinate:

Select ST_X(ST_Transform(geom, 27700)) AS XCoordinate FROM TABLENAME