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 <[email protected]>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:[email protected]] > *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 <[email protected]> > 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: [email protected]**** > > 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 > [email protected] > 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. >
_______________________________________________ Qgis-user mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/qgis-user
