Performing Spatial Queries on Database Tables Using the FeatureReader

Introduction

The FeatureReader, SpatialRelator, and SpatialFilter transformers are great tools for performing spatial queries on data in FME Workbench. They allow you to quickly select your query parameters without the need to fuss over the syntax of database-specific SQL.

This article describes how to use the FeatureReader to perform spatial and/or attribute queries. In the workbench, you need to connect the features you wish to use in your spatial query to the Initiator port of the FeatureReader transformer. If you're performing a spatial query, it is important that the geometry of the query feature is in the same coordinate system as those to be read using the FeatureReader. The CSMapReprojector or Reprojector transformers can be used for this purpose. The files section provides workspaces, files, and an example.
 

Step-by-step Instructions

The download spatialquery-featurereader.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.

1. Reading into FME Workbench

Add a Google KML Reader and set Dataset to VancouverNeighborhoods.kml in FME Form. Please note the different neighborhood polygons; one neighborhood will be used to create a spatial query in the FeatureReader.
 

VancouverNeighborhoods.kml in FME Workbench

2. Reproject

The source KML data must be in the same coordinate system as the database data. KML is always in the LL84 coordinate system. 

In the FME Workbench after creating a blank workspace, add a KML reader with OGC / Google KML as the format if not already specified. Run the workspace to see the data displayed in the Visual Preview. A feature can be selected in the Visual Preview which will populate the Feature information. You can then confirm the Coordinate System (LL84) listed under Geometry.



Also in the FME Workbench, the database of interest can be accessed to verify the projection used. Add a FeatureReader and for Format select PostGIS and set Dataset to Create Database Connection. Under Parameters... enter:

• Host: postgis.train.safe.com
• Port: 5432
• Database: fmedata
• Username: fmedata
• Password: fmedata
• Schema: public

In Feature Types to Read, select PostalAddress. To verify a reprojection may be needed, you can inspect the data by selecting a feature within the data. A feature can be selected in the Visual Preview which will populate the Feature information. You can then confirm the Coordinate System (EPSG:26910) listed under Geometry.



In FME Workbench, after adding a reader for the KML file, the file can be reprojected. 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 AddressPoints table.



3. Extract Bounding Box

The Tester extracts an area of interest to be used in the spatial query. In this example, one of the neighborhoods, Fairview, is used. Add a Tester and set the Left Value to @Value(NeighborhoodName), the Operator to =, and the Right Value to Fairview.

4. Connect the added FeatureReader

Connect the Tester to the FeatureReader by connecting it to the Passed output port on the Tester. The Database Connection should already be in place after reading the data and creating the connection. If the Database connection needs to be recreated the credentials are provided above.

Setup the query Constraints parameters of the FeatureReader. To perform a spatial query select a spatial predicate using the Spatial Filter parameter. The Connection in this example has been named PostalAddress since we are interested in the Postal Addresses.

After the Database Connection is established, you can use the WHERE Clause parameter to constrain the data using an attribute query. Note that this WHERE clause will be applied to all the tables you've selected to read. Try adding a WHERE Clause and compare the difference:

"Status" not like 'Retired'

Spatial Filter will be set to Contains so that only features which are within the Fairview neighborhood polygon will be returned (based on the Tester Parameters).
 

Once all the connections are made, the entire workspace can be rerun to ensure all the data has the same projection.

5. Inspect Result

When the result of the query along with the bounding box is seen in Visual Preview, we can see that the FeatureReader returns only the point features that are contained by the bounding area - the Fairview Neighborhood.
 

PostalAddress points within the Fairview neighborhood

Performance

Let the database do the work is a good adage to follow when ever possible. Having the database perform the spatial query and return only the query results is generally going to give better performance than reading the entire table and filtering in FME.

Another example showing how to use the SQLExecutor to perform a native spatial query.

Data Attribution

Data used in this tutorial originates from open data made available by the City of Vancouver , British Columbia. It contains information licensed under the Open Government License - Vancouver.