create_layers.rst

Creating Layers

.. only:: html

   .. contents::
      :local:

Layers can be created in many ways, including:

• empty layers from scratch
• layers from existing layers
• layers from the clipboard
• layers as a result of an SQL-like query based on one or many layers (:ref:`virtual layers <vector_virtual_layers>`)

QGIS also provides tools to import/export from/to different formats.

.. index:: Create new layers

.. index:: Shapefile, SpatiaLite, GPX

Creating new vector layers

QGIS allows you to create new layers in different formats. It provides tools for creating GeoPackage, Shapefile, SpatiaLite, GPX format and Temporary Scratch layers (aka memory layers). Creation of a :ref:`new GRASS layer <creating_new_grass_vectors>` is supported within the GRASS plugin.

.. index:: New GeoPackage layer

Creating a new GeoPackage layer

To create a new GeoPackage layer, press the :menuselection:`New GeoPackage Layer...` button in the :menuselection:`Layer --> Create Layer -->` menu or from the :guilabel:`Data Source Manager` toolbar. You can also create a new GeoPackage layer through the :guilabel:`Browser Panel` by selecting the :guilabel:`Create Database and Layer...`. The :guilabel:`New GeoPackage Layer` dialog will be displayed as shown in :numref:`figure_create_geopackage`.

Creating a New GeoPackage layer dialog

1. The first step is to indicate the database file location. This can be done by pressing the :guilabel:`...` button to the right of the :guilabel:`Database` field and select an existing GeoPackage file or create a new one. QGIS will automatically add the right extension to the name you provide.
2. Give the new layer / table a name (:guilabel:`Table name`)
3. Define the :guilabel:`Geometry type`. If not a geometryless layer, you can specify whether it should :guilabel:`Include Z dimension` and/or :guilabel:`Include M values`.
4. Specify the coordinate reference system using the button

To add fields to the layer you are creating:

1. Enter the :guilabel:`Name` of the field
2. Select the data :guilabel:`Type`. Supported types are :guilabel:`Text data`, :guilabel:`Whole number` (both integer and integer64), :guilabel:`Decimal number`, :guilabel:`Date` and :guilabel:`Date and time`, :guilabel:`Binary (BLOB)` and :guilabel:`Boolean`.
3. Depending on the selected data format, enter the :guilabel:`Maximum length` of values.
4. Click on the :guilabel:`Add to Fields List` button
5. Reproduce the steps above for each field you need to add
6. You can later change the fields order using the ^{Move Up} and ^{Move Down} buttons
7. Once you are happy with the attributes, click :guilabel:`OK`. QGIS will add the new layer to the legend, and you can edit it as described in section :ref:`sec_edit_existing_layer`.

By default, when creating a GeoPackage layer, QGIS generates a :guilabel:`Feature id column` called fid which acts as the primary key of the layer. The name can be changed. The geometry field, if availabe, is named geometry, and you can choose to :guilabel:`Create a spatial index` on it. These options can be found under the :guilabel:`Advanced Options` together with the :guilabel:`Layer identifier` (short human readable name of the layer) and the :guilabel:`Layer description`.

Further management of GeoPackage layers can be done with the :ref:`DB Manager <dbmanager>`.

Creating a new Shapefile layer

To create a new ESRI Shapefile format layer, press the :menuselection:`New Shapefile Layer...` button in the :menuselection:`Layer --> Create Layer -->` menu or from the :guilabel:`Data Source Manager` toolbar. The :guilabel:`New Shapefile Layer` dialog will be displayed as shown in :numref:`figure_create_shapefile`.

1. Provide a path and file name using the :guilabel:`...` button next to :guilabel:`File name`. QGIS will automatically add the right extension to the name you provide.
2. Next, indicate the :guilabel:`File encoding` of the data
3. Choose the :guilabel:`Geometry type` of the layer: No Geometry (resulting in a :file:`.DBF` format file), point, multipoint, line or polygon
4. Specify whether the geometry should have additional dimensions: :guilabel:`None`, :guilabel:`Z (+ M values)` or :guilabel:`M values`
5. Specify the coordinate reference system using the button

Creating a new Shapefile layer dialog

To add fields to the layer you are creating:

1. Enter the :guilabel:`Name` of the field
2. Select the data :guilabel:`Type`. Only :guilabel:`Decimal number`, :guilabel:`Whole number`, :guilabel:`Text data`, :guilabel:`Date`, and :guilabel:`Boolean` data types attributes are supported.
3. Depending on the selected data format, enter the :guilabel:`Length` and :guilabel:`Precision`.
4. Click on the :guilabel:`Add to Fields List` button
5. Reproduce the steps above for each field you need to add
6. You can later change the fields order using the ^{Move Up} and ^{Move Down} buttons
7. Once you are happy with the attributes, click :guilabel:`OK`. QGIS will add the new layer to the legend, and you can edit it as described in section :ref:`sec_edit_existing_layer`.

By default, a first integer id column is added but can be removed.

.. index:: New SpatiaLite layer

Creating a new SpatiaLite layer

To create a new SpatiaLite layer, press the :menuselection:`New SpatiaLite Layer...` button in the :menuselection:`Layer --> Create Layer -->` menu or from the :guilabel:`Data Source Manager` toolbar. The :guilabel:`New SpatiaLite Layer` dialog will be displayed as shown in :numref:`Figure_create_spatialite`.

Creating a New SpatiaLite layer dialog

1. The first step is to indicate the database file location. This can be done by pressing the :guilabel:`...` button to the right of the :guilabel:`Database` field and select an existing SpatiaLite file or create a new one. QGIS will automatically add the right extension to the name you provide.
2. Provide a name (:guilabel:`Layer name`) for the new layer
3. Define the :guilabel:`Geometry type`. If not a geometryless layer, you can specify whether it should :guilabel:`Include Z dimension` and/or :guilabel:`Include M values`.
4. Specify the coordinate reference system using the button.

To add fields to the layer you are creating:

1. Enter the :guilabel:`Name` of the field
2. Select the data :guilabel:`Type`. Supported types are :guilabel:`Text data`, :guilabel:`Whole number`, :guilabel:`Decimal number`, :guilabel:`Date` and :guilabel:`Date time`.
3. Click on the :guilabel:`Add to Fields List` button
4. Reproduce the steps above for each field you need to add
5. You can later change the fields order using the ^{Move Up} and ^{Move Down} buttons
6. Once you are happy with the attributes, click :guilabel:`OK`. QGIS will add the new layer to the legend, and you can edit it as described in section :ref:`sec_edit_existing_layer`.

If desired, you can select :guilabel:`Create an autoincrementing primary key` under the :guilabel:`Advanced Options` section. You can also rename the :guilabel:`Geometry column` (geometry by default).

Further management of SpatiaLite layers can be done with :ref:`DB Manager <dbmanager>`.

.. index:: New Mesh layer

Creating a new Mesh layer

To create a new Mesh layer, press the :menuselection:`New Mesh Layer...` button in the :menuselection:`Layer --> Create Layer -->` menu or from the :guilabel:`Data Source Manager` toolbar. The :guilabel:`New Mesh Layer` dialog will be displayed as shown in :numref:`figure_create_mesh`.

Creating a New Mesh layer dialog

1. The first step is to indicate the mesh file location. This can be done by pressing the :guilabel:`...` button to the right of the :guilabel:`File name` field and select an existing mesh file or create a new one.

2. Provide a name (:guilabel:`Layer name`), i.e. the name the layer is displayed with in the :guilabel:`Layers` panel

3. Select the :guilabel:`File format`: currently supported mesh file formats are 2DM Mesh File (*.2dm), Selafin File (*.slf) and UGRID (*.nc).

4. Indicate the :ref:`Coordinate Reference System <crs_selector>` to assign to the dataset

5. The above steps will generate an empty layer that you can afterwards digitize vertices and add dataset groups to. It's however also possible to initialize the layer with an existing mesh layer, i.e. populate the new layer with vertices or faces from the other. To do so:

   1. Check :guilabel:`Initialize Mesh using`
   2. and select either a :guilabel:`Mesh from the current project` or :guilabel:`Mesh from a file`. Informations on the selected mesh file are displayed for checkup.

   Note that only the frame of the mesh layer is transferred to the new layer; their datasets are not copied.

.. index:: New GPX layer

Creating a new GPX layer

To create a new GPX file:

1. Select :menuselection:`Create Layer -->` :menuselection:`New GPX Layer...` from the :menuselection:`Layer` menu.
2. In the dialog, choose where to save the new file, name it and press :guilabel:`Save`.
3. Three new layers are added to the :guilabel:`Layers Panel`:
   • a point layer to digitize locations (waypoints) with fields storing the name, elevation, comment, description, source, url and url name
   • a line layer to digitize sequences of locations that make up a planned route (routes) with fields storing the name, symbol, number, comment, description, source, url, url name
   • and a line layer to track the receiver’s movement over time (tracks) with fields storing the name, symbol, number, comment, description, source, url, url name.
4. You can now edit any of them as described in section :ref:`sec_edit_existing_layer`.

.. index:: New Temporary Scratch layer

Creating a new Temporary Scratch Layer

Temporary Scratch Layers are in-memory layers, meaning that they are not saved on disk and will be discarded when QGIS is closed. They can be handy for storing features you temporarily need or as intermediate layers during geoprocessing operations.

To create a new Temporary Scratch layer, choose the :menuselection:`New Temporary Scratch Layer...` entry in the :menuselection:`Layer --> Create Layer -->` menu or in the :guilabel:`Data Source Manager` toolbar. The :guilabel:`New Temporary Scratch Layer` dialog will be displayed as shown in :numref:`figure_create_temporary`. Then:

1. Provide the :guilabel:`Layer name`
2. Select the :guilabel:`Geometry type`. Here you can create a:
   • No Geometry type layer, served as simple table,
   • Point or MultiPoint layer,
   • LineString or MultiLine layer,
   • CompoundCurve, MultiCurve or NurbsCurve layer,
   • Polygon or MultiPolygon layer,
   • CurvePolygon or MultiSurface layer,
   • Triangle, PolyhedralSurface or TIN layer.
3. For geometric types, specify the dimensions of the dataset: check whether it should :guilabel:`Include Z dimension` and/or :guilabel:`Include M values`
4. Specify the coordinate reference system using the button.
5. Add fields to the layer. Note that unlike many formats, temporary layers can be created without any fields. This step is thus optional.
   1. Enter the :guilabel:`Name` of the field
   2. Select the data :guilabel:`Type`: :guilabel:`Text`, :guilabel:`Whole number`, :guilabel:`Decimal number`, :guilabel:`Boolean`, :guilabel:`Date`, :guilabel:`Time`, :guilabel:`Date & Time` and :guilabel:`Binary (BLOB)` are supported.
   3. Depending on the selected data format, enter the :guilabel:`Length` and :guilabel:`Precision`
   4. Click on the :guilabel:`Add to Fields List` button
   5. Repeat the steps above for each field you need to add
   6. You can later change the fields order using the ^{Move Up} and ^{Move Down} buttons
6. Once you are happy with the settings, click :guilabel:`OK`. QGIS will add the new layer to the :guilabel:`Layers` panel, and you can edit it as described in section :ref:`sec_edit_existing_layer`.

Creating a new Temporary Scratch layer dialog

You can also create prepopulated temporary scratch layers using e.g. the clipboard (see :ref:`paste_into_layer`) or as a result of a :ref:`Processing algorithm <processing_algs>`.

Tip

Permanently store a memory layer on disk

To avoid data loss when closing a project with temporary scratch layers, you can save these layers to any vector format supported by QGIS:

• clicking the indicator icon next to the layer;
• selecting the :guilabel:`Make permanent` entry in the layer contextual menu;
• using the :menuselection:`Export -->` entry from the contextual menu or the :menuselection:`Layer --> Save As...` menu.

Each of these commands opens the :guilabel:`Save Vector Layer as` dialog described in the :ref:`general_saveas` section and the saved file replaces the temporary one in the :guilabel:`Layers` panel. If the saved file name is different from the original one in the :guilabel:`Layers` panel, a message bar will pop up, and you can click the :guilabel:`Rename layer in layers panel` button to homogenize the naming.

.. index:: Save layer

Creating new layers from an existing layer

Layers (raster, vector and point cloud) can be saved in a different format and/or reprojected to a different coordinate reference system (CRS) using the :menuselection:`Layer --> Save As...` menu or right-clicking on the layer in the :guilabel:`Layers panel` and selecting:

• :menuselection:`Export --> Save As...` for raster and point cloud layers
• :menuselection:`Export --> Save Features As...` or :menuselection:`Export --> Save Selected Features As...` for vector layers.
• Drag and drop the layer from the layer tree to the PostgreSQL entry in the :guilabel:`Browser Panel`. Note that you must have a PostgreSQL connection in the :guilabel:`Browser Panel`.

Common parameters

The :guilabel:`Save Layer as...` dialog shows several parameters to change the behavior when saving the layer. Among the common parameters for raster and vector are:

• :guilabel:`File name`: the location of the file on the disk. It can refer to the output layer or to a container that stores the layer (for example database-like formats such as GeoPackage, SpatiaLite or Open Document Spreadsheets).
• :guilabel:`CRS`: can be changed to reproject the data
• :guilabel:`Extent`: restricts the extent of the input that is to be exported using the :ref:`extent_selector <extent_selector>` widget
• :guilabel:`Add saved file to map`: to add the new layer to the canvas

However, some parameters are specific to certain formats:

Raster specific parameters

Depending on the format of export, some of these options may not be available:

• :guilabel:`Output mode` (it can be raw data or rendered image)
• :guilabel:`Format`: exports to any raster format GDAL can write to, such as GeoTiff, GeoPackage, MBTiles, Geospatial PDF, SAGA GIS Binary Grid, Intergraph Raster, ESRI .hdr Labelled...
• :guilabel:`Resolution`
• :guilabel:`Create Options`: use advanced options (file compression, block sizes, colorimetry...) when generating files, either from the :ref:`predefined create profiles <gdal_createoptions>` related to the output format or by setting each parameter.
• :guilabel:`Pyramids` creation
• :guilabel:`VRT Tiles` in case you opted to :guilabel:`Create VRT`
• :guilabel:`No data values`

Saving as a new raster layer

Vector specific parameters

Depending on the format of export, some of these options may be available:

• :guilabel:`Format`: exports to any vector format GDAL can write to, such as GeoPackage, GML, ESRI Shapefile, AutoCAD DXF, ESRI FileGDB, Mapinfo TAB or MIF, SpatiaLite, CSV, KML, ODS, ...
• :guilabel:`Layer name`: available when the :guilabel:`File name` refers to a container-like format, this entry represents the output layer.
• :guilabel:`Encoding`
• :guilabel:`Save only selected features`
• :guilabel:`Select fields to export and their export options`: provides means to export fields with custom names and :ref:`form widget <configure_field>` settings:
  • Check rows under the :guilabel:`Name` column to choose fields to keep in the output layer, or press :guilabel:`Select All` or :guilabel:`Deselect All` buttons
  • Toggle the :guilabel:`Use aliases for exported name` checkbox to populate the :guilabel:`Export name` column with corresponding field aliases or reset to the original field name. Double-clicking a cell will also edit the name.
  • Depending on whether attribute form custom widgets are in use, you can :guilabel:`Replace all selected raw field values by displayed values`. E.g. if a value map widget is applied to a field, the output layer will contain the description values instead of the original values. The replacement can also be done on a field by field basis, in the :guilabel:`Replace with displayed values` column.
• :guilabel:`Persist layer metadata`: ensures that any layer :ref:`metadata <vectormetadatamenu>` present in the source layer will be copied and stored:
  • in the newly created layer, if the output is of GeoPackage format
  • as a :file:`.qmd` file along with the output layer, for other formats. Note that file-based formats supporting more than one dataset (e.g. SpatiaLite, DXF,...) may have unintended behavior.
• :guilabel:`Symbology export`: can be used mainly for DXF export and for all file formats who manage OGR feature styles (see note below) as DXF, KML, tab file formats:
  • No symbology: default style of the application that reads the data
  • Feature symbology: save style with OGR Feature Styles (see note below)
  • Symbol Layer symbology: save with OGR Feature Styles (see note below) but export the same geometry multiple times if there are multiple symbology symbol layers used
  • A Scale value can be applied to the latest options

Note

OGR Feature Styles are a way to store style directly in the data as a hidden attribute. Only some formats can handle this kind of information. KML, DXF and TAB file formats are such formats. For advanced details, you can read the OGR Feature Styles specification document.

• :guilabel:`Geometry`: you can configure the geometry capabilities of the output layer
  • :guilabel:`geometry type`: keeps the original geometry of the features when set to Automatic, otherwise removes or overrides it with any type. You can add an empty geometry column to an attribute table and remove the geometry column of a spatial layer.
  • :guilabel:`Force multi-type`: forces creation of multi-geometry features in the layer.
  • :guilabel:`Include z-dimension` to geometries.

Tip

Overriding layer geometry type makes it possible to do things like save a geometryless table (e.g. :file:`.csv` file) into a shapefile WITH any type of geometry (point, line, polygon), so that geometries can then be manually added to rows with the ^{Add Part} tool.

• :guilabel:`Datasource Options`, :guilabel:`Layer Options` or :guilabel:`Custom Options` which allow you to configure advanced parameters depending on the output format. Some are described in :ref:`supported_format` but for full details, see the GDAL driver documentation. Each file format has its own custom parameters, e.g. for the GeoJSON format have a look at the GDAL GeoJSON documentation.

Saving as a new vector layer

.. index:: Overwrite file, Append features

When saving a vector layer into an existing file, depending on the capabilities of the output format (Geopackage, SpatiaLite, FileGDB...), the user can decide whether to:

• overwrite the whole file
• overwrite only the target layer (the layer name is configurable)
• append features to the existing target layer
• append features, add new fields if there are any.

For formats like ESRI Shapefile, MapInfo .tab, feature append is also available.

Point Cloud specific parameters

Similar to raster and vector layers, point cloud layers can be saved in a different format and/or reprojected to a different coordinate reference system (CRS). This allows you to export a point cloud layer to vector or point cloud formats. Current supported formats are: Temporary scratch (memory layer), GeoPackage, ESRI Shapefile, DXF and LAS/LAZ point cloud. In addition to the common parameters listed above, exporting point cloud layers includes the following options:

• :guilabel:`Filter by Polygon Layer`: Allows you to filter the point cloud data based on a polygon layer.
• :guilabel:`Elevation Range`: Enables filtering of the point cloud data based on a specified Z range.
• :guilabel:`Limit number of points`: Provides an option to limit the number of points exported from the point cloud layer.

Saving a point cloud layer as a new layer

.. index:: DXF Export

Creating new DXF files

Besides the :guilabel:`Save As...` dialog which provides options to export a single layer to another format, including :file:`*.DXF`, QGIS provides another tool to export multiple layers as a single DXF layer. It's accessible in the :menuselection:`Project --> Import/Export --> Export Project to DXF...` menu.

Exporting a project to DXF dialog

In the :guilabel:`DXF Export` dialog:

1. Provide the destination file.

2. Choose the symbology mode and scale (see the :ref:`OGR Feature Styles <ogr_features_note>` note), if applicable.

3. Select the data :guilabel:`Encoding`.

4. Select the :guilabel:`CRS` to apply: the selected layers will be reprojected to the given CRS.

5. Select the layers to include in the DXF files either by checking them in the table widget or automatically picking them from an existing :ref:`map theme <map_themes>`. The :guilabel:`Select All` and :guilabel:`Deselect All` buttons can help to quickly set the data to export.

   For each layer, you can:

   • Override the output layer name without altering the original project layer. For this, click on the :guilabel:`Layer` name in the dialog and write the output name to use.
   • :guilabel:`Output layer attribute`: Choose whether to export all the features in a single DXF layer or rely on a field whose values are used to split the features into layers in the DXF output. In the latter case, each layer will take its name from the corresponding field value.
   • :guilabel:`Allow data defined symbol blocks`:
   • :guilabel:`Maximum number of symbol blocks`: creates symbol blocks up to the specified limit, starting with the ones containing the highest number of references. The other symbols are written as they are. -1 means no limitation.

Optionally, you can also choose to:

• :guilabel:`Use the layer title as name if set` instead of the layer name itself: the title is taken from the :ref:`metadata <vectormetadatamenu>` or :ref:`server <vectorservermenu>` properties of the layer;
• :guilabel:`Export features intersecting the current map extent`;
• :guilabel:`Force 2d output (eg. to support polyline width)`;
• :guilabel:`Export label as MTEXT elements` or TEXT elements;
• :guilabel:`Use only selected features`;
• :guilabel:`Export lines with zero width`: all the lines are exported with minimal width 0 (hairline) if enabled. This helps keep the lines minimal in the file regardless of the zoom level, and can be handy for doing further CAD-editing with the exported dxf, especially if there are many features next to each other on the map.

Note

The precedence for defining the output layer name is as follows:

1. The field value from :guilabel:`Output layer attribute`
2. The overridden name in :guilabel:`Layer` column
3. The :guilabel:`Use the layer title as name if set` option
4. The layer name

Current settings defined in the :guilabel:`DXF Export` dialog may be stored in an XML file for reusing them in other sessions. For this, the :guilabel:`Settings` combo box has two options: :guilabel:`Load Settings from File...` and :guilabel:`Save Settings to File...`.

Creating new layers from the clipboard

Features that are on the clipboard can be pasted into a new layer. To do this, Select some features, copy them to the clipboard, and then paste them into a new layer using :menuselection:`Edit --> Paste Features as -->` and choosing:

• :guilabel:`New Vector Layer...`: the :guilabel:`Save vector layer as...` dialog appears (see :ref:`general_saveas` for parameters)
• or :guilabel:`Temporary Scratch Layer...`: you need to provide a name for the layer

A new layer, filled with selected features and their attributes is created (and added to map canvas).

Note

Creating layers from the clipboard is possible with features selected and copied within QGIS as well as features from another application, as long as their geometries are defined using well-known text (WKT).

Creating SQL Query Layers

Beside loading an entire layer in a project or creating new layers from scratch or pasted features, you can also load layers generated on the fly from other layer(s). They are the result of a more or less advanced filter using SQL language, applied to stored layers regardless of their data provider or their availability in the active project. This means that temporary layers are not compatible with this feature. Depending on the provider, one or more layers can be used to write the query. The generated layer remains dependent on the layer(s) involved in the query and is loaded with the ^Filter icon next to it.

This feature is accessible:

• From the :guilabel:`Browser` panel, right-click on a supported data (plain layer, database connection, schema or table) and select Execute SQL... entry in the contextual menu.
• From the :guilabel:`Layers` panel, select a loaded layer, right-click and select Execute SQL... entry in the contextual menu.

This opens a window with a central text box widget where you can write SQL queries.

Executing SQL queries in the Execute SQL window

At the top of the dialog, a toolbar provides a set of tools to create, store and manipulate your queries:

• ^{Open Queries...}: fills the text editor widget with contents from an existing :file:`.sql` file

• ^{Save Queries...} and ^{Save Queries as...} help you store the written query to a :file:`.sql` file

• Statements of the query can be adjusted using the ^Cut, ^Copy and ^Paste buttons. Likewise, you can ^Undo or ^Redo your changes.

• The ^{Find & Replace} enables, at the bottom of the dialog, a widget allowing to look for a particular string in your SQL code. The search can be case sensitive, affect partial or whole word, rely on a regular expression. It is then possible to navigate through the found strings, replacing them one by one or all in a row.

• Use ^{Clear SQL Editor} to wipe the text editor.

• The ^History button opens a dialog storing previously run queries. More at :ref:`sql_history`.

• As previously mentioned, queries can be saved as an :file:`.sql` file stored on disk. Using the ^{Store Current Query} button, they can also be stored:

  • In the active :guilabel:`User Profile`, in the associated :file:`QGIS3.ini` file, thus accessible in subsequent projects
  • or as part of the :guilabel:`Current Project`.

  Clicking an entry from the stored queries drop-down menu inserts that query in the expression being written. The stored entry can also be deleted, from the drop-down menu.

Running and loading queries as layer

In the central part of the :guilabel:`Execute SQL` dialog, you build your query using the SQL syntax supported by the underlying provider (e.g., OGR, GeoPackage, PostgreSQL).

By default, if opened from a layer entry, a sample SQL query is provided. Editing tools to select, cut, copy, paste, undo and redo are available as well from the contextual menu.

Tip

Finding the right SQL syntax for your dataset

To find the appropriate SQL dialect for your dataset, press the :guilabel:`...` button next to the :guilabel:`Subset filter` option in the bottom part of the dialog.

When ready, pressing the :guilabel:`Execute` button below the text area will run the query. It is possible to highlight a portion of the SQL to only execute that portion when pressing Ctrl+R or clicking the :guilabel:`Execute selection` button. Use the :guilabel:`Stop` button to abort the execution.

A successful query execution will display a table at the bottom of the dialog with returned features. You can select specific cells in the result set. Use the Ctrl+C shortcut to copy the selected cells to the clipboard. The copied data is available as a formatted table. This allows you to paste the data into other applications, such as spreadsheet where it will show up as a table.

The returned table can be loaded in QGIS expanding the :guilabel:`Load as new layer` group and configuring parameters (their availability depends on the layer provider):

• :guilabel:`Column(s) with unique values` to indicate primary key of the data,
• :guilabel:`Geometry column`: check the box to load the layer as a spatial one, and indicate the geometry field name
• :guilabel:`Subset filter`: allows to filter the results using a WHERE clause. It can be written in the text box, or built using the :ref:`Query builder <vector_query_builder>` after you pressed :guilabel:`...`. Make sure to use fields that are available in the SQL layer.
• :guilabel:`Avoid selecting by feature ID`
• :guilabel:`Layer name` in the project

At any time, in the :guilabel:`Layers panel`, you can adjust the output layer by right-clicking and selecting :guilabel:`Update SQL expression...`. The :guilabel:`Update SQL` dialog opens, prefilled with the applied query that you can edit as you wish. Once ready, press :guilabel:`Update layer` and the layer will be modified in-place.

Query History

The :guilabel:`Query History` dialog displays all the previously run queries, sorted by date and data provider type. It is accessible from the :menuselection:`Database -->` :guilabel:`Query History…` menu, or pressing the ^History button from the :guilabel:`Execute SQL` dialog.

History of the executed SQL queries

At the top of the dialog, a search box can be used to filter out the queries. The search is done through every strings displayed in the query history. If the dialog is opened from within the :guilabel:`Execute SQL` dialog, the search box is preceded by the :ref:`Execute SQL toolbar <execute_sql_toolbar>`.

Below, collapsible and sorted date entries list their related queries. Each query is identifiable by its SQL command, and when expanded, reveals:

• The :guilabel:`Connection`: the source of the data the query is based on
• The :guilabel:`Row count`: number of returned features
• The :guilabel:`Execution time`

Hover over an entry and a tooltip displays the full query. Likewise, selecting an entry also displayed it in the bottom part of the dialog. You can interact with the text, copying all or part of it.

Double-click the SQL command entry and the :guilabel:`Execute SQL` dialog opens, filled with the clicked query. Any query being written is overwritten. Right-click the SQL command entry and you can either:

• :guilabel:`Load SQL Command…`, loads the target command into the :guilabel:`Execute SQL` dialog, replacing any existing query. It is the same as double clicking the entry.
• :guilabel:`Copy SQL Command` and paste it wherever you want.

.. index:: Virtual layers

Creating virtual layers

A virtual layer is a special kind of vector layer. It allows you to define a layer as the result of an SQL query involving any number of other vector layers that QGIS is able to open. Virtual layers do not carry data by themselves and can be seen as views.

To create a virtual layer, open the virtual layer creation dialog by:

• choosing the :guilabel:`Add/Edit Virtual Layer` entry in the :menuselection:`Layer --> Add Layer -->` menu;
• enabling the :guilabel:`Add Virtual Layer` tab in the :guilabel:`Data Source Manager` dialog;
• or using the :guilabel:`DB Manager` dialog tree.

The dialog allows you to specify a :guilabel:`Layer name` and an SQL :guilabel:`Query`. The query can use the name (or id) of loaded vector layers as tables, as well as their field names as columns.

For example, if you have a layer called airports, you can create a new virtual layer called public_airports with an SQL query like:

SELECT *
FROM airports
WHERE USE = "Civilian/Public"

The SQL query will be executed, regardless of the underlying provider of the airports layer, even if this provider does not directly support SQL queries.

Create virtual layers dialog

Joins and complex queries can also be created, for example, to join airports and country information:

SELECT airports.*, country.population
FROM airports
JOIN country
ON airports.country = country.name

Note

It's also possible to create virtual layers using the SQL window of :ref:`dbmanager`.

Embedding layers for use in queries

Besides the vector layers available in the map canvas, the user can add layers to the :guilabel:`Embedded layers` list, which can be used in queries without the need to have them showing in the map canvas or Layers panel.

To embed a layer, click :guilabel:`Add` and provide the :guilabel:`Local name`, :guilabel:`Provider`, :guilabel:`Encoding` and the path to the :guilabel:`Source`.

The :guilabel:`Import` button allows adding layers in the map canvas into the Embedded layers list. Those layers can then be removed from the Layers panel without breaking existent queries.

Supported query language

The underlying engine uses SQLite and SpatiaLite to operate.

It means you can use all of the SQL your local installation of SQLite understands.

Functions from SQLite and spatial functions from SpatiaLite can also be used in a virtual layer query. For instance, creating a point layer out of an attribute-only layer can be done with a query similar to:

SELECT id, MakePoint(x, y, 4326) as geometry
FROM coordinates

:ref:`Functions of QGIS expressions <functions_list>` can also be used in a virtual layer query.

To refer the geometry column of a layer, use the name geometry.

Contrary to a pure SQL query, all the fields of a virtual layer query must be named. Don't forget to use the as keyword to name your columns if they are the result of a computation or a function call.

Performance issues

With default parameters, the virtual layer engine will try its best to detect the type of the different columns of the query, including the type of the geometry column if one is present.

This is done by introspecting the query when possible or by fetching the first row of the query (LIMIT 1) as a last resort. Fetching the first row of the result just to create the layer may be undesirable for performance reasons.

The creation dialog parameters:

• :guilabel:`Unique identifier column`: specifies a field of the query that represents unique integer values that QGIS can use as row identifiers. By default, an autoincrementing integer value is used. Defining a unique identifier column speeds up the selection of rows by id.
• :guilabel:`No geometry`: forces the virtual layer to ignore any geometry field. The resulting layer is an attribute-only layer.
• Geometry :guilabel:`Column`: specifies the name of the geometry column.
• Geometry :guilabel:`Type`: specifies the type of the geometry.
• Geometry :guilabel:`CRS`: specifies the coordinate reference system of the virtual layer.

Special comments

The virtual layer engine tries to determine the type of each column of the query. If it fails, the first row of the query is fetched to determine column types.

The type of a particular column can be specified directly in the query by using some special comments.

The syntax is the following: /*:type*/. It has to be placed just after the name of a column. type can be either int for integers, real for floating point numbers or text.

For instance:

SELECT id+1 as nid /*:int*/
FROM table

The type and coordinate reference system of the geometry column can also be set thanks to special comments with the following syntax /*:gtype:srid*/ where gtype is the geometry type (point, linestring, polygon, multipoint, multilinestring or multipolygon) and srid an integer representing the EPSG code of a coordinate reference system.

Use of indexes

When requesting a layer through a virtual layer, the source layer indices will be used in the following ways:

• if an = predicate is used on the primary key column of the layer, the underlying data provider will be asked for a particular id (FilterFid)
• for any other predicates (>, <=, !=, etc.) or on a column without a primary key, a request built from an expression will be used to request the underlying vector data provider. It means indexes may be used on database providers if they exist.

A specific syntax exists to handle spatial predicates in requests and triggers the use of a spatial index: a hidden column named _search_frame_ exists for each virtual layer. This column can be compared for equality to a bounding box. Example:

SELECT *
FROM vtab
WHERE _search_frame_=BuildMbr(-2.10,49.38,-1.3,49.99,4326)

Spatial binary predicates like ST_Intersects are sped up significantly when used in conjunction with this spatial index syntax.