Asset Publisher

Importing layers contained in Sqlite files


 

Importing layers contained in Sqlite files

If you want to use layers created with third-party software, such as the QGIS desktop application. Here you have a quick guide so you can view and edit the layers with our Cartodruid application. You can prepare a field trip on your desktop equipment, marking the waypoints. To later collect the data of each point on the ground with Cartodruid. Once the field work is done, you can download the Sqlite files from your mobile to process the information collected as you need it.

Create a new layer from a Sqlite file

Once we have a sqlite file with spatialite format compatible with Cartodruid we can perform the import with a few simple steps.

  1. Display the TOC or table of contents. Where the project layers appear.
  2. Click on add layer and a layer selection dialog appears.
  3. We select "Vector Layer" since the layers with spatialite format are of vector type.
  4. Now we choose to load Sqlite file and the file manager that we have installed on our mobile will open.

We look for the sqlite file that contains the layer that we want to import and the layer configuration form appears. We select one of the layers contained in the sqlite and complete the rest of the fields to finish accepting the configuration.

If everything is correct we already have our imported layer, and we can manage it and view it in cartodruid.

Sqlite format compatible with Cartodruid

In order for the sqlite file to be compatible with cartodruid it must meet the following specifications:

  • The sqlite file format must be Spatialite.
  • Las geometrías de capa con las que trabaja cartodruid son (POINT, MULTILINESTRING y MULTIPOLYGON).
  • The layer geometries that cartodruid works with are (POINT, MULTILINESTRING and MULTIPOLYGON).
  • The data table that corresponds to the layer must have a simple primary key of type INTEGER. If you do not have a primary key, a key will be created.
  • The geometry must be indexed. If the geometry is not indexed, a new indexing will be created.

At import, the selected layer is checked for compliance with Cartodruid compatible specifications. If it does not comply, the reason is indicated, and if it does, but a change is needed. We will be asked for confirmation before doing it.

Sqlite file properties query

If we want to consult the sqlite database file, we can use the free tool Spatialite-gui that shows us the tables contained in the file.

Sqlite is the database that contains the information of the entities. If the entities have geographic information, it is stored in a specific structure. In the case of Cartodruid, the format it uses is Spatialite.

The tables contained in the ¿User Data¿ section are the tables that contain the entities of each layer, while the tables in the ¿Metadata¿ section contain the information on how to handle the data in the user tables.

In the metadata table called "geometry_columns" we can see the declared fields of geometry and their characteristics. Next step to identify the fields used in version 4 of Spatialite. Depending on the version, you can change the field name and its content. You can check it at the following link https://www.gaia-gis.it/fossil/libspatialite/wiki?name=switching-to-4.0.

f_table_name:
Name of the user table that has a geometry field.
f_geometry_column:
Name of the column that stores the geometry.
geometry_type:
(1, POINT; 5, MULTILINESTRING; 6, MULTIPOLYGON)
coord_dimension:
(2, 2D; 3, 3D)
srid:
Spatial reference identifier.
spatial_index_enabled:
If you have created a geometry index.

A different geographic information storage structure than Spatialite is FDO_OGR. In this case we still have the table "geometry_columns", but the last column changes its meaning.

And if the geometry storage structure is in OGC_GPKG format, the "geometry_columns" table no longer exists.