Category: Uncategorized

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.