PostGIS Reader Parameters

Select an existing connection, or Add Database Connection to define a new connection.

The new connection can be made visible only to the current user, or can be shared among multiple users.

This specifies the machine running the PostGIS/PostgreSQL ORDBMS as either an IP address or host name. The database must have proper permissions and be set up to accept TCP/IP connections if connecting from a remote machine.

When connecting remotely, this specifies the TCP/IP port on which to connect to the ORDBMS service. The default port is 5432.

This specifies the name of the PostGIS-enabled PostgreSQL database. The database must exist in the ORDBMS.

Enter the username and password to access the service.

Schemas are used to organize tables. This parameter provides the list of viewable schemas.

If this parameter is left blank, the Table List dialog will only show choices from the search path for the current user. Selecting specific schemas here will cause tables from those schemas to be shown instead.

This parameter also specifies the schemas to fetch candidate tables when merging feature types. If it is not set, candidate tables are fetched based on the search path for the current user.

After you have specified the database connection, click the Browse button to select tables for import. A connection window appears while the system compiles a table from the database.

Once the table list appears, you can select one or more tables, and then click OK to dismiss the window. The table name(s) will appear in the Table List field in the parameter box.

Use this parameter to expose Format Attributes in Workbench when you create a workspace:

• In a dynamic scenario, it means these attributes can be passed to the output dataset at runtime.
• In a non-dynamic scenario, you can use this parameter to expose additional attributes on multiple feature types.

When selected, this parameter removes any portions of imported features being read that are outside the Search Envelope.

The example below illustrates the results of the Search Envelope when Clip to Search Envelope is not selected (set to No) and when it is selected (set to Yes).

• No: Any features that cross the search envelope boundary will be read, including the portion that lies outside of the boundary.
• Yes: Any features that cross the search envelope boundary will be clipped at the boundary, and only the portion that lies inside the boundary will be read. The underlying function for the Clip to Search Envelope function is an intersection; however, when Clip to Search Envelope is selected, a clipping operation is also performed in addition to the intersection.

The spatial relationship between the provided bounding box and the geometries in the geometry column of the table.

There are two types of operations:

• Maximum Bounding Rectangle (MBR) operations determine adherence to a given spatial relationship using only the bounding box of the geometry.
• Full spatial operations use the actual geometry.

Full spatial relationship comparisons are only available if GEOS is enabled on the PostGIS server. If not, all envelope comparisons are made using the default MBR operation MBR_OVERLAPS.

Note that when processing long arcs, the shortest path is taken. PostGIS uses great circle arcs as the "interpolation line" between two points. For more information, see Using PostGIS: Data Management and Queries.

The number of rows that are retrieved at one time into local memory from the data source. For example, if set to 10, the reader reads ten rows into local memory, and processes features from this memory buffer. After the reading the last row, the reader retrieves the next 10 rows from the data source.

Note: If this parameter is incorrectly set, it will cause significantly degraded performance. The optimum value depends primarily on the characteristics of individual records and the transport between the database and the client machine. It is less affected by the quantity of rows that are to be retrieved. The optimal value is the default value of 10.

This parameter allows for the execution of SQL statements before opening a table for reading. For example, it may be necessary to create a temporary view before attempting to read from it.

For detailed information about SQL functions, click the corresponding menu item in the SQL to Run editor help.

Available menu options depend on the format.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors ;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

This parameter allows for the execution of SQL statements after a set of tables has been read. For example, it may be necessary to clean up a temporary view after creating it.

For detailed information about SQL functions, click the corresponding menu item in the SQL to Run editor help.

Available menu options depend on the format.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors ;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.