RASTER_COVERAGES_QUERYS 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 rasterlite2 raster-layers

--> 'List of Sql-Commands'

--> 'List of Themes'

--> 'List of Database-Designing'

--> 'List of Geopaparazzi Specific Themes'

--> 'List of Geometry Layers-Querys'

--> 'List of GeoPackage R10 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 spatialite 4.2 with a valid raster_coverages table

SELECT DISTINCT
 coverage_name||';'||
 compression||';'||
 'RasterLite2'||';'||
 REPLACE(title,';','-')||';'||
 REPLACE(abstract,';','-')||';' AS vector_key,
 pixel_type||';'||
 tile_width||';'||
 srid||';'||
 horz_resolution
AS vector_data,
 num_bands||';'||
 extent_minx||','||extent_miny||','||
 extent_maxx||','||extent_maxy||';'||
 strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent
FROM
 raster_coverages
WHERE
 (statistics  IS NOT NULL) AND
 (extent_minx IS NOT NULL) AND
 (extent_miny IS NOT NULL) AND
 (extent_maxx IS NOT NULL) AND
 (extent_maxy IS NOT NULL)
ORDER BY
 coverage_name ASC,
 title 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

  • berlin_postgrenzen.1890;LOSSY_WEBP;RasterLite2;Berlin Straube Postgrenzen;1890 - 1:17777;
    • coverage_name
    • compression : as information only - not needed for geopaparazzi
    • table-type : only RasterLite2
    • title
      • any ; will be replaced with -
    • abstract
      • any ; will be replaced with -

• vector_data

  • RGB;512;3068;1.13008623862252
    • pixel_type : not needed for geopaparazzi
    • tile_size
    • srid : the srid of the Raster (here Soldner Berlin)
    • horz_resolution : as information only - not needed for geopaparazzi

• vector_extent

  • 3;17903.0354299312,17211.5335278146,29889.8601630003,26582.2086184726;2014-05-09T03:28:27.067Z
    • num_bands : not needed for geopaparazzi
    • 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
  • RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42
    • this query will only return invalid records
      • this can happen under the following conditions

SELECT RL2_CreateCoverage('raster_coverages_query_extent_invalid',
        'UINT8','RGB',3,'WEBP',80,512,512,3068,1.130086238622524);

• but no: SELECT RL2_LoadRaster(...) was executed
  • no imagie, no validitie ...

-- 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
 coverage_name||';'||
 compression||';'||
 'RasterLite2'||';'||
 REPLACE(title,';','-')||';'||
 REPLACE(abstract,';','-')||';' AS vector_key,
 pixel_type||';'||
 tile_width||';'||
 srid||';'||
 horz_resolution
AS vector_data,
 CASE
  WHEN statistics IS NULL
   THEN 'statistics'
   ELSE pixel_type
  END||';'||
 CASE
  WHEN extent_minx IS NULL
   THEN 'extent_minx'
   ELSE extent_minx
  END||','||
 CASE
  WHEN extent_miny IS NULL
   THEN 'extent_miny'
   ELSE extent_miny
  END||','||
 CASE
  WHEN extent_maxx IS NULL
   THEN 'extent_maxx'
   ELSE extent_maxx
  END||','||
 CASE
  WHEN extent_maxy IS NULL
   THEN 'extent_maxy'
   ELSE extent_maxy
  END||';'||
  strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent
 AS vector_extent
FROM
 raster_coverages
WHERE
 (statistics IS NULL) OR
 (extent_minx IS NULL) OR
 (extent_miny IS NULL) OR
 (extent_maxx IS NULL) OR
 (extent_maxy IS NULL)
ORDER BY
 coverage_name ASC,
 title ASC;

--

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

SELECT DISTINCT
 coverage_name||';'||
 compression||';'||
 'RasterLite2'||';'||
 REPLACE(title,';','-')||';'||
 REPLACE(abstract,';','-')||';' AS vector_key,
 pixel_type||';'||
 tile_width||';'||
 srid||';'||
 horz_resolution
AS vector_data
FROM
 raster_coverages
ORDER BY
 coverage_name ASC,
 title ASC;

This will return the first 2 fields above:

• this query may not be needed since
  • RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42
    • will now be used

---

2014-05-09: Mark Johnson, Berlin Germany

---