<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:m="http://schemas.microsoft.com/office/2004/12/omml" 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 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@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","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Arial","sans-serif";
color:#0070C0;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:517160951;
mso-list-template-ids:-1030327460;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></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=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Hi Jonathan<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Thanks for those tips that helped a lot, it also helped that I removed some records from the user_sdo_geom_metadata table that were related to some excessively large views as this was impacting upon the speed of QGIS assessing all of the layers – it now works perfectly!<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Thanks again<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Steve<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'><o:p> </o:p></span></p><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Jonathan Moules [mailto:jonathanmoules@warwickshire.gov.uk] <br><b>Sent:</b> 12 July 2013 11:27<br><b>To:</b> Steven Campbell<br><b>Cc:</b> Nathan Woodrow; qgis-user<br><b>Subject:</b> Re: [Qgis-user] OS Mastermap, SQL Server and QGIS<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Hi Steve, <o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><p class=MsoNormal><span style='font-size:11.5pt;font-family:"Arial","sans-serif";color:#0070C0'>As an aside, do you know if the Oracle connector uses the user_sdo_geom_metadata table to identify the SRID and extents of the tables?</span><o:p></o:p></p></blockquote><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Not that particular table, not for extents (not sure about SRID). There are various options for the connector which make things much faster. From the help:<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><div><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'><b>Only look in meta data table</b> Restricts the displayed tables to those that are in the all_sdo_geom_metadata view. This can speed up the initial display of spatial tables.<o:p></o:p></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'><b>Use estimated table statistics</b> for the layer metadata Whenthe layer is setup various metadata is required for the Oracle table. Thisincludes information such as the table row count, geometry type and spatialextents of the data in the geometry column. If the table contains a largenumber of rows determining this metadata is time consuming. By activating thisoption the following fast table metadata operations are done: Row count isdetermined from all_tables.num_rows. Table extents are always determined withthe SDO_TUNE.EXTENTS_OF function even if a layer filter is applied. The tablegeometry is determined from the first 100 non-null geometry rows in the table.<o:p></o:p></li></ul></div></div><div><p class=MsoNormal>Those two in particular speed things up.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Jonathan<o:p></o:p></p></div><p class=MsoNormal style='margin-bottom:12.0pt'><o:p> </o:p></p><div><p class=MsoNormal>On 11 July 2013 16:19, Steven Campbell <<a href="mailto:s.campbell@poole.gov.uk" target="_blank">s.campbell@poole.gov.uk</a>> wrote:<o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Hi Nathan </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Thanks for making the change – it is sooo much quicker now to load, especially for such a large dataset as Mastermap!</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Just a quick question though, if I try to connect to a secondary MSSQL database, it takes a very long time to find the tables. Generally what I do is I open a boundary file table (stored in one MSSQL table) so I can zoom in to a small location, and then go to open my Mastermap but the initial time taken by QGIS to detect the Mastermap table takes a considerable time – once found it is quick to open, pan etc – it’s just the initial connection that seems very slow.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>As an aside, do you know if the Oracle connector uses the user_sdo_geom_metadata table to identify the SRID and extents of the tables?</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Thanks again!</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'>Steve</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#0070C0'> </span><o:p></o:p></p><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Nathan Woodrow [mailto:<a href="mailto:madmanwoo@gmail.com" target="_blank">madmanwoo@gmail.com</a>] <br><b>Sent:</b> 02 July 2013 04:02<br><b>To:</b> Steven Campbell<br><b>Cc:</b> qgis-user<br><b>Subject:</b> Re: [Qgis-user] OS Mastermap, SQL Server and QGIS</span><o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hey Steven,<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I have made the change in master now too allow QGIS too look in the spatial index table for faster extent loading. Works like a treat.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I would still have the geometry_columns table to allow QGIS to find the tables quicker but you don't need the max_x, etc stuff.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks for the tip.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regards,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Nathan<o:p></o:p></p></div></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Sat, Jun 29, 2013 at 12:00 AM, Steven Campbell <<a href="mailto:s.campbell@poole.gov.uk" target="_blank">s.campbell@poole.gov.uk</a>> wrote:<o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Hi</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Does anyone else have significant time delays when trying to open large datasets from a SQL database in QGIS? I have uploaded my Ordnance survey MasterMap data (coverage is the size of an average English County) and although the initial connection to identify the table is very quick, the loading of the table (even when zoomed in to a very big scale (i.e. 1:100) it takes a very long time to open. Once opened the speed is very quick, but getting it to open takes a lot of patience as the application appears to hang.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>On a slightly related topic, if I have connected to one MSSQL database, QGIS then struggles if I try to connect to an additional database, again it just appears to hang for a considerable period of time (30 minutes or more at times), it seems odd especially as the connection to the first SQL database is almost instant....</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Any suggestions as to how I can improve this speed would be greatly appreciated.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Thanks</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Steve Campbell</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>GIS Manager</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Corporate Strategy and Communications</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Borough of Poole | Civic Centre | Poole BH15 2RU</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Tel: 01202 633 362</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Email: <a href="mailto:s.campbell@poole.gov.uk" target="_blank">s.campbell@poole.gov.uk</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Arial","sans-serif"'>Website:<a href="http://www.boroughofpoole.com/" target="_blank">www.boroughofpoole.com</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><i><span style='font-family:"Arial","sans-serif";color:#00B050'>Think Green! Please Recycle</span></i></b><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><p>DISCLAIMER:<br>This email and any files transmitted with it may be confidential, legally privileged and protected in law and are intended solely for the use of the individual to whom it is addressed. The copyright in all documentation is the property of the Borough of Poole and this email and any documentation must not be copied or used other than as strictly necessary for the purpose of this email, without prior written consent which may be subject to conditions. Any view or opinions presented are solely those of the author and do not necessarily represent those of the Borough of Poole. The Borough of Poole reserves the right to inspect incoming and outgoing emails. If you have received this email in error please contact the sender by return and confirm that its contents have been destroyed. Telephone enquiries should be directed to the Borough switchboard on 01202 633633.<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'><br>_______________________________________________<br>Qgis-user mailing list<br><a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div><p class=MsoNormal style='margin-bottom:12.0pt'><br>_______________________________________________<br>Qgis-user mailing list<br><a href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></p></div><p class=MsoNormal><br><br><span style='font-family:"Arial","sans-serif";color:#222222;background:white'>This transmission is intended for the named addressee(s) only and may contain sensitive or protectively marked material up to RESTRICTED and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation.</span><o:p></o:p></p></div></body></html>