@billt asked:

"Help, oh #lazyweb - does anyone know how to take a list of postcodes in an excel s/sheet and turn it into a list of counties?"

asked 13 Dec '10, 10:02

psychemedia's gravatar image

psychemedia ♦♦
accept rate: 11%

Peform the following query against the following sparql endpoint http://api.talis.com/stores/ordnance-survey/services/sparql:

PREFIX postcode: <http://data.ordnancesurvey.co.uk/ontology/postcode/>

select ?county
   where {
   <http://data.ordnancesurvey.co.uk/id/postcodeunit/EX11HD> postcode:county ?county .

As discussed above beware that many postcodes will not be in counties because only England has counties (administratively speaking) and even in England they do not cover the whole country.

Another point of interest is that postcodes do NOT respect administrative boundaries. So a postcode unit might not be fully contained in a county. Postcode to ward/district/county look ups are done as a 'point in polygon' query. A rough centroid in computed for the postcode unit, and then the containing administrative polygon for that point is returned. So the county relationship used above is a weaker statement that 'within'.


answered 14 Jan '11, 12:40

John%20Goodwin's gravatar image

John Goodwin
accept rate: 25%

edited 14 Jan '11, 13:11

rgrp's gravatar image

rgrp ♦♦

This query to the Ordnance Survey Linked Data endpoint seems to map from a postcode to a county, though there may be more efficient ways?

PREFIX postcode: <http://data.ordnancesurvey.co.uk/ontology/postcode/>
PREFIX admingeo: <http://data.ordnancesurvey.co.uk/ontology/admingeo/>

select ?name where {
<http://data.ordnancesurvey.co.uk/id/postcodeunit/SO164GU> postcode:ward ?ward.
?ward admingeo:inDistrict ?district.
?district <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches> ?county.
?county a admingeo:County.
?county <http://www.w3.org/2000/01/rdf-schema#label> ?name.

And a recipe here shows a clunky way of writing a formula using Google Apps script to call a Linked Data in Google spreadsheets: Using Data From Linked Data Datastores the Easy Way (i.e. in a spreadsheet, via a formula)

It's all a bit clunky though...


answered 13 Dec '10, 10:07

psychemedia's gravatar image

psychemedia ♦♦
accept rate: 11%

Hmmm... if a postcode area touches a county border, will two counties be returned?

(13 Dec '10, 10:14) psychemedia ♦♦

From a Twitter conversation with @gothwin, it seems all is not so simple...

If a postcode lies within a county (as defined in the administrative geography sense), <http://data.ordnancesurvey.co.uk/ontology/postcode/county> will exist. But "unitary authorities and metropolitan districts are not in counties" (nor is the Greater London Authority). So for example, the SO16 4GU postcode area is not associated with a county (it lies within "The City of Southampton" unitary authority area), but EX1 1HD is.

Which is to say, in the Ordnance Survey/admingeo namespace, areas as identified with a postcode may not be associated with a county.

@gothwin did hint that "we are looking at publish 'people's geography' with traditional counties", which


answered 14 Dec '10, 10:13

psychemedia's gravatar image

psychemedia ♦♦
accept rate: 11%

Your answer
toggle preview

Follow this question

By Email:

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



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



Asked: 13 Dec '10, 10:02

Seen: 2,188 times

Last updated: 14 Jan '11, 13:11

powered by OSQA