Several transformers provide the ability to attach data from an external database to features as they flow from their source to their destination.
The Joiner transformer allows you to query a database to retrieve attributes associated with a feature. One or more feature attributes are used as keys to join to one or more columns in the database, and the values from the matching database table row (or rows) are added as feature attributes. 1 to 0 or 1, 1 to 1, and 1 to many relationships are supported.
The external database can be an Access MDB file, an ODBC connection, MySQL, PostGRES, PostGIS, Oracle 7, 8, 8i, or 9i, a dBase III (dbf) file, or a CSV file.
A wizard is used to set the database type, and connection parameters, and key field properties. If a 1 to many relationship is defined, a list name must be specified to hold the attribute data retrieved from the database. Each row retrieved is another entry in the list. Subsequent transformers can perform operations on the resulting list.
An optional prefix can be added to all the attributes retrieved from the database to allow repeated joins to the same database table to store their results into unique attributes.
The SQL Executor transformer executes an arbitrary SQL statement against a database. If the SQL statement resulted in a row (or rows) being returned, the attributes from the row are added to the feature. This transformer can also be used in conjunction with a Creator transformer to perform database operations such as DROPping or CREATEing tables at the beginning or end of a translation.
This transformer operates against MySQL, PostGRES, PostGIS, ODBC connections, or directly on Oracle databases.
The ArcSDEQuerier performs queries on an ArcSDE spatial database. The queries can have both a spatial as well as nonspatial component to them.
One query is issued to the ArcSDE database for each feature that enters the transformer. The results of the query, which could be many, many features, are then output. If the query mode is <delete>, the results of the query are deleted from ArcSDE before they are output from the transformer).
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.
Performs spatial queries against an Oracle Spatial database. The queries can have both a spatial as well as nonspatial component to them. One query is issued to the Oracle database for each feature that enters the transformer. The results of the query are then output.
The query feature’s bounding box is used to define the geometry that will be used to define the spatial component of the query.
Database Readers (Oracle, Access MDB, ODBC, dBase, CSV)
The database reader can be used to read rows from nonspatial databases, such as Microsoft Access, ODBC, and Oracle.
Database reader feature type properties allow you to specify a complete and arbitrary SQL SELECT statement to be used to determine the features that will be returned. Click the Feature Type Parameters tab. The SELECT statement can involve joins to other tables in additional to filtering data from a single table. As well, the parameters tab allows for a WHERE clause to filter the features that are extracted.
If the attribute data is held in CSV or dBase III files, then the CSV or dBase readers can be used to directly read the data. No filtering via a WHERE clause is available in these cases.
Using a database reader in conjunction with another reader of feature data and the FeatureMerger transformer, can in some situations be more efficient than using the Joiner or SQLExecutor. This is because the Joiner and SQLExecutor make queries of the database for each feature that passes through them. However, if the relevant parts of the database can be identified ahead of time and read in a single query by a database reader, and the resulting attribute features routed to a FeatureMerger transformer as SUPPLIER features, they can efficiently be paired up to the feature data acting as a REQUESTOR in the FeatureMerger.