<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-GB link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi All<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Matt Walker asked if I would document my use of the OSMM Loader,
so here goes<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I tested both the initial (direct PostGIS loading using INSERT)
and the significantly quicker PGDump version on national coverage OSMM.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I used the
non-geo chunks, as I didn’t want to have to post process the database
tables to remove identical features. Matt has this down as a future improvement
and I would second that along with COU as being extremely useful. </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Details as
follows</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>1) Step 1
was to use the Astun loader to create the SQL Dump/COPY files. For this I used
a High-CPU Windows 64 Bit with Python 2.5 and GDAL 1.8 installed via the
OSGeo4W installer.</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Using
Non-Geo chunks of OSMM there are 1297 files equating to 35.9GB, I used 6
threads (the computer had 8 cores) to process 216 files/6Gb each and this took
15 hours and outputted 215Gb of SQL files, one for each Gzip file</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>2) Step 2
Data Loading</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I used the
inbuilt PSQL loader and run 2 threads on a Large Windows Amazon Instance to
load the data in PostGIS. Only 2 threads could be used as the Large instances
only have 2 cores – although it was only using 30-40% of total CPU, so could
potentially up this to 4 threads.</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>The load
took 13-14 hours</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>3) Running
the spatial indexes</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I amended
the config of PostGIS and upped the maintenance memory to 1Gb. This helped
created the spatial indexes in less than 10 hours compared to the original 17
hours with default PostGIS</span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> </span></font><o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>So SQL
creation + data load + indexes = 40 hours.<o:p></o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I guess for
many people it will be the fear of using a commandline tool (people love a good
GUI) and the fact they do not have access to these powerful computers?<o:p></o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I have been very
impressed though and would like to thank Matt for his support<o:p></o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Tim<o:p></o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<div id=corpsig>
<div style='margin-bottom:9.0pt'>
<p class=MsoNormal style='line-height:12.0pt'><strong><b><font size=2
color="#4d4d4d" face=Tahoma><span style='font-size:10.5pt;font-family:Tahoma;
color:#4D4D4D'>Tim Martin Msc Bsc(Hons)</span></font></b></strong><font size=2
color="#4d4d4d" face=Tahoma><span style='font-size:10.5pt;font-family:Tahoma;
color:#4D4D4D'><o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal style='line-height:12.0pt'><strong><b><font size=1
color=gray face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;
color:gray'>Web Services Consultant and INSPIRE Technical Business
Development Manager</span></font></b></strong><font size=1 color=gray
face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;color:gray'><o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal style='line-height:12.0pt'><span class=organization-name><b><font
size=1 color=gray face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;
color:gray;font-weight:bold'>Ordnance Survey</span></font></b></span><font
size=1 color=gray face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;
color:gray'> <o:p></o:p></span></font></p>
</div>
<div><span title=work>
<p class=MsoNormal style='line-height:12.0pt'><st1:City w:st="on"><st1:place
w:st="on"><span class=teltype><font size=1 color=gray face=Tahoma><span
style='font-size:9.0pt;font-family:Tahoma;color:gray'>Mobile</span></font></span></st1:place></st1:City><span
class=teltype><font size=1 color=gray face=Tahoma><span style='font-size:9.0pt;
font-family:Tahoma;color:gray'>: </span></font></span><span class=value><font
size=1 color=gray face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;
color:gray'>+44 (0) 7917 550 225</span></span></font></span><font size=1
color=gray face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;
color:gray'><o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal style='line-height:12.0pt'><font size=1 color=gray
face=Tahoma><span style='font-size:9.0pt;font-family:Tahoma;color:gray'><a
href="http://www.ordnancesurvey.co.uk">www.ordnancesurvey.co.uk</a> | <a
href="mailto:Tim.Martin@ordnancesurvey.co.uk">Tim.Martin<span class=email>@ordnancesurvey.co.uk</span></a>
</span></font><font size=1 color=gray face=Tahoma><span style='font-size:9.0pt;
font-family:Tahoma;color:gray'><o:p></o:p></span></font></p>
</div>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>
<pre>This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person.
Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.
Thank you for your cooperation.
Ordnance Survey
Adanac Drive
Southampton SO16 0AS
Tel: 08456 050505
http://www.ordnancesurvey.co.uk