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

Mon Jul 1 03:23:04 PDT 2013

Hey Steven,

I didn't know about the sys,spatial_index_tesellations table.  That might
be better to use as it doesn't require the user to do any extra work.
 Ignore the min_x, min_y stuff I will update the code to use this new table
and let you know once it's done.

However creating the geometry columns table isn't a bad idea anyway as it
will let QGIS find the layers quicker when using the browser, or list
layers dialog.

Here is a create script:

CREATE TABLE [dbo].[geometry_columns](
[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,
 CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED
(
[f_table_catalog] ASC,
[f_table_schema] ASC,
 [f_table_name] ASC,
[f_geometry_column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This is what mine looks like:

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

- Nathan

On Mon, Jul 1, 2013 at 8:03 PM, Steven Campbell <s.campbell at poole.gov.uk>wrote:

> Hi Nathan****
>
> ** **
>
> Thanks for this info, excuse me for sounding a bit thick here, but as I
> can’t find any Geometry_columns table in my SQL database, I’m guessing I
> need to create one as QGIS uses this table to get the extents of the data
> in SQL, and if it doesn’t exist it works them out on the fly?****
>
> ** **
>
> Do I have to use ogr2ogr to create this table or can I build the table and
> put in the information myself – or can I use the
> sys.spatial_index_tesellations table instead as this contains my bounding
> boxes for x and y?****
>
> ** **
>
> Thanks****
>
> ** **
>
> Steve****
>
> ** **
>
> *From:* Nathan Woodrow [mailto:madmanwoo at gmail.com]
> *Sent:* 01 July 2013 00:11
> *To:* Steven Campbell
> *Cc:* qgis-user
> *Subject:* Re: [Qgis-user] OS Mastermap, SQL Server and QGIS****
>
> ** **
>
> 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****
>
> ** **
>
> 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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130701/5f6c03e0/attachment.html>