Performing Native Spatial Queries on Database Tables Using the SQLExecutor

Introduction

The FeatureReader, SpatialRelator, and SpatialFilter transformers are great tools for performing generalized spatial queries on a database in FME Workbench. They allow you to quickly select your query parameters without the need to fuss over the syntax of database-specific SQL. However, if you are performing spatial queries on extremely large datasets and you aren’t afraid of writing a little bit of SQL, you may benefit from the performance gains of implementing your spatial query with a native SQL statement in the SQLExecutor transformer.

Overview

A section of neighborhood polygon data is extracted and becomes the bounding box for a spatial query. The geometry of the bounding box is extracted as OGC Well Known Text, and a SQL statement is created. This spatial SQL query is applied to postal address data stored in a PostGIS database, and the results are viewed with FME Data Inspector.

Complete native spatial query workspace

Step-by-step Instructions

The download spatialqueries-sqlexecutor.fmwt is the complete workspace. If you would like to create the workspace yourself, please download vancouverneighborhoods.kml and follow the steps below to create it.

Part 1: Create a Bounding Box

1. Source Data

Select Reader > Add Reader and enter KML as the format. Navigate to and select the VancouverNeighborhoods.kml dataset.

2. Reproject

The source data must be in the same coordinate system as the database data. Add a CsmapReprojector, leave the Source Coordinate System as <Read from feature> and set the Destination Coordinate System to UTM83-10. UTM83-10 matches the coordinate system of the data in the PostGIS database.

3. Extract Bounding Box

Extract a bounding box to be used by the ST_Within function. In this example, one of the neighborhoods, Fairview, is used as the bounding box.

Part 2: Set Up an SQL Statement

4. Geometry Encoding

It is important that the geometry is stored in a database-friendly format for spatial queries. This means that the geometry needs to be structured in a way that is appropriate for the database format being used. For example, for a PostGIS database, OGC Well-Known Text (WKT) is appropriate for a geometry spatial query. In the image of an example workspace below, the geometry has been extracted to OGC Well-Known Text (WKT) format with a GeometryExtractor transformer before being passed to the SQLExecutor.

Please add a GeometryExtractor, setting the Geometry Encoding to OGC Well Known Text and OGC Version (WKT/WKB Only) to 1.2. This encodes the geometry in a structure appropriate for the database.

5. Add a SQLExecutor transformer

In FME Workbench, you need to connect the base features you wish to use in your spatial query to the input port of the SQLExecutor transformer. Connect the GeometryExtractor output port to the input port of a SQLExecutor.

In the parameters dialog of the SQLExecutor transformer fill in the ‘Reader’ section parameters with the format and connection details of the database you wish to query.

Please enter the following PostGIS connection details:

• Host: postgis.train.safe.com
• Port: 5432
• Database: fmedata
• Username: fmedata
• Password: fmedata
• Schemas for Table Listing: public

6. Compose a Native SQL Statement

Various databases differ slightly in the required syntax of their spatial queries. The best way to get started is to read the documentation provided by the spatial database that you are using.

In the SQLExecutor, under the ‘Parameters’ section click on the 3 dots next to SQL Statement parameter, this will open up a text editor that allows you to compose an SQL statement. Please enter the following SQL as the SQL Statement. This SQL statement queries features from a PostGIS database based on the geometry of the bounding box that was created in FME Workbench:

SELECT * FROM public."PostalAddress" as ap 
WHERE ST_Within(ap.geom,ST_GeomFromText('@Value(_geometry)',26910))=TRUE

The query returns the rows of the ‘PostalAddress’ table where the ST_Within function evaluates to true.

The ST_Within function takes two arguments:

1. The name of the geometry column (ap.geom) of the candidate features in the database table
2. The base geometry generated by the ST_GeomFromText function, which itself takes two arguments: the value of the WKT geometry attribute of the input bounding box feature and a Spatial Reference ID (SRID).

Results

Please examine the output in FME Data Inspector. When the result of the query along with the bounding box is sent to the Inspector, we can see that the SQLExecutor returns only the point features that fall within the box.

AI Assist 

New to FME 2023.1, the AI Assist (Tech Preview) functionality is available in the SQL Statement Editor to help new and experienced users build out SQL Statements. Schema information from your database can also be sent to the AI service to return a result containing the correct table and column names.

How to use the AI Assist with SQL
Starting in FME 2023.1, you can access the AI Assist through the SQL Statement Editor. It will appear as a button in the bottom left corner of the window.


Using the sample screenshot below, we will provide an overview of each of the components of the AI Assist window. 

1.  SQL Query Description
This is where you can specify your request in plain English. For example:
"Get all records from the PostalAddress table that are within the city of Vancouver"

This would return a SQL statement such as: 

  SELECT DISTINCT * FROM “public”.”PostalAddress” WHERE “PostalCity” = Vancouver

Send Database Schema to AI
When enabled, FME scans your database schema to provide a more accurate result from the AI Assist. The resulting SQL is tailored to the tables and columns in your database instead of providing generic naming. 

Tip: The Send database schema to AI parameter can prolong the time it takes to return an SQL query from the AI Assist. This is because FME must parse through all the database tables and column names and send them to the AI. The number of tables and columns in each table will dictate how long it takes FME to scan your database schema.

2. Status
Provides an overview of the status of the AI request and whether it succeeded or not.

3. AI Results
The results from the description after it has been run through the AI data model. This will include a functional SQL query to use in the SQL editor. If Send database schema to AI is enabled, then the query may include accurate table and column names.

4. Explanation
To help users understand the AI results, each object within the SQL query is broken down with an explanation.

Disclaimers
The AI Assist is still in Tech Preview and is subject to change in future FME versions.
For legal information regarding the AI Assist, please see our AI Terms of Use.
If you have any feedback regarding the use of AI Assist in the SQL Editor, please comment on this article or contact us at info@safe.com.