<!doctype html>
<title>Inserting OSTN15 into database</title>
<h1>Inserting OSTN15 into database</h1>
<p id="status">Processing records...</p>
<?php
/*
Follow the instructions in the OL below.
You can change "true" to "false" in the following 'if' statement, when you are ready:
*/
if( true ) {
print '<script type="text/javascript">document.getElementById("status").textContent = "Script has not yet been configured correctly.";</script>';
print '<ol>
<li><a href="https://www.ordnancesurvey.co.uk/documents/resources/OSTN15-OSGM15-DevelopersPack.zip">Download and unpack the OSTN15 data from Ordnance Survey</a>.</li>
<li>Put the OSTN15_OSGM15_DataFile.txt from the download on your own server.</li>
<li>Download this file from the <a href="http://www.howtocreate.co.uk/php/">PHP downloads page</a>.</li>
<li>Put this file on your own server.</li>
<li>Edit this file to configure the path to the OSTN15_OSGM15_DataFile.txt file.</li>
<li>Edit this file to provide the database commands for your database.</li>
<li>Edit this file to remove these instructions.</li>
<li>Load this file as a web page using your browser, via http.</li>
<li>If you make use of the OSTN15 or OSGN15 data within your software, then your software must include the following copyright statement: "© Copyright and database rights Ordnance Survey Limited 2016, © Crown copyright and and database rights Land & Property Services 2016 and/or © Ordnance Survey Ireland, 2016. All rights reserved.".</li>
</ol>';
}
$canbatch = true; //can your database handle batching 1000 results at a time, to improve performance, uses a little more memory
$filehandle = @fopen( 'path/to/OSTN15_OSGM15_DataFile.txt', 'r' );
$batchdata = array();
function put_data_in_database( $lineparts = false ) {
global $handler, $canbatch, $batchdata, $filehandle, $line;
if( $lineparts ) {
$batchdata[] = "($lineparts)";
}
$datacount = count($batchdata);
if( $datacount && ( !$canbatch || !$lineparts || $datacount == 1000 ) ) {
database_query_command( 'INSERT INTO ostn15 VALUES ' . implode( ',', $batchdata ), $handler );
if( $error = database_error_command() ) {
print '<p>Error: '.htmlspecialchars($error).'<br>'.htmlspecialchars($line).'</p>';
fclose($filehandle);
exit;
}
$batchdata = array();
}
}
set_time_limit( 60 * 60 * ( $canbatch ? 1 : 24 ) );
$done = 0;
if( $filehandle ) {
$handler = @database_connect_command( 'somehost', 'someuser', 'somepassword', 'somedatabase' );
if( $handler ) {
database_query_command( 'CREATE TABLE ostn15 ( record INT(6), easting INT(6), northing INT(7), se FLOAT, sn FLOAT, sg FLOAT, datum INT(2), PRIMARY KEY( easting, northing ) ) CHARSET=utf8', $handler );
fgets($filehandle); //ignore the header
while( ( $line = fgets($filehandle) ) !== false ) {
if( strlen($line) ) {
$done++;
if( !( $done % 1000 ) ) {
print '<script type="text/javascript">document.getElementById("status").textContent = "Processed ' . $done . ' records";</script>';
ob_flush();
flush();
}
$lineparts = explode( ',', $line );
for( $x = 0; $x < 7; $x++ ) {
//protect the database from accidental injections
$lineparts[$x] *= 1;
}
$lineparts = implode( ',', $lineparts );
put_data_in_database( $lineparts );
}
}
put_data_in_database();
print '<script type="text/javascript">document.getElementById("status").textContent = "Processed ' . $done . ' records";</script>';
print '<p>Success.</p>';
} else {
print '<p>Error connecting to DB.</p>';
}
fclose($filehandle);
} else {
print '<p>Error opening data file.</p>';
}
?>