[Qgis-user] OS Mastermap, SQL Server and QGIS

Fri Jul 12 05:04:35 PDT 2013

Hi Jonathan

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!

Thanks again

Steve

From: Jonathan Moules [mailto:jonathanmoules at warwickshire.gov.uk] 
Sent: 12 July 2013 11:27
To: Steven Campbell
Cc: Nathan Woodrow; qgis-user
Subject: Re: [Qgis-user] OS Mastermap, SQL Server and QGIS

Hi Steve, 

	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?

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:

*	Only look in meta data table 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.
*	Use estimated table statistics 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.

Those two in particular speed things up.

Jonathan

On 11 July 2013 16:19, Steven Campbell <s.campbell at poole.gov.uk> wrote:

Hi Nathan 

Thanks for making the change - it is sooo much quicker now to load,
especially for such a large dataset as Mastermap!

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.

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?

Thanks again!

Steve

From: Nathan Woodrow [mailto:madmanwoo at gmail.com] 
Sent: 02 July 2013 04:02
To: Steven Campbell
Cc: qgis-user
Subject: Re: [Qgis-user] OS Mastermap, SQL Server and QGIS

Hey Steven,

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.

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.

Thanks for the tip.

Regards,

Nathan

On Sat, Jun 29, 2013 at 12:00 AM, Steven Campbell
<s.campbell at poole.gov.uk> wrote:

Hi

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.

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....

Any suggestions as to how I can improve this speed would be greatly
appreciated.

Thanks

Steve Campbell

GIS Manager

Corporate Strategy and Communications

Borough of Poole | Civic Centre | Poole BH15 2RU

Tel: 01202 633 362

Email: s.campbell at poole.gov.uk

Website:www.boroughofpoole.com <http://www.boroughofpoole.com/> 

Think Green! Please Recycle

DISCLAIMER:
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.

_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130712/a9736ac1/attachment.html>