On April Fool’s day, the Ordnance Survey opened up its data for people to reuse with less restrictions applied. At the heart of everything we do, place perhap the most important. The Scheme uses National Grid References and place names that you would find on an OS map. The things that these maps depict, often inform where people discover objects; they represent habitation in a past and present form, sometimes concurrently, sometimes from anitiquity. The last word in that sentence is the key to why I wanted to use the 1:50 000 data in our web application (our database). Two categories of place are defined within this dataset:
- Roman Antiquity – of which there are 237 instances
- Antiquity – of which there are 5252 instances
If you download the 1:50k dataset from the Ordnance Survey or from the mysociety cache (remember they are charity, so don’t abuse their servers), there is a document that outlines what the fields mean in the dataset. The important one here was the f_code or feature code column. The data is available via SPARQL (see Leigh Dodd’s article on this), but I wanted to keep a local copy of this data on my server so that I could use it and transform it for some other tasks. After downloading and unzipping onto our server (placing it into the /tmp folder will save you getting error 13 codes with the mysqlimport later), I then created the following MySQL table:
[crayon-51985576687c3/]
I then needed to import this data, which I accomplished using mysqlimport command in my terminal as below (I use putty at work and OSX terminal at home). Note that I renamed the gazetteer data to the same table name as the mysql table and retained the txt extension. Fields are delimited by a colon and there is no header row.
[crayon-51985576687d5/]
The import should run through and insert 259080 rows of data. Even though I am only interested in the antiquity type fields, I have imported the lot in case I want the rest later. Now I have the data installed, I can manipulate it and use it in the way that I want. If you know your grid references, then it is apparent that the data presents at just 1KM square resolution; this is the maximum precision level to which we publicly display our find spots, so it will tie in quite nicely to the public display of information.
However, I wanted decimal degrees for the latitude and longitude within my table. I therefore inserted two new rows into the MySQL table – latitude and longitude (DOUBLE) and then used a php function to convert the 1km square grid reference into lat/lon values. I’ve also done some further manipulations to get the imprecise centred 1KM grid reference WOEID to get enhanced geographical data.
Now the grid reference has been converted into decimal values, I can now plot these quickly onto a Google Map or use mathematical formulae to get distance from a point; for example the Haversine.
%20=%202%20R%20%5Carcsin%5Cleft(%5Csqrt%7Bh%7D%5C,%5Cright)&bg=ffffff&%23038;fg=000000&%23038;s=0 "d = R \, {haversin}^{-1}(h) = 2 R \arcsin\left(\sqrt{h}\,\right)")
There’s s some very good discussion and code available on these resources, so there’s no point reinventing the wheel:
- Haversine formula can be obtained from various sources, and this page lists 9 scripting language variants.
- Vincenty formula can be found on the Movable Type script pages (via @codepo8).
- More detailed explanation can be found at the Seventh Sense blog
For the Scheme’s database, I wanted to work out if objects were close to an antiquity or Roman antiquity or whether a MP’s constituency or district has them within their bounding box (as found from querying theyworkforyou’s api – more on that later.) As I use Zend Framework, I created a model and then used a view helper to render data onto a finds record. If you’re interested in my code for this, you’re welcome to have it….this one uses the Haversine and is just the model that generates a MySQL query against my database table.
[crayon-51985576687e5/]
Below is a record of an object from the controversial Water Newton rally, which was near the site of the Roman town of Durobrivae, near Chesterton in Cambridgeshire [WOEID: 39263, 1KM NGR - TL1295, Lat: 52.561508 Lon: -0.364190].
You’ll see that there are two Scheduled Monument Alerts – there are actually 5 entries in the National Monuments Record for this SAM – and there is 1:50k OS alert for a Roman antiquity. These SAM alerts aren’t shown to users below ‘research’ level. I can then click through to find all records associated within a certain distance of this OS point, and map them if I have at least ‘Research’ user rights on our database. In future, I will try and link these place names through to other linked data resources. By tying them to a WOEID, I can find archaeological photos on Flickr for example.
I don’t think that this breaches the OS licence and there are probably other ways to accomplish this in php, I just dabble in code, so don’t rip me to shreds….
)