Building SQL Query

Feature Description

In GIS applications, SQL Query can extract, update, delete, or manage geographic data from spatial databases. The following are some specific examples of using SQL Query:

  • Query cities with a population exceeding one million.
  • Calculate the average rainfall in a specific region during a certain time period.
  • Update feature attributes in a specific area, such as changing land ownership records.

Supported data types for SQL Query include: point, line, surface, text, CAD, attribute table, 3D point, 3D line, 3D surface, network, route dataset.

Feature Entry

  • Spatial Analysis tab->Query group->SQL Query

Steps

  1. Click the Spatial Analysis tab -> Query group -> SQL Query button to open the SQL Query dialog.
  2. Set the data source and dataset information required for the query. If you need to perform a join query with external data, use the Set Join Fields function on the right to configure it. If the dataset is an external vector file directly opened, such as SHP, GDB, or Geopackage, join queries are not supported.
  3. Build the SQL Query expression, offering the following two methods:
    • Build Query Interactively: Check the Expression item, and simply select logical statements, query fields, logical operators, and judgment values in the expression input box below to complete the SQL Query expression.
    • Write SQL Expression to Build Query: Check the SQLEdit item, and manually enter the where clause of the SQL Expression in the expression input box.
      Note:

      When using aggregate functions, you need to directly enter the expression in the result reserved fields. For example, to query the maximum value of the 'SmArea' field, directly enter 'Max(SmArea)' in the result reserved fields.

  4. After building the expression, you can check its correctness. Click the Check expression button. If the expression is correct, it will prompt 'Expression is correct' below the expression input box; if incorrect, it will prompt 'Expression error'. When there is an expression error, if the data source of the dataset is a PostGIS database data source, you can click Details after the prompt to view specific errors (UDBX data sources and other database data sources do not support viewing error details yet).
  5. In the advanced group, you can set the sorting, grouping, and result reserved fields for the search results.
    • Sort field: The attribute table of the search results will be sorted in ascending or descending order based on the sort field.
    • Group Field: Records with the same value in the specified field are grouped together, and then statistics are performed on the search results using aggregate functions, math, etc. When the Query Mode is Query Properties, the Group Field can be set. For details, refer to Group Statistics Example.
    • Result Reserved Fields: Set the select fields to be retained in the search results.
  6. Set the query mode, including Query Spatial and Attributes and Query Properties.
    • Query Spatial and Attributes: Supports outputting the search results as a dataset and simultaneously displaying them highlighted on the map or in the scene.
    • Query Properties: View the search results only as an attribute table; outputting the result dataset is not supported.
  7. Set show results, providing the following three display methods:
    • Browse Attribute Table: Open the search results as an attribute table.
    • Highlight in Map: Highlight on the map.
    • Highlight in Scene: Highlight the query result in the scene window.
  8. Set save results, which saves the search results as a new dataset.
  9. Click the Query button in the dialog to execute the query. If there are errors in the expression or parameters, the output window will prompt "Failed to query. Please check the SQL syntax."

Related Topics

Build SQL Expression Query

Common Expressions for Building SQL Query

Function Descriptions for Building SQL Query

Attribute Query Example

Group Statistics Example

Join Query Example