ArcSDEQuerier

Categories

Workflows

Performs queries on an ArcSDE spatial database. The queries can have both a spatial and a nonspatial component.

One query is issued to the ArcSDE database for each feature that enters the transformer.  The results of the query are then output to the output port that matches the table name, if it exists; otherwise, results are output to the OTHER port.

If the Mode parameter is set to Delete, the results of the query are deleted from ArcSDE before they are output from the transformer. If the Mode parameter is set to Update, the features in ArcSDE matching the query feature will be updated with that feature's complete set of attributes.

The query feature defines the geometry which will be used to define the spatial component of the query, unless the search method is SDE_NONE.  In that case, only an attribute query as defined by the WHERE clause will be executed.

An attribute named _table_name will be added to each result feature, specifying which table the result feature came from.

An attribute named _matched_records will be added to each query feature, specifying how many database rows the query matched.

Parameters

ArcSDE Connection

The ArcSDE server name.

The instance to which FME is to connect. The instance can also be of the form port:<port-number>, which is usually port:5151 (you may have to confirm this with your System Administrator).

This field identifies the SDE database from which features are retrieved. In SDE, the dataset is referred to as the Database. This field is required regardless of the underlying RDBMS of the SDE. Some RDBMSes, such as Oracle, do not require a value, whereas others, such SQLServer, do. For databases that do not require the value, the field will be ignored.

The username and password to access the database. This overrides the ODBC DSN settings.

The SDE version to which FME connects. The version must already exist and the current user must have privileges set so that it can access the version. If the VERSION_NAME directive is not used, then FME attempts to connect to SDE.DEFAULT. If there is no SDE schema, FME then attempts to connect to dbo.DEFAULT. If the name is not prefixed by the owner of the version, then it is assumed that the owner is the current user. This setting is only applicable when dealing with versioned tables.

If the Remove Table Qualifier parameter is set to Yes, then user names will not be included in table names when they are not required.

If the Table Name in Attribute parameter is blank, the set of tables to query is defined by the Tables parameter, which is set in the "Select Tables" panel.  Alternatively, if the Table Name in Attribute parameter is not blank, the tables to query are read from the specified attribute in the input features.  In this case, the tables to query should be specified as a colon-separated list.

ArcSDE Spatial Query Operators

The complete set of ArcSDE spatial query operators is supported, and each is described below:

No spatial filtering is done.  Any features matching the Where Clause parameter are output, and if no where clause was specified, then all features from each target table are output.

The envelope of the output feature overlaps or touches the envelope of the search feature.

The search feature shares at least one common point with the output feature.

The search feature and the output feature have intersecting line segments.

The search feature and the output feature share one or more common line segments.

The search feature and the output feature have line segments that intersect or have a common point.

The search feature and the output area feature edge touch (ET) or their areas intersect (AI).

The search feature and the output feature's area intersect.

The output feature and search feature have intersecting areas with no edge touching. One feature is therefore contained in the other.

The search feature is contained in the output feature. For area features, this is clear. If search feature is a line, then a linear feature will be output when the search feature path is included in output feature. If search feature is a point, then the search feature will be one of the output features vertices.

The output feature is contained by the search feature. If both features are linear features, then the output feature must lie on the search feature's path. Point features that lie on a search feature vertex are also output.

The returned feature must be an area feature that does not touch or share a vertex with the search feature. The returned feature contains the search feature.

The returned feature is contained within the search feature. The returned features cannot touch the edge of, or share a vertex with, the search feature.

The returned feature contains the first point of the search feature.

The returned feature has the same feature type and geometry. This is used to find duplicate data.

The Search Order parameter controls the manner in which the search is performed.  If Optimize is specified, then the SDE engine decides how to perform the search. If Attribute First is specified, then the attribute portion of the search is performed first and then the spatial component is performed on the set resulting from the attribute set. If Spatial First is specified, then the spatial search is performed first and then the attribute search is performed on the resulting set.  This is useful if the Optimize setting makes the wrong choice and you want to force the search to be performed in a different order.

  • Result Attributes Only: result feature attributes are based solely on the query results.
  • Keep Query Attributes if Conflict: result feature attributes are a combination of both the query results and the query feature's attributes. If there is a conflict, attribute values are taken from the query feature.
  • Keep Result Attributes if Conflict: result feature attributes are a combination of both the query results and query feature's attributes. If there is a conflict, attribute values are taken from the query results.
  • Result Geometry Only: result feature geometry is taken from the query results.
  • Query Geometry Only: result feature geometry is taken from the query feature.
  • Aggregate Query and Result Geometry: result feature geometry is an aggregate of the geometry from the query feature followed by the geometry from the query results.

Specifies whether or not duplicate features will be output from the transformer. If the Mode parameter is set to Update, this parameter also specifies whether duplicate updates will be performed.

Specifies whether or not the relationships between the query and result geometries should be computed. Refer to the SDE30QueryFactory factory documentation for more information.

Specifies whether a connection to SDE will remain open until the process running FME finishes. Open connections are shared by SDE Readers, SDE Writers and ArcSDEQuerier transformers when they have the same connection parameters. A non-persistent connection will be closed as soon as possible after all clients have finished with it.

A persistent connection is useful for workspaces that are published using FME Server.

Editing Transformer Parameters

Using a set of menu options, transformer parameters can be assigned by referencing other elements in the workspace. More advanced functions, such as an advanced editor and an arithmetic editor, are also available in some transformers. To access a menu of these options, click beside the applicable parameter. For more information, see Transformer Parameter Menu Options.

Defining Values

There are several ways to define a value for use in a Transformer. The simplest is to simply type in a value or string, which can include functions of various types such as attribute references, math and string functions, and workspace parameters. There are a number of tools and shortcuts that can assist in constructing values, generally available from the drop-down context menu adjacent to the value field.

Using the Text Editor

The Text Editor provides a convenient way to construct text strings (including regular expressions) from various data sources, such as attributes, parameters, and constants, where the result is used directly inside a parameter.

Text Editor

Using the Arithmetic Editor

The Arithmetic Editor provides a convenient way to construct math expressions from various data sources, such as attributes, parameters, and feature functions, where the result is used directly inside a parameter.

Arithmetic Editor

Conditional Values

Set values depending on one or more test conditions that either pass or fail.

Parameter Condition Definition Dialog

Content

Expressions and strings can include a number of functions, characters, parameters, and more.

When setting values - whether entered directly in a parameter or constructed using one of the editors - strings and expressions containing String, Math, Date/Time or FME Feature Functions will have those functions evaluated. Therefore, the names of these functions (in the form @<function_name>) should not be used as literal string values.

String Functions

These functions manipulate and format strings.

Special Characters

 A set of control characters is available in the Text Editor.

Math Functions

Math functions are available in both editors.
Date/Time Functions Date and time functions are available in the Text Editor.

Math Operators

These operators are available in the Arithmetic Editor.

FME Feature Functions

These return primarily feature-specific values.

FME Parameters

FME and workspace-specific parameters may be used.
Creating and Modifying User Parameters Create your own editable parameters.

Dialog Options - Tables

Transformers with table-style parameters have additional tools for populating and manipulating values.

Row Reordering

Enabled once you have clicked on a row item. Choices include:

  • Add a row
  • Remove a row
  • Move current row up one
  • Move current row down one
  • Move current row to top
  • Move current row to bottom

Cut, Copy, and Paste

Enabled once you have clicked on a row item. Choices include:

  • Cut a row - delete and copy to clipboard
  • Copy a row to the clipboard
  • Paste a row from the clipboard

Cut, copy, and paste may be used within a transformer, or between transformers.

Filter

Start typing a string, and the matrix will only display rows matching those characters. Searches all columns. This only affects the display of attributes within the transformer - it does not alter which attributes are output.

Import

Import populates the table with a set of new attributes read from a dataset. Specific application varies between transformers.

Reset/Refresh

Generally resets the table to its initial state, and may provide additional options to remove invalid entries. Behavior varies between transformers.

Note: Not all tools are available in all transformers.

FME Licensing Level

FME Professional Edition and above

FME Community

The FME Community is the place for demos, how-tos, articles, FAQs, and more. Get answers to your questions, learn from other users, and suggest, vote, and comment on new features.

Search for samples and information about this transformer on the FME Community.