span8
span4
span8
span4
The FeatureReader,SpatialRelator and SpatialFilter transformers are great tools for performing generalized spatial queries on a database in 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.
Note: Though this example focuses on the PostGIS format,it is also applicable to other database formats.
spatialqueries-sqlexecutor.fmwt
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 viewed with Data Inspector.
Complete native spatial query workspace
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.
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
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.
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 which 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 toOGC Well Known Textand OGC Version (WKT/WKB Only) to1.2.This encodes the geometry in a structure appropriate for the database.
Setting Geometry Encoding and OGC Version (WKT/WKB Only)
5.Add a SQL Executor transformer
In workbench,you need to connect the base features you wish to use in your spatial query to the input port of the SQLExecutor transformer.Please 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:
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 toSQL Statementparameter,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 workbench:
SELECT * FROM fmedata2015."PostalAddress" as ap WHERE ST_Within(ap.geom,ST_GeomFromText('@Value(_geometry)',26910))=TRUE
The query returns the rows of the ‘PostalAddress' table where theST_Withinfunctionevaluates to true.
The ST_Within function takes two arguments:
(1) the first is the name of the geometry column (ap.geom) of the candidate features in the database table
(2) the second is 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).
Please examine the output in 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.
Result of the SQLExecutor spatial query with the Fairview neighborhood in the background
Transformer | CPU | Peak Memory |
SQLExecutor | 7.9 secs | 14.0 MB |
Clipper | 11.2 secs | 17.3 MB |
FeatureReader | 8.0 secs | 13.3 MB |
SpatialFilter | 11.6 secs | 15.9 MB |
Using the SQLExecutor or SQLCreator to issue commands to a database
Using the SQLExecutor to do a SQL Join
Writing custom data types to an Oracle table
Read Oracle Sequence Nextval and use in Counter
Performing spatial queries on database tables using the FeatureReader
Executing a Stored Procedure on Microsoft SQL Server with FME
Splitting SQL statements using the FME_SQL_DELIMITER directive
© 2019 亚搏在线Safe Software Inc |Legal