Licensing options for this format begin with FME Desktop Professional Edition.
Overview
PostGIS is a geometric layer over a PostgreSQL Object-Relational Database Management System (ORDBMS) that provides geometry and Spatial Reference System (SRS) handling. The PostGIS reader/writer module enables FME to read geometric PostGIS data as well as underlying attribute data stored in PostgreSQL.
The PostGIS reader/writer is specifically designed to handle the geometric and SRS portions of the data. When reading attribute-only tables from PostgreSQL, the PostgreSQL reader/writer should be used instead. The PostGIS reader/writer communicates directly with the PostgreSQL libpq interface for maximum throughput.
This chapter assumes familiarity with PostGIS and PostgreSQL, the attribute and geometry types supported, and its indexing mechanisms.
For more information, please see the PostgreSQL home at
and the PostGIS home at
http://postgis.refractions.net/
Reader Overview
FME considers a PostGIS dataset to be a database containing a collection of relational tables together with their corresponding geometries. The tables must be defined in the workspace before they can be read. Arbitrary WHERE clauses and joins are fully supported, as well as an entire arbitrary SQL SELECT statement; however, the user then assumes responsibility for the correctness of the statement or clause including quoting where necessary.
When reading from the PostGIS/PostgreSQL database, each table is considered a feature type in FME and each row of a table at least one feature in FME. In the case of heterogeneous geometry collections, they may become more than one FME feature.
The basic reading process involves opening a connection to the database, querying metadata, and querying data. The data is read using a text cursor and rows are fetched to the client machine in batches of 10000 by default. There is one cursor per input table.
If null geometries are read, they are treated as non-geometry features and the attributes are preserved.
Table and column names are truncated at 64 characters. If duplicate names are produced by truncation, the behavior is undetermined. Please ensure that table names comply with PostgreSQL naming conventions.
Spaces and special characters are permissible in both table and column names. Case sensitivity has also been implemented.
UNICODE support has been added to work with a client’s system encoding. Although there is no way to explicitly specify the encoding, the client is assumed to have entered data and created tables and columns in the encoding of their operating system. Multiple system encodings are supported via the native PostgreSQL conversions between client and server, particularly if the server encoding is set to UNICODE.
Qualified table naming is supported in the form <schemaname>.<tablename>. The schema search path is read and interpreted to determine a user’s default schema when writing and the available schema to read from when reading. Failing a valid schema search path, the default public schema is used for newer databases.
Writer Overview
The PostGIS writer module stores both geometry and attributes into an PostgreSQL database.
Attribute case is preserved, unless the option to lowercase attributes is set to Yes.
The PostGIS writer provides the following capabilities:
- Transaction Support: The PostGIS writer provides transaction support that eases the data loading process. Occasionally, a data load operation terminates prematurely due to data difficulties. The transaction support provides a mechanism for reloading corrected data without data loss or duplication.
- Index Creation: The PostGIS writer can set up and populate indexes as part of the loading process. By default, a GiST index is created on the primary geometry column of a table and not on any other columns. Additional columns can be individually indexed. Composite column indexes are not supported at this time.
- Bulk Loading: By default, the PostGIS writer uses a bulk loading technique to ensure speedy data loading.