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:

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 '&lt;' and '&gt;' 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:

ExampleQueryLayer.crtd

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 &gt; 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 &gt; 10

) a

WHERE st_intersects(g.geometry,a.geometry) = 1;