Instructions for use

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

• Query cities with a population exceeding 1 million.
• Calculate the average rainfall in a specific area over a certain period.
• Update the attributes of features within a specific area, such as changing land ownership records.

The data types supported by SQL Query include: points, lines, polygons, text, CAD, attribute tables, 3D points, 3D lines, 3D polygons, networks, and route datasets.

Function Access

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

Operating Steps

1. Click the Spatial Analysis tab -> Query group-> SQL Query button to pop up the SQL Query dialog.
2. Set the data source and dataset information required for the query. If you need to perform an associated query with external data, use the Set Associated Fields function on the right to configure. If the dataset is an external vector file directly opened, such as SHP, GDB, or Geopackage, associated queries are not supported.
3. Build the SQL Query expression, with 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.
     
   • Build Query by Writing SQL Expression: Check the SQLEdit item, and manually enter the content of the WHERE clause in the SQL Expression in the expression input box.
     
     Notes:

     When using aggregates, 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 input box; if incorrect, it will prompt "Wrong expression". For wrong expressions, if the dataset's data source is a PostGIS database, 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).
5. In the advanced grouping, you can set the sorting, grouping, and result reserved fields for the search result.
   • Sort field: The attribute table of the search result will be arranged in ascending or descending order based on the sort field.
   • Group Field: Records with the same value on the specified field are grouped together, and then aggregates, mathematical operations, etc., are used to analyze the search result. When Query Mode is set to Query Properties, the Group Field can be configured. For details, refer to Group Statistics Example.
   • Result reserved fields: Set the selected fields to be retained in the search result.
6. Set the query mode, including Query Spatial and Attributes and Query Properties.
   • Query Spatial and Attributes: Supports outputting the search result as a dataset and simultaneously displaying it in the map or highlighting it in the scene.
   • Query Properties: Only views the search result as an attribute table and does not support outputting the result dataset.
7. Set the display method for results, providing the following three options:
   • Browse Attribute Table: Opens the search result in attribute table form.
   • Highlight in Map: Highlights the result in the map.
   • Highlight in Scene: Highlights the query result in the scene window.
8. Set save results, i.e., saving the search result 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

Building SQL Expression Query

Common Expressions for Building SQL Query

Function Descriptions for Building SQL Query

Attribute Query Example

Group Statistics Example

Associated Query Example