|
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. |
|
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? |
|
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. 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
|
|
I am afraid this approach is doomed to failure. The problems are several:
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. 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 ♦
|
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?
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).