Documentation home Previous page Next page

USSampleData coordinate system

The EPSG code corresponding to Albers Equal Area with WGS 84 is most likely 45556. In order to work with this projection you must load the appropriate row into spatial_ref_sys table. The SQL INSERT string is attached in the USSampleProjCS.sql file. You can load it from the command prompt by:

psql -U gdouser -d ussampledata -f USSampleProjCS.sql

Coordinate systems details

PostGIS uses its own coordinate system information. Each coordinate system is identified by Srid, which should actually be the EPSG code. The information about (for PostGIS) known coordinate systems is stored in the "spatial_ref_sys" metatable.

This is pretty similar to what Oracle does, however we cannot ignore the Srid information similarly as Oracle GDO does! The reason is that PostGIS stores Srid value in each geometry, and refuses to store new geometries, if correct Srid is not provided. Therefore we need some mechanism to coop with that.

PostGIS internally uses the Proj4 open source project. The information contained in a proj4 coordinate system definition cannot be 1-1 mapped to the information stored by INGR coordinate system definition. So the resolution we provide here is to collect all known coordinate systems in csf format, which can be uniquely matched with EPSG codes. The list is delivered with the source code and can be arbitrary extended.

The coordinate systems are compiled into a binary file called "csres.bin" and linked with the project executables. A question why not to read directly the csf files from a defined location during the code execution might arise. We believe there at least two good reasons not to do that:
  1. Portability: for delivering the GDO binaries, to link the csf files is more convenient way
  2. Dependency: the PostGIS GDO module would have to load the PCleint module and other dependencies (to access the CoordSystem object), which is probably not a good idea for such a low level module like a GDO server is.

There are variety of places where PostGIS GDO must lookup for the coordinate system, in both direction: csf from Srid and vice versa. The most tricky part is to find Srid from GCoordSystem table row. We use the matching criteria base on the GCoordSystem table reference available in GDO.pdf document. The only exception is the VerticalDatum - we ignore this value at the moment. The main reason for this is the confusion with USSampleData 6.1 version, where all geometries are stored with "user defined" vertical datum, except USSampleImage table, which uses EGM96 vertical datum! We are actually not sure
at the moment, whether EPSG codes (as well as Proj4 Srid) take vertical datums into consideration or not.

Geometry construction and handling

PostGIS internally stores metainformation about geometry column in the "geometry_columns" metatable. We somehow abuse this table for our custom information, as will be explained later.

Native geometry format

PostGIS stores geometries in the WKB (Well Known Binary) format, and provides variety of functions to convert them to and from WKT (Well Known Text) format. We don't use the latter one at all.

However, the true storage format in PostGIS is called EWKB (Extended Well Known Binary). The extension affects the standard (WKB, WKT) format in two ways: first it stores the Srid information, and second, it can also store arc segments.

PostGIS defines several geometry subtypes. We can both read and write all of them. However, if geometry tables are created via GDO, we only support three PostGIS geometry types: MULTIPOINT, MULTILINE and MULTIPOLYGON. These types cannot store arcs, however, if GeoMedia user attempts to store arcs in such a geometry field, PostGIS GDO server will silently stroke them.

Text and raster image formats

PostGIS does provide neither graphics text nor raster image types. So we define such fields as binary fields ("bytea" type in PostgreSQL) and write our own information into geometry_columns table with our "user defined" types "GRAPHICTEXT" and "IMAGE". We hope that this little hack will not interfere with PostGIS, at least so far performed test does not indicate any conflicts.

The text and raster geometries are stored normally as INGR binary format and cannot take part in server side spatial operations such as spatial filtering.

Point geometries

All point geometries are served as oriented point geometry with orientation angle 0. PostGIS does not provide oriented point geometry. If this is an requirement in the future, the oriented point geometries can be stored by the same mechanism as graphics text and images, however they couldn't be spatially filtered then.

Geometry dimensions

PostGIS can store the geometries as 2D, 3D, 4D and with some linear referencing. We can basically read and store all of them, however, when geometry tables are created via GDO, we defines the geometry fields as 3D without linear referencing.


PostGIS GDO support PostgreSQL schemas. When reading the list of available tables, PostGIS GDO reads tables from all schemas except pg_catalog and information_schema. Tables from public schema are displayed only with their names, tables from other schema are prefixed with the schema name, so the table name is of the form: <schema_name>.<table_name>. This somehow follows the internal PostgreSQL convention. Moreover, with this setup, beginners do not note the existence of the schemas at all, while advanced users may employ them.

If you create a table from GeoMedia, you can simply put it into an existing schema specifying the full name as in the previous paragraph. Also when importing data using "Output to Feature Classes ..." command, the new table names are suggested without prefix. However, you can put a prefix manually to each table to be newly created.

Metadata schema

When creating metadata using PostGIS GDO Database Utilities, new schema "gdo" is attempted to be created and all metadata tables are put into this schema. However, if the user creates GDO metadata tables manually in another schema, PostGIS GDO will find them and will use it.


The GAliasTable is presented "in memory" for the read-only server, must be physically created for read/write and schema updatable versions.

Hiding metatables

As explained in the previous part, PostgreSQL metadata are filtered by omitting the default schema they are placed in. However, the two PostGIS metatables (spatial_ref_sys, geometry_columns and geography_columns view) are created in the "public" schema by default. So PostGIS GDO currently hides them, so they are not included in the GTableDefs collection at all. Another possibility would be to list them in the GAliasTable.

Documentation home Previous page Next page

Last edited May 26, 2011 at 2:21 PM by pkrejcir, version 2


No comments yet.