[Qgis-developer] Geospackage Slow in QGIS

[Jeremy Palmer]

I've just been using the new implementation of Geopackage in QGIS :) However 
each map render or feature info is very slow for large database files - even 
with the new GDAL/OGR Geopackage spatial index support. Each time QGIS renders 
a new map view or executes a feature info it re-opens the Geopackage file in 
QgsOgrFeatureIterator via OGROpen, and the Geopackage driver open method runs 
PRAGMA integrity_check which is very slow (4 secs with my 2GB test file). This 
integrity check is actually a Geopackage requirement.

How can this be fixed? At the in QGIS or GDAL/OGR level?

Cheers,
Jeremy

This message contains information, which may be in confidence and may be 
subject to legal privilege. If you are not the intended recipient, you must not 
peruse, use, disseminate, distribute or copy this message. If you have received 
this message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. LINZ accepts no 
responsibility for changes to this email, or for any attachments, after its 
transmission from LINZ. Thank You.
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Andrea Peri]

More probably there is some optimal adjust chance, but the geopackage
format is for exchange data. It is sub optimal for work daily.
 Il 17/mag/2014 09:40 "Jeremy Palmer"  ha scritto:

> I've just been using the new implementation of Geopackage in QGIS :)
> However each map render or feature info is very slow for large database
> files - even with the new GDAL/OGR Geopackage spatial index support. Each
> time QGIS renders a new map view or executes a feature info it re-opens the
> Geopackage file in QgsOgrFeatureIterator via OGROpen, and the Geopackage
> driver open method runs PRAGMA integrity_check which is very slow (4 secs
> with my 2GB test file). This integrity check is actually a Geopackage
> requirement.
>
> How can this be fixed? At the in QGIS or GDAL/OGR level?
>
> Cheers,
> Jeremy
>
> This message contains information, which may be in confidence and may be
> subject to legal privilege. If you are not the intended recipient, you must
> not peruse, use, disseminate, distribute or copy this message. If you have
> received this message in error, please notify us immediately (Phone 0800
> 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ
> accepts no responsibility for changes to this email, or for any
> attachments, after its transmission from LINZ. Thank You.
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Jeremy Palmer]

HI Stefan,

Call from QGIS is here: 
https://github.com/qgis/QGIS/blob/master/src/providers/ogr/qgsogrfeatureiterator.cpp#L45

GDAL code is here:

http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/gpkg/ogrgeopackagedatasource.cpp#L421

My profiler says all the time is in OGRGeoPackageDataSource::Open.

Also a simple check via the command line for the given database:

qlite3 test_parcels.gpkg
sqlite> .timer ON
sqlite> PRAGMA integrity_check;
ok
CPU Time: user 3.384211 sys 0.756047

Cheers,
Jeremy

From: Stefan Keller [sfkel...@gmail.com]
Sent: Saturday, 17 May 2014 8:20 p.m.
To: Jeremy Palmer
Cc: qgis-developer@lists.osgeo.org
Subject: Re: [Qgis-developer] Geospackage Slow in QGIS

Hi Jeremy

Is this PRAGMA integrity_check really the bottle neck?
Is it an integrity check on a data type (e.g. integer - its probably actually 
an integrity check on SRID)?
Can you point to the code?

Yours, Stefan



2014-05-17 9:32 GMT+02:00 Jeremy Palmer 
mailto:jpal...@linz.govt.nz>>:
I've just been using the new implementation of Geopackage in QGIS :) However 
each map render or feature info is very slow for large database files - even 
with the new GDAL/OGR Geopackage spatial index support. Each time QGIS renders 
a new map view or executes a feature info it re-opens the Geopackage file in 
QgsOgrFeatureIterator via OGROpen, and the Geopackage driver open method runs 
PRAGMA integrity_check which is very slow (4 secs with my 2GB test file). This 
integrity check is actually a Geopackage requirement.

How can this be fixed? At the in QGIS or GDAL/OGR level?

Cheers,
Jeremy

This message contains information, which may be in confidence and may be 
subject to legal privilege. If you are not the intended recipient, you must not 
peruse, use, disseminate, distribute or copy this message. If you have received 
this message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. 
LINZ accepts no responsibility for changes to this email, or for any 
attachments, after its transmission from LINZ. Thank You.
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Paolo Cavallini]

Il 17/05/2014 10:04, Andrea Peri ha scritto:
> More probably there is some optimal adjust chance, but the geopackage format 
> is for
> exchange data. It is sub optimal for work daily.

Hi Andrea,
gpkg seems to aim at a complete replacement of shp:
http://www.geopackage.org/
not only for data transfer, but for everyday usage.
Do you have more info on this?
All the best.
-- 
Paolo Cavallini - www.faunalia.eu
Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Stefan Keller]

So it's this code fragment?
419 /* Requirement 6: The SQLite PRAGMA integrity_check SQL command
SHALL return â??okâ? */
420/* http://opengis.github.io/geopackage/#_file_integrity */
421if( strncmp(pszFilename, "/vsicurl/", strlen("/vsicurl/")) != 0 &&
422OGRERR_NONE != PragmaCheck("integrity_check", "ok", 1) )

Looks strange to me why a single file check woold be the bottlenet since
this should be a single call.

I would have expected that some repeated calls (like reading
objects/records and checking SRID or data types) would be the bottleneck
since SQlite is not performant when used with PRAGMA checks. This would be
a check one could disable with a parameter.

--S.



2014-05-17 10:29 GMT+02:00 Jeremy Palmer :

> HI Stefan,
>
> Call from QGIS is here:
> https://github.com/qgis/QGIS/blob/master/src/providers/ogr/qgsogrfeatureiterator.cpp#L45
>
> GDAL code is here:
>
>
> http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/gpkg/ogrgeopackagedatasource.cpp#L421
>
> My profiler says all the time is in OGRGeoPackageDataSource::Open.
>
> Also a simple check via the command line for the given database:
>
> qlite3 test_parcels.gpkg
> sqlite> .timer ON
> sqlite> PRAGMA integrity_check;
> ok
> CPU Time: user 3.384211 sys 0.756047
>
> Cheers,
> Jeremy
> 
> From: Stefan Keller [sfkel...@gmail.com]
> Sent: Saturday, 17 May 2014 8:20 p.m.
> To: Jeremy Palmer
> Cc: qgis-developer@lists.osgeo.org
> Subject: Re: [Qgis-developer] Geospackage Slow in QGIS
>
> Hi Jeremy
>
> Is this PRAGMA integrity_check really the bottle neck?
> Is it an integrity check on a data type (e.g. integer - its probably
> actually an integrity check on SRID)?
> Can you point to the code?
>
> Yours, Stefan
>
>
>
> 2014-05-17 9:32 GMT+02:00 Jeremy Palmer  jpal...@linz.govt.nz>>:
> I've just been using the new implementation of Geopackage in QGIS :)
> However each map render or feature info is very slow for large database
> files - even with the new GDAL/OGR Geopackage spatial index support. Each
> time QGIS renders a new map view or executes a feature info it re-opens the
> Geopackage file in QgsOgrFeatureIterator via OGROpen, and the Geopackage
> driver open method runs PRAGMA integrity_check which is very slow (4 secs
> with my 2GB test file). This integrity check is actually a Geopackage
> requirement.
>
> How can this be fixed? At the in QGIS or GDAL/OGR level?
>
> Cheers,
> Jeremy
>
> This message contains information, which may be in confidence and may be
> subject to legal privilege. If you are not the intended recipient, you must
> not peruse, use, disseminate, distribute or copy this message. If you have
> received this message in error, please notify us immediately (Phone 0800
> 665 463 or i...@linz.govt.nz) and destroy the
> original message. LINZ accepts no responsibility for changes to this email,
> or for any attachments, after its transmission from LINZ. Thank You.
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Stefan Keller]

Hi Jeremy

Is this PRAGMA integrity_check really the bottle neck?
Is it an integrity check on a data type (e.g. integer - its probably
actually an integrity check on SRID)?
Can you point to the code?

Yours, Stefan



2014-05-17 9:32 GMT+02:00 Jeremy Palmer :

> I've just been using the new implementation of Geopackage in QGIS :)
> However each map render or feature info is very slow for large database
> files - even with the new GDAL/OGR Geopackage spatial index support. Each
> time QGIS renders a new map view or executes a feature info it re-opens the
> Geopackage file in QgsOgrFeatureIterator via OGROpen, and the Geopackage
> driver open method runs PRAGMA integrity_check which is very slow (4 secs
> with my 2GB test file). This integrity check is actually a Geopackage
> requirement.
>
> How can this be fixed? At the in QGIS or GDAL/OGR level?
>
> Cheers,
> Jeremy
>
> This message contains information, which may be in confidence and may be
> subject to legal privilege. If you are not the intended recipient, you must
> not peruse, use, disseminate, distribute or copy this message. If you have
> received this message in error, please notify us immediately (Phone 0800
> 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ
> accepts no responsibility for changes to this email, or for any
> attachments, after its transmission from LINZ. Thank You.
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Auto increment field for shapefiles?

[Paolo Cavallini]

Il 17/05/2014 00:38, mmekuria ha scritto:
> I am not sure what you mean, Paulo. But there is already the Feature Id
> (Object Id in ArcGIS) in QgsFeature. Do you mean having that a mandatory
> field that gets populated every time a feature is created within a
> shapefile?

Almost: I mean let the user define a field as SERIAL
http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
also for non-database data types (especially shp).
It does not seem too difficult, now that we have a record number function in the
expressions.
All the best.

-- 
Paolo Cavallini - www.faunalia.eu
Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Andrea Peri]

Do you know the requirements of gpkg ?
Il 17/mag/2014 10:46 "Paolo Cavallini"  ha scritto:

> Il 17/05/2014 10:04, Andrea Peri ha scritto:
> > More probably there is some optimal adjust chance, but the geopackage
> format is for
> > exchange data. It is sub optimal for work daily.
>
> Hi Andrea,
> gpkg seems to aim at a complete replacement of shp:
> http://www.geopackage.org/
> not only for data transfer, but for everyday usage.
> Do you have more info on this?
> All the best.
> --
> Paolo Cavallini - www.faunalia.eu
> Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Paolo Cavallini]

Il 17/05/2014 11:03, Andrea Peri ha scritto:
> Do you know the requirements of gpkg ?

They should be really minimal.
This could help understanding speed issues:
http://openjump.blogspot.fi/2014/02/openjump-plus-reads-ogc-geopackages.html
All the best.
-- 
Paolo Cavallini - www.faunalia.eu
Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[G. Allegri]

OT: there was already a nice, and very well supporyed, spatial Sqlite
extension before this Geopackage lobby made its way in OGC... What a pity.

giovanni
Il 17/mag/2014 11:24 "Paolo Cavallini"  ha scritto:

> Il 17/05/2014 11:03, Andrea Peri ha scritto:
> > Do you know the requirements of gpkg ?
>
> They should be really minimal.
> This could help understanding speed issues:
>
> http://openjump.blogspot.fi/2014/02/openjump-plus-reads-ogc-geopackages.html
> All the best.
> --
> Paolo Cavallini - www.faunalia.eu
> Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Even Rouault]

Le samedi 17 mai 2014 10:46:39, Stefan Keller a écrit :
> So it's this code fragment?
> 419 /* Requirement 6: The SQLite PRAGMA integrity_check SQL command
> SHALL return â??okâ? */
> 420/* http://opengis.github.io/geopackage/#_file_integrity */
> 421if( strncmp(pszFilename, "/vsicurl/", strlen("/vsicurl/")) != 0 &&
> 422OGRERR_NONE != PragmaCheck("integrity_check", "ok", 1) )
> 
> Looks strange to me why a single file check woold be the bottlenet since
> this should be a single call.
> 
> I would have expected that some repeated calls (like reading
> objects/records and checking SRID or data types) would be the bottleneck
> since SQlite is not performant when used with PRAGMA checks. This would be
> a check one could disable with a parameter.

I've just noticed that thread. Actually I found that integrity_check was slow 
when operating on remote databases with /vsicurl/ and didn't verify how slow 
it could be with local big files, but its documentation at 
http://www.sqlite.org/pragma.html#pragma_integrity_check suggests that it 
might be bery slow indeed.
Reading the GeoPackage spec, nothing mandates to do the integrity_check at 
open time. It only mandates that if you run "pragma integrity_check" it should 
retrun OK. So I've disabled the check by default.

Even


-- 
Geospatial professional services
http://even.rouault.free.fr/services.html
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[aperi2007]

I have really difficulty to write of a so complex question in english.
:(
However try to explain.
The shapefile is absolutely a bad solution for interchange usage.
We experiment every day troubles. Sometimes the troubles was discovered 
after many time.

The shapefile born as a simply format for a simply GIS product.
SO the major request was "t be simpler possible".
And infact the shapefile is really simply.
The shp specs use the DBF format, but forgot that of DBF format there 
are many ,sometimes also really distinct to each other.
(read the old clipper dbf spec and the old ashton-tate dbf spec to 
understand what this mean).


So two shapefile could be not compatible at all.
Also the shapefile don't sauy nothing of character-set usage.
This is a bad question because often we must try more than one 
Character-set and hope to have no mixing of CS to retrieve correctly the 
texts.
Also the shapefile don't has a NULL definition and also it allow the 
strange mix of MULTIPART and SIMPLY geometries.


All this and so on are foe for a real interchangeable format because
usually this mean that I export from a remote system , from a distinct 
GIS solution to every other solution.


We every day use the gdal to transform (thx to gdal forever), but gdal 
cannot do miracles.


A format born for interchange is surely the GML.
It allow to define a different SRS for every geometry, also allow to 
host every typeof geometry ,
but the GML failed in the market because the geometric definition was 
not well explained and so every GIS software

choose to do its own interpretation of the geometrical tag in the GML.

Obviously the postgis , oralce and so on are not really nterchange 
formats because they are only dump of the DBMS information.

Another good candidate for interchange was the spatialite.
The lastversion 4.2 is really good.
It host metadata, host styles, host history and convert al to the UTF-8 
internal.

It has some limitation respect to the GML.
The GML allow to have a distinct SRS for every geometry instead the 
spatialite allow a SRS for every table (dataset).


But this limitation will not be a real limit today, perhaps in the next 
2-3 year it will became a limit.


But the spatialite has another limit for a good interchange format.
It is also an elaboration unit.
This is an advantage for me , but not for a real interchange format that 
need only host data.

And give the elaboration phase to the GIS.
This is a philosophy I don't like more, but has its own reasons .

So why the geopackage is not a good work format ?
The response is because it is a interchange format. The need of a work 
frmat are different from that of  a work format.
The first need to but not redundant, more compact, but without loss any 
information or setting and compressable.


The shapefile is nothing.
It is not a good interchange format, and not a good working format.
:)

The spatialite is like a DBMS, it is a good work format.
And has some point of interest in the interchange.
The capability internally to speak with shapefile, postgres, dxf, wfs 
and GML/XML are good options.


Also it hs a good elaboration capability.
Is has no the limit of the shapefile and this is good.
It host the styles and this is good.

But has also some bad points.
It is no so universal diffuse.
Only qgis know it, and the qgis seem to have an own spatialite dialect 
not fully compatible with the original spatialite.
The styles on spatialite are supported from qgis but they are 
incompatible respect at original spatialite version and this is a bad point.

The metadata tables of spatialite are ignored from qgis.
So the spatialite seem to be a chatedral in the desert.

The spatialite will never to be a real interchange format if the only 
software to use it is qgis and the same qgis is not real compatible with 
the original spatialite spec.


So the spatialite actually is a really good and strong work format .

Instead surely geopackage are no one strong point in elaboration so I'm 
not surprise that it is slow.

It don't born to do this work.
:)

A.

Il 17/05/2014 11:24, Paolo Cavallini ha scritto:

Il 17/05/2014 11:03, Andrea Peri ha scritto:

Do you know the requirements of gpkg ?

They should be really minimal.
This could help understanding speed issues:
http://openjump.blogspot.fi/2014/02/openjump-plus-reads-ogc-geopackages.html
All the best.


___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Martin Dobias]

Hi

On Sat, May 17, 2014 at 2:32 PM, Jeremy Palmer  wrote:
> I've just been using the new implementation of Geopackage in QGIS :) However 
> each map render or feature info is very slow for large database files - even 
> with the new GDAL/OGR Geopackage spatial index support. Each time QGIS 
> renders a new map view or executes a feature info it re-opens the Geopackage 
> file in QgsOgrFeatureIterator via OGROpen, and the Geopackage driver open 
> method runs PRAGMA integrity_check which is very slow (4 secs with my 2GB 
> test file). This integrity check is actually a Geopackage requirement.
>
> How can this be fixed? At the in QGIS or GDAL/OGR level?

Actually I am wondering how different is the format of GeoPackage from
the format of SpatiaLite... we could use our native SpatiaLite
provider also for GeoPackage files if they are close enough. Any
ideas?

Regards
Martin
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Stefan Keller]

Ciao A.

You wrote:
> Instead surely geopackage are no one strong point in elaboration so I'm
not surprise that it is slow.

The bottleneck has been identified and Even resolved it.
As Jeremy reported it was the PRAGMA integrity_check which checked the
whole file(!) when opening.
Now it remains to be tested how much faster opening of the GeoPackage is.

-S.




2014-05-17 13:44 GMT+02:00 aperi2007 :

>
> I have really difficulty to write of a so complex question in english.
> :(
> However try to explain.
> The shapefile is absolutely a bad solution for interchange usage.
> We experiment every day troubles. Sometimes the troubles was discovered
> after many time.
> The shapefile born as a simply format for a simply GIS product.
> SO the major request was "t be simpler possible".
> And infact the shapefile is really simply.
> The shp specs use the DBF format, but forgot that of DBF format there are
> many ,sometimes also really distinct to each other.
> (read the old clipper dbf spec and the old ashton-tate dbf spec to
> understand what this mean).
>
> So two shapefile could be not compatible at all.
> Also the shapefile don't sauy nothing of character-set usage.
> This is a bad question because often we must try more than one
> Character-set and hope to have no mixing of CS to retrieve correctly the
> texts.
> Also the shapefile don't has a NULL definition and also it allow the
> strange mix of MULTIPART and SIMPLY geometries.
>
> All this and so on are foe for a real interchangeable format because
> usually this mean that I export from a remote system , from a distinct GIS
> solution to every other solution.
>
> We every day use the gdal to transform (thx to gdal forever), but gdal
> cannot do miracles.
>
> A format born for interchange is surely the GML.
> It allow to define a different SRS for every geometry, also allow to host
> every typeof geometry ,
> but the GML failed in the market because the geometric definition was not
> well explained and so every GIS software
> choose to do its own interpretation of the geometrical tag in the GML.
>
> Obviously the postgis , oralce and so on are not really nterchange formats
> because they are only dump of the DBMS information.
> Another good candidate for interchange was the spatialite.
> The lastversion 4.2 is really good.
> It host metadata, host styles, host history and convert al to the UTF-8
> internal.
> It has some limitation respect to the GML.
> The GML allow to have a distinct SRS for every geometry instead the
> spatialite allow a SRS for every table (dataset).
>
> But this limitation will not be a real limit today, perhaps in the next
> 2-3 year it will became a limit.
>
> But the spatialite has another limit for a good interchange format.
> It is also an elaboration unit.
> This is an advantage for me , but not for a real interchange format that
> need only host data.
> And give the elaboration phase to the GIS.
> This is a philosophy I don't like more, but has its own reasons .
>
> So why the geopackage is not a good work format ?
> The response is because it is a interchange format. The need of a work
> frmat are different from that of  a work format.
> The first need to but not redundant, more compact, but without loss any
> information or setting and compressable.
>
> The shapefile is nothing.
> It is not a good interchange format, and not a good working format.
> :)
>
> The spatialite is like a DBMS, it is a good work format.
> And has some point of interest in the interchange.
> The capability internally to speak with shapefile, postgres, dxf, wfs and
> GML/XML are good options.
>
> Also it hs a good elaboration capability.
> Is has no the limit of the shapefile and this is good.
> It host the styles and this is good.
>
> But has also some bad points.
> It is no so universal diffuse.
> Only qgis know it, and the qgis seem to have an own spatialite dialect not
> fully compatible with the original spatialite.
> The styles on spatialite are supported from qgis but they are incompatible
> respect at original spatialite version and this is a bad point.
> The metadata tables of spatialite are ignored from qgis.
> So the spatialite seem to be a chatedral in the desert.
>
> The spatialite will never to be a real interchange format if the only
> software to use it is qgis and the same qgis is not real compatible with
> the original spatialite spec.
>
> So the spatialite actually is a really good and strong work format .
>
> Instead surely geopackage are no one strong point in elaboration so I'm
> not surprise that it is slow.
> It don't born to do this work.
> :)
>
> A.
>
> Il 17/05/2014 11:24, Paolo Cavallini ha scritto:
>
>  Il 17/05/2014 11:03, Andrea Peri ha scritto:
>>
>>> Do you know the requirements of gpkg ?
>>>
>> They should be really minimal.
>> This could help understanding speed issues:
>> http://openjump.blogspot.fi/2014/02/openjump-plus-reads-
>> ogc-geopackages.html
>> All the best.
>>
>
> 

Re: [Qgis-developer] Geospackage Slow in QGIS

[Stefan Keller]

Hi Martin

It has a different binary encoding compared to Spatialite.
And in addition it integrates raster tiles (MBTiles) and metadata.

-S.


2014-05-17 14:10 GMT+02:00 Martin Dobias :

> Hi
>
> On Sat, May 17, 2014 at 2:32 PM, Jeremy Palmer 
> wrote:
> > I've just been using the new implementation of Geopackage in QGIS :)
> However each map render or feature info is very slow for large database
> files - even with the new GDAL/OGR Geopackage spatial index support. Each
> time QGIS renders a new map view or executes a feature info it re-opens the
> Geopackage file in QgsOgrFeatureIterator via OGROpen, and the Geopackage
> driver open method runs PRAGMA integrity_check which is very slow (4 secs
> with my 2GB test file). This integrity check is actually a Geopackage
> requirement.
> >
> > How can this be fixed? At the in QGIS or GDAL/OGR level?
>
> Actually I am wondering how different is the format of GeoPackage from
> the format of SpatiaLite... we could use our native SpatiaLite
> provider also for GeoPackage files if they are close enough. Any
> ideas?
>
> Regards
> Martin
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[G. Allegri]

>
> The spatialite will never to be a real interchange format if the only
> software to use it is qgis and the same qgis is not real compatible with
> the original spatialite spec.
>
> So the spatialite actually is a really good and strong work format .
>
> Instead surely geopackage are no one strong point in elaboration so I'm
> not surprise that it is slow.
>
>
As you will know the GeoPackage SQLite Extension is pending for future
versions of the specification. In case it would make Spatialite/Geopackage
purposesquite similar...

giovanni



>
> Il 17/05/2014 11:24, Paolo Cavallini ha scritto:
>
>  Il 17/05/2014 11:03, Andrea Peri ha scritto:
>>
>>> Do you know the requirements of gpkg ?
>>>
>> They should be really minimal.
>> This could help understanding speed issues:
>> http://openjump.blogspot.fi/2014/02/openjump-plus-reads-
>> ogc-geopackages.html
>> All the best.
>>
>
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>



-- 
Giovanni Allegri
http://about.me/giovanniallegri
Twitter: https://twitter.com/_giohappy_
blog: http://blog.spaziogis.it
GEO+ geomatica in Italia http://bit.ly/GEOplus
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Auto increment field for shapefiles?

[kimaidou]

Hi Paolo

+1 for a QGIS Field edittype called "Serial" and implementing
auto-incrementation for non-database formats :

* in the form, make the field visible but not editable
* for new object, get the next number

QGIS could store the current serial state in the project file and update it
during the commit, and manage multiple inserts and errors on insert

Michael


2014-05-17 11:02 GMT+02:00 Paolo Cavallini :

> Il 17/05/2014 00:38, mmekuria ha scritto:
> > I am not sure what you mean, Paulo. But there is already the Feature Id
> > (Object Id in ArcGIS) in QgsFeature. Do you mean having that a mandatory
> > field that gets populated every time a feature is created within a
> > shapefile?
>
> Almost: I mean let the user define a field as SERIAL
> http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
> also for non-database data types (especially shp).
> It does not seem too difficult, now that we have a record number function
> in the
> expressions.
> All the best.
>
> --
> Paolo Cavallini - www.faunalia.eu
> Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Andrea Peri]

Hi Giovanni.

You have point an interesting question.
The question is:

geopackage and spatialite will be binary compatible (almost in the blob
where there are store the geometry) ?
I guess no because I don't realize how a geopackage spec could support some
different binary formats.

If this is true (i cannot be sure now, hope wrong obviously)
this mean that will be another different format in a sqlite.

The first is spatialite, the second is the porting of qgis to spatialite,
the third is this next coming version of geopackage on spatialite.

A.



2014-05-17 14:45 GMT+02:00 G. Allegri :

> The spatialite will never to be a real interchange format if the only
>> software to use it is qgis and the same qgis is not real compatible with
>> the original spatialite spec.
>>
>> So the spatialite actually is a really good and strong work format .
>>
>> Instead surely geopackage are no one strong point in elaboration so I'm
>> not surprise that it is slow.
>>
>>
> As you will know the GeoPackage SQLite Extension is pending for future
> versions of the specification. In case it would make Spatialite/Geopackage
> purposesquite similar...
>
> giovanni
>
>
>
>>
>> Il 17/05/2014 11:24, Paolo Cavallini ha scritto:
>>
>>  Il 17/05/2014 11:03, Andrea Peri ha scritto:
>>>
 Do you know the requirements of gpkg ?

>>> They should be really minimal.
>>> This could help understanding speed issues:
>>> http://openjump.blogspot.fi/2014/02/openjump-plus-reads-
>>> ogc-geopackages.html
>>> All the best.
>>>
>>
>> ___
>> Qgis-developer mailing list
>> Qgis-developer@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
>
>
>
> --
> Giovanni Allegri
> http://about.me/giovanniallegri
> Twitter: https://twitter.com/_giohappy_
> blog: http://blog.spaziogis.it
> GEO+ geomatica in Italia http://bit.ly/GEOplus
>



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Auto increment field for shapefiles?

[Andrea Peri]

The dbf spec has a type serial ?

I don't remenber , but not sure of this.

If the dbf has not a serial type.

This enhance will mena to produce a new kind of shapfile
readable only from QGIS.

I guess should be avoided to produce incompatible new formats.

Andrea.


2014-05-17 11:02 GMT+02:00 Paolo Cavallini :

> Il 17/05/2014 00:38, mmekuria ha scritto:
> > I am not sure what you mean, Paulo. But there is already the Feature Id
> > (Object Id in ArcGIS) in QgsFeature. Do you mean having that a mandatory
> > field that gets populated every time a feature is created within a
> > shapefile?
>
> Almost: I mean let the user define a field as SERIAL
> http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
> also for non-database data types (especially shp).
> It does not seem too difficult, now that we have a record number function
> in the
> expressions.
> All the best.
>
> --
> Paolo Cavallini - www.faunalia.eu
> Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[G. Allegri]

2014-05-17 16:04 GMT+02:00 Andrea Peri :

> Hi Giovanni.
>
> You have point an interesting question.
> The question is:
>
> geopackage and spatialite will be binary compatible (almost in the blob
> where there are store the geometry) ?
> I guess no because I don't realize how a geopackage spec could support
> some different binary formats.
>

No, the Geopackage BLOB is different from Spatialite. Have a look to the
paragraph 2.1.3.1.1 in the Geopackage specification.


>
> If this is true (i cannot be sure now, hope wrong obviously)
> this mean that will be another different format in a sqlite.
>

The Luciad libgpkg has a compatibility mode for Spaltialite 1.x,2.x and
3.x, which *should* guarantee read/write correctness for Spatialite layouts.
Anyway, GDAL has its own implementation, and the gpkg driver is strictly
directed to the Geopackage layout.


>
> The first is spatialite, the second is the porting of qgis to spatialite,
> the third is this next coming version of geopackage on spatialite.
>

Yes, it will be a mess! :)
When you will receive e sqlite DB what of the three forms will it be? Trial
and error, if you don't know it ;)

giovanni


>
> A.
>
>
>
> 2014-05-17 14:45 GMT+02:00 G. Allegri :
>
>  The spatialite will never to be a real interchange format if the only
>>> software to use it is qgis and the same qgis is not real compatible with
>>> the original spatialite spec.
>>>
>>> So the spatialite actually is a really good and strong work format .
>>>
>>> Instead surely geopackage are no one strong point in elaboration so I'm
>>> not surprise that it is slow.
>>>
>>>
>> As you will know the GeoPackage SQLite Extension is pending for future
>> versions of the specification. In case it would make Spatialite/Geopackage
>> purposesquite similar...
>>
>> giovanni
>>
>>
>>
>>>
>>> Il 17/05/2014 11:24, Paolo Cavallini ha scritto:
>>>
>>>  Il 17/05/2014 11:03, Andrea Peri ha scritto:

> Do you know the requirements of gpkg ?
>
 They should be really minimal.
 This could help understanding speed issues:
 http://openjump.blogspot.fi/2014/02/openjump-plus-reads-
 ogc-geopackages.html
 All the best.

>>>
>>> ___
>>> Qgis-developer mailing list
>>> Qgis-developer@lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>>
>>
>>
>>
>> --
>> Giovanni Allegri
>> http://about.me/giovanniallegri
>> Twitter: https://twitter.com/_giohappy_
>> blog: http://blog.spaziogis.it
>> GEO+ geomatica in Italia http://bit.ly/GEOplus
>>
>
>
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -
>



-- 
Giovanni Allegri
http://about.me/giovanniallegri
Twitter: https://twitter.com/_giohappy_
blog: http://blog.spaziogis.it
GEO+ geomatica in Italia http://bit.ly/GEOplus
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Auto increment field for shapefiles?

[Paolo Cavallini]

Il 17/05/2014 14:56, kimaidou ha scritto:
> Hi Paolo
> 
> +1 for a QGIS Field edittype called "Serial" and implementing 
> auto-incrementation for
> non-database formats :
> 
> * in the form, make the field visible but not editable
> * for new object, get the next number
> 
> QGIS could store the current serial state in the project file and update it 
> during
> the commit, and manage multiple inserts and errors on insert

Thanks for the notes. Issue added:
http://hub.qgis.org/issues/10295
Anyone interested in implementing or sponsoring it?
All the best.

-- 
Paolo Cavallini - www.faunalia.eu
Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Auto increment field for shapefiles?

[Alex Mandel]

On 05/17/2014 07:07 AM, Andrea Peri wrote:
> The dbf spec has a type serial ?
> 
> I don't remenber , but not sure of this.
> 
It does but not in dbf IV which is what shapefiles use (from a little
online poking) only in later versions like dbf 7.

> If the dbf has not a serial type.
> 
> This enhance will mena to produce a new kind of shapfile
> readable only from QGIS.
> 
> I guess should be avoided to produce incompatible new formats.
> 
> Andrea.
> 
I think what's being proposed is that the QGIS project is the only thing
that tracks that's serial, in the dbf field definition would stay
Integer. Of course editing the file in another program can easily break it.

I'd rather see effort into making other formats 1st class to help move
people off of shapefiles. Note ArcGIS does now support Spatialite
http://blog.geomusings.com/2013/08/07/spatialite-and-arcgis-10-dot-2/
And we expect Geopackage adoption to follow in the next few years.

Course if this really bugs someone, by all means implement something
just don't break the compatibility of the files.

-Alex

> 
> 2014-05-17 11:02 GMT+02:00 Paolo Cavallini :
> 
>> Il 17/05/2014 00:38, mmekuria ha scritto:
>>> I am not sure what you mean, Paulo. But there is already the Feature Id
>>> (Object Id in ArcGIS) in QgsFeature. Do you mean having that a mandatory
>>> field that gets populated every time a feature is created within a
>>> shapefile?
>>
>> Almost: I mean let the user define a field as SERIAL
>> http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
>> also for non-database data types (especially shp).
>> It does not seem too difficult, now that we have a record number function
>> in the
>> expressions.
>> All the best.
>>
>> --
>> Paolo Cavallini - www.faunalia.eu
>> Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html
>> ___
>> Qgis-developer mailing list
>> Qgis-developer@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
> 
> 
> 
> 
> 
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
> 

___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Jeremy Palmer]

Hi Even,

Thanks for the change! I've now tested the GDAL trunk with QGIS and it's 
lightning fast with large files - problem solved.

Thanks once again,
Jeremy

From: Even Rouault [even.roua...@mines-paris.org]
Sent: Saturday, 17 May 2014 10:48 p.m.
To: qgis-developer@lists.osgeo.org
Cc: Stefan Keller; Jeremy Palmer
Subject: Re: [Qgis-developer] Geospackage Slow in QGIS

Le samedi 17 mai 2014 10:46:39, Stefan Keller a écrit :
> So it's this code fragment?
> 419 /* Requirement 6: The SQLite PRAGMA integrity_check SQL command
> SHALL return â??okâ? */
> 420/* http://opengis.github.io/geopackage/#_file_integrity */
> 421if( strncmp(pszFilename, "/vsicurl/", strlen("/vsicurl/")) != 0 &&
> 422OGRERR_NONE != PragmaCheck("integrity_check", "ok", 1) )
>
> Looks strange to me why a single file check woold be the bottlenet since
> this should be a single call.
>
> I would have expected that some repeated calls (like reading
> objects/records and checking SRID or data types) would be the bottleneck
> since SQlite is not performant when used with PRAGMA checks. This would be
> a check one could disable with a parameter.

I've just noticed that thread. Actually I found that integrity_check was slow
when operating on remote databases with /vsicurl/ and didn't verify how slow
it could be with local big files, but its documentation at
http://www.sqlite.org/pragma.html#pragma_integrity_check suggests that it
might be bery slow indeed.
Reading the GeoPackage spec, nothing mandates to do the integrity_check at
open time. It only mandates that if you run "pragma integrity_check" it should
retrun OK. So I've disabled the check by default.

Even


--
Geospatial professional services
http://even.rouault.free.fr/services.html

This message contains information, which may be in confidence and may be 
subject to legal privilege. If you are not the intended recipient, you must not 
peruse, use, disseminate, distribute or copy this message. If you have received 
this message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. LINZ accepts no 
responsibility for changes to this email, or for any attachments, after its 
transmission from LINZ. Thank You.
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Jeremy Palmer]

I'm wondered why QGIS needs a native SpatiaLite provider and connection 
dialogue any more. Unlike other database providers such as PostGIS, Oracle, 
MSSQL the SpatiaLite provider doesn't seem to have anything special that 
requires a QGIS provider. From a users perspective it just adds to the 
confusion and complexity for adding data. Why not just use GDAL/OGR for both 
Geopackage and Spatialite?

Cheers
Jeremy

From: Stefan Keller [sfkel...@gmail.com]
Sent: Sunday, 18 May 2014 12:24 a.m.
To: Martin Dobias
Cc: Jeremy Palmer; qgis-developer@lists.osgeo.org
Subject: Re: [Qgis-developer] Geospackage Slow in QGIS

Hi Martin

It has a different binary encoding compared to Spatialite.
And in addition it integrates raster tiles (MBTiles) and metadata.

-S.


2014-05-17 14:10 GMT+02:00 Martin Dobias 
mailto:wonder...@gmail.com>>:
Hi

On Sat, May 17, 2014 at 2:32 PM, Jeremy Palmer 
mailto:jpal...@linz.govt.nz>> wrote:
> I've just been using the new implementation of Geopackage in QGIS :) However 
> each map render or feature info is very slow for large database files - even 
> with the new GDAL/OGR Geopackage spatial index support. Each time QGIS 
> renders a new map view or executes a feature info it re-opens the Geopackage 
> file in QgsOgrFeatureIterator via OGROpen, and the Geopackage driver open 
> method runs PRAGMA integrity_check which is very slow (4 secs with my 2GB 
> test file). This integrity check is actually a Geopackage requirement.
>
> How can this be fixed? At the in QGIS or GDAL/OGR level?

Actually I am wondering how different is the format of GeoPackage from
the format of SpatiaLite... we could use our native SpatiaLite
provider also for GeoPackage files if they are close enough. Any
ideas?

Regards
Martin
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

This message contains information, which may be in confidence and may be 
subject to legal privilege. If you are not the intended recipient, you must not 
peruse, use, disseminate, distribute or copy this message. If you have received 
this message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. LINZ accepts no 
responsibility for changes to this email, or for any attachments, after its 
transmission from LINZ. Thank You.
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [Qgis-developer] Geospackage Slow in QGIS

[Andrea Peri]

+1
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer