斯潘8
span4
The FeatureReader,SpatialRelator和SpatialFilter Transformers是在工作台中的数据库上执行通用空间查询的重要工具。它们允许您快速地选择查询参数,而无需为特定于数据库的SQL语法操心。然而,如果您在非常大的数据集上执行空间查询,并且不害怕编写一点SQL,您可能会受益于使用SQLExecutor Transformer中的本机SQL语句实现空间查询的性能提升。
注意:虽然这个例子关注PostGIS格式,it is also applicable to other database formats.
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.该空间SQL查询应用于存储在PostGIS数据库中的邮政地址数据。以及使用数据检查器查看的结果。
完成本机空间查询工作区
下载spacequeries-sqleexecutor.fmt是完整的工作区。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.导航到并选择vancouverneighborhods.kml数据集。
2.重新设计
源数据必须与数据库数据在同一坐标系中。添加csmapreprojector,leave the Source Coordinate System as
三。Extract Bounding Box
Extract a bounding box to be used by the ST_Within function.在这个例子中,一个社区,费尔维尤is used as the bounding box.
4.几何编码
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,对于PostGIS数据库,OGC已知文本(WKT)适用于几何空间查询。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众所周知的文本and OGC Version (WKT/WKB Only) to1.2.This encodes the geometry in a structure appropriate for the database.
设置几何编码和OGC版本(仅限WKT/WKB)
5.添加SQL执行器转换器
In workbench,您需要将您希望在空间查询中使用的基本特性连接到SQLExecutor转换器的输入端口。请将geometryextractor输出端口连接到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
不同的数据库在其空间查询所需的语法上稍有不同。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 Statement参数,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:
从fmedata2015中选择*作为ap,其中st_在(ap.geom,st_geomfromtext('@value(_geometry)',26910))=true
The query returns the rows of the ‘PostalAddress' table where theST_Withinfunctionevaluates to true.
函数内的st_接受两个参数:
(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
变压器 | CPU | 峰值记忆 |
SQLExecutor | 7.9秒 | 14兆字节 |
剪刀 | 11.2 secs | 17.3兆字节 |
特技演员 | 8.0 secs | 13.3兆字节 |
SpatialFilter | 11.6秒 | 15.9 MB |
Using the SQLExecutor or SQLCreator to issue commands to a database
Using the SQLExecutor to do a SQL Join
如何使用本机空间数据库SQL命令在sqlexecutor/sqlcreator中执行空间查询
Read Oracle Sequence Nextval and use in Counter
Executing a Stored Procedure on Microsoft SQL Server with FME
© 2019 亚搏在线Safe Software Inc |Legal