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.
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.It is important that the geometry is stored in a database-friendly format for these queries;In the example workspace below the geometry has been extracted to OGC Well-Known Text (WKT) format with a GeometryExtractor transformer before being passed to the SQL executor.
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.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.
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.
Here is an example of an SQL statement that queries features from a PostGIS database based on the geometry of an input bounding box that was created in workbench:
SELECT *
FROM fme."ADDRESS_POINTS" as ap
WHERE ST_Within(ap.geom,ST_GeomFromText('@Value(_geometry)',2277))=TRUE
The query returns the rows of the ‘ADDRESS_POINTS' table of the 'fme' database where the ST_Within function evaluates to true.The ST_Within function takes two arguments: the first is the name of the geometry column (ap.geom) of the candidate features in the database table,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 four digit Spatial Reference ID (SRID).
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.
Performing native spatial queries on database tables using the SQLExecutor
Using the SQLExecutor or SQLCreator to issue commands to a database
Using the SQLExecutor to do a SQL Join
Splitting SQL statements using the FME_SQL_DELIMITER directive
The SQLCreator and SQLExecutor Transformers
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
Executing SQL commands within a workspace using the SQLExecutor
© 2019 亚搏在线Safe Software Inc |Legal