28.1.3. Database
28.1.3.1. Export to PostgreSQL
Exports a vector layer to a PostgreSQL database, creating a new relation. If a relation with the same name exists, it can be removed before the new relation is created. Prior to this a connection between QGIS and the PostgreSQL database has to be created (see eg Creating a stored Connection).
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
Layer to import |
|
[vector: any] |
Vector layer to add to the database |
Database (connection name) |
|
[string] |
Name of the database connection (not the database name). Existing connections will be shown in the combobox. |
Schema (schema name) Optional |
|
[string] Default: ‘public’ |
Name of the schema to store the data. It can be a new one or already exist. |
Table to import to (leave blank to use layer name) Optional |
|
[string] Default: ‘’ |
Defines a table name for the imported vector file. If nothing is added, the layer name will be used. |
Primary key field Optional |
|
[tablefield: any] |
Sets the primary key field from an existing field in the vector layer. A column with unique values can be used as Primary key for the database. |
Geometry column |
|
[string] Default: ‘geom’ |
Defines the name of the geometry column in the new PostGIS table. Geometry information for the features is stored in this column. |
Encoding Optional |
|
[string] Default: ‘UTF-8’ |
Defines the encoding of the output layer |
Overwrite |
|
[boolean] Default: True |
If the specified table exists, setting this option to
|
Create spatial index |
|
[boolean] Default: True |
Specifies whether to create a spatial index or not |
Convert field names to lowercase |
|
[boolean] Default: True |
Converts the field names of the input vector layer to lowercase |
Drop length constraint on character fields |
|
[boolean] Default: False |
Should length constraints on character fields be dropped or not |
Create single-part geometries instead of multi-part |
|
[boolean] Default: False |
Should the features of the output layer be single-part instead of multi-part. By default the existing geometries information are preserved. |
Outputs
The algorithm has no output.
Python code
Algorithm ID: qgis:importintopostgis
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.
28.1.3.2. Export to SpatiaLite
Exports a vector layer to a SpatiaLite database. Prior to this a connection between QGIS and the SpatiaLite database has to be created (see eg SpatiaLite Layers).
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
Layer to import |
|
[vector: any] |
Vector layer to add to the database |
File database |
|
[vector: any] |
The SQLite/SpatiaLite database file to connect to |
Table to import to (leave blank to use layer name) Optional |
|
[string] Default: ‘’ |
Defines the table name for the imported vector file. If nothing is specified, the layer name will be used. |
Primary key field Optional |
|
[tablefield: any] |
Use a field in the input vector layer as the primary key |
Geometry column |
|
[string] Default: ‘geom’ |
Defines the name of the geometry column in the new SpatiaLite table. Geometry information for the features is stored in this column. |
Encoding Optional |
|
[string] Default: ‘UTF-8’ |
Defines the encoding of the output layer |
Overwrite |
|
[boolean] Default: True |
If the specified table exists, setting this option to
|
Create spatial index |
|
[boolean] Default: True |
Specifies whether to create a spatial index or not |
Convert field names to lowercase |
|
[boolean] Default: True |
Convert the field names of the input vector layer to lowercase |
Drop length constraint on character fields |
|
[boolean] Default: False |
Should length constraints on character fields be dropped or not |
Create single-part geometries instead of multi-part |
|
[boolean] Default: False |
Should the features of the output layer be single-part instead of multi-part. By default the existing geometries information are preserved. |
Outputs
The algorithm has no output.
Python code
Algorithm ID: qgis:importintospatialite
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.
28.1.3.3. Package layers
Adds layers to a GeoPackage.
If the GeoPackage exists and Overwrite existing GeoPackage
is checked, it will be overwritten (removed and recreated).
If the GeoPackage exists and Overwrite existing GeoPackage
is not checked, the layer will be appended.
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
Input layers |
|
[vector: any] [list] |
The (vector) layers to import into the GeoPackage.
Raster layers are not supported. If a raster layer is
added, a
|
Overwrite existing GeoPackage |
|
[boolean] Default: False |
If the specified GeoPackage exists, setting this option to
|
Save layer styles into GeoPackage |
|
[boolean] Default: True |
Save the layer styles |
Save only selected features |
|
[boolean] Default: False |
If a layer has a selection, setting this option to |
Export related layers following relations defined in the project |
|
[boolean] Default: False |
If an input layer has relations set in the project,
setting this option to |
Destination GeoPackage |
|
[file] Default: |
Specify where to store the GeoPackage file. One of
|
Outputs
Label |
Name |
Type |
Description |
---|---|---|---|
Layers within new package |
|
[string] [list] |
The list of layers added to the GeoPackage. |
Python code
Algorithm ID: native:package
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.
28.1.3.4. PostgreSQL execute and load SQL
Allows a SQL database query to be performed on a PostgreSQL database connected to QGIS and loads the result. The algorithm won’t create a new layer: it is designed to run queries on the layer itself.
Example
Set all the values of an existing field to a fixed value. The SQL query string will be:
UPDATE your_table SET field_to_update=20;
In the example above, the values of the field
field_to_update
of the tableyour_table
will be all set to20
.Create a new
area
column and calculate the area of each feature with theST_AREA
PostGIS function.-- Create the new column "area" on the table your_table" ALTER TABLE your_table ADD COLUMN area double precision; -- Update the "area" column and calculate the area of each feature: UPDATE your_table SET area=ST_AREA(geom);
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
Database (connection name) |
|
[string] |
The database connection (not the database name). Existing connections will be shown in the combobox. |
SQL query |
|
[string] |
Defines the SQL query, for example
|
Unique ID field name |
|
[string] Default: id |
Sets the primary key field (a column in the result table) |
Geometry field name Optional |
|
[string] Default: ‘geom’ |
Name of the geometry column (a column in the result table) |
Outputs
Label |
Name |
Type |
Description |
---|---|---|---|
SQL layer |
|
[vector: any] |
The resulting vector layer to be loaded into QGIS. |
Python code
Algorithm ID: qgis:postgisexecuteandloadsql
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.
28.1.3.5. PostgreSQL execute SQL
Allows a SQL database query to be performed on a PostgreSQL database connected to QGIS. The algorithm won’t create a new layer: it is designed to run queries on the layer itself.
Example
Set all the values of an existing field to a fixed value. The SQL query string will be:
UPDATE your_table SET field_to_update=20;
In the example above, the values of the field
field_to_update
of the tableyour_table
will be all set to20
.Create a new
area
column and calculate the area of each feature with theST_AREA
PostGIS function.-- Create the new column "area" on the table your_table" ALTER TABLE your_table ADD COLUMN area double precision; -- Update the "area" column and calculate the area of each feature: UPDATE your_table SET area=ST_AREA(geom);
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
Database (connection name) |
|
[string] |
The database connection (not the database name). Existing connections will be shown in the combobox. |
SQL query |
|
[string] |
Defines the SQL query, for example
|
Outputs
No output is created. The SQL query is executed in place.
Python code
Algorithm ID: native:postgisexecutesql
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.
28.1.3.6. SpatiaLite execute SQL
Allows a SQL database query to be performed on a SpatiaLite database. The algorithm won’t create a new layer: it is designed to run queries on the layer itself.
See also
PostgreSQL execute SQL, Execute SQL
For some SQL query examples see PostGIS SQL Query Examples.
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
File Database |
|
[vector] |
The SQLite/SpatiaLite database file to connect to |
SQL query |
|
[string] Default: ‘’ |
Defines the SQL query, for example
|
Outputs
No output is created. The SQL query is executed in place.
Python code
Algorithm ID: native:spatialiteexecutesql
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.
28.1.3.7. SpatiaLite execute SQL (registered DB)
Allows a SQL database query to be performed on a SpatiaLite database connected to QGIS. The algorithm won’t create a new layer: it is designed to run queries on the layer itself.
See also
PostgreSQL execute SQL, Execute SQL
For some SQL query examples see PostGIS SQL Query Examples.
Parameters
Label |
Name |
Type |
Description |
---|---|---|---|
Database |
|
[enumeration] Default: not set |
Select a SQLite/SpatiaLite database connected to the current session |
SQL query |
|
[string] Default: ‘’ |
Defines the SQL query, for example
|
Outputs
No output is created. The SQL query is executed in place.
Python code
Algorithm ID: native:spatialiteexecutesqlregistered
import processing
processing.run("algorithm_id", {parameter_dictionary})
The algorithm id is displayed when you hover over the algorithm in the Processing Toolbox. The parameter dictionary provides the parameter NAMEs and values. See Using processing algorithms from the console for details on how to run processing algorithms from the Python console.