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:
[bash toolbar="true"]
CREATE TABLE `osdata` (
`id` int(11) NOT NULL,
`km_ref` char(6) collate utf8_unicode_ci default NULL,
`name` char(60) collate utf8_unicode_ci default NULL,
`tile_ref` char(4) collate utf8_unicode_ci default NULL,
`lat_degrees` int(2) default NULL,
`lat_minutes` float default NULL,
`lon_degrees` int(2) default NULL,
`lon_minutes` float default NULL,
`northing` int(7) default NULL,
`easting` int(7) default NULL,
`gmt` char(1) collate utf8_unicode_ci default NULL,
`county_code` char(2) collate utf8_unicode_ci default NULL,
`county` char(20) collate utf8_unicode_ci default NULL,
`full_county` char(60) collate utf8_unicode_ci default NULL,
`f_code` char(3) collate utf8_unicode_ci default NULL,
`e_date` char(11) collate utf8_unicode_ci default NULL,
`update_code` char(1) collate utf8_unicode_ci default NULL,
`sheet1` int(3) default NULL,
`sheet2` int(3) default NULL,
`sheet3` int(3) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=’OSDATA 1:50000′;[/bash]
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.
[bash toolbar="true"]mysqlimport –user={username} –password={password} –fields-terminated-by=":" {your database name} ‘/tmp/osdata.txt’[/bash]
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.
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.
[PHP toolbar="true"]
<?php
class Osdata extends Zend_Db_Table_Abstract
{
protected $_name = ‘osdata’;
protected $_primary = ‘id’;
public function get50KNearby($lat,$long,$distance,)
{
$radius = 6378.137; //KM value, swap to 3960.00 for Imperial miles
$pi = ’3.141592653589793′;
$nearbys = $this->getAdapter();
$select = $nearbys->select()
->from($this->_name,array(‘name’,'id’, ‘latitude’, ‘longitude’,'distance’ => ‘acos((SIN(‘.$pi.’*’.$lat.’/180 ) * SIN(‘.$pi.’* latitude /180)) + (cos(‘.$pi.’*’.$lat.’/180) * COS(‘.$pi.’* latit$
->where($radius . ‘ * ACOS((SIN(‘.$pi.’*’.$lat.’/180) * SIN(‘.$pi.’* latitude/180)) + (COS(‘.$pi.’*’.$lat.’/180) * cos(‘.$pi.’* latitude /180 ) * COS(‘.$pi.’* longitude /180 -’.$pi.’* ( ‘.$long.$
->where(’1=1′)
->where(new Zend_Db_Expr(‘f_code = "R" OR f_code = "A"’))
->order($radius . ‘ * ACOS((SIN(‘.$pi.’*’.$lat.’/180 ) * SIN(‘.$pi.’* latitude/180)) + (COS(‘.$pi.’*’.$lat.’/180) * cos(‘.$pi.’ * latitude /180 ) * COS(‘.$pi.’* longitude /180 – ‘.$pi.’* (‘.$long.’$
return $nearbys->fetchAll($select);
}
}
}
[/php]
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….
Birmingham Museums & Art Gallery and Stoke Potteries Museum are seeking expressions of interest for several Conservation opportunities associated with the amazing Staffordshire Hoard. These would suit experienced conservation professionals and students and allow the successful applicants to influence the future of these objects in a world-class museum setting. Competition will no doubt be very fierce for these posts. Below are the basic details for each role and a PDF of the brief and please note that dates of closing differ for the first post and the second two posts.
)
