Category: Uncategorized

It’s been a busy 6 months, trying to update all of my websites whilst commuting etc & it’s taking a long time especially as the front end’s are being done from scratch. If someone ever tells you it’s simple to move from a classic website to one using microservices, don’t believe them – it will take some time.

Anyhow, progress is progress. Here’s the current status:

• The blog is now on it’s own domain, new layout, still not happy about how it looks though.
• Documentation and API’s are now on the area51.onl site with more being added as I finish them.
• departureboards.mobi is almost complete, just got some minor layout issues to solve.
• uktra.in is next. Most of the backend stuff is done, just got to port the CMS over and rewrite the timetable search & real time search pages.
• map.lu is running to the point it’s now providing maps again to the other sites.
• maidstoneweather.com & the weather station are still sadly down. I’ve got the replacement electronics ready but again it’s finding time to fix it – it’s only taken a year so far :-S
• trainwatch.uk is still dormant – not found a use for that domain yet.

So there’s still plenty to do. When you spend 20+ hours just commuting on top of the working week it’s a problem, but I’m getting there.

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.