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

[Nathan Woodrow]

Hey Steven,

There is a trick you can do to get your layers to load a bit quicker.  The
thing that takes the most time when loading a layer is calculating the
extents, this is done each time the layer is opened and for large layers
this can have a pretty big performance hit.  So to fix that issue I have
made the SQL driver search for a  min_x, min_y, max_x, max_y in the
geometry_columns
table in order to just read the extent so there is no on the fly
calculation needed when the layer is loaded.

So your geometry_columns table should look like this:

[f_table_catalog] [varchar](128) NOT NULL,
[f_table_schema] [varchar](128) NOT NULL,
[f_table_name] [varchar](256) NOT NULL,
[f_geometry_column] [varchar](256) NOT NULL,
[coord_dimension] [int] NOT NULL,
[srid] [int] NOT NULL,
[geometry_type] [varchar](30) NOT NULL,
[min_x] [double] NULL,
[min_y] [double] NULL,
[max_x] [double] NULL,
[max_y] [double] NULL


The last four columns are the new ones and you can calculate a rough
extents to go in the table like:





select min([ogr_geometry].STPointN(1).STX),

       min([ogr_geometry].STPointN(1).STY),

       max([ogr_geometry].STPointN(1).STX),

       max([ogr_geometry].STPointN(1).STY)

FROM [TABLE]


An entry in the table might look like:


http://i.imgur.com/gq8DayI.png


Note: This will only work in the current dev version of QGIS.


- 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****
>
> *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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130701/9f7f8f5e/attachment.html>