GEOPACKAGE_QUERY_R10 geopaparazzi specific - geopaparazzi/Spatialite-Tasks-with-Sql-Scripts GitHub Wiki

This is a collection of geopaparazzi specific sql-querys used to determine

if this database has valid geopackage based on Revision 10

Note:

there is only a minimal support of GeoPackage files
- due to the lack of extensive samples - or - otherwise the usage of this format
this has been tested with the only GeoPackage files R10 that I have seen
- 20140101.world_Haiti.gpkg [the only sample with tiles and geometries]
- haiti-vectors-split.gpkg [only geometries]
- geonames_belgium.gpkg [only geometries]
- simple_sewer_features.gpkg [only geometries]
gdal-command to import a shape to a GeoPackage R10
- April 2014 GDAL/OGR 1.11.0 release

ogr2ogr -s_srs EPSG:3068 -t_srs EPSG:3068 -f GPKG
 Berlin_Postleitzahlen.gpkg
 postleitzahlen_shape/20121010.Postleitzahlen/Plz201208.shp

--> 'List of Sql-Commands'

--> 'List of Themes'

--> 'List of Database-Designing'

--> 'List of Geopaparazzi Specific Themes'

--> 'List of Geometry Layers-Querys'

--> 'List of RasterLite2 Layers-Querys'

<-- 'Index Page for GeoPackage'

There are 3 categories:

return only valid results
- if invalid an empty result will be returned
return only invalid results
- if valid an empty result will be returned
return minimal results
- if valid or invalid the same results will be returned

for GeoPackage R10 with a valid geopackage_contents table

SELECT DISTINCT
 table_name||';'||
 CASE
  WHEN data_type = 'features' THEN
  (
   SELECT column_name FROM gpkg_geometry_columns
   WHERE table_name = ''||table_name||''
  )
  WHEN data_type = 'tiles' THEN 'tile_data'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'GeoPackage_features'
  WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'
  END||';'||
 REPLACE(identifier,';','-')||';'||
 REPLACE(description,';','-')||';' AS vector_key,
 CASE
  WHEN data_type = 'features' THEN
  ( -- and now the horror begins ...
   CASE
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'GEOMETRY' THEN '0'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'POINT' THEN '1'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'LINESTRING' THEN '2'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'POLYGON' THEN '3'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'MULTIPOINT' THEN '4'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'MULTILINESTRING' THEN '5'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'MULTIPOLYGON' THEN '6'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'GEOMETRYCOLLECTION' THEN '7'
   END
   -- ... to be continued ...
  )
  WHEN data_type = 'tiles' THEN
  (
   SELECT min(zoom_level) FROM gpkg_tile_matrix
   WHERE table_name = ''||table_name||''
  )
  END||';'||
 CASE
  WHEN data_type = 'features' THEN
  ( -- ... and now for something completely different ...
   CASE
    WHEN
    (
     SELECT z||','||m FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = '0,0' THEN '2'
    WHEN
    (
     SELECT z||','||m FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = '1,0' THEN '3'
    WHEN
    (
     SELECT z||','||m FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = '1,1' THEN '4'
   END
   -- ... ich habe fertig.
  )
  WHEN data_type = 'tiles' THEN
  (
   SELECT max(zoom_level) FROM gpkg_tile_matrix
   WHERE table_name = ''||table_name||''
  )
  END||';'||
 CASE
  WHEN srs_id = '1' THEN '4326'
  WHEN srs_id = '2' THEN '3857'
  ELSE srs_id
  END||';'||
 '0'||';' AS vector_data,
 '-1'||';'||
 min_x||';'||
 min_y||';'||
 max_x||';'||
 max_y||';'||
 last_change AS vector_extent
FROM
 gpkg_contents
WHERE
 (last_change IS NOT NULL) AND
 (min_x IS NOT NULL) AND
 (min_y IS NOT NULL) AND
 (max_x IS NOT NULL) AND
 (max_y IS NOT NULL)
ORDER BY
 table_name ASC,
 identifier ASC;

Note:

all queries shown here return this format
each field uses ; as a delimiter
this is the same format as used for --> 'Geometry Layers-Querys'
- the parsing logic will be the same, even if many of the fields are only informational

This will return 3 fields:

vector_key
- fromosm_tiles;tile_data;GeoPackage_tiles;OSM Tiles;© OpenStreetMap contributors, See http://www.openstreetmap.org/copyright;
- geonames;geometry;GeoPackage_features;Geonames;Data from http://www.geonames.org/, under Creative Commons Attribution 3.0 License;
  - table_name
  - geometry_field
    - for features
      - the name of the geometry-field returned from
    - for tiles
      - always tile_data
  - data_type
    - features will be replaced with GeoPackage_features
    - tiles will be replaced with GeoPackage_tiles
  - identifier :
    - any ; will be replaced with -
  - description :
    - any ; will be replaced with -
vector_data
- 0;10;3857;0;
- 1;2;4326;0;
  - geometry_type:
    - for features`
      - the geometry_type
    - for tiles
      - the min Zoom-level
  - coordinate dimension:
    - for features
      - value as use with spatialite
    - for tiles
      - the max Zoom-level
  - srid : the set srid
    - if 1 was used: will set as 4326
    - if 2 was used: will set as 3857
    - otherwise given srid
  - SpatialIndex
    - always 0
vector_extent
- -1;-20037508.3428;-20037471.2051;20037508.3428;20037471.2051;2013-12-25T06:18:35.000Z
- -1;-75.5;18.0;-71.06667;20.08333;2013-12-24T16:32:14.000000Z
  - -1 : row_count [cannot be sub-queried]
  - bounds : separated with a ,
    - x_min
    - y_min
    - x_max
    - y_max
  - date-time stamp : not used by geopaparazzi

This query will attempt to analyze what error has occurred
- GEOPACKAGE_QUERY_EXTENT_INVALID_R10
  - this query will only return invalid records

-- This will return the same 3 fields above:

only those fields have no valid value
- will return the field-name
  - in this way we can attempt why it is invalid
  - thus need not be loaded into geopaparazzi

SELECT DISTINCT
 table_name||';'||
 CASE
  WHEN data_type = 'features' THEN
  (
   SELECT column_name FROM gpkg_geometry_columns
   WHERE table_name = ''||table_name||''
  )
  WHEN data_type = 'tiles' THEN 'tile_data'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'GeoPackage_features'
  WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'
  END||';'||
 REPLACE(identifier,';','-')||';'||
 REPLACE(description,';','-')||';' AS vector_key,
 CASE
  WHEN data_type = 'features' THEN
  ( -- and now the horror begins ...
   CASE
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'GEOMETRY' THEN '0'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'POINT' THEN '1'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'LINESTRING' THEN '2'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'POLYGON' THEN '3'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'MULTIPOINT' THEN '4'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'MULTILINESTRING' THEN '5'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'MULTIPOLYGON' THEN '6'
    WHEN
    (
     SELECT geometry_type_name FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = 'GEOMETRYCOLLECTION' THEN '7'
   END
   -- ... to be continued ...
  )
  WHEN data_type = 'tiles' THEN
  (
   SELECT min(zoom_level) FROM gpkg_tile_matrix
   WHERE table_name = ''||table_name||''
  )
  END||';'||
 CASE
  WHEN data_type = 'features' THEN
  ( -- ... and now for something completely different ...
   CASE
    WHEN
    (
     SELECT z||','||m FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = '0,0' THEN '2'
    WHEN
    (
     SELECT z||','||m FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = '1,0' THEN '3'
    WHEN
    (
     SELECT z||','||m FROM gpkg_geometry_columns
     WHERE table_name = ''||table_name||''
    ) = '1,1' THEN '4'
   END
   -- ... ich habe fertig.
  )
  WHEN data_type = 'tiles' THEN
  (
   SELECT max(zoom_level) FROM gpkg_tile_matrix
   WHERE table_name = ''||table_name||''
  )
  END||';'||
 CASE
  WHEN srs_id = '1' THEN '4326'
  WHEN srs_id = '2' THEN '3857'
  ELSE srs_id
  END||';'||
 '0'||';' AS vector_data,
 '-1'||';'||
 CASE
  WHEN min_x IS NULL
   THEN 'min_x'
   ELSE min_x
  END||','||
 CASE
  WHEN min_y IS NULL
   THEN 'min_y'
   ELSE min_y
  END||','||
 CASE
  WHEN max_x IS NULL
   THEN 'max_x'
   ELSE max_x
  END||','||
 CASE
  WHEN max_y IS NULL
   THEN 'max_y'
   ELSE max_y
  END||';'||
 CASE
  WHEN last_change IS NULL
   THEN 'last_change'
   ELSE last_change
  END
 AS vector_extent
FROM
 gpkg_contents
WHERE
 (last_change IS NULL) OR
 (min_x IS NULL) OR
 (min_y IS NULL) OR
 (max_x IS NULL) OR
 (max_y IS NULL)
ORDER BY
 table_name ASC,
 identifier ASC;

This query will attempt to return information that should never fail:
- GEOPACKAGE_QUERY_EXTENT_LIST_R10
  - this query will retrieve minimal information on a valid or invalid database

SELECT DISTINCT
 table_name||';'||
 CASE
  WHEN data_type = 'features' THEN
  (
   SELECT column_name FROM gpkg_geometry_columns
   WHERE table_name = ''||table_name||''
  )
  WHEN data_type = 'tiles' THEN 'tile_data'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'GeoPackage_features'
  WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'
  END||';'||
 REPLACE(identifier,';','-')||';'||
 REPLACE(description,';','-')||';' AS vector_key
FROM
 gpkg_contents
ORDER BY
 table_name ASC,
 identifier ASC;

This will return the first 2 fields above:

this query may not be needed since
- GEOPACKAGE_QUERY_EXTENT_INVALID_R10
  - will now be used

2015-07-20: Mark Johnson, Berlin Germany