Metadata and statistic infos supported in version 4.0.0
backThe "vector_layers" Metadata view
This new VIEW supports a synoptic snapshot of all "vector layers" available in the database file, irrespectively from their physical storage type.i.e. all Spatial Tables, Spatial Views and registered Virtual Shapes (aka external Shapefiles) are now nicely accessible altogether using a plain view.
The following example explains how all this practically works.
| layer_type | table_name | geometry_column | geometry_type | coord_dimension | srid | spatial_index_enabled |
| SpatialTable | comuni | Geometry | 6 | 2 | 23032 | 1 |
| SpatialTable | province | Geometry | 6 | 2 | 23032 | 1 |
| SpatialTable | regioni | Geometry | 6 | 2 | 23032 | 0 |
| SpatialTable | regioni_elem | Geometry | 3 | 2 | 23032 | 1 |
| SpatialView | prov_reg | Geometry | 6 | 2 | 23032 | 1 |
| SpatialView | com_prov | Geometry | 6 | 2 | 23032 | 1 |
| VirtualShape | com2011 | Geometry | 6 | 2 | 23032 | 0 |
| VirtualShape | prov2011 | Geometry | 6 | 2 | 23032 | 0 |
| VirtualShape | reg2011 | Geometry | 6 | 2 | 23032 | 0 |
This View is based on the following tables: geometry_columns, views_geometry_columns and virts_geometry_columns.
The "geometry_columns_time" Metadata tableThis new table keeps trace of the most recent changes affecting each Spatial Table; every INSERT, UPDATE or DELETE operation will now immediately touch the corresponding timestamp value (encoded accordingly to ISO-8601 spcifications).The conventional timestamp value 0000-01-01T00:00:00.000Z simply intends "this event never occurred". a very short technical note about the underlying implementationOn v.4.0.0 each Spatial Table is now supported by three more Triggers, each one of them intercepting the corresponding event and keeping care to correctly update the appropriate timestamp value.The following example explains how all this practically works. |
 |
| f_table_name | f_geometry_column | last_insert | last_update | last_delete |
| comuni | Geometry | 2012-08-09T17:45:32.312Z | 0000-01-01T00:00:00.000Z | 0000-01-01T00:00:00.000Z |
| province | Geometry | 2012-08-09T17:45:49.429Z | 0000-01-01T00:00:00.000Z | 0000-01-01T00:00:00.000Z |
| regioni | Geometry | 2012-08-09T17:46:12.684Z | 0000-01-01T00:00:00.000Z | 0000-01-01T00:00:00.000Z |
| regioni_elem | Geometry | 2012-08-09T17:49:24.843Z | 0000-01-01T00:00:00.000Z | 0000-01-01T00:00:00.000Z |
The "vector_layers_statistics" Metadata view
This new VIEW supports a synoptic snapshot of statistic infos for all "vector layers", irrespectively from their physical storage type.i.e. all Spatial Tables, Spatial Views and registered Virtual Shapes (aka external Shapefiles) are now nicely accessible altogether using a plain view.
The following example explains how all this practically works.
| layer_type | table_name | geometry_column | last_verified | row_count | extent_min_x | extent_min_y | extent_max_x | extent_max_y |
| SpatialTable | comuni | Geometry | 2012-08-09T18:36:03.231Z | 8094 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220492.095518 |
| SpatialTable | province | Geometry | 2012-08-09T18:36:03.812Z | 110 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220491.200018 |
| SpatialTable | regioni | Geometry | 2012-08-09T18:36:03.032Z | 20 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220491.200018 |
| SpatialTable | regioni_elem | Geometry | 2012-08-09T18:36:03.351Z | 551 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220491.200018 |
| SpatialView | com_prov | Geometry | 2012-08-09T18:36:03.687Z | 8094 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220492.095518 |
| SpatialView | prov_reg | Geometry | 2012-08-09T18:36:03.881Z | 110 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220491.200018 |
| VirtualShape | com2011 | Geometry | 2012-08-09T18:36:04.834Z | 8094 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220492.095518 |
| VirtualShape | prov2011 | Geometry | 2012-08-09T18:36:05.125Z | 110 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220491.200018 |
| VirtualShape | reg2011 | Geometry | 2012-08-09T18:36:05.448Z | 20 | 313360.999831 | 3933878.175118 | 1312106.500031 | 5220491.200018 |
Please note: the last_verified timestamp allows to check if the collected statistic infos are still valid or not, by performing a simple comparison of the corresponding timestamp values stored in geometry_columns_time.
This View is based on the following tables: geometry_columns_statistics, views_geometry_columns_statistics and virts_geometry_columns_statistics.
The "vector_layers_field_infos" Metadata view
This new VIEW supports a synoptic snapshot of statistic infos for all fields (aka columns) belonging to any "vector layer".i.e. all Spatial Tables, Spatial Views and registered Virtual Shapes (aka external Shapefiles) are now nicely accessible altogether using a plain view.
The following example explains how all this practically works.
| layer_type | table_name | geometry_column | ordinal | column_name | null_values | integer_values | double_values | text_values | blob_values | max_size | integer_min | integer_max | double_min | double_max |
| VirtualShape | reg2011 | Geometry | 0 | PKUID | 0 | 20 | 0 | 0 | 0 | NULL | 1 | 8094 | NULL | NULL |
| VirtualShape | reg2011 | Geometry | 1 | Geometry | 0 | 0 | 0 | 0 | 20 | 1481253 | NULL | NULL | NULL | NULL |
| VirtualShape | reg2011 | Geometry | 2 | COD_REG | 0 | 20 | 0 | 0 | 0 | NULL | 1 | 20 | NULL | NULL |
| VirtualShape | reg2011 | Geometry | 3 | NOME_REG | 0 | 0 | 0 | 20 | 0 | 28 | NULL | NULL | NULL | NULL |
| VirtualShape | reg2011 | Geometry | 4 | SHAPE_Leng | 0 | 0 | 20 | 0 | 0 | NULL | NULL | NULL | 325841.796613 | 2113774.900030 |
| VirtualShape | reg2011 | Geometry | 5 | SHAPE_Area | 0 | 0 | 20 | 0 | 0 | NULL | NULL | NULL | 3260902257.970000 | 25832379739.500000 |
Please note: SQLite doesn't supports a strong concept of column datatype. It could be sometimes useful, but surely is not exactly what many popular GIS applications will expect. On SQLite every single cell value can arbitrary be of anyone of the supported datatypes, irrespectively from the specific column declaration:
- NULL: exactly corresponding to SQL NULL
- INTEGER: any integer value, with a maximum size of 64 bit
- DOUBLE: any 64 bit floating point value
- TEXT: any text string of arbitrary length (max. 1 GB)
- BLOB: any binary data of arbitrary length (max. 1 GB)
This View is based on the following tables: geometry_columns_field_infos, views_geometry_columns_field_infos and virts_geometry_columns_field_infos.
The "vector_layers_auth" Metadata view
This new VIEW supports a synoptic snapshot of all "vector layers" available in the database file, irrespectively from their physical storage type.i.e. all Spatial Tables, Spatial Views and registered Virtual Shapes (aka external Shapefiles) are now nicely accessible altogether using a plain view.
The following example explains how all this practically works.
| layer_type | table_name | geometry_column | read_only | hidden |
| SpatialTable | comuni | Geometry | 0 | 0 |
| SpatialTable | province | Geometry | 0 | 0 |
| SpatialTable | regioni | Geometry | 0 | 0 |
| SpatialTable | regioni_elem | Geometry | 0 | 0 |
| SpatialView | com_prov | Geometry | 0 | 0 |
| SpatialView | prov_reg | Geometry | 1 | 0 |
| VirtualShape | com2011 | Geometry | 1 | 0 |
| VirtualShape | prov2011 | Geometry | 1 | 0 |
| VirtualShape | reg2011 | Geometry | 1 | 0 |
Please note: now that Updatable Views are supported, not all Views must be assumed to be read-only.
The hidden value could be useful for some smart GIS client, in order to ignore at all some layers (e.g. the ones intended to be effectively accessed via some specific View).
This View is based on the following tables: geometry_columns_auth, views_geometry_columns_auth and virts_geometry_columns_auth.
back