Hello I am making a map for a project in UK and I try to add some data about the buildings. The only unique id that I have form my data is the postcode for each building. The map that I got from Open Street Map, for each building there is an osm_id and I would like to join the data with a database that each osm_id has one postcode and after I would be able to add any data that I have for each building with postcode.

Is it possible to find a database with postcodes and osm_id for UK?

Thanks.

asked 15 Oct '13, 13:01

chgiorgos's gravatar image

chgiorgos
41114
accept rate: 0%

I am not sure whether this can help:

http://wiki.openstreetmap.org/wiki/Free_The_Postcode

And I fear you have to specify your question with some examples:

Do you have external own data without postal codes, and you want to add them including the correct postal code, and you want to derive the postal code from already existing OSM objects?

(15 Oct '13, 17:22) stephan75
1

Well, I have Excel data for energy consumption in UK for the buildings. Also I have the map form open street map data with the buildings. To connect the data with the map I need a unique ID. For the buildings in the map the ID is the osm_id. For my energy consumption data the unique ID is the postcode. So I am looking for a database that is giving me for each postcode in UK the osm_id in the open street map.

For example if I am looking in the online map for a building with postcode EC1A 2DE I write the post code in the search engine and it shows me the exact building and at the information of that building is written the osm_id (in this case 338475896). So, maybe there is a database with this combination (osm_id - postcode).

(15 Oct '13, 17:51) chgiorgos

I suspect that there won't be an already-existing complete database which contains a list of OSM IDs and postcodes. This is because many people who have added buildings to OSM haven't added their postcodes. For example, if you search OSM for your example postcode "EC1A 2DE" no exact match comes back. Nearby you can see this church (which has got a postcode mapped) and this adjacent building which hasn't.

It may be possible to infer a postcode for an OSM ID from various external sources - here's a blog post by @SK53 talking about one approach. I wouldn't underestimate the work required though.

Obviously postcode isn't unique except for large buildings - although maybe you're only interested in certain large buildings which do have a unique postcode?

permanent link

answered 15 Oct '13, 20:04

SomeoneElse's gravatar image

SomeoneElse ♦
31.7k63324734
accept rate: 15%

I am afraid this approach is doomed to failure.

The problems are several:

  • OSM Ids are system keys, they do not correspond to anything in the real world. They are not unique identifiers for things in the real world. A building may be represented by several osm objects, several buildings may be represented by a single osm objects, or a building may be built up from several objects in a relation.
  • OSM Ids are not persistent, and may get reused in other objects. (The actual risk of this is small, as IDs are not reused once deleted).
  • OSM Ids are only unique within a class of object (nodes, ways, relations). You need to use a compound key of object type and osm_id to ensure uniqueness within OSM.
  • OSM has a tiny fraction (less than 2%) of all buildings in the UK mapped.
  • Postcodes are not unique identifiers either.
  • Postcodes are based on addresses (technically delivery points) not buildings: a building can have more than one address (e.g., a block of flats), many buildings share a postcode. Some buildings don't have postal addresses. Postal delivery offices may have hundreds of postcodes.
  • Postcode geodata is provided with a centroid which should co-locate with only one of the buildings, there is typically around a 2% chance that it is the right one for houses, probably better for some commercial properties. For campus organisations such as universities the chance is much lower as all buildings share a postcode.
  • There are no freely-available polygons which accurately show the area which contains locations for a given postcode. Using Voronoi polygons on the OpenData sets is not an adequate substitute.
  • Postcodes are intermingled (a block of flats may have one postcode, and the buildings either side another different postcode). Therefore closeness to the centroid is only a partial indicator of matching.
  • Postcodes are usually assigned on the basis of streets. It is possible for a centroid to be located closer to another street than the one it applies to.

If the data you have only contains the postcode as a building locator then it is not possible to find the building in any dataset. If you have some other criteria (for instance are the buildings public buildings) then this may help by reducing the candidates.

If you have addresses then it may be possible to enrich your data with a location by using Nominatim to provide a set of co-ordinates. Again this is chancy because OSM only has a small proportion of UK addresses mapped.

Sorry for having to be negative about your project, but I'd hate you expending a lot of effort for no result.

permanent link

answered 16 Oct '13, 12:37

SK53's gravatar image

SK53 ♦
22.0k45227344
accept rate: 20%

You broke my heart but I will keep trying. :) The data that I have to locate a building is:

Address 1 =Flat A

Address 2 = 111, Brixton Hill

City = London

Postcode = SW2 1AA

I told that the postcode is unique for each building (in UK) but maybe is not true. What do you think? With this data would be able to connect my data with open street map buildings?

(16 Oct '13, 12:51) chgiorgos
2

No there is hope! Read up on Nominatim on the wiki and you can find how to get a lat/lon pair for such addresses (when they are in OSM). With that you can find any building at that location.

Start with an area which is well-mapped for address information: Sutton Coldfield, Tendring and Broxtowe are all areas with good quality addresses. London is patchy, but probably reasonably good inside the Circle Line.

(16 Oct '13, 13:00) SK53 ♦

If you're prepared to do a bit of programming:

Download an extract of OpenStreetMap data for the UK from Geofabrik. Save this to a database, for example by using the osm2pgsql tool to save it to a Postgres database with the PostGIS extension.

Download the CodePoint Open dataset from Ordnance Survey. This is free of charge and licensed liberally. Add this to another table in the same database.

You can then create a spatial query to associate OSM IDs (in one table) with postcodes (in another). The PostGIS docs explain the functions you can use in writing your SQL.

This is not for the beginner, but if you have some experience in SQL databases, you shouldn't find it impossible.

Note that CodePoint Open only has the centrepoint of each postcode area, not the full boundary; so this approach will sometimes suggest postcodes which are slightly 'off'. The only way to avoid this is to buy the expensive full data from Royal Mail or Ordnance Survey.

permanent link

answered 15 Oct '13, 23:57

Richard's gravatar image

Richard ♦
27.2k40241360
accept rate: 19%

edited 16 Oct '13, 00:00

I think that I understand what you are saying. I know a little SQL but I still don't understand what query I should write. Because the osm_id is random.

I did something else, I found a database with post codes and longitude, latitude and I add the points in the GIS, after I add the map data. I selected by location all the building that they have a point inside. So now I have all the buildings with a point inside that is written its postcode.

With this method I have 2 problems. I don't know how to join the tables "by location" (because know I have something common. The data are in the point that is into the building). So I want to take the data from, the point and put it in the building.

The second problem is that only the 30% of the points are located in a building.

(16 Oct '13, 10:39) chgiorgos
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×511
×42
×29

question asked: 15 Oct '13, 13:01

question was seen: 4,518 times

last updated: 16 Oct '13, 13:00

powered by OSQA