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 Raster reader/writer module enables FME to read PostGIS raster data as well as underlying attribute data stored in PostgreSQL.
The PostGIS Raster reader/writer is specifically designed to handle the raster portions of the data. When reading attribute-only tables from PostgreSQL, the PostgreSQL reader/writer should be used instead, and when reading or writing geometric or geographic data, the PostGIS reader/writer should be used. The PostGIS Raster 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 and the PostGIS home.
PostGIS (Raster) Product and System Requirements
|
Format |
FME Platform |
Operating System |
||||
|---|---|---|---|---|---|---|
|
Reader/Writer |
FME Form |
FME Flow |
FME Flow Hosted |
Windows 64-bit |
Linux |
Mac |
|
Reader |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Writer |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
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.
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 rasters are read, they are treated as non-geometric 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.
When the table contains a unique ID column, the reader will obtain rasters from the database only when they are referenced for improved performance. A unique ID column is defined as an integer column with a UNIQUE or PRIMARY KEY constraint that contains no null values. For example, when a unique ID column is present, connecting the reader to a Logger transformer in FME Workbench will not cause the raster data to be requested from the database. If no unique ID column is available, or when executing an arbitrary SQL query, the reader will download all rasters to local storage before translation continues.
Writer Overview
The PostGIS raster writer module stores both geometry and attributes into an PostgreSQL database. Note that attribute case is preserved, unless the option to lowercase attribute names is checked. The PostGIS raster writer provides the following capabilities:
- Transaction Support: The PostGIS raster 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 raster 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 raster writer uses a bulk loading technique to ensure speedy data loading.