I had a case of needing to convert Ordnance Survey grid coordinates like 548420,247240 that were in a table from NAPTAN into latitude & longitude so that I could display them in a map.
Most references online appear to do this by running the data through some application then importing it back into the database but as I’m using PostGIS I wanted to do it directly on the database.
So here’s how I did it.
First here’s the table I want to process:
gis=> select crscode,stationname,easting,northing from naptan limit 5; crscode | stationname | easting | northing ---------+---------------------------------+---------+---------- ABA | Aberdare Rail Station | 300400 | 202800 AUR | Aberdour Rail Station | 319100 | 685400 AVY | Aberdovey Rail Station | 260600 | 296000 ABE | Aber Rail Station | 314870 | 186950 AGL | Abergele & Pensarn Rail Station | 294612 | 378681 (5 rows)
Now you see we have the rail station’s coordinates as an easting & northing pair.
First I added a geometry column to the table naptan:
gis=> select AddGeometryColumn( 'public', 'naptan', 'the_geom', 27700, 'POINT', 2);
addgeometrycolumn
------------------------------------------------------
public.naptan.the_geom SRID:27700 TYPE:POINT DIMS:2
(1 row)
Here 27700 is the ESRI code for the OS grid system.
Next I updated the table to create geometries for each entry:
gis=> update naptan set the_geom=GeomFromText('POINT('||easting||' '||northing||')',27700);
UPDATE 2603
This updates each row using the easting and northing columns.
Finally I needed to add two new columns for latitude & longitude and populate them with the final values:
gis=> alter table naptan add column lat real; ALTER TABLE gis=> alter table naptan add column long real; ALTER TABLE gis=> update naptan set long=st_x(st_transform(the_geom,4326)), lat=st_y(st_transform(the_geom,4326)); UPDATE 2603
That’s it, we now have the table populated with lat/long coordinates:
gis=> select crscode,stationname,lat,long from naptan limit 5; crscode | stationname | lat | long ---------+---------------------------------+---------+---------- ABA | Aberdare Rail Station | 51.7151 | -3.44308 AUR | Aberdour Rail Station | 56.0546 | -3.30056 AVY | Aberdovey Rail Station | 52.544 | -4.05707 ABE | Aber Rail Station | 51.575 | -3.22983 AGL | Abergele & Pensarn Rail Station | 53.2946 | -3.58262
Hopefully this is correct – if it isn’t please let me know but the final data looks ok to me.
