This was from 2008 but again it applies to today’s potentially disastrous result

View original post 745 more words
This was from 2008 but again it applies to today’s potentially disastrous result
View original post 745 more words
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.
If in emacs you need a different file coding system (line terminator), for example you are on a windows system and need to type a unix like text file (or vice versa), you can easily convert the buffer coding system.
Dos to unix
M-x set-buffer-file-coding-system RET undecided-unix
save the file (C-x C-s)
or
C-x RET f undecided-unix
C-x C-f
Unix to dos
M-x set-buffer-file-coding-system RET undecided-dos
save the file (C-x C-s)
or
C-x RET f undecided-dos
C-x C-f