Asset Publisher
Layer with SQL query as data source
In the latest version of Cartodruid we have introduced as a novelty the possibility of having a layer whose data source is an SQL query. Below we explain how to configure this layer, as well as several restrictions to take into account.
Configuration
The layer is configured like any other vector layer as indicated in points 5.2 Vector Layer Configuration and 5.4 Data Source Configuration of the CartoDruid Reference Manual with the following modifications:
- In the tag attributesClassName you have to put QuerySqlite as the class that accesses the data
<attributesClassName>QuerySqlite</attributesClassName>
- As data source descriptor we are going to use SpatiaLiteQueryServiceDescriptor
<sources>
< SpatiaLiteQueryServiceDescriptor>
...
</ SpatiaLiteQueryServiceDescriptor>
</sources>
To configure the query within SpatiaLiteQueryServiceDescriptor we have the following fields:
fields |
Fields that the query will return. We can put * if we want all the fields to be returned. |
from |
Table or tables involved. The table containing the source geometries will have the alias "g" associated with it. You can also add nested SQL subqueries. |
where (optional) |
Query filtering condition. |
groupBy (optional) |
Field by which we are going to group the data. |
orderBy (optional) |
Field or fields by which we are going to order the result of the query. |
primaryKey |
Field that will act as an identifier in the result of the query (it must be within the fields list of fields). |
indexTable |
Identifier of the spatial index of the table containing the geometries. |
dbURL |
Name of the sqlite file that contains the data. |
Keep in mind that since the configuration is inside an XML file, we cannot use '<' and '>' in the where conditions or in the subqueries since they are reserved characters. We must replace them with '<' and '>' respectively.
Restrictions
- Any type of editing, both of geometries and alphanumeric values, will be disabled. You can select features and identify them to see their values..
- The geometries displayed can only come from a table. Operations can be performed on these geometries in the same table, for example, that the geometry is the result of the union of geometries grouped by the value of another field in the table.
- The operations on the permitted geometries will be those permitted by Spatialite, which are listed in the following link: Spatialite SQL functions reference list
Configuration Examples
We have prepared three configuration examples which are available in the following project that you can import into Cartodruid:
Query with 2 tables
In this query we have the main table subparcelas and another called uso_sigpac that we join by the fields uso_sigpac from subparcelas and c_uso_sigpac from uso_sigpac. And as a result all the fields of subparcelas and the fields d_uso_sigpac from uso_sigpac are shown.
<SpatiaLiteQueryServiceDescriptor>
<fields>g.*, u.d_uso_sigpac</fields>
<from>subparcelas g, uso_sigpac u</from>
<where>g.uso_sigpac = u.c_uso_sigpac</where>
<dbURL>exampleQueryLayer.sqlite</dbURL>
<primaryKey>codigo_subparcela</primaryKey>
<indexTable>idx_subparcelas_Geometry</indexTable>
</SpatiaLiteQueryServiceDescriptor>
Resulting query:
SELECT g.*, u.d_uso_sigpac FROM subparcelas g, uso_sigpac u WHERE g.uso_sigpac = u.c_uso_sigpac;
Query with geographic operation
In this case we have the subparcelas table, in which we have entities with the same codigo_parcela. In the query we group by this field and the geometries would be the result of the union of the geometries with the same codigo_parcela. Additionally, ¿subparcelas¿ field is added with the list of the subplots that have been grouped.
<SpatiaLiteQueryServiceDescriptor>
<fields>codigo_parcela, group_concat("codigo_subparcela") as subparcelas, casttomulti( st_union(st_buffer(geometry,0.1))) as geometry
</fields>
<from>subparcelas g</from>
<groupBy>codigo_parcela</groupBy>
<dbURL>exampleQueryLayer.sqlite</dbURL>
<primaryKey>codigo_parcela</primaryKey>
<indexTable>idx_subparcelas_Geometry</indexTable>
</SpatiaLiteQueryServiceDescriptor>
Resulting Query:
SELECT codigo_parcela, group_concat("codigo_subparcela") as subparcelas, casttomulti( st_union(st_buffer(geometry,0.1))) as geometry u.d_uso_sigpac FROM subparcelas g
GROUP BY codigo_parcela;
Query with a Subquery
In this case we have a subquery within the from with which we filter the subparcelas whose geometry has an area greater than 10 hectares and then keep the entities of the inspeccion layer that intersect with those obtained in the previous subquery.
<SpatiaLiteQueryServiceDescriptor>
<fields>g.*</fields>
<from>inspeccion g, (SELECT geometry FROM subparcelas WHERE st_area(geometry)/10000 > 10) a
</from>
<where>st_intersects(g.geometry,a.geometry) = 1</where>
<dbURL>exampleQueryLayer.sqlite</dbURL>
<primaryKey>c_uuid</primaryKey>
<indexTable>idx_inspeccion_Geometry</indexTable>
</SpatiaLiteQueryServiceDescriptor>
Resulting Query:
SELECT g.*
FROM inspeccion g, (
SELECT geometry
FROM subparcelas
WHERE st_area(geometry)/10000 > 10
) a
WHERE st_intersects(g.geometry,a.geometry) = 1;