GEOPACKAGE_QUERY_R09 geopaparazzi specific

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

if this database has valid geopackage based on Revision 9
- support for this is now discontinued
  - --> 'GEOPACKAGE_QUERY_R10'

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 R9 that I have seen
- Luciad_GeoPackage.gpkg
- Sample_Geopackage_Haiti.gpkg

--

--> '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'

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 R9 with a valid `geopackage_contents` table * **GEOPACKAGE_QUERY_EXTENT_VALID_R9** * this query will only return ***valid*** records

SELECT DISTINCT
 table_name||';'||
 CASE
  WHEN data_type = 'features' THEN 'shape'
  WHEN data_type = 'tiles' THEN 'tile_data'
  WHEN data_type = 'featuresWithRasters' THEN 'location'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'GeoPackage_features'
  WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'
  WHEN data_type = 'featuresWithRasters' THEN 'GeoPackage_featuresWithRasters'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'ROWID'
  WHEN data_type = 'tiles' THEN
  (
   SELECT min(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'ROWID'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN '-1'
  WHEN data_type = 'tiles' THEN
  (
   SELECT max(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'picture'
  END AS vector_key,
 CASE
  WHEN data_type = 'features' THEN 'ROWID'
  WHEN data_type = 'tiles' THEN
  (
   SELECT min(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'ROWID'
  END||';'||
 REPLACE(description,';','-')||';'||
 CASE
  WHEN srid = '1' THEN '4326'
  WHEN srid = '2' THEN '3857'
  ELSE srid
  END||';'||
 REPLACE(identifier,';','-')||';' AS vector_data,
 '-1'||';'||
 min_x||';'||
 min_y||';'||
 max_x||';'||
 max_y||';'||
 last_change AS vector_extent
FROM
 geopackage_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'](VECTOR_LAYERS_QUERYS-geopaparazzi-specific) * the `parsing logic` will be the same, even if many of the fields are only informational

This will return 3 fields:

vector_key
- lakemead_clipped;shape;GeoPackage_features;ROWID;-1
- o18229_tif_tiles;tile_data;GeoPackage_tiles;0;4
- observations;location;GeoPackage_featuresWithRasters;ROWID;picture
  - table_name
  - geometry_field
    - for features and featuresWithRasters
      - the (hopefully) default name of the geometry-field shape
    - for tiles
      - the (hopefully) default name of the tiles-field location
  - data_type
    - features will be replaced with GeoPackage_features
    - tiles will be replaced with GeoPackage_tiles
    - featuresWithRasters will be replaced with GeoPackage_featuresWithRasters
  - Primary-Key:
    - for tiles
      - the max Zoom-level
    - for features and featuresWithRasters
      - ROWID : not used
  - picture:
    - for featuresWithRasters
      - the (hopefully) default name of the tiles-field picture
    - for features and tiles
      - -1 : not used
vector_data
- lakemead_clipped;Lake Mead (Clipped);4326;-1;
- o18229.tif;Hoover Dam Aerial;3857;-1;
- observations;Observations;3857;-1;
  - geometry_type:
    - for featuresandfeaturesWithRasters`
      - the geometry_type
    - for ``tiles`
      - -1 : not used
  - description :
    - any ; will be replaced with -
  - srid : the set srid
    - if 1 was used: will set as 4326
    - if 2 was used: will set as 3857
  - identifier :
    - any ; will be replaced with -
vector_extent
- -1;-114.739703991949;36.0133552500798;-114.7260476107;36.0226841914896;2013-01-18T17:37:23.000Z
  - nothing : not used
  - 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_R9** * 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 'shape'
  WHEN data_type = 'tiles' THEN 'tile_data'
  WHEN data_type = 'featuresWithRasters' THEN 'location'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'GeoPackage_features'
  WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'
  WHEN data_type = 'featuresWithRasters' THEN 'GeoPackage_featuresWithRasters'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'ROWID'
  WHEN data_type = 'tiles' THEN
  (
   SELECT min(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'ROWID'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN '-1'
  WHEN data_type = 'tiles' THEN
  (
   SELECT max(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'picture'
  END AS vector_key,
 REPLACE(identifier,';','-')||';'||
 REPLACE(description,';','-')||';'||
 '-1'||';'||
 CASE
  WHEN srid = '1' THEN '4326'
  WHEN srid = '2' THEN '3857'
  ELSE srid
  END||';'||
 srid||';'||
 REPLACE(identifier,';','-')||';' 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
 geopackage_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_R9** * this query will retrieve minimal information on a ***valid*** or ***invalid*** database

SELECT DISTINCT
 table_name||';'||
 CASE
  WHEN data_type = 'features' THEN 'shape'
  WHEN data_type = 'tiles' THEN 'tile_data'
  WHEN data_type = 'featuresWithRasters' THEN 'location'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'GeoPackage_features'
  WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'
  WHEN data_type = 'featuresWithRasters' THEN 'GeoPackage_featuresWithRasters'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN 'ROWID'
  WHEN data_type = 'tiles' THEN
  (
   SELECT min(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'ROWID'
  END||';'||
 CASE
  WHEN data_type = 'features' THEN '-1'
  WHEN data_type = 'tiles' THEN
  (
   SELECT max(zoom_level) FROM tile_matrix_metadata
   WHERE t_table_name = ''||table_name||''
  )
  WHEN data_type = 'featuresWithRasters' THEN 'picture'
  END AS vector_key
FROM
 geopackage_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_R9
  - will now be used

2014-05-10: Mark Johnson, Berlin Germany

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

GEOPACKAGE_QUERY_R09 geopaparazzi specific

Clone this wiki locally