Reply to: UK Postcode data

[Spacecadet]

Are you going to need to run this on the fly? I've had to do similar things before, but the centroid was already known.

The difficulty of working it out on the fly is that you're not going to be using indexes. If you are limiting the search radius then you can cut out most of the UK from your search. It's pointless including newcastle addresses when calculating the distance when your searching for a 5 mile radius from somewhere in Bath.

Work to a square first, this will allow you to go 5 miles east, west, north and south without having to do any fancy calculations. (you can index your lat/long northings/easting columns to help speed this bit up)
From that much smaller subset you can then workout what is in the 5 mile radius

[fullyautomatix]

I have now imported the Code point post code data into SQL Server 2000. I havent yet migrated into 2008 ( in a few months I will). So anyway I cannot use the mumbo jumbo like spatial index and the like.

So there are about 1.5 million records in the table.

I have written a stored proc to fetch all the post codes within a certain defined distance of a particular post code.

When i try to find all post codes within 5 miles of, say , Se1 0AA I get back proper records but the query itself takes at least 5 seconds if not more.

Has anybody managed to get the queries run faster ? Is there a better algorithm ? I copied my algorithm from a MS article.

[Durbs]

OSNI (OS Northern Ireland) are a seperate organisation - so no idea I'm afraid - never worked with NI data myself either! Not much help I know!

Seems the Northern ireland info is included in the OS Code-Point paid version so i guess they license it from their Irish counterparts.

Unfortunately my Plan B really requires the NI and Isle of Man data so going to have to buy it somewhere, prices i've seen so far for postcode stuff from Royal Mail & OS are just though.

[Muttley08]

Can you ask them if i can have the Northern Irish and Manx Code-Point data?

OSNI (OS Northern Ireland) are a seperate organisation - so no idea I'm afraid - never worked with NI data myself either! Not much help I know!

[Durbs]

if there's any questions I can help with...

Can you ask them if i can have the Northern Irish and Manx Code-Point data?

[Muttley08]

There's another vector product to be released in May as well - working @ OS at the moment if there's any questions I can help with...

[TimberWolf]

Incidentally there was a radio programme on Radio 4 today concerning open data:

Open data

On April 1st, the Ordnance Survey announced that much of its map data would now be openly and freely available. It's the latest move in a trend which has seen more and more information from UK public bodies put online for anyone to use. Charles Arthur, Technology Editor of The Guardian, and a founder of the "Free Our Data" campaign, and Chris Taggart from Openly Local - a website which aims to make local government information more accessible, discuss how and why this data is useful for all of us.
http://www.bbc.co.uk/programmes/b00rxh71

I didn't listen to most of it (was in car and reached destination before it finished), so can't really comment about how interesting it is, and can't be arsed to listen again ATM.

[TimberWolf]

The Ordnance Survey does provide some very detailed information about the whole business of transformation from one coordinate system to another which might meet your needs.

Fascinating reading, even if you aren't using the data, e.g.:

Relative to the centre of the Earth, a point on the ground can move as much as a metre up and down every day just because of the tidal influences of the sun and moon. The relative motion of two continents can be 10 centimetres a year, which is significant for mapping because it is constant year after year – after 50 years a region of the earth may have moved by 5 metres relative to a neighbouring continent. Many other small effects can be observed – the sinking of Britain when the tide comes in over the continental shelf (a few centimetres), the sinking of inland areas under a weather system high (about 5 millimetres) , and the rising of the land in response to the melting of the last Ice Age (about 2 millimetres per year in Scotland, up to 1 centimetre per year in Scandinavia) .

[NickFitz]

Cheers for that, I've converted point data before and got a SQL function or two to handle that. they're not perfect but since the postcode data is only accurate to 100m the functions are accurate enough.

Its shape files which I need to understand better and convert...there does seem to be some stuff around though which can open them up to convert them into a different copordinate system

I presume you've read the Wikipedia article on shapefiles.

This chap a lot of useful info specifically about the OS data, and MySociety.org has a bunch of shapefile-parsing code buried somewhere in that CVS repository, although you need to check the licensing if you plan to use it in a commercial product.

[Spacecadet]

The Ordnance Survey does provide some very detailed information about the whole business of transformation from one coordinate system to another which might meet your needs.

Cheers for that, I've converted point data before and got a SQL function or two to handle that. they're not perfect but since the postcode data is only accurate to 100m the functions are accurate enough.

Its shape files which I need to understand better and convert...there does seem to be some stuff around though which can open them up to convert them into a different copordinate system

[NickFitz]

Just downloaded these to have a look
The bloody shapefiles are in northings and eastings as well!!
at least the ones i've been opening are and overlaying the postcode data confirms that as the postcodes appear in the areas I would expect (not out to sea somewhere)

I've converted files before using the ESRI ArcGIS but not got access to that any more

Having a look at some conversion software now, I need to convert it as the mapping software I want to put it in to needs lat/long.

I'll post later when I've had a more sober look at them

The Ordnance Survey does provide some very detailed information about the whole business of transformation from one coordinate system to another which might meet your needs.

[Spacecadet]

Just downloaded these to have a look
The bloody shapefiles are in northings and eastings as well!!
at least the ones i've been opening are and overlaying the postcode data confirms that as the postcodes appear in the areas I would expect (not out to sea somewhere)

I've converted files before using the ESRI ArcGIS but not got access to that any more

Having a look at some conversion software now, I need to convert it as the mapping software I want to put it in to needs lat/long.

I'll post later when I've had a more sober look at them

[Durbs]

Coded Plan B up using my shiny new OS data then puzzled over why any NI address calculations were returning my exception value of 999999.

Then just read "Code-Point Open includes 120 postcode areas in England, Scotland and Wales, but does not include the postcode areas for the Isle of Man, the Channel Islands and Northern Ireland."

We need a smiley of a developer on his knees shaking his fists at the sky, sobbing and screaming Noooooooooooooooooooooo!!!!!

Ah well.

[TimberWolf]

I've just downloaded one of the (52 I think) 1:10,000 OS street view files and am amazed at the level of detail provided. Not sure how many of those 52 I'd be able to download before a monetary consideration would be proffered. It would also be somewhat more than a 5 minute job to turn the data into a nice personal app that allows one to play around with the images, road names and post code data; license terms withstanding. Drool time, but may be too time consuming to exploit fully for personal interest.

[OwlHoot]

I checked those OS OpenSpace pages last week, and the site was so hellishly slow it was unusable.