creating an ESRI compatible Geopackage

[David Anderson]

I what I thought was an great idea this morning.

ESRI ArcGIS as of 10.2.2 supports the geopackage format.  Spatialite supports the geopackage format with some tools for creating it https://groups.google.com/forum/#!searchin/spatialite-users/geopackage/spatialite-users/_YyyQskTP9Q/S4RRy2IhjWQJ

Thus it seems a rather straight forward task to create a geopackage, pop in some data, then use ArcGIS to do the display stuff.

Alas it was not as easy as it appeared to be.

Doing this block of commands ( in Python using a SQLite connection)

db_conn.execute('create table my_points (pnt_id integer primary key,name text)')
db_conn.execute("select gpkgAddGeometryColumn('my_points','the_geom','POINT',0,0,26912)")
db_conn.execute("select gpkgAddGeometryTriggers('my_points','the_geom')")
db_conn.execute("select gpkgAddSpatialIndex('my_points','the_geom')")
db_conn.execute("insert into my_points(name,Shape) values ('use gpkgMakePoint',gpkgMakePoint(419831.339,5161289.04))")

Did not produce a feature class that ArcMap would display.  The table shows up as a table in ArcCatalog.

Doing this chunk of commands

db_conn.execute('drop table my_points')
db_conn.execute("delete from gpkg_extensions where table_name='my_points'")

db_conn.execute("create table my_points (OBJECTID integer primary key,name text,the_geom Point)")
db_conn.execute("select gpkgAddGeometryTriggers('my_points','the_geom')")

db_conn.execute("insert into my_points(name,Shape) values ('use gpkgMakePoint',gpkgMakePoint(419831.339,5161289.04,26912))")

Got Arc to recognize the table as a feature class.  Still it would not display.  This also lost the SRID entry in gpkg_spatial_ref_sys.  I manually added that but still no success.

I've checked the table and data structure against the examples on the GeoPackages site and an ESRI created feature class.  Everything appears to be the same but ESRI tools will not recognize the geometry.  One of the reasons for the table drop and add is that ESRI appears to want the geometry field with a Point data type in the create table statement.  Spatialite creates the geometry field as a BLOB type.

Has anyone tried something similar?  If so what worked for you.

David

[Even Rouault]

Le jeudi 23 avril 2015 00:56:29, David Anderson a écrit :
> I what I thought was an great idea this morning.
> ESRI ArcGIS as of 10.2.2 supports the geopackage format. Spatialite
> supports the geopackage format with some tools for creating it
> https://groups.google.com/forum/#!searchin/spatialite-users/geopackage/spat

> ialite-users/_YyyQskTP9Q/S4RRy2IhjWQJ Thus it seems a rather straight

Just regarding that, I see it has been confirmed and clarified very recently in
the geopackage spec (yellow corrigendum):
"Req 30b: The declared SQL type of the geometry column in a vector feature
user data table SHALL be the uppercase geometry type name from Annex E
specified by the geometry_type_name column for that column_name and table_name
in the gpkg_geometry_columns table."

>
> Has anyone tried something similar? If so what worked for you.
>
> David

--
Spatialys - Geospatial professional services
http://www.spatialys.com

[je...@imagemattersllc.com]

David,

FWIW, there are reports that 10.2.2 isn't as compatible as Esri says it is.

https://twitter.com/sfkeller/status/582981544161214464

-Jeff

[David Anderson]

I was able to get the process to work.

Thanks for the hint about proper capitalization.  I am not sure it is absolutely required as the Belgium Geonames example data set uses the value of "Point" it is good to be standards compliant.

I am also not surprised that ESRI is not 100% standards compliant.

The big change was including the SRID in the makepoint call.  Looking at the documentation for the ESRI spatial SQL functions http://resources.arcgis.com/en/help/main/10.2/index.html#/ST_Point/006z0000007q000000/ where the function call for making a point uses the SRID, it makes sense that it needed to be included.

For the curious here is the code that works for me.

Enter code here...#import sqlite3
import apsw
import time

now = time.asctime(time.localtime(time.time()))

# settting up the database connection

db_conn_all.enableloadextension(True)
db_conn_all.loadextension('mod_spatialite')
db_conn = db_conn_all.cursor()

# setting up GeoPackage pragma
db_conn.execute('select gpkgCreateBaseTables()')

# Making a spatial table

db_conn.execute('create table my_points (pnt_id integer primary key,name text)')
db_conn.execute("select gpkgAddGeometryColumn('my_points','the_geom','POINT',0,0,26912)")
db_conn.execute("select gpkgAddGeometryTriggers('my_points','the_geom')")
db_conn.execute("select gpkgAddSpatialIndex('my_points','the_geom')")

# try inserting into the gpkg_contents table
# the ESRI create feature class puts an entry in this table that the Spatialite function does not

db_conn.execute("insert into gpkg_contents (table_name,data_type,identifier,description,last_change,min_x,min_y,max_x,max_y,srs_id) values ('my_points','features','my_points',NULL,'2015-04-22T14:58:01.000Z',NULL,NULL,NULL,NULL,26912)")

# Change the DDL to use the geometry data type

db_conn.execute('drop table my_points')
db_conn.execute("delete from gpkg_extensions where table_name='my_points'")

db_conn.execute("create table my_points (OBJECTID integer primary key,name text,the_geom POINT)")

db_conn.execute("select gpkgAddGeometryTriggers('my_points','the_geom')")

# add the SRID
db_conn.execute('select gpkgInsertEpsgSRID(26912)')

# add some data
for x_coor in range(419830,419890,10):
    for y_coor in range(5161230,5161290,10):
        db_conn.execute("insert into my_points(name,the_geom) values ('use gpkgMakePoint',gpkgMakePoint(?,?,?))",[x_coor,y_coor,26912])

Much of the horsing around in the script could be avoided if the gpkgAddGeometryColumn  call returned a POINT datatype in the table creation DDL SQL instead of type BLOB.

[Jukka Rahkonen]

Hi,

I tried to follow your road with spatialite-gui 1.8.0 and with command line spatialite CLI v. 4.2.1-rc1 but I was stopped at the third command:

select gpkgCreateBaseTables();
create table my_points (pnt_id integer primary key,name text);
select gpkgAddGeometryColumn('my_points','the_geom','POINT',0,0,4326);

Error: FOREIGN KEY constraint failed

I had already edited the SRID into 4326 from your 26912 because after the first two command the gpkg_spatial_ref_sys table has only three rows for SRIDs -1, 0, and 4326. I wonder what is going wrong.

-Jukka Rahkonen-

[Pepijn Van Eeckhoudt]

See http://www.geopackage.org/spec/#gpkg_geometry_columns_sql. gpkg_geometry_columns.table_name has a foreign key constraint to gpkg_contents.table_name so you need to add your table in gpkg_contents before calling gpkgAddGeometryColumn.

Foreign keys were a point of much debate during the spec definition. I personally think their a PITA to work with since it forces you to do each step in exactly the right order. I ended up doing the following most of the time to make things easier

SAVEPOINT “creating my feature table"

PRAGMA foreign_keys = false;

CREATE TABLE …

SELECT AddGeometryColumn…

INSERT INTO gpkg_contents…

PRAGMA foreign_keys = true;

PRAGMA foreign_key_check(‘gpkg_contents’);

PRAGMA foreign_key_check(‘gpkg_geometry_columns');

RELEASE “creating my feature table"

Whether you run into this or not depends on the default state of foreign key checking which is a SQLite compile time option.

Pepijn

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at http://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[Pepijn Van Eeckhoudt]

> Foreign keys were a point of much debate during the spec definition. I personally think their a PITA to work with since it forces you to do each step in exactly the right order.

Just wanted to add ‘and the order of the steps is counter intuitive’. Nothing against foreign keys in general. :)

Pepijn

[a.fu...@lqt.it]

Hi Jukka,

On Fri, 24 Apr 2015 01:07:35 -0700 (PDT), Jukka Rahkonen wrote:
> I had already edited the SRID into 4326 from your 26912 because after
> the first two command the gpkg_spatial_ref_sys table has only three
> rows for SRIDs -1, 0, and 4326.
>

you can freely add any other useful SRID by invoking
gpkgInsertEpsgSRID();
in your specific case:

SELECT gpkgInsertEpsgSRID(26912);

> select gpkgCreateBaseTables();
> create table my_points (pnt_id integer primary key,name text);
> select
> gpkgAddGeometryColumn('my_points','the_geom','POINT',0,0,4326);
>
> Error: FOREIGN KEY constraint failed
>

> I wonder what is going wrong.
>

the "gpkg_geometry_columns" table declares a FOREIGN KEY constraint
referencing "gpkg_contents"; you are supposed to properly register
any newly created feature table before attempting to create a
geometry column on it. something like:

create table my_points (pnt_id integer primary key,name text);

INSERT INTO gpkg_contents (table_name, data_type, srs_id) VALUES
('my_points', 'features', 4326);

select gpkgAddGeometryColumn('my_points','the_geom','POINT',0,0,4326);

dirty trick: you can simply disable Foreign Key constrains by issuing a
"PRAGMA foreign_keys=0" directive.

this is the default behaviour of the sqlite3 CLI tool (always ignoring
FK constraints unless explicitly requested by the user).
both spatialite CLI and spatialite GUI works the opposite way (always
enforcing FK constraints unless explicitly disabled by the user)

bye Sandro

[Jukka Rahkonen]

Hi,

Inserting data into gpkg_contents makes it work. I suppose that David must have foreign keys turned off. Also the next command
select gpkgAddGeometryTriggers('my_points','the_geom');

is probably unnecessary. At least I get a message that triggers already exists at this state.

When we got into fiddling with PRAGMA, a dirty trick from http://stackoverflow.com/questions/4007014/alter-column-in-sqlite can be used for fixing the data type of geometry column.

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE my_points (pnt_id integer primary key,name text, the_geom POINT)' WHERE NAME = 'my_points';
PRAGMA writable_schema = 0;

After vacuum or disconnecting / connecting db:

select * from sqlite_master where name='my_points';

0    pnt_id    integer  0    NULL    1
1    name      text     0    NULL    0
2    the_geom  POINT    0    NULL    0

[Jukka Rahkonen]

Hi,

Do I read right that you are inserting points with three coordinates into POINT geometry? You should either drop the third coordinate or use POINTZ or POINTM.

-Jukka Rahkonen-

[a.fu...@lqt.it]

Hi Pepijn,

for sure FK constraints can frequently cause severe headaches.
anyway discovering too late that a critical DB (may be containing
many million rows) is damaged beyond any possible repair simply
because it's plagued by broken cross-references could probably
be even worst.
you can't have your cake and eat it ;-)

bye sandro

[a.fu...@lqt.it]

On Fri, 24 Apr 2015 02:16:49 -0700 (PDT), Jukka Rahkonen wrote:
> Hi,
>
> Inserting data into gpkg_contents makes it work.
>

second thought: it could be probably smarter changing the
code implementing gpkgAddGeometryColumn() so to automatically
register the new feature table if required.

I've inspected the code and this is already supported by
gpkgCreateTilesTable(); adding the same feature even on
gpkgAddGeometryColumn() seems to be appropriate.

bye Sandro

[Pepijn Van Eeckhoudt]

In GeoPackage you shouldn’t use pointz, pointm and pointzm as column types. You should set the z and m flags to the correct value when adding the geometry column. In this case z should be 1 or 2 and m should be 0 probably. So

select gpkgAddGeometryColumn('my_points','the_geom','POINT’, 1 ,0,26912)

or

select gpkgAddGeometryColumn('my_points','the_geom','POINT’, 2 ,0,26912)

The Simple Features SQL spec has four parallel geometry type hierarchies (XY, XYZ, ZYM, XYZM). SQL/MM has a single geometry type hierarchy and allows mixing.

Another point of much debate which ended up in favour of the latter. The benefit is that this gives you better support for transcoding from/to GML (and perhaps other formats as well) since GML allows mixing coordinate dimensions in a feature type and within multi geometries as well.  

Pepijn

[Jukka Rahkonen]

Good, we find the truth by performing a few rounds of iteration. I add some references:

http://www.geopackage.org/spec/#geometry_type
and from Table 1. GeoPackage Data Types:
"Geometry Types XY, XYZ, XYM and XYZM geometries use the same data type."
And from Table 6. Geometry Columns Table or View Definition:
"0: z values prohibited; 1: z values mandatory; 2: z values optional"
"0: m values prohibited; 1: m values mandatory; 2: m values optional"

Also were well and correctly described in https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

gpkgAddGeomtryColumn( table_name Sting, geometry_column_name String , geometry_type String , with_z Integer , with_m Integer , srs_id Integer ) : void


Adds a geometry column to the specified table:

• geometry_type is a normal WKT name:
  • "GEOMETRY"
  • "POINT"
  • "LINESTRING"
  • "POLYGON"
  • "MULTIPOINT"
  • "MULTILINESTRING"
  • "MULTIPOLYGON"
  • "GEOMETRYCOLLECTION"
• with_z is a flag (0 for no z values, 1 for mandatory z values, 2 for optional z values)
• with_m is a flag (0 for no m values, 1 for mandatory m values, 2 for optional m values)

-Jukka-

[Jukka Rahkonen]

Hi,

Here you can find my trial with an SQL script http://latuviitta.org/downloads/make_gpkg.txt.
It creates point, linestring, polygon, and general geometry tables and corresponding metadata. It would be nice if people could test with their software if the result is GPKG conformant.

Those who do not bother to run the script with spatialite-CLI or spatialite-gui the ready made GeoPackage is also available http://latuviitta.org/downloads/spatialite_42_test.gpkg.

-Jukka Rahkonen-

[Pepijn Van Eeckhoudt]

All the spatialite specific tables in there are a bit iffy, but most gpkg clients will probably just ignore them. It would be better if they weren’t there though they only work properly if you’re using spatialite.

There’s a couple of errors in the metadata table schemas

build pepijn$ ./shell/gpkg -gpkg ~/Downloads/spatialite_42_test.gpkg 
SQLite version 3.8.8 2015-01-16 12:08:06
libgpkg version 0.9.18
Enter ".help" for usage hints.
GeoPackage> select checkspatialmetadata();
Error: Column gpkg_contents.last_change has incorrect type (expected: DATETIME, actual: TEXT)
Column gpkg_data_column_constraints.description is missing

Column gpkg_geometry_columns.column_name should have 'not null' constraint
Column gpkg_geometry_columns.geometry_type_name should have 'not null' constraint
Column gpkg_geometry_columns.z has incorrect type (expected: TINYINT, actual: INTEGER)

Pepijn

[a.fu...@lqt.it]

On Fri, 24 Apr 2015 14:05:23 +0200, Pepijn Van Eeckhoudt wrote:
> All the spatialite specific tables in there are a bit iffy, but most
> gpkg clients will probably just ignore them. It would be better if
> they weren’t there though they only work properly if you’re using
> spatialite.
>

Creating a crystal-clear GPKG not containing any spatialite
specific table is really simple.

You simply have to avoid using either spatialite CLI or
spatialite GUI because both tools will always create a new
DB-file specifically inteded for SpatiaLite.
Anyway a third option exists, and is straighforward.

A) edit the SQL scriptd prepare by Jukka
add the following statement before the very first line:
SELECT load_extension('mod_spatialite');

B) now you simply have to execute from the shell:
sqlite3 test.gpkg <make_gpkg.sql

this way you'll get an absolutely clean GPKG

bye Sandro

[Pepijn Van Eeckhoudt]

Hi Sandro,

Great, I didn’t know that. The only thing that remains is a couple of small errors in the create gpkg_* tables. Would be best to fix those as well since the OGC test suite will probably also check for those. The attached patch contains the necessary fixes.

Best regards,

Pepijn

[Jukka Rahkonen]

Hi Sandro,

Check also if there is something wrong with GEOMETRYCOLLECTION:

select gpkgAddGeometryColumn('geometrycollection_gpkg','geometry','GEOMETRYCOLLECTION',0,0,4326);

Error: near line 113: gpkgAddGeometryColumn() error: argument 3 [geometry_type]
not a recognised geometry type

-Jukka-

[Even Rouault]

I suppose this should be GEOMCOLLECTION.

See http://trac.osgeo.org/gdal/ticket/5937 and
http://lists.opengeospatial.org/pipermail/geopackage/2014-May/000015.html

>
> -Jukka-

[Jukka Rahkonen]

Right, this is accepted:

select gpkgAddGeometryColumn('geometrycollection_gpkg','geometry','GEOMCOLLECTION',0,0,4326);

Documentation lags behind the actual behaviour:
https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p16gpkg

I've updated the script in http://latuviitta.org/downloads/make_gpkg.txt and now it creates tables for all variants of XY and XYZ geometries. I planned to stop here but obviously I must add also the XYM and XYZM tables later.


-Jukka-

[David Anderson]

Quite the discussion

Just a few points.  The data creation statement uses the form

gpkgMakePoint (x Double precision , y Double precision , srid Integer )

 as ESRI seems to want the SRID in the geometry blob.  I don't know why.  Mysterious are the ways of ESRI.

As my script shows I am running from a Python SQLite connection.  It seems that foreign key constraints are turned off.  The gpkg_contents table is populated somewhere in the first block of call because I recall copying values from the table to use in the gpkg_contents insert statement that is issued in line 21. 

I had to go with the drop/add table option because the SQLite connector wouldn't allow changes to the SQLite_master table.  The PRAGMA workaround is great.  I tried it.  That will save a few steps.

Still even that wouldn't have to happen if the original gpkgAddGeometryColumn call created DDL with the geometry type value instead of BLOB.

[Pepijn Van Eeckhoudt]

GeoPackage uses a prefixed version of WKB (see http://www.geopackage.org/spec/#gpb_format for details). The prefix bit contains the srid and optionally the envelope of the geometry. This was done for ease of implementation and efficiency. Without the SRID there’s no simple way an implementation can for instance check that the two parameters for ST_Intersects are defined in the same SRID.

Caching the envelope allows functions like ST_MinX/MaxX to provide an answer very quickly. Without this cache you have to scan over every point of a geometry every time these functions are called, which in turns make fast manipulation of the spatial index much more difficult. Additionally the cached envelope provides a way to make spatial predicates more efficient by testing the envelopes before testing the actual geometry.

Best regards,

Pepijn