VECTOR_LAYERS_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 a valid spatial database

--> 'List of Sql-Commands'

--> 'List of Themes'

--> 'List of Database-Designing'

--> 'List of Geopaparazzi Specific Themes'

--> 'List of RasterLite2 Layers-Querys'

--> 'List of GeoPackage R10 Layers-Querys'

---

Note: when the term view is used : only spatial-views are meant by this

• non-spatial-views are not supported

---

There a 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

---

There are 3 Database types to support:

• for spatialite 4.0 with a valid vector_layers_statistics view

  • not all of which have a layers_statistics table
    • mostly original 3.1.0 databases still have this table
    • 4.1.0 databases do not have this table anymore

• for spatialite 4.0 with non-working vector_layers_statistics

  • but still has a valid layers_statistics table
    • mostly original 3.1.0 databases
      • where UpdateLayerStatistics has not been called for each table
        • this is a situation that can be permanently resolved by geopaparazzi
          • SELECT UpdateLayerStatistics('table_name','geometry_name');
            • returns 1 and valid vector_layers_statistics has a valid entry

• for pre-spatialite 4.0 Databases

  • for spatialite 2.4 until 3.1.0
    • tables and views must be handled separately
      • thus different query’s must be used

---

Goals:

• despite the different sql needed
  • the returned result should be the same

---

Tips:

• before coping a Database for use with geopaparazzi
  • the VECTOR_LAYERS_QUERY_EXTENT_VALID_V4 sql should be run against the Database
    • using spatialite_gui
  • only when this shows the correct results
    • can you assume that it will work correctly with geopaparazzi

---

• for spatialite 4.0 with a valid vector_layers_statistics view

• VECTOR_LAYERS_QUERY_EXTENT_VALID_V4
  • this query will only return valid records
    • Note: spatial_index_enabled must be 1
      • geopaparazzi cannot display a geometry without a SpatialIndex

SELECT DISTINCT
 vector_layers_statistics.table_name||';'||
 vector_layers_statistics.geometry_column||';'||
 vector_layers_statistics.layer_type||';ROWID;-1' AS vector_key,
 vector_layers.geometry_type||';'||
 vector_layers.coord_dimension||';'||vector_layers.srid||';'||
 vector_layers.spatial_index_enabled||';' AS vector_data,
 vector_layers_statistics.row_count||';'||
 vector_layers_statistics.extent_min_x||','|| 
 vector_layers_statistics.extent_min_y||','||
 vector_layers_statistics.extent_max_x||','||
 vector_layers_statistics.extent_max_y||';'|| 
 vector_layers_statistics.last_verified AS vector_extent 
FROM  
 vector_layers_statistics 
 INNER JOIN vector_layers ON 
 vector_layers_statistics.table_name =  vector_layers.table_name AND 
 vector_layers_statistics.geometry_column =  vector_layers.geometry_column 
WHERE 
 (vector_layers.spatial_index_enabled = 1) AND 
 (vector_layers_statistics.row_count IS NOT NULL) AND 
 (vector_layers_statistics.row_count > 0) AND 
 (vector_layers_statistics.extent_min_x IS NOT NULL) AND 
 (vector_layers_statistics.extent_min_y IS NOT NULL) AND 
 (vector_layers_statistics.extent_max_x IS NOT NULL) AND 
 (vector_layers_statistics.extent_max_y IS NOT NULL) 
ORDER BY  
 vector_layers_statistics.layer_type DESC, 
 vector_layers_statistics.table_name ASC, 
 vector_layers_statistics.geometry_column ASC;

Note:

• all queries shown here return this format
• each field uses ; as a delimiter
• pre-spatialite 4 values are converted to spatialite 4 values

This will return 3 fields:

• vector_key
  • geometries_berlin_1307;soldner_segments;SpatialView;ROWID;-1
    • table-name
    • geometry-name
    • table-type : only SpatialView or SpatialTable
    • Primary-Key : ROWID will be later replaced with the real PK
    • View read-only : -1 will be later replaced with the real value (0 or 1)
• vector_data
  • 5;2;3068;1;
    • geometry-type : 2 = LINESTRING ; 5 = POLYGON
      • older spatialite databases can have LINESTRING or POLYGON
        • the sql will be replaced these with the numeric value used today
    • coordinate dimension : 2 = XY
    • srid : the srid of the geometry (here Soldner Berlin)
    • Spatial Index Status : 1 = Spatial Index is activ
      • if this is true : a recovery can be attempted by geopaparazzi
      • otherwise the geometry will be considered invalid
• vector_extent
  • 2;24276.0426920011,20506.4637327919,25863.544725456,21938.6169137599;2014-04-15T05:20:10.254Z
    • row count : amount of rows
    • 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
  • VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4
    • this query will only return invalid records

SELECT DISTINCT
 vector_layers_statistics.table_name||';'||
 vector_layers_statistics.geometry_column||';'||
 vector_layers_statistics.layer_type||';ROWID;-1' AS vector_key,
 vector_layers.geometry_type||';'|| 
 vector_layers.coord_dimension||';'|| vector_layers.srid||';'||
 vector_layers.spatial_index_enabled||';' AS vector_data,
 CASE 
  WHEN vector_layers_statistics.row_count IS NULL 
   THEN 'row_count' 
   ELSE vector_layers_statistics.row_count 
  END||';'|| 
 CASE 
  WHEN vector_layers_statistics.extent_min_x IS NULL 
   THEN 'extent_min_x' 
   ELSE vector_layers_statistics.extent_min_x 
  END||','|| 
 CASE 
  WHEN vector_layers_statistics.extent_min_y IS NULL 
   THEN 'extent_min_y' 
   ELSE vector_layers_statistics.extent_min_y 
  END||','|| 
 CASE 
  WHEN vector_layers_statistics.extent_max_x IS NULL 
   THEN 'extent_max_x' 
   ELSE vector_layers_statistics.extent_max_x 
  END||','|| 
 CASE 
  WHEN vector_layers_statistics.extent_max_y IS NULL 
   THEN 'extent_max_y' 
   ELSE vector_layers_statistics.extent_max_y 
  END||';'|| 
 CASE 
  WHEN vector_layers_statistics.last_verified IS NULL 
   THEN 'last_verified' 
   ELSE vector_layers_statistics.last_verified 
  END
 AS vector_extent 
FROM  
 vector_layers_statistics 
 INNER JOIN vector_layers ON 
 vector_layers_statistics.table_name = vector_layers.table_name AND 
 vector_layers_statistics.geometry_column =  vector_layers.geometry_column 
WHERE 
 (vector_layers.spatial_index_enabled = 0) OR 
 (vector_layers_statistics.row_count IS NULL) OR 
 (vector_layers_statistics.row_count == 0) OR 
 (vector_layers_statistics.extent_min_x IS NULL) OR
 (vector_layers_statistics.extent_min_y IS NULL) OR 
 (vector_layers_statistics.extent_max_x IS NULL) OR 
 (vector_layers_statistics.extent_max_y IS NULL) 
ORDER BY 
 vector_layers_statistics.layer_type DESC, 
 vector_layers_statistics.table_name ASC, 
 vector_layers_statistics.geometry_column ASC;

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 to decide if a recovery is possible
      • if the bounds are not NULL and
        • the row count is NULL or 0
          • then the Table/View is empty
            • thus need not be loaded into geopaparazzi

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

SELECT DISTINCT
 vector_layers_statistics.table_name||';'||
 vector_layers_statistics.geometry_column||';'||
 vector_layers_statistics.layer_type||';ROWID;-1' AS vector_key,
 vector_layers.geometry_type||';'|| 
 vector_layers.coord_dimension||';'||
 vector_layers.srid||';'||vector_layers.spatial_index_enabled||';' AS vector_data
FROM  
 vector_layers_statistics 
 INNER JOIN vector_layers ON  
 vector_layers_statistics.table_name =  vector_layers.table_name AND
 vector_layers_statistics.geometry_column =  vector_layers.geometry_column 
ORDER BY 
 vector_layers_statistics.layer_type DESC, 
 vector_layers_statistics.table_name ASC, 
 vector_layers_statistics.geometry_column ASC;

This will return the first 2 fields above:

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

---

• for spatialite 4.0 with non-working vector_layers_statistics
  • This should only be needed for cases where UpdateLayerStatistics has failed

• LAYERS_QUERY_EXTENT_VALID_V4
  • • this query will only return valid records
    • Note: spatial_index_enabled must be 1
      • geopaparazzi cannot display a geometry without a SpatialIndex

SELECT DISTINCT
 layer_statistics.table_name||';'||
 layer_statistics.geometry_column||';'||
 vector_layers.layer_type||';ROWID;-1' AS vector_key,
 vector_layers.geometry_type||';'||
 vector_layers.coord_dimension||';'||
 vector_layers.srid||';'||
 vector_layers.spatial_index_enabled||';' AS vector_data,
 layer_statistics.row_count||';'||layer_statistics.extent_min_x||','||
 layer_statistics.extent_min_y||','||layer_statistics.extent_max_x||','||
 layer_statistics.extent_max_y||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent 
FROM 
 geometry_columns 
 INNER JOIN layer_statistics ON 
 geometry_columns.f_table_name = layer_statistics.table_name AND 
 geometry_columns.f_geometry_column = layer_statistics.geometry_column 
 INNER JOIN vector_layers ON 
 vector_layers.table_name = layer_statistics.table_name AND 
 vector_layers.geometry_column = layer_statistics.geometry_column
WHERE 
 (geometry_columns.spatial_index_enabled = 1) AND 
 (layer_statistics.row_count IS NOT NULL) AND 
 (layer_statistics.row_count > 0) AND 
 (layer_statistics.extent_min_x IS NOT NULL) AND 
 (layer_statistics.extent_min_y IS NOT NULL) AND 
 (layer_statistics.extent_max_x IS NOT NULL) AND 
 (layer_statistics.extent_max_y IS NOT NULL) 
ORDER BY  
 vector_layers.layer_type DESC, 
 vector_layers.table_name ASC, 
 vector_layers.geometry_column ASC;

• This query will attempt to analyze what error has occurred
  • LAYERS_QUERY_EXTENT_INVALID_V4
    • this query will only return invalid records

SELECT DISTINCT
 layer_statistics.table_name||';'||
 layer_statistics.geometry_column||';'||
 vector_layers.layer_type||';ROWID;-1' AS vector_key,
 vector_layers.geometry_type||';'|| 
 vector_layers.coord_dimension||';'|| 
 vector_layers.srid||';'||
 vector_layers.spatial_index_enabled||';' AS vector_data,
 CASE 
  WHEN layer_statistics.row_count IS NULL 
  THEN 'row_count' 
  ELSE layer_statistics.row_count 
 END ||';'|| 
 CASE 
  WHEN layer_statistics.extent_min_x IS NULL 
   THEN 'extent_min_x' 
   ELSE layer_statistics.extent_min_x 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_min_y IS NULL 
   THEN 'extent_min_y' 
   ELSE layer_statistics.extent_min_y 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_max_x IS NULL 
   THEN 'extent_max_x' 
   ELSE layer_statistics.extent_max_x 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_max_y IS NULL 
   THEN 'extent_max_y' 
   ELSE layer_statistics.extent_max_y 
  END ||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent 
FROM 
 geometry_columns 
 INNER JOIN layer_statistics ON 
 geometry_columns.f_table_name = layer_statistics.table_name AND 
 geometry_columns.f_geometry_column = layer_statistics.geometry_column 
 INNER JOIN vector_layers ON 
 vector_layers.table_name = layer_statistics.table_name AND 
 vector_layers.geometry_column = layer_statistics.geometry_column 
WHERE 
 (geometry_columns.spatial_index_enabled = 0) OR 
 (layer_statistics.row_count IS NULL) OR 
 (layer_statistics.row_count == 0) OR 
 (layer_statistics.extent_min_x IS NULL) OR 
 (layer_statistics.extent_min_y IS NULL) OR 
 (layer_statistics.extent_max_x IS NULL) OR 
 (layer_statistics.extent_max_y IS NULL) 
ORDER BY  
 vector_layers.layer_type DESC, 
 vector_layers.table_name ASC, 
 vector_layers.geometry_column ASC;

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

SELECT DISTINCT
 layer_statistics.table_name||';'||
 layer_statistics.geometry_column||';'||
 vector_layers.layer_type||';ROWID;-1' AS vector_key,
 vector_layers.geometry_type||';'|| 
 vector_layers.coord_dimension||';'|| 
 vector_layers.srid||';'|| 
 vector_layers.spatial_index_enabled||';' AS vector_data
FROM 
 geometry_columns 
 INNER JOIN layer_statistics ON 
 geometry_columns.f_table_name = layer_statistics.table_name AND 
 geometry_columns.f_geometry_column = layer_statistics.geometry_column 
 INNER JOIN vector_layers ON 
 vector_layers.table_name = layer_statistics.table_name AND 
 vector_layers.geometry_column = layer_statistics.geometry_column 
ORDER BY  
 vector_layers.layer_type DESC, 
 vector_layers.table_name ASC, 
 vector_layers.geometry_column ASC;

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

---

• for pre-spatialite 4.0 Databases tables
  • Notes:
    • these queries will not run on spatialite 4.* Databases
    • geometry_columns uses the name type instead of geometry_type
      • type can be:
        • POINT,LINESTRING, POLYGON or GEOMETRYCOLLECTION plus MULTI*
          • all of my samples use these values
      • coord_dimension can be:
        • XY,XYZ, XYM or XYZM or
        • 2,3, or 4 (there is no extra value for XYM)
      • these queries return the values used in the present spatialite version
    • if these queries fail with a
      • no such table: layer_statistics
        • then a SELECT UpdateLayerStatistics() must be executed
          • that will create the needed layer_statistics table
            • then try the query again

---

• LAYERS_QUERY_EXTENT_VALID_V3
  • • this query will only return valid records
    • Note: spatial_index_enabled must be 1
      • geopaparazzi cannot display a geometry without a SpatialIndex

SELECT DISTINCT
 f_table_name||';'||f_geometry_column||';'||
 'SpatialTable'||';ROWID;-1' AS vector_key,
 CASE 
  WHEN type = 'GEOMETRY' THEN '0' 
  WHEN type = 'POINT' THEN '1' 
  WHEN type = 'LINESTRING' THEN '2' 
  WHEN type = 'POLYGON' THEN '3' 
  WHEN type = 'MULTIPOINT' THEN '4' 
  WHEN type = 'MULTILINESTRING' THEN '5' 
  WHEN type = 'MULTIPOLYGON' THEN '6' 
  WHEN type = 'GEOMETRYCOLLECTION' THEN '7' 
 END||';'||
 CASE 
  WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2' 
  WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3' 
  WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4' 
 END||';'||srid||';'||spatial_index_enabled||';' AS vector_data,
layer_statistics.row_count||';'||layer_statistics.extent_min_x||','||layer_statistics.extent_min_y||','||
layer_statistics.extent_max_x||','||layer_statistics.extent_max_y||';'||
strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent 
FROM 
 geometry_columns 
 INNER JOIN layer_statistics ON 
  geometry_columns.f_table_name = layer_statistics.table_name AND 
  geometry_columns.f_geometry_column = layer_statistics.geometry_column 
WHERE 
 (geometry_columns.spatial_index_enabled = 1) AND 
 (layer_statistics.row_count IS NOT NULL) AND 
 (layer_statistics.row_count > 0) AND 
 (layer_statistics.extent_min_x IS NOT NULL) AND 
 (layer_statistics.extent_min_y IS NOT NULL) AND 
 (layer_statistics.extent_max_x IS NOT NULL) AND 
 (layer_statistics.extent_max_y IS NOT NULL) 
ORDER BY 
 layer_statistics.table_name ASC,
 layer_statistics.geometry_column ASC;

• This query will attempt to analyze what error has occurred
  • LAYERS_QUERY_EXTENT_INVALID_V3
    • this query will only return invalid records

SELECT DISTINCT
 f_table_name||';'||f_geometry_column||';'||
 'SpatialTable'||';ROWID;-1' AS vector_key,
 CASE 
  WHEN type = 'GEOMETRY' THEN '0' 
  WHEN type = 'POINT' THEN '1' 
  WHEN type = 'LINESTRING' THEN '2' 
  WHEN type = 'POLYGON' THEN '3' 
  WHEN type = 'MULTIPOINT' THEN '4' 
  WHEN type = 'MULTILINESTRING' THEN '5' 
  WHEN type = 'MULTIPOLYGON' THEN '6' 
  WHEN type = 'GEOMETRYCOLLECTION' THEN '7' 
 END||';'||
 CASE 
  WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2' 
  WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3' 
  WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4' 
 END||';'||srid||';'||spatial_index_enabled||';' AS vector_data,
 CASE 
  WHEN layer_statistics.row_count IS NULL 
   THEN 'row_count' 
   ELSE layer_statistics.row_count 
  END ||';'|| 
 CASE 
  WHEN layer_statistics.extent_min_x IS NULL 
   THEN 'extent_min_x' 
   ELSE layer_statistics.extent_min_x 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_min_y IS NULL 
   THEN 'extent_min_y' 
   ELSE layer_statistics.extent_min_y 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_max_x IS NULL 
   THEN 'extent_max_x' 
  ELSE layer_statistics.extent_max_x 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_max_y IS NULL 
   THEN 'extent_max_y' 
   ELSE layer_statistics.extent_max_y 
  END ||';'||
 strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent 
FROM 
 geometry_columns 
 INNER JOIN layer_statistics ON 
 geometry_columns.f_table_name = layer_statistics.table_name AND 
 geometry_columns.f_geometry_column = layer_statistics.geometry_column 
WHERE 
 (geometry_columns.spatial_index_enabled = 0) OR 
 (layer_statistics.row_count IS NULL) OR
 (layer_statistics.row_count == 0) OR
 (layer_statistics.extent_min_x IS NULL) OR
 (layer_statistics.extent_min_y IS NULL) OR
 (layer_statistics.extent_max_x IS NULL) OR
 (layer_statistics.extent_max_y IS NULL) 
ORDER BY  
 layer_statistics.table_name ASC,
 layer_statistics.geometry_column ASC;

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

SELECT DISTINCT
 f_table_name||';'||f_geometry_column||';'||
 'SpatialTable'||';ROWID;-1' AS vector_key,
 CASE 
  WHEN type = 'GEOMETRY' THEN '0' 
  WHEN type = 'POINT' THEN '1' 
  WHEN type = 'LINESTRING' THEN '2' 
  WHEN type = 'POLYGON' THEN '3' 
  WHEN type = 'MULTIPOINT' THEN '4' 
  WHEN type = 'MULTILINESTRING' THEN '5' 
  WHEN type = 'MULTIPOLYGON' THEN '6' 
  WHEN type = 'GEOMETRYCOLLECTION' THEN '7' 
 END||';'||
 CASE 
  WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2' 
  WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3' 
  WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4' 
 END||';'||srid||';'||spatial_index_enabled||';' AS vector_data 
FROM 
 geometry_columns 
 INNER JOIN layer_statistics ON 
 geometry_columns.f_table_name = layer_statistics.table_name AND 
 geometry_columns.f_geometry_column = layer_statistics.geometry_column 
ORDER BY  
 layer_statistics.table_name ASC,
 layer_statistics.geometry_column ASC;

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

---

• for pre-spatialite 4.0 Databases views

  • Note: read_only will be to 0 as default

  • VIEWS_QUERY_EXTENT_VALID_V3

    • • this query will only return valid records
      • Note: spatial_index_enabled must be 1
        • geopaparazzi cannot display a geometry without a SpatialIndex

SELECT DISTINCT 
 view_name||';'||view_geometry||';'||
 'SpatialView'||';ROWID;-1' AS vector_key,
 CASE 
  WHEN type = 'GEOMETRY' THEN '0' 
  WHEN type = 'POINT' THEN '1' 
  WHEN type = 'LINESTRING' THEN '2' 
  WHEN type = 'POLYGON' THEN '3' 
  WHEN type = 'MULTIPOINT' THEN '4' 
  WHEN type = 'MULTILINESTRING' THEN '5' 
  WHEN type = 'MULTIPOLYGON' THEN '6' 
  WHEN type = 'GEOMETRYCOLLECTION' THEN '7' 
 END||';'||
 CASE 
  WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2' 
  WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3' 
  WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4' 
 END||';'||srid||';'||spatial_index_enabled||';' AS vector_data,
 layer_statistics.row_count||';'||layer_statistics.extent_min_x||','||
 layer_statistics.extent_min_y||','||layer_statistics.extent_max_x||','||
 layer_statistics.extent_max_y||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent
FROM 
 views_geometry_columns
 INNER JOIN layer_statistics ON 
 views_geometry_columns.view_name = layer_statistics.table_name AND 
 views_geometry_columns.view_geometry = layer_statistics.geometry_column 
 INNER JOIN geometry_columns ON 
 geometry_columns.f_table_name = views_geometry_columns.f_table_name AND 
 geometry_columns.f_geometry_column = views_geometry_columns.f_geometry_column 
WHERE 
 (geometry_columns.spatial_index_enabled = 1) AND 
 (layer_statistics.row_count IS NOT NULL) AND 
 (layer_statistics.row_count > 0) AND 
 (layer_statistics.extent_min_x IS NOT NULL) AND 
 (layer_statistics.extent_min_y IS NOT NULL) AND 
 (layer_statistics.extent_max_x IS NOT NULL) AND 
 (layer_statistics.extent_max_y IS NOT NULL) 
ORDER BY 
 layer_statistics.table_name ASC,
 layer_statistics.geometry_column ASC;

• This query will attempt to analyze what error has occurred
  • VIEWS_QUERY_EXTENT_INVALID_V3
    • this query will only return invalid records

SELECT DISTINCT
 view_name||';'||view_geometry||';'||
 'SpatialView'||';ROWID;-1' AS vector_key,
 CASE 
  WHEN type = 'GEOMETRY' THEN '0' 
  WHEN type = 'POINT' THEN '1' 
  WHEN type = 'LINESTRING' THEN '2' 
  WHEN type = 'POLYGON' THEN '3' 
  WHEN type = 'MULTIPOINT' THEN '4' 
  WHEN type = 'MULTILINESTRING' THEN '5' 
  WHEN type = 'MULTIPOLYGON' THEN '6' 
  WHEN type = 'GEOMETRYCOLLECTION' THEN '7' 
 END||';'||
 CASE 
  WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2' 
  WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3' 
  WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4' 
 END||';'||srid||';'||spatial_index_enabled||';' AS vector_data,
 CASE 
  WHEN layer_statistics.row_count IS NULL 
   THEN 'row_count' 
   ELSE layer_statistics.row_count 
  END ||';'|| 
 CASE 
  WHEN layer_statistics.extent_min_x IS NULL 
   THEN 'extent_min_x' 
   ELSE layer_statistics.extent_min_x 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_min_y IS NULL 
   THEN 'extent_min_y' 
   ELSE layer_statistics.extent_min_y 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_max_x IS NULL 
   THEN 'extent_max_x' 
   ELSE layer_statistics.extent_max_x 
  END ||','|| 
 CASE 
  WHEN layer_statistics.extent_max_y IS NULL 
   THEN 'extent_max_y' 
   ELSE layer_statistics.extent_max_y 
  END ||';'||
strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent 
FROM 
 views_geometry_columns
 INNER JOIN layer_statistics ON 
 views_geometry_columns.view_name = layer_statistics.table_name AND 
 views_geometry_columns.view_geometry = layer_statistics.geometry_column 
 INNER JOIN geometry_columns ON 
 geometry_columns.f_table_name = views_geometry_columns.f_table_name AND 
 geometry_columns.f_geometry_column = views_geometry_columns.f_geometry_column 
WHERE 
 (geometry_columns.spatial_index_enabled = 0) OR 
 (layer_statistics.row_count IS NULL) OR
 (layer_statistics.row_count == 0) OR
 (layer_statistics.extent_min_x IS NULL) OR
 (layer_statistics.extent_min_y IS NULL) OR
 (layer_statistics.extent_max_x IS NULL) OR
 (layer_statistics.extent_max_y IS NULL)  
ORDER BY 
 layer_statistics.table_name ASC,
 layer_statistics.geometry_column ASC;

This will return the first 2 fields above:

• VIEWS_QUERY_EXTENT_LIST_V3
  • this query will retrieve minimal information on a valid or invalid database

SELECT DISTINCT
 view_name||';'||view_geometry||';'||
 'SpatialView'||';ROWID;-1' AS vector_key,
 CASE 
  WHEN type = 'GEOMETRY' THEN '0' 
  WHEN type = 'POINT' THEN '1' 
  WHEN type = 'LINESTRING' THEN '2' 
  WHEN type = 'POLYGON' THEN '3' 
  WHEN type = 'MULTIPOINT' THEN '4' 
  WHEN type = 'MULTILINESTRING' THEN '5' 
  WHEN type = 'MULTIPOLYGON' THEN '6' 
  WHEN type = 'GEOMETRYCOLLECTION' THEN '7' 
 END||';'||
 CASE 
  WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2' 
  WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3' 
  WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4' 
 END||';'||srid||';'||spatial_index_enabled||';' AS vector_data 
FROM 
 views_geometry_columns
 INNER JOIN layer_statistics ON 
 views_geometry_columns.view_name = layer_statistics.table_name AND 
 views_geometry_columns.view_geometry = layer_statistics.geometry_column 
 INNER JOIN geometry_columns ON 
 geometry_columns.f_table_name = views_geometry_columns.f_table_name AND 
 geometry_columns.f_geometry_column = views_geometry_columns.f_geometry_column 
ORDER BY  
 layer_statistics.table_name ASC,
 layer_statistics.geometry_column ASC;

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

---

These sql queries are build as static strings in DaoSpatialite

• and are used as needed, depending on which Database version was found

---

2014-05-09: Mark Johnson, Berlin Germany

---