Intergraph GeoMedia SQL Server Warehouse Reader/Writer

FME can read from and write to Intergraph GeoMedia Access and SQL Server Warehouses, which store both spatial and attribute data.

Intergraph GeoMedia SQL Server Warehouse (FM0_SQL) Product and System Requirements

Format

Platform

Operating System

Reader/Writer

FME Desktop License

FME Server

FME Cloud

Windows 64-bit

Linux

Mac

Reader

Available in FME Professional Edition and higher

Yes

No

Yes

No

No

Writer

Available in FME Professional Edition and higher

Yes

No

Yes

No

No

Overview

GeoMedia warehouses store both geometry and attributes for features in the form of columns within the tables of a database. Tables can be divided into two groups based on content. The first group contains meta-information about the formatting of the data, including coordinate systems, tables aliases, modification logs, a data table list, and a field level index. The second group is the list of tables that actually contain the geometry features and their attributes. For example, a single Microsoft Access .mdb or .accdb file or a single SQL Server database contains all the necessary information for an image.

To retrieve information from a GeoMedia warehouse, an Open DataBase Connectivity (ODBC) source must be set up. GeoMedia SQL Server Warehouse requires a database name with associated parameters (and will also accept a data source name [DSN]).

Once the reader has all the required information, it then dynamically creates a temporary ODBC source (when a filename or database name is supplied) to connect to that database.

Point, line, area, arc, and text primitive geometric data can be stored in the tables produced by GeoMedia, as well as composites (aggregates), boundaries (donuts) and collections (aggregates) of those types. A given data table holding multiple types of geometries can be read by the reader, but the writer only produces tables containing one specific geometry type each, including boundaries, composites or collections of that type. The result is that one table containing many types of features may be converted into several tables – one for each type of feature by the writer.

This is especially true of the collection type in GeoMedia which is very generally a collection of any primitive type and has no corresponding equivalent in FME. Syntactically, the type is simply appended to the table name separated by an underscore (for example, tableOfManyTypes_area).

The key table for determining the names of the spatial tables is called the GFeatures table. This table contains the list of names of the geometry and attribute data tables. This information can also be retrieved from the FieldLookup table which also contains the specific fields of each geometry table. Before either of these lookups can happen, however, you have to find the GFeatures and FieldLookup tables. Table names can be aliased and there is only one table that must have a constant name (the GAliasTable). From here, you can look up the given names of the other metadata tables in the specific database you are viewing.

GeoMedia warehouses hold three-dimensional geometries. A geometry table may contain any mix of attributes the user has specified, but must contain a column containing the actual geometry object. This column is a blob type and is simply an encoded block of binary data. Each geometry blob type is encoded in a unique way and varies in length.

GeoMedia can store text in two variations: plain text and rich text. Since FME supports plain text only, the GeoMedia reader will convert all rich text to plain text and set the text size to either the default (1 ground unit) or to the user-supplied size in ground units using the Text Size in Ground Units parameter. If the text being read is in rich text format, it also sets the attribute fm0_rtf_text_string to the original formatted string.

The GeoMedia writer will first check to see whether or not the attribute fm0_rtf_text_string is set. If it is set, then the formatted string will be used to write out as rich text. If the attribute is not set, then the GeoMedia writer by default will write plain text unless the Plain Text writer parameter is unchecked. In this case, the GeoMedia writer will write rich text using either a default font size of 10 or a user-supplied font size using Font Size parameter. Allowed font size is between 1 to 1024 inclusive. At this time the font size for rich text is the only supported style for writing.

Reading is performed by parsing the tables and respective binary BLOBs directly from the SQL Server database. The GeoMedia SQL Server Warehouse Reader/Writer does not require the installation of GeoMedia but does require access to Microsoft SQL Server.

Coordinate system support exists for both reading and writing: in the best case, a GeoMedia warehouse that contains a defined coordinate system can be read and converted to any of the supported writer formats in FME which also support coordinate systems, with the same coordinate system name. If the coordinate system is not specifically identified to have the exact same defined name within FME, the attributes of the coordinate system are still transferred, producing an identical coordinate system in all but name. The reverse is also true: the writer can interpret any given FME coordinate system and convert it to either a named GeoMedia coordinate system or an equivalent created coordinate system.

One issue involves the type of projection used in a coordinate system definition which GeoMedia calls the Base Storage Type. This type can be set to one of projected, geographic or geocentric. Projected types are the usual case and are handled normally by FME, though it is notable that the storage center values from the GeoMedia coordinate system become built into the coordinates and are cleared in translated warehouses. All geographic types are represented in the Lat/Long coordinate system but remain identical in appearance. The storage center values here will represent how to convert the coordinates to radians since they will be provided in degrees, as is consistent with the way GeoMedia itself handles geographic types. Finally, the geocentric type is not supported by FME.

Native spatial indexing in GeoMedia is supported by both the reader and the writer. However, some conditions apply. The reader will preserve spatial indexes read from a GeoMedia warehouse and a writer will automatically create new indexes (when creating new feature tables) based on the data, as long as the following is true:

The CREATE_SPATIAL_INDEX setting is not set to No,

The registry key HKEY_LOCAL_MACHINE\SOFTWARE\Intergraph\Applications has a string value called DefaultJCache which must be set to the correct version of GeoMedia (for example, GeoMedia Professional_05.00).

The autodt.ini file must contain a valid datum mapping between the current coordinate system datum and the WGS84 datum. (See the file for more detail. It is likely to be located in a folder such as C:\Program Files\GeoMedia Professional\Program\cssruntm\cfg\autodt.ini).

When appending to an existing warehouse, the creation of a Spatial Index depends on whether the SpatialKeyFieldName property is set for the geometry column and the column itself exists. If the property is set and the column exists, then a spatial key will be automatically created for the geometry, regardless of the CREATE_SPATIAL_INDEX setting.

For the most part, spatial index creation should happen automatically for most known coordinate systems since the default for the writer creating them is Yes, and the registry key mentioned above should be set when GeoMedia is installed.

Reader Overview

The GeoMedia Warehouse Reader produces FME features for all data held in the Microsoft Access .mdb or .accdb files or a Microsoft SQL Server database, with the exception of image (coverage) data. The reader opens the connection to the source dataset and reads the GAliasTable to determine the proper table names to be used. Next it reads the table of GFeaturesTable type to determine the list of tables that contain geometry data. This list of data tables to be read is modified by the specified reader and feature type parameters. Each geometry table is then read and its features are processed and returned one at a time. When a table is exhausted, the reader starts on the next data table in the list until all tables are read. Issues with reading in a table may result in a specific feature being skipped and sometimes an entire table depending on the severity of the error, but the reader will always try to perform as much translation as possible.

Geometries from GeoMedia do not map exactly to FME geometries. This will have the following effects on the resulting FME features:

  • Collections map to one aggregate feature for each FME fm0_type depending on which types exist in the collection.
  • Multilevel composites may be flattened out to simpler first- or second-level nesting.
  • Because GeoMedia is not strict in its typing, the reader can produce some nonsensical features that may be skipped, e.g., a line aggregate containing points.

Writer Overview

The GeoMedia SQL Server Warehouse Writer writes to existing Microsoft SQL Server databases.

If the database does not exist, the translation will be terminated. If the metadata tables and/or data tables do not exist in the database specified in the Writer Connection parameter, they will be created if the user has permission to create and write to tables to the database.

If the metadata tables exist, information about newly created data table(s) will be appended to them.

As FME routes features to the writer, the writer determines the layer (feature type) they are in and write the features to the corresponding table. The writer writes to one database, but may create many tables with that one database.

The GeoMedia SQL Server Warehouse Writer does not require GeoMedia to be installed. However, for successful writing, users must have sufficient permissions for creating and deleting tables, and inserting and updating rows in existing tables. All metadata and feature tables will be created with dbo ownership, which ensures that any user with permissions has access to the tables. A table with dbo ownership means that it is owned by the database administrator.

When writing to GeoMedia SQL Server warehouse, the writer will create four additional columns for storing the extents of geometry. These columns are required by GeoMedia. The names for these columns are derived from the name of the geometry column in the table being written. For example, if the name of the geometry column is Geometry, then the four derived column names will be Geometry_xlo, Geometry_ylo, Geometry_xhi and Geometry_yhi.