Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
Thanks Nyall - "I'll be back !" - in about a week or so Den fre. 7. dec. 2018 kl. 00.09 skrev Nyall Dawson : > On Fri, 7 Dec 2018 at 00:13, Bo Victor Thomsen > wrote: > > > There is not much difference in the SQL Server execution times for the 3 > queries (around 200 - 300 ms) , so that can't explain the time difference > in QGIS > > > > QGIS 2 with native driver and QGIS 3 with ogr driver uses appr. the same > time showing the map. > > QGIS 3 with native driver uses appr. twice the time to show the results > on the map. > > Ok, thanks. In that case it appears as though this regression was > probably caused by the move to Qt5 or somewhere in the QGIS 3.0 > refactoring (as opposed to a deliberate change) > > Next things to try: > > 1. Download debugview > (https://docs.microsoft.com/en-us/sysinternals/downloads/debugview), > start it, and then open QGIS. Test your layer and see if there's any > relevant console output which may explain things > > 2. Run some little PyQGIS scripts to help narrow down where the issue > is. I.e. run these scripts on a large layer with the different > versions and time their execution: > > # fetch attributes only, no geometry > req = QgsFeatureRequest().setFlags(QgsFeatureRequest.NoGeometry) > for f in iface.activeLayer().getFeatures(req): > pass > > # fetch geometry only, no attributes > req = QgsFeatureRequest().setSubsetOfAttributes([]) > for f in iface.activeLayer().getFeatures(req): > pass > > This will help determine whether the regression is in the geometry > parsing, attribute parsing, or somewhere else... > > Nyall > > > > > > > > Den tor. 6. dec. 2018 kl. 11.54 skrev Nyall Dawson < > nyall.daw...@gmail.com>: > >> > >> On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen > >> wrote: > >> > > >> > Hi Nyall - > >> > > >> > I'm running QGIS 3.4.2 on Windows ver.10. > >> > > >> > > >> > > >> > Have there been any other changes to the SQLServer driver besides the > validity check? (I remember vaguely something about the internal > representation of spatial objects in the driver) > >> > >> No, nothing that would explain this. Just minor bug fixing and the port > to Qt 5. > >> > >> I wonder if you could log the queries coming from QGIS and see if you > >> can identify any changes from 2.18? > >> > >> Nyall > >> > >> > >> > >> > >> > > >> > I'm asking, because I've done this type of testing QGIS 2.x before > where the time difference between Postgres and SQL Server was relatively > small when doing simple MBR based searches - somewhere in the vicinity of > 20% > >> > > >> > > >> > > >> > I would happily ditch MS SQLServer forever for spatial work and > mainly use Postgres. However, my customers have a different opinion :-( > >> > > >> > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson < > nyall.daw...@gmail.com>: > >> >> > >> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen > >> >> wrote: > >> >> > >> >> > I've tried switching the validity check off as described. As far > as I can measure, there is no time difference with or without the validity > check. When does the validity check kick in? Writing or reading the > features? Or both? > >> >> > > >> >> > >> >> It's on read. Writing always uses a make valid call for SQL Server to > >> >> try to avoid triggering the issue. > >> >> > >> >> > And the validity check doesn't explain the obvious time difference > between the OGR driver and the native QGIS driver for SQL Server > >> >> > >> >> Well, it would if OGR wasn't doing this check by default. What > >> >> platform are you connecting from? Windows or Linux? > >> >> > >> >> > However, I will use your explanation about SQL Server's behavior > regarding invalid geometries as an argument for my customers to switch to > Postgres instead of using SQLServer :-) > >> >> > >> >> There's also these points: https://www.pg-versus-ms.com/ (I think I > >> >> could write as much again on the spatial side of things alone.) If > you > >> >> have a choice, Postgres is far superior in so many ways. > >> >> > >> >> Nyall > >> >> > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson < > nyall.daw...@gmail.com>: > >> >> >> > >> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen > >> >> >> wrote: > >> >> >> > > >> >> >> > Hi list - > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > I've done some experiments with a dataset consisting of 44 > rows and uploaded this to two database servers: Postgres and SQLServer. > Both tables has indexes on Primary key and the spatial column. > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > And then connected to both tables in QGIS. The SQL server is 3 > times slower in retrieving the dataset than Postgres in QGIS! > >> >> >> > > >> >> >> > >> >> >> It's probably the extra validity checks which were added. SQL > Server > >> >> >> itself is broken by design when it comes to spatial data handling > and > >> >> >> if it encounters an invalid geometry it will silently abort the > >> >> >> request and y
Re: [QGIS-Developer] [Ubuntu] Bionic updates
Hi Jurgen, New packages have just landed on UbuntuGIS Unstable ppa. Can you please rebuild your QGIS 3.4.x package that depends on that ppa? Thanks! Angelos On 11/19/18 5:08 PM, Angelos Tzotsos wrote: Hi all, I have pushed some updates to experimental ppa for testing. The changes include: * PROJ 5.2.0 * GEOS 3.7.0 * GDAL 2.3.2 * PostGIS 2.5.0 (with sfcgal support) * OSSIM 2.5.2 * MapServer 7.2.1 * OTB 6.6.0 * pgRouting 2.6.1 * Mapnik 3.0.21 * MapProxy 1.11.0 * Fiona 1.8.1 * Rasterio 1.0.10 * QGIS 2.18.25 * Saga 2.3.1 This is a call for testing so we can move the packages to Unstable ppa soon :) Big thanks to the DebianGIS team for their help with the packages. Cheers, Angelos -- Angelos Tzotsos, PhD Charter Member Open Source Geospatial Foundation http://users.ntua.gr/tzotsos ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
On Fri, 7 Dec 2018 at 00:13, Bo Victor Thomsen wrote: > There is not much difference in the SQL Server execution times for the 3 > queries (around 200 - 300 ms) , so that can't explain the time difference in > QGIS > > QGIS 2 with native driver and QGIS 3 with ogr driver uses appr. the same time > showing the map. > QGIS 3 with native driver uses appr. twice the time to show the results on > the map. Ok, thanks. In that case it appears as though this regression was probably caused by the move to Qt5 or somewhere in the QGIS 3.0 refactoring (as opposed to a deliberate change) Next things to try: 1. Download debugview (https://docs.microsoft.com/en-us/sysinternals/downloads/debugview), start it, and then open QGIS. Test your layer and see if there's any relevant console output which may explain things 2. Run some little PyQGIS scripts to help narrow down where the issue is. I.e. run these scripts on a large layer with the different versions and time their execution: # fetch attributes only, no geometry req = QgsFeatureRequest().setFlags(QgsFeatureRequest.NoGeometry) for f in iface.activeLayer().getFeatures(req): pass # fetch geometry only, no attributes req = QgsFeatureRequest().setSubsetOfAttributes([]) for f in iface.activeLayer().getFeatures(req): pass This will help determine whether the regression is in the geometry parsing, attribute parsing, or somewhere else... Nyall > > Den tor. 6. dec. 2018 kl. 11.54 skrev Nyall Dawson : >> >> On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen >> wrote: >> > >> > Hi Nyall - >> > >> > I'm running QGIS 3.4.2 on Windows ver.10. >> > >> > >> > >> > Have there been any other changes to the SQLServer driver besides the >> > validity check? (I remember vaguely something about the internal >> > representation of spatial objects in the driver) >> >> No, nothing that would explain this. Just minor bug fixing and the port to >> Qt 5. >> >> I wonder if you could log the queries coming from QGIS and see if you >> can identify any changes from 2.18? >> >> Nyall >> >> >> >> >> > >> > I'm asking, because I've done this type of testing QGIS 2.x before where >> > the time difference between Postgres and SQL Server was relatively small >> > when doing simple MBR based searches - somewhere in the vicinity of 20% >> > >> > >> > >> > I would happily ditch MS SQLServer forever for spatial work and mainly use >> > Postgres. However, my customers have a different opinion :-( >> > >> > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson >> > : >> >> >> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen >> >> wrote: >> >> >> >> > I've tried switching the validity check off as described. As far as I >> >> > can measure, there is no time difference with or without the validity >> >> > check. When does the validity check kick in? Writing or reading the >> >> > features? Or both? >> >> > >> >> >> >> It's on read. Writing always uses a make valid call for SQL Server to >> >> try to avoid triggering the issue. >> >> >> >> > And the validity check doesn't explain the obvious time difference >> >> > between the OGR driver and the native QGIS driver for SQL Server >> >> >> >> Well, it would if OGR wasn't doing this check by default. What >> >> platform are you connecting from? Windows or Linux? >> >> >> >> > However, I will use your explanation about SQL Server's behavior >> >> > regarding invalid geometries as an argument for my customers to switch >> >> > to Postgres instead of using SQLServer :-) >> >> >> >> There's also these points: https://www.pg-versus-ms.com/ (I think I >> >> could write as much again on the spatial side of things alone.) If you >> >> have a choice, Postgres is far superior in so many ways. >> >> >> >> Nyall >> >> >> >> > >> >> > >> >> > >> >> > >> >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson >> >> > : >> >> >> >> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen >> >> >> wrote: >> >> >> > >> >> >> > Hi list - >> >> >> > >> >> >> > >> >> >> > >> >> >> > I've done some experiments with a dataset consisting of 44 rows >> >> >> > and uploaded this to two database servers: Postgres and SQLServer. >> >> >> > Both tables has indexes on Primary key and the spatial column. >> >> >> > >> >> >> > >> >> >> > >> >> >> > And then connected to both tables in QGIS. The SQL server is 3 times >> >> >> > slower in retrieving the dataset than Postgres in QGIS! >> >> >> > >> >> >> >> >> >> It's probably the extra validity checks which were added. SQL Server >> >> >> itself is broken by design when it comes to spatial data handling and >> >> >> if it encounters an invalid geometry it will silently abort the >> >> >> request and you'll be missing features from the layer. But there's *no >> >> >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the >> >> >> "safer is better" approach and forces a validity check and make valid >> >> >> step as part of the queries sent to SQL Server. This avoids the >> >> >> potentially missin
[QGIS-Developer] Plugin [1412] GeoDataFarm approval notification.
Plugin GeoDataFarm approval by pcav. The plugin version "[1412] GeoDataFarm 2.0.4" is now approved Link: http://plugins.qgis.org/plugins/geodatafarm/ ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] [Qgis-user] Question About QgsGeometry Static Functions
Thanks Nyall. I think I had a misunderstanding in that the XY routines were the preferred method to use unless you have Z/M dimensions. I will look at changing my code. Calvin On Wed, Dec 5, 2018 at 6:06 PM Nyall Dawson wrote: > On Wed, 5 Dec 2018 at 02:52, C Hamilton wrote: > > > > QgsGeometry has the following static functions > > > > QgsGeometry.fromPolylineXY( list of QgsPointXY) > > QgsGeometry.fromMultiPolylineXY( list of QgsPointXY lists) > > > > If I am working with QgsPoint rather than QgsPointXY there is > > > > QgsGeometry.fromPolyline( list of QgsPoint) > > > > However there is not a similar multi polyline function. Why not? > > Because there's been no demand for this, until now. But in general all > those fromPolylineXY etc methods should be avoided wherever possible. > They are very slow (lots of list allocations) and don't handle Z/M > dimensions. > > You're better to work directly with the QGIS geometry subclasses like > QgsLineString, QgsMultiLineString instead. > > Nyall > > > > > > QgsGeometry.fromMultiPolyline( list of QgsPoint list) > > > > What is the proper way to implement this? > > > > Thanks > > ___ > > Qgis-user mailing list > > qgis-u...@lists.osgeo.org > > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
Hi Nyall - Back again ..
The 3 different sql queries from QGIS.
-- QGIS 3, native driver
SELECT [cell100_key],[geom_flade] FROM [dbo].[cells3] WHERE
[geom_flade].STIsValid() = 1 AND
[geom_flade].Filter([geometry]::STGeomFromText('POLYGON((823546.46544876880943775
610, 936453.53455123119056225 610, 936453.53455123119056225
615, 823546.46544876880943775 615, 823546.46544876880943775
610))',25832)) = 1
-- QGIS 3 OGR driver
select [cell100_key], [geom_flade] from [dbo].[cells3] where
[geom_flade].STIntersects(geometry::STGeomFromText('POLYGON((823546.465448769
610,936453.534551231 610,936453.534551231 615,823546.465448769
615,823546.465448769 610))',25832)) = 1
-- QGIS 2, native driver
SELECT [cell100_key],[geom_flade] FROM [dbo].[cells3] where
[geom_flade].STIsValid() = 1 AND
[geom_flade].Filter([geometry]::STGeomFromText('POLYGON((826739.13043478259351104
610, 933260.86956521740648896 610, 933260.86956521740648896
615, 826739.13043478259351104 615, 826739.13043478259351104
610))',25832)) = 1
There is not much difference in the SQL Server execution times for the 3
queries (around 200 - 300 ms) , so that can't explain the time difference
in QGIS
QGIS 2 with native driver and QGIS 3 with ogr driver uses appr. the same
time showing the map.
QGIS 3 with native driver uses appr. twice the time to show the results on
the map.
Den tor. 6. dec. 2018 kl. 11.54 skrev Nyall Dawson :
> On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen
> wrote:
> >
> > Hi Nyall -
> >
> > I'm running QGIS 3.4.2 on Windows ver.10.
> >
> >
> >
> > Have there been any other changes to the SQLServer driver besides the
> validity check? (I remember vaguely something about the internal
> representation of spatial objects in the driver)
>
> No, nothing that would explain this. Just minor bug fixing and the port to
> Qt 5.
>
> I wonder if you could log the queries coming from QGIS and see if you
> can identify any changes from 2.18?
>
> Nyall
>
>
>
>
> >
> > I'm asking, because I've done this type of testing QGIS 2.x before where
> the time difference between Postgres and SQL Server was relatively small
> when doing simple MBR based searches - somewhere in the vicinity of 20%
> >
> >
> >
> > I would happily ditch MS SQLServer forever for spatial work and mainly
> use Postgres. However, my customers have a different opinion :-(
> >
> > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson <
> nyall.daw...@gmail.com>:
> >>
> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen
> >> wrote:
> >>
> >> > I've tried switching the validity check off as described. As far as I
> can measure, there is no time difference with or without the validity
> check. When does the validity check kick in? Writing or reading the
> features? Or both?
> >> >
> >>
> >> It's on read. Writing always uses a make valid call for SQL Server to
> >> try to avoid triggering the issue.
> >>
> >> > And the validity check doesn't explain the obvious time difference
> between the OGR driver and the native QGIS driver for SQL Server
> >>
> >> Well, it would if OGR wasn't doing this check by default. What
> >> platform are you connecting from? Windows or Linux?
> >>
> >> > However, I will use your explanation about SQL Server's behavior
> regarding invalid geometries as an argument for my customers to switch to
> Postgres instead of using SQLServer :-)
> >>
> >> There's also these points: https://www.pg-versus-ms.com/ (I think I
> >> could write as much again on the spatial side of things alone.) If you
> >> have a choice, Postgres is far superior in so many ways.
> >>
> >> Nyall
> >>
> >> >
> >> >
> >> >
> >> >
> >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson <
> nyall.daw...@gmail.com>:
> >> >>
> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen
> >> >> wrote:
> >> >> >
> >> >> > Hi list -
> >> >> >
> >> >> >
> >> >> >
> >> >> > I've done some experiments with a dataset consisting of 44
> rows and uploaded this to two database servers: Postgres and SQLServer.
> Both tables has indexes on Primary key and the spatial column.
> >> >> >
> >> >> >
> >> >> >
> >> >> > And then connected to both tables in QGIS. The SQL server is 3
> times slower in retrieving the dataset than Postgres in QGIS!
> >> >> >
> >> >>
> >> >> It's probably the extra validity checks which were added. SQL Server
> >> >> itself is broken by design when it comes to spatial data handling and
> >> >> if it encounters an invalid geometry it will silently abort the
> >> >> request and you'll be missing features from the layer. But there's
> *no
> >> >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the
> >> >> "safer is better" approach and forces a validity check and make valid
> >> >> step as part of the queries sent to SQL Server. This avoids the
> >> >> potentially missing features, but comes at a large cost.
> >> >>
> >> >> If you're 100% sure that your tables have no invalid geometries (and
> >> >> never will have any
[QGIS-Developer] Plugin [971] Disconnected Islands approval notification.
Plugin Disconnected Islands approval by pcav. The plugin version "[971] Disconnected Islands 2.0.1" is now approved Link: http://plugins.qgis.org/plugins/disconnected-islands/ ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
Ok, I'll take a look and monitor the queries from QGIS 2 and 3 and report back. It will take a couple of days And thanks Nyall Den tor. 6. dec. 2018 kl. 11.54 skrev Nyall Dawson : > On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen > wrote: > > > > Hi Nyall - > > > > I'm running QGIS 3.4.2 on Windows ver.10. > > > > > > > > Have there been any other changes to the SQLServer driver besides the > validity check? (I remember vaguely something about the internal > representation of spatial objects in the driver) > > No, nothing that would explain this. Just minor bug fixing and the port to > Qt 5. > > I wonder if you could log the queries coming from QGIS and see if you > can identify any changes from 2.18? > > Nyall > > > > > > > > I'm asking, because I've done this type of testing QGIS 2.x before where > the time difference between Postgres and SQL Server was relatively small > when doing simple MBR based searches - somewhere in the vicinity of 20% > > > > > > > > I would happily ditch MS SQLServer forever for spatial work and mainly > use Postgres. However, my customers have a different opinion :-( > > > > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson < > nyall.daw...@gmail.com>: > >> > >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen > >> wrote: > >> > >> > I've tried switching the validity check off as described. As far as I > can measure, there is no time difference with or without the validity > check. When does the validity check kick in? Writing or reading the > features? Or both? > >> > > >> > >> It's on read. Writing always uses a make valid call for SQL Server to > >> try to avoid triggering the issue. > >> > >> > And the validity check doesn't explain the obvious time difference > between the OGR driver and the native QGIS driver for SQL Server > >> > >> Well, it would if OGR wasn't doing this check by default. What > >> platform are you connecting from? Windows or Linux? > >> > >> > However, I will use your explanation about SQL Server's behavior > regarding invalid geometries as an argument for my customers to switch to > Postgres instead of using SQLServer :-) > >> > >> There's also these points: https://www.pg-versus-ms.com/ (I think I > >> could write as much again on the spatial side of things alone.) If you > >> have a choice, Postgres is far superior in so many ways. > >> > >> Nyall > >> > >> > > >> > > >> > > >> > > >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson < > nyall.daw...@gmail.com>: > >> >> > >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen > >> >> wrote: > >> >> > > >> >> > Hi list - > >> >> > > >> >> > > >> >> > > >> >> > I've done some experiments with a dataset consisting of 44 > rows and uploaded this to two database servers: Postgres and SQLServer. > Both tables has indexes on Primary key and the spatial column. > >> >> > > >> >> > > >> >> > > >> >> > And then connected to both tables in QGIS. The SQL server is 3 > times slower in retrieving the dataset than Postgres in QGIS! > >> >> > > >> >> > >> >> It's probably the extra validity checks which were added. SQL Server > >> >> itself is broken by design when it comes to spatial data handling and > >> >> if it encounters an invalid geometry it will silently abort the > >> >> request and you'll be missing features from the layer. But there's > *no > >> >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the > >> >> "safer is better" approach and forces a validity check and make valid > >> >> step as part of the queries sent to SQL Server. This avoids the > >> >> potentially missing features, but comes at a large cost. > >> >> > >> >> If you're 100% sure that your tables have no invalid geometries (and > >> >> never will have any!), you *can* switch this check off. But be > >> >> warned... if you ever introduce invalid geometries into your tables, > >> >> you'll get data loss. The setting is under the SQL Server > connection's > >> >> properties -- "skip invalid geometry handling". > >> >> > >> >> Let me know if this helps at all > >> >> > >> >> Nyall > >> > > >> > > >> > > >> > -- > >> > Med venlig hilsen > >> > > >> > Bo Victor Thomsen > >> > > > > > > > > > -- > > Med venlig hilsen > > > > Bo Victor Thomsen > > > -- Med venlig hilsen Bo Victor Thomsen ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen wrote: > > Hi Nyall - > > I'm running QGIS 3.4.2 on Windows ver.10. > > > > Have there been any other changes to the SQLServer driver besides the > validity check? (I remember vaguely something about the internal > representation of spatial objects in the driver) No, nothing that would explain this. Just minor bug fixing and the port to Qt 5. I wonder if you could log the queries coming from QGIS and see if you can identify any changes from 2.18? Nyall > > I'm asking, because I've done this type of testing QGIS 2.x before where the > time difference between Postgres and SQL Server was relatively small when > doing simple MBR based searches - somewhere in the vicinity of 20% > > > > I would happily ditch MS SQLServer forever for spatial work and mainly use > Postgres. However, my customers have a different opinion :-( > > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson : >> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen >> wrote: >> >> > I've tried switching the validity check off as described. As far as I can >> > measure, there is no time difference with or without the validity check. >> > When does the validity check kick in? Writing or reading the features? Or >> > both? >> > >> >> It's on read. Writing always uses a make valid call for SQL Server to >> try to avoid triggering the issue. >> >> > And the validity check doesn't explain the obvious time difference between >> > the OGR driver and the native QGIS driver for SQL Server >> >> Well, it would if OGR wasn't doing this check by default. What >> platform are you connecting from? Windows or Linux? >> >> > However, I will use your explanation about SQL Server's behavior regarding >> > invalid geometries as an argument for my customers to switch to Postgres >> > instead of using SQLServer :-) >> >> There's also these points: https://www.pg-versus-ms.com/ (I think I >> could write as much again on the spatial side of things alone.) If you >> have a choice, Postgres is far superior in so many ways. >> >> Nyall >> >> > >> > >> > >> > >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson >> > : >> >> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen >> >> wrote: >> >> > >> >> > Hi list - >> >> > >> >> > >> >> > >> >> > I've done some experiments with a dataset consisting of 44 rows and >> >> > uploaded this to two database servers: Postgres and SQLServer. Both >> >> > tables has indexes on Primary key and the spatial column. >> >> > >> >> > >> >> > >> >> > And then connected to both tables in QGIS. The SQL server is 3 times >> >> > slower in retrieving the dataset than Postgres in QGIS! >> >> > >> >> >> >> It's probably the extra validity checks which were added. SQL Server >> >> itself is broken by design when it comes to spatial data handling and >> >> if it encounters an invalid geometry it will silently abort the >> >> request and you'll be missing features from the layer. But there's *no >> >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the >> >> "safer is better" approach and forces a validity check and make valid >> >> step as part of the queries sent to SQL Server. This avoids the >> >> potentially missing features, but comes at a large cost. >> >> >> >> If you're 100% sure that your tables have no invalid geometries (and >> >> never will have any!), you *can* switch this check off. But be >> >> warned... if you ever introduce invalid geometries into your tables, >> >> you'll get data loss. The setting is under the SQL Server connection's >> >> properties -- "skip invalid geometry handling". >> >> >> >> Let me know if this helps at all >> >> >> >> Nyall >> > >> > >> > >> > -- >> > Med venlig hilsen >> > >> > Bo Victor Thomsen >> > > > > > -- > Med venlig hilsen > > Bo Victor Thomsen > ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
Hi Nyall - I'm running QGIS 3.4.2 on Windows ver.10. Have there been any other changes to the SQLServer driver besides the validity check? (I remember vaguely something about the internal representation of spatial objects in the driver) I'm asking, because I've done this type of testing QGIS 2.x before where the time difference between Postgres and SQL Server was relatively small when doing simple MBR based searches - somewhere in the vicinity of 20% I would happily ditch MS SQLServer forever for spatial work and mainly use Postgres. However, my customers have a different opinion :-( Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson : > On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen > wrote: > > > I've tried switching the validity check off as described. As far as I > can measure, there is no time difference with or without the validity > check. When does the validity check kick in? Writing or reading the > features? Or both? > > > > It's on read. Writing always uses a make valid call for SQL Server to > try to avoid triggering the issue. > > > And the validity check doesn't explain the obvious time difference > between the OGR driver and the native QGIS driver for SQL Server > > Well, it would if OGR wasn't doing this check by default. What > platform are you connecting from? Windows or Linux? > > > However, I will use your explanation about SQL Server's behavior > regarding invalid geometries as an argument for my customers to switch to > Postgres instead of using SQLServer :-) > > There's also these points: https://www.pg-versus-ms.com/ (I think I > could write as much again on the spatial side of things alone.) If you > have a choice, Postgres is far superior in so many ways. > > Nyall > > > > > > > > > > > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson < > nyall.daw...@gmail.com>: > >> > >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen > >> wrote: > >> > > >> > Hi list - > >> > > >> > > >> > > >> > I've done some experiments with a dataset consisting of 44 rows > and uploaded this to two database servers: Postgres and SQLServer. Both > tables has indexes on Primary key and the spatial column. > >> > > >> > > >> > > >> > And then connected to both tables in QGIS. The SQL server is 3 times > slower in retrieving the dataset than Postgres in QGIS! > >> > > >> > >> It's probably the extra validity checks which were added. SQL Server > >> itself is broken by design when it comes to spatial data handling and > >> if it encounters an invalid geometry it will silently abort the > >> request and you'll be missing features from the layer. But there's *no > >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the > >> "safer is better" approach and forces a validity check and make valid > >> step as part of the queries sent to SQL Server. This avoids the > >> potentially missing features, but comes at a large cost. > >> > >> If you're 100% sure that your tables have no invalid geometries (and > >> never will have any!), you *can* switch this check off. But be > >> warned... if you ever introduce invalid geometries into your tables, > >> you'll get data loss. The setting is under the SQL Server connection's > >> properties -- "skip invalid geometry handling". > >> > >> Let me know if this helps at all > >> > >> Nyall > > > > > > > > -- > > Med venlig hilsen > > > > Bo Victor Thomsen > > > -- Med venlig hilsen Bo Victor Thomsen ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen wrote: > I've tried switching the validity check off as described. As far as I can > measure, there is no time difference with or without the validity check. When > does the validity check kick in? Writing or reading the features? Or both? > It's on read. Writing always uses a make valid call for SQL Server to try to avoid triggering the issue. > And the validity check doesn't explain the obvious time difference between > the OGR driver and the native QGIS driver for SQL Server Well, it would if OGR wasn't doing this check by default. What platform are you connecting from? Windows or Linux? > However, I will use your explanation about SQL Server's behavior regarding > invalid geometries as an argument for my customers to switch to Postgres > instead of using SQLServer :-) There's also these points: https://www.pg-versus-ms.com/ (I think I could write as much again on the spatial side of things alone.) If you have a choice, Postgres is far superior in so many ways. Nyall > > > > > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson : >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen >> wrote: >> > >> > Hi list - >> > >> > >> > >> > I've done some experiments with a dataset consisting of 44 rows and >> > uploaded this to two database servers: Postgres and SQLServer. Both tables >> > has indexes on Primary key and the spatial column. >> > >> > >> > >> > And then connected to both tables in QGIS. The SQL server is 3 times >> > slower in retrieving the dataset than Postgres in QGIS! >> > >> >> It's probably the extra validity checks which were added. SQL Server >> itself is broken by design when it comes to spatial data handling and >> if it encounters an invalid geometry it will silently abort the >> request and you'll be missing features from the layer. But there's *no >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the >> "safer is better" approach and forces a validity check and make valid >> step as part of the queries sent to SQL Server. This avoids the >> potentially missing features, but comes at a large cost. >> >> If you're 100% sure that your tables have no invalid geometries (and >> never will have any!), you *can* switch this check off. But be >> warned... if you ever introduce invalid geometries into your tables, >> you'll get data loss. The setting is under the SQL Server connection's >> properties -- "skip invalid geometry handling". >> >> Let me know if this helps at all >> >> Nyall > > > > -- > Med venlig hilsen > > Bo Victor Thomsen > ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
Hi Nyall - Thanks for taking your time answering me :-) I've tried switching the validity check off as described. As far as I can measure, there is no time difference with or without the validity check. When does the validity check kick in? Writing or reading the features? Or both? And the validity check doesn't explain the obvious time difference between the OGR driver and the native QGIS driver for SQL Server However, I *will* use your explanation about SQL Server's behavior regarding invalid geometries as an argument for my customers to switch to Postgres instead of using SQLServer :-) Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson : > On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen > wrote: > > > > Hi list - > > > > > > > > I've done some experiments with a dataset consisting of 44 rows and > uploaded this to two database servers: Postgres and SQLServer. Both tables > has indexes on Primary key and the spatial column. > > > > > > > > And then connected to both tables in QGIS. The SQL server is 3 times > slower in retrieving the dataset than Postgres in QGIS! > > > > It's probably the extra validity checks which were added. SQL Server > itself is broken by design when it comes to spatial data handling and > if it encounters an invalid geometry it will silently abort the > request and you'll be missing features from the layer. But there's *no > way* for QGIS to detect when this occurs! Accordingly QGIS takes the > "safer is better" approach and forces a validity check and make valid > step as part of the queries sent to SQL Server. This avoids the > potentially missing features, but comes at a large cost. > > If you're 100% sure that your tables have no invalid geometries (and > never will have any!), you *can* switch this check off. But be > warned... if you ever introduce invalid geometries into your tables, > you'll get data loss. The setting is under the SQL Server connection's > properties -- "skip invalid geometry handling". > > Let me know if this helps at all > > Nyall > -- Med venlig hilsen Bo Victor Thomsen ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen wrote: > > Hi list - > > > > I've done some experiments with a dataset consisting of 44 rows and > uploaded this to two database servers: Postgres and SQLServer. Both tables > has indexes on Primary key and the spatial column. > > > > And then connected to both tables in QGIS. The SQL server is 3 times slower > in retrieving the dataset than Postgres in QGIS! > It's probably the extra validity checks which were added. SQL Server itself is broken by design when it comes to spatial data handling and if it encounters an invalid geometry it will silently abort the request and you'll be missing features from the layer. But there's *no way* for QGIS to detect when this occurs! Accordingly QGIS takes the "safer is better" approach and forces a validity check and make valid step as part of the queries sent to SQL Server. This avoids the potentially missing features, but comes at a large cost. If you're 100% sure that your tables have no invalid geometries (and never will have any!), you *can* switch this check off. But be warned... if you ever introduce invalid geometries into your tables, you'll get data loss. The setting is under the SQL Server connection's properties -- "skip invalid geometry handling". Let me know if this helps at all Nyall ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
[QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver
Hi list - I've done some experiments with a dataset consisting of 44 rows and uploaded this to two database servers: Postgres and SQLServer. Both tables has indexes on Primary key and the spatial column. And then connected to both tables in QGIS. The SQL server is *3 times* slower in retrieving the dataset than Postgres in QGIS! And it's not SQL server's fault!! I've made the same experiment using GeoServer as a client where Postgres is only slightly faster. To make matters worse... In QGIS you can connect to the SQL server database using the "Layer" > "Add Layer" > "Add Vector layer" - i.e. using the OGR driver - to connect to the SQL Server table. This makes the retrieval of SQL Server data nearly as fast as Postgres. I have to conclude, that the native QGIS SQLServer driver is really, really bad. What's the matter with this driver? I recollect, doing the same experiment in QGIS 2.x, that a simple fetch of SQL server data was nearly as fast as retrieving data from a comparable Postgres database server (I’m not talking about complex spatial queries, where Postgres blows SQL Server totally out of the water, but simple retrieval of layer data using a MBR search) Unfortunately, I can't just switch to the OGR driver since I have some write issues with this driver. What gives ??? -- Med venlig hilsen Bo Victor Thomsen ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
[QGIS-Developer] Plugin [412] Qgis2threejs approval notification.
Plugin Qgis2threejs approval by pcav. The plugin version "[412] Qgis2threejs 2.3" is now approved Link: http://plugins.qgis.org/plugins/Qgis2threejs/ ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
[QGIS-Developer] Plugin [1357] Least-Cost-Paths Network approval notification.
Plugin Least-Cost-Paths Network approval by pcav. The plugin version "[1357] Least-Cost-Paths Network 0.1.3 Experimental" is now approved Link: http://plugins.qgis.org/plugins/LCPNetwork/ ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
