[gdal-dev] Testing of OGR generated GeoPackage datasets with other implementations

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] Testing of OGR generated GeoPackage datasets with other implementations

Even Rouault-2

Hi,

 

I've committed recently a small improvement regarding vector and attribute tables in the GeoPackage driver, so as to get the number of features quickly. This can be interesting when dealing with huge tables. Without that, getting the number of features require a full table scan. So I've added a "ogr_feature_count" column to the gpkg_contents table, and triggers to update it.

To my understanding, such an extension is allowed by the specification.

While running through the OGR GPKG driver those triggers are temporarily disabled during feature insertion and deletion so as to make them faster, and restored back at dataset closing.

The driver of course behaves well if the column is missing or if its content is NULL (will revert to full table scan to get feature count)

Earlier versions of the OGR GPKG driver are also happy with the new layout of the DB.

As a provision, I've added a ADD_OGR_FEATURE_COUNT_COLUMN=NO dataset creation option to disable this, but it is my hope this will be not needed.

 

So I'd be interested if people could test

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france.gpkg

(or any other gpkg you may generate with today's ogr2ogr) with other software package that don't use OGR underneath (ArcGIS for example).

 

Suggested tests:

- check that the DB opens well and the layer is listed

- check that the layer opens well (you should see France and its overseas territories)

- check that you can add or delete features to the layer. Check afterwards that the ogr_feature_count is well updated (it should, thanks to the triggers):

ogrinfo ne_10m_admin_0_countries_france.gpkg \

-sql "select * from gpkg_contents"

 

- check that you can add a new layer to the DB (ie insertion of new content in gpkg_contents works well, despite the new column added). The value of the ogr_feature_count column should be NULL.

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: [gdal-dev] Testing of OGR generated GeoPackage datasets with other implementations

jratike80
Even Rouault-2 wrote
So I'd be interested if people could test
http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france.gpkg
(or any other gpkg you may generate with today's ogr2ogr) with other software package that
don't use OGR underneath (ArcGIS for example).
Hi,

MapInfo Pro 15.2 does not open your geopackage. It does open the geopackage which I cleaned with a bit older GDAL build as

ogr2ogr -f gpkg clean.gpkg ne_10m_admin_0_countries_france.gpkg

MapInfo did not give any error message, it just does not find any layers and quits. I could not find anything like error log from MapInfo directory. I will send a message to MapInfo support as well with a link to your geopackage.

-Jukka Rahkonen-

Reply | Threaded
Open this post in threaded view
|

Re: Testing of OGR generated GeoPackage datasets with other implementations

Even Rouault-2

On jeudi 2 mars 2017 06:36:06 CET jratike80 wrote:

> Even Rouault-2 wrote

>

> > So I'd be interested if people could test

> > http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france.gpk

> > g

> > (or any other gpkg you may generate with today's ogr2ogr) with other

> > software package that

> > don't use OGR underneath (ArcGIS for example).

>

> Hi,

>

> MapInfo Pro 15.2 does not open your geopackage. It does open the geopackage

> which I cleaned with a bit older GDAL build as

>

> ogr2ogr -f gpkg clean.gpkg ne_10m_admin_0_countries_france.gpkg

>

> MapInfo did not give any error message, it just does not find any layers and

> quits. I could not find anything like error log from MapInfo directory. I

> will send a message to MapInfo support as well with a link to your

> geopackage.

 

Thanks for your feedback

 

Interesting, would you mind testing

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_no_ogr_feature_count.gpkg

 

which is the same with today's trunk but with -dsco ADD_OGR_FEATURE_COUNT_COLUMN=NO

 

If that works (and it should probably given your above testing), that means I should revise my design as

we want to be compatible with existing implementations.

 

I've though of an alternative that would use an auxiliary table instead of extending gpkg_contents, like a

gpkg_ogr_contents table. I've simulated it manually in

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alternative.gpkg

 

Generated with :

cp ne_10m_admin_0_countries_france_no_ogr_feature_count.gpkg ne_10m_admin_0_countries_france_alternative.gpkg

ogrinfo ne_10m_admin_0_countries_france_alternative.gpkg -sql "CREATE TABLE gpkg_ogr_contents (table_name TEXT NOT NULL, feature_count INTEGER DEFAULT NULL)"

ogrinfo ne_10m_admin_0_countries_france_alternative.gpkg -sql "INSERT INTO gpkg_ogr_contents VALUES ('ne_10m_admin_0_countries', 1)"

 

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Testing of OGR generated GeoPackage datasets with other implementations

jratike80
In reply to this post by jratike80

Hi

 

Right, ne_10m_admin_0_countries_france_no_ogr_feature_count.gpkg opens with MapInfo 15.2.

 

-Jukka-

 

Lähettäjä: Even Rouault [mailto:[hidden email]]
Lähetetty: 2. maaliskuuta 2017 15:49
Vastaanottaja: [hidden email]
Kopio: Rahkonen Jukka (MML) <[hidden email]>
Aihe: Re: [gdal-dev] Testing of OGR generated GeoPackage datasets with other implementations

 

On jeudi 2 mars 2017 06:36:06 CET jratike80 wrote:

> Even Rouault-2 wrote

>

> > So I'd be interested if people could test

> > http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france.gpk

> > g

> > (or any other gpkg you may generate with today's ogr2ogr) with other

> > software package that

> > don't use OGR underneath (ArcGIS for example).

>

> Hi,

>

> MapInfo Pro 15.2 does not open your geopackage. It does open the geopackage

> which I cleaned with a bit older GDAL build as

>

> ogr2ogr -f gpkg clean.gpkg ne_10m_admin_0_countries_france.gpkg

>

> MapInfo did not give any error message, it just does not find any layers and

> quits. I could not find anything like error log from MapInfo directory. I

> will send a message to MapInfo support as well with a link to your

> geopackage.

 

Thanks for your feedback

 

Interesting, would you mind testing

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_no_ogr_feature_count.gpkg

 

which is the same with today's trunk but with -dsco ADD_OGR_FEATURE_COUNT_COLUMN=NO

 

If that works (and it should probably given your above testing), that means I should revise my design as

we want to be compatible with existing implementations.

 

I've though of an alternative that would use an auxiliary table instead of extending gpkg_contents, like a

gpkg_ogr_contents table. I've simulated it manually in

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alternative.gpkg

 

Generated with :

cp ne_10m_admin_0_countries_france_no_ogr_feature_count.gpkg ne_10m_admin_0_countries_france_alternative.gpkg

ogrinfo ne_10m_admin_0_countries_france_alternative.gpkg -sql "CREATE TABLE gpkg_ogr_contents (table_name TEXT NOT NULL, feature_count INTEGER DEFAULT NULL)"

ogrinfo ne_10m_admin_0_countries_france_alternative.gpkg -sql "INSERT INTO gpkg_ogr_contents VALUES ('ne_10m_admin_0_countries', 1)"

 

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Testing of OGR generated GeoPackage datasets with other implementations

Even Rouault-2

On jeudi 2 mars 2017 13:55:19 CET Rahkonen Jukka (MML) wrote:

> http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alter

> native.gpkg

 

And what about

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alternative.gpkg

if you don't mind ?

 

Note: I've just refreshed it to add triggers since my inital upload.

 

ogrinfo ne_10m_admin_0_countries_france_alternative.gpkg -sql "CREATE TRIGGER trigger_insert_ogr_feature_count_ne_10m_admin_0_countries AFTER INSERT ON ne_10m_admin_0_countries BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE table_name = 'ne_10m_admin_0_countries'; END"

ogrinfo ne_10m_admin_0_countries_france_alternative.gpkg -sql "CREATE TRIGGER trigger_delete_ogr_feature_count_ne_10m_admin_0_countries AFTER DELETE ON ne_10m_admin_0_countries BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE table_name = 'ne_10m_admin_0_countries'; END"

 

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Testing of OGR generated GeoPackage datasets with other implementations

jratike80
It opens as well.

BTW. the layer is described as POLYGON in gpkg_geometry_columns but the one feature in the main table is MULTIPOLYGON.

-Jukka-
Reply | Threaded
Open this post in threaded view
|

Re: Testing of OGR generated GeoPackage datasets with other implementations

Even Rouault-2

On jeudi 2 mars 2017 07:25:10 CET jratike80 wrote:

> Even Rouault-2 wrote

>

> > And what about

> > http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alt

> > ernative.gpkg if you don't mind ?

>

> It opens as well.

 

OK. I've changed the implementation to reflect the above prototyping and regenerated from it

http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alternative2.gpkg

 

>

> BTW. the layer is described as POLYGON in gpkg_geometry_columns but the one

> feature in the main table is MULTIPOLYGON.

 

Yes, that's because I converted from a shapefile which has the loose polygon/multipolygon typing.

The above dataset has been regenerated with -nlt MULTIPOLYGON

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Testing of OGR generated GeoPackage datasets with other implementations

jratike80
Even Rouault-2 wrote
OK. I've changed the implementation to reflect the above prototyping and regenerated from it
http://even.rouault.free.fr/geopackage/ne_10m_admin_0_countries_france_alternative2.gpkg
MapInfo is fine with that one, too.

-Jukka-