Build SQL Query

Feature Description

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

  • Query cities with population exceeding 1 million
  • Calculate average rainfall in a region over specified time periods
  • Update feature attributes in specific areas, such as modifying land ownership records

Supported data types for SQL Query include: Point, Line, Region, Text, CAD, Attribute Table, 3D Point, 3D Line, 3D Region, Network, Route Dataset.

Feature Entry

  • Spatial Analysis Tab->Query Group->SQL Query

Steps

  1. Click Spatial Analysis Tab -> Query Group-> SQL Query button to open the SQL Query dialog.
  2. Set data source and dataset information for the query. Use the Set Join Fields function on the right for cross-dataset queries.
  3. Build SQL Query expressions using either method:
    • Build Query Interactively: Check Expression and construct SQL statements by selecting logical operators, query fields, and criteria values in the expression builder.
    • Write SQL Expression: Check SQLEdit and manually input WHERE clause content in the expression box.
      Note:

      When using aggregates, directly enter expressions in Result Reserved Fields. E.g., to find maximum "SmArea" value, enter "Max(SmArea)" in Result Reserved Fields.

  4. Validate expression syntax by clicking Check Expression. Valid expressions show confirmation messages. For PostGIS data sources, click Details after error messages to view specific issues (not supported for UDBX and other databases).
  5. In Advanced Settings, configure sorting, grouping and result fields:
    • Sort Field: Arrange query results in ascending/descending order.
    • Group Field: Group records with identical values for statistical operations. Available when Query Mode is Query Properties. See Grouping Statistics Example.
    • Result Reserved Fields: Specify fields to retain in query results.
  6. Select query mode:
    • Query Spatial and Attributes: Output results as datasets with map/scene highlighting.
    • Query Properties: View results as attribute tables without dataset output.
  7. Choose display methods:
    • Browse Attribute Table: Open results in attribute table view.
    • Highlight in Map: Visually emphasize results in map view.
    • Highlight In Scene: Visually emphasize results in 3D scene.
  8. Optionally save results as new datasets.
  9. Click Query to execute. Error messages like "Failed to query. Please check the SQL syntax." will display for invalid expressions.

Related Topics

Building SQL Expression Queries

Common SQL Query Expressions

SQL Query Function Reference

Attribute Query Examples

Group Statistics Examples

Join Query Examples