GeoMedia 2015

SQL Server Spatial Data Server

User Guide

January 2015
 SQL Server Spatial Data Server
User Guide

CONTENTS

Contents ..................................................................................................................................... 4

Introduction, Delivery, AND Connection ..................................................................................... 6

Prerequisites ......................................................................................................................... 6
Connections .......................................................................................................................... 6
Password Persistence ............................................................................................................ 6
Permissions ........................................................................................................................... 7
SQL Server Warehouse Requirements ................................................................................... 7

Data Storage and Type Matching ................................................................................................ 9

Geometry Storage ................................................................................................................. 9

GeoMedia's Binary Geometry to Native Geometry Type Matching ...................................... 10
SQL Server to GeoMedia Data Type Matching ..................................................................... 13
GeoMedia to SQL Server Data Type Matching ..................................................................... 14

GeoMedia Metadata Requirements ......................................................................................... 15

Scalar Functions .................................................................................................................. 16

AttributeProperties Table .................................................................................................... 16
FieldLookup Table ............................................................................................................... 17
GAliasTable ......................................................................................................................... 18
GCoordSystem Table ........................................................................................................... 18
GeometryProperties Table .................................................................................................. 19
GFeatures Table .................................................................................................................. 20
GFieldMapping Table .......................................................................................................... 20
GIndexColumns Table.......................................................................................................... 21
GParameters Table.............................................................................................................. 22
GPickLists Table................................................................................................................... 24
Queue Table........................................................................................................................ 25
ModifiedTables ................................................................................................................... 25
ModificationLog Table ......................................................................................................... 26
Data Server Required Triggers in SQL Server Spatial ............................................................ 29
SQL Server Spatial Indexing ................................................................................................. 30

Working with SQL Server Spatial .............................................................................................. 32

Using Existing Native Spatial Data........................................................................................ 32

Importing Spatial Data......................................................................................................... 34
Existing Standard SQL Server Data ....................................................................................... 34
Feature Class Definition ...................................................................................................... 34
Undo/Redo ......................................................................................................................... 35
Default Values ..................................................................................................................... 35
Spatial Filtering ................................................................................................................... 36
Page 4
 SQL Server Spatial Data Server
User Guide

Views and Join Views .......................................................................................................... 36

Database Utilities ..................................................................................................................... 37

Exporting to SQL Server ............................................................................................................ 39

Page 5
 SQL Server Spatial Data Server
User Guide

INTRODUCTION, DELIVERY, AND CONNECTION

GeoMedia’s SQL Server Spatial data server makes it easier to connect to Microsoft’s Sequel Server
(SQL Server 2012 or later) databases that use native spatial geometry storage. This allows GeoMedia
applications to use native SQL Server Spatial databases as geospatial warehouses. Once installed, the
data server is accessed through GeoMedia Professional's Warehouse > New Connection command.

Prerequisites
SQL Server connections do not require client software. The SQL Server Spatial data server will be
installed whether SQL Server is present or not. Connections can be made to SQL Server installations
that are configured as case-sensitive or case-insensitive. Both Windows authentication and SQL Server
authentication are supported for all user accounts. A SQL Server database must already exist and must
have the required metadata tables before a SQL Server connection can be made. The data server has
full read-write capability, but the ability to access and edit database objects is controlled by the
privileges on the login account used for the database connection.

Connections
GeoMedia applications require specific metadata tables to exist in the SQL Server database before a
connection can be made. This metadata is created using GeoMedia Professional's Database Utilities or
during the bulk import of data from GeoMedia Professional’s Export to SQL Server command. The
metadata and geometry storage formats used by the SQL Server spatial data server is different from
the metadata used by the standard SQL Server data server, they cannot be used interchangeably.

See the GeoMedia Metadata Requirements section of this document for a list of the required tables.

To make a connection to SQL Server, provide a valid server name, and then a valid username and
password. Any databases the specified user has privilege to see will appear in the drop-down
database list.

SQL Server has two modes for validating users: Windows domain authentication and SQL Server
authentication. If the SQL Server connection is set to use Windows authentication (the default), your
domain login account will need to be added to SQL Server by a database administrator and
appropriate privileges will need to be granted on the databases you want to access. On connection,
you will only need to supply the server name and the database name.

If you are using SQL Server authentication, you will need to have a valid SQL Server user account and
password as well as the appropriate privileges on the database you want to connect to.

Password Persistence
When using SQL Server authentication, GeoMedia stores the SQL Server connection password in the
GeoWorkspace. This is meant as a convenience and allows users to open existing GeoWorkspaces
containing SQL Server connections without having to re-enter connection passwords. However, this is
a drawback to those users wanting higher levels of security. If you do not want the passwords to be
Page 6
 SQL Server Spatial Data Server
User Guide

persisted in the GeoWorkspace, you must use domain authentication. Domain authenticated
connections do not store any user or password information in the GeoWorkspace and have the added
benefit of not prompting you to re-enter passwords.

Permissions
In SQL Server warehouses, access to database objects is controlled by the object’s owner through the
use of permissions. GeoMedia requires all objects in your SQL Server database to be in the DBO
schema. Objects that are not owned by DBO will not be accessible or visible in GeoMedia except by
the user who created them.

When creating database objects using GeoMedia Professional’s Feature Class Definition command,
the user account must be assigned the DB_OWNER role. For database objects created outside of
GeoMedia Professional, only a user account with the role DB_OWNER will ensure that the resulting
objects are in the DBO schema.

SQL server users who need to be restricted to read-only access should be assigned the
DB_DATAREADER role, and users who need read-write access should be assigned the
DB_DATAWRITER role. All other specific SQL Server privileges are honored as long as the DBO
ownership criterion is met when creating database objects.

There are also four scalar functions that are required for any access to native spatial data through
GeoMedia. Execute privileges are required on these four functions for any user who does not have the
DB_OWNER role. See the Scalar Functions section for more information.

SQL Server Warehouse Requirements

Connections to SQL Server spatial warehouses have several restrictions and requirements that must be
adhered to. Violation of any of these requirements may lead to a connection failure or the inability to
load data from the connection. These connection requirements and restrictions are listed below:

 All geometries are stored in 3 dimensions; 2 dimensional geometries are not supported.
 All non-system SQL Server database objects must be owned by dbo. Users who create objects
(tables, views, etc.) in the database must have the DB_OWNER role.
 Names of tables, views, indexes, and fields are always expressed in their defined cases. The server
will preserve the case of identifiers but will be case-insensitive on comparisons.
 A local SQL Server client is not required; however, client-side administrative tools are required when
importing data generated by the Export to SQL Server command. The server drop-down list on the
New Connection dialog box is only populated when SQL Server agents are active.
 Do not use SQL Server’s TIMESTAMP data type. This data type is not related to date/time functions
and is not supported. A list of supported data types is presented in the SQL Server to GeoMedia
Data Type Matching section. Data types that do not appear in this list are not supported and are
generally ignored by the data server.
 All DML operations (inserts, updates, and deletes) will require a clustered primary key. Both multi-
column and character-based primary keys are supported but are not recommended for insert
Page 7
 SQL Server Spatial Data Server
User Guide

operations as the user will need to manually enter the appropriate key value(s). For the best results
and the best performance, use an integer-based auto-increment (identity) primary key column or a
uniqueidentifier based primary key column with the NewID() function assigned as the default value
(uniqueidentifiers are mapped to GDBText in GeoMedia).
 Views are editable as long as they are key preserved and have the appropriate metadata entries in
the GINDEXCOLUMNS table. Any column in the view that is both unique and not null can act as the
pseudo primary key, which can be assigned via GeoMedia Professional's Database Utilities. Even
when key preserved, DML operations on join-views will require the use of INSTEAD OF triggers.
 GeoMedia metadata must be present before making a connection to the database. The required
metadata can be created using GeoMedia Professional's Database Utilities or using the metadata
script created by the Export to SQL Server command.
 Metadata entries must exist for all tables and views for them to be visible in the GeoMedia
environment. Database Utilities can be used to make the metadata assignments.

Page 8
 SQL Server Spatial Data Server
User Guide

DATA STORAGE AND TYP E MATCHING

The SQL Server Spatial data server uses two spatial data storage columns: one column is used to store
the SQL Server's native spatial data types (GEOMETRY or GEOGRAPHY); the second one is a binary
column (VARBINARY(MAX)) storing the GDO (GeoMedia Data Object - GeoMedia's native binary
storage format) geometry blob used for unsupported geometries (for example, arcs, oriented points,
text, and raster).

Geometry Storage
The default native storage data type is GEOMETRY because most data is assumed to be projected. The
GEOGRAPHY data type is fully supported as well but will require the use of an EPSG spatial reference
system identifier (SRID). For geographic data, each feature must fit inside a single hemisphere.
Objects larger than a single hemisphere are not supported and may throw an argument exception.
Geographic spatial filter areas must also fit inside a single hemisphere.

The default geometry type used by GeoMedia Professional's Feature Class Definition command (or any
other GeoMedia command that creates a table) is determined by the TypeForNativeGeometryStorage
parameter in the GParameters metadata table.

The default spatial reference system identifier (SRID) is 0 because that is what SQL Server expects for
GEOMETRY data types. SQL Server does not store the EPSG SRID's for projected data, and a NULL SRID
is not allowed. You can enter a custom SRID for a geometry but it will not be utilized for any
coordinate system operations. For GEOGRAPHY data types, a valid SRID is required, and it must be one
of the EPSG SRIDs currently stored in SQL Server's sys.spatial_reference_systems table. The default
SRID used by GeoMedia Professional's Feature Class Definition command (or any other GeoMedia
command that creates a table) is determined by the DefaultNativeGeometrySrid parameter in the
GParameters metadata table.

Every native spatial geometry column must have a corresponding GeoMedia binary column. If the
table is created using Feature Class Definition, the following columns are present in the base table for
the feature:

Name Data Type Description

<geometry_column_name> varbinary(MAX) The binary GDO geometry column.

<geometry_column_name SPA GEOMETRY/GEOGRAPHY Native geometry column.

The native geometry column in SQL Server (<GDO geometry name>_SPA) stores the exact
representation for geometries that are currently supported by SQL Server. For unsupported GeoMedia
geometry types, an approximation of the GeoMedia geometry type is stored in the native geometry
column while the actual geometry is stored in the VARBINARY column. The approximation is, as
follows:

 The point origin for GeoMedia's ORIENTEDPOINTGEOMETRY

 The text origin for GeoMedia's TEXTPOINTGEOMETRY
Page 9
 SQL Server Spatial Data Server
User Guide

 A line geometry for GeoMedia's POLYLINEGEOMETRY. In this case the geometry is represented
exactly in its native format, but because the polylines are converted back from native line format,
they are also stored in the GDO column to have the information about its type – a two-point
POLYLINEGEOMETRY is the same as LINEGEOMETRY in native format.
 A two-point polyline for GeoMedia's LINEGEOMETRY. In this case the geometry is represented
exactly in native format, but because the line geometries are converted back from the native
polyline format, they are also stored in the GDO column to provide the information about its type.
 Any and all items required for GeoMedia's COMPOSITEPOLYLINEGEOMETRY, even if all the items
are fully supported by the underlying data source, since there is no way to convert it back to
GeoMedia's COMPOSITEPOLYLINEGEOMETRY from native format.
 Any and all items required for GeoMedia's COMPOSITEPOLYGONGEOMETRY, even if all the items
are fully supported by the underlying data source, because there is no way to convert it back to
GeoMedia's COMPOSITEPOLYGONGEOMETRY from native format.
 Any and all items required for GeoMedia's GEOMETRYCOLLECTION. The VARBINARY column is
used only if any of the items are not fully supported by SQL Server's geometry.
 The raster footprint for GeoMedia's RASTERGEOMETRY.
For tables created outside Feature Class Definition, the VARBINARY(MAX) column used by GeoMedia
must be added manually, and GeoMedia's metadata must contain the association between the native
geometry column and the varbinary column. Use GeoMedia Professional's Database Utilities to insert
the metadata required to view native geometry tables in the GeoMedia environment.

The use of _SPA is just a naming convention used by GeoMedia applications; you do not need
to use this naming convention if you are creating or modifying tables outside of GeoMedia applications
as long as GeoMedia's metadata reflects the association between the GDO geometry and the native
geometry column.

GeoMedia's Binary Geometry to Native Geometry Type Matching

To write geometric data to SQL Server, GeoMedia’s SQL Server Spatial data server converts GeoMedia
native GDO geometry format to SQL Server native spatial format using the following:

GeoMedia Geometry Type SQL Server GDO column Native column content
Geometry Type content

PointGeometry POINT (x y z) Exact point

OrientedPointGeometry POINT (x y z) Full GDO Exact point, no orientation
TextPointGeometry POINT (x y z) Full GDO Point origin
LineGeometry LINESTRING( Full GDO Two-point linestring
x1 y1 z1,
x2 y2 z2)
PolylineGeometry LINESTRING( N-point linestring

Page 10
 SQL Server Spatial Data Server
User Guide

GeoMedia Geometry Type SQL Server GDO column Native column content
Geometry Type content
x1 y1 z1,
…,
xN yN zN)
ArcGeometry LINESTRING( Full GDO Stroked N-point linestring
x1 y1 z1,
…,
xN yN zN)
CompositePolylineGeometry MULTILINESTRING( Full GDO, even Composite members need to
( when no member be approximated (like arcs) in
is approximated, a multiline string; otherwise,
x11 y11 z11,
in order to exact multiline string.
…,
recreate
xN1 yN1 zN1 composite.
),
…,
(x1M y1M z1M,
…,
xNM yNM zNM
))
PolygonGeometry POLYGON( Exact polygon
(
x1 y1 z1,
…,
xN yN zN
))
CompositePolygonGeometry MULTILINESTRING( Full GDO, even Composite members need to
( when no member be approximated (like arcs) in
is approximated, a closed multiline string;
x11 y11 z11,
in order to otherwise, exact closed
…, multiline string.
recreate
xN1 yN1 zN1 composite.
),
…,
(x1M y1M z1M,
…,

Page 11
 SQL Server Spatial Data Server
User Guide

GeoMedia Geometry Type SQL Server GDO column Native column content
Geometry Type content
xNM yNM zNM
))
BoundaryGeometry POLYGON( Full GDO if any of Composite members need to
( the members be approximated (like arcs) in
(exterior, interior) a polygon string; otherwise,
x1_int y1_int z1_int,
cannot be exact polygon string.
…,
represented fully
xN_int yN_int zN_int by native type;
), otherwise, this
( column will be
null.
x11_ext y11_ext
z11_ext,
…,
xN1_ext yN1_ext
zN1_ext)
),
…,
(
x11M_ext y11M_ext
z11M_ext,
…,
xN1M_ext yN1M_ext
zN1M_ext)
))
RasterGeometry POLYGON ( Full GDO Exact polygon of the raster
x1 y1 z1, footprint.
x2 y2 z2,
x3 y3 z3,
x4 y4 z4,
x1 y1 z1)
GeometryCollection MULTPOINT for point Full GDO if any of A collection of exact
geometries, the members representations or
MULTILINESTRING for (exterior, interior) approximations, according to
line geometries, cannot be the rules established above,
represented fully for each GDO collection
MULTIPOLYGON for
by native type; member.

Page 12
 SQL Server Spatial Data Server
User Guide

GeoMedia Geometry Type SQL Server GDO column Native column content
Geometry Type content
area geometries, otherwise, this
GEOMETRYCOLLECTIO column will be
N when mixed NULL.

SQL Server to GeoMedia Data Type Matching

To use data from SQL Server, GeoMedia’s SQL Server Spatial data server converts SQL Server data
types to GeoMedia data types. The following table shows how the SQL Server data types are mapped
to the GeoMedia types. Any SQL Server data types missing from this list are considered unsupported
and are ignored.

SQL Server Data Type GeoMedia Data Type

binary LongBinary
varbinary
bit Boolean
char(size) Text if size <= 255
varchar(size) Memo otherwise
nchar(size) *Memo only
nvarchar(size)
ntext*
datetime Date
smalldatetime
decimal(p,s) or Integer if s = 0 and p < 6
numeric(p,s) Long if s = 0 and p >= 6 and p < 11
p is precision Double for all other cases.
s is scale
float Double
int Long
money Currency
real Single
smallint Integer
tinyint Byte
uniqueidentifier* Text (GUIDs)

Page 13
 SQL Server Spatial Data Server
User Guide

*Uniqueidentifiers are a special case when used with the SQL Server spatial data server. If the primary
key column is defined as a uniqueidentifier, the SQL Server function NewID() must be assigned as the
default value. This will allow GeoMedia to automatically generate a new key value during inserts. If this
is not done, insert operations will fail. Internally, GeoMedia will treat the uniqueidentifier as a text field.

GeoMedia to SQL Server Data Type Matching

The following table identifies the mapping used when converting from GeoMedia data types to SQL
Server data types and whether specific metadata is required for the mapping:

GeoMedia Data Type SQL Server Data Type Metadata Required?

Boolean bit No
Byte tinyint No
Integer smallint Yes for autonumber
Long int No
Single real No
Double float No
Currency money No
Date datetime No
Text nvarchar No
LongBinary varbinary No
Memo ntext No
Text (GUID) uniqueidentifier Yes (for conversion)

Page 14
 SQL Server Spatial Data Server
User Guide

GEOMEDIA METADATA REQUIREMENTS

GeoMedia applications require specific metadata objects, and these must exist in the SQL Server
database before a connection can occur. GeoMedia's metadata tables contain information about both
the attribute and geometry tables stored in the database. The metadata functions control the follow
of geometry data to and from GeoMedia applications. The following table lists the required metadata
and its object type.

GeoMedia Metadata Objects Type

AttributeProperties Table
FieldLookup Table
GAliasTable Table
GCoordSystem Table
GeometryProperties Table
GFeatures Table
GFieldMapping Table
GIndexColumns Table
GParameters Table
GPickLists Table
GQueue Table
GTileIndexes Table
ModifiedTables View
ModificationLog Table
Binary2SqlGeography Function
Binary2SqlGeometry Function
SqlGeography2Binary Function
SqlGeometry2Binary Function

To create GeoMedia's required metadata objects, you must use one of the following methods:

 Database Utilities – Use Database Utilities from the GeoMedia Professional program group. Enter
the server name and login as the database owner (or administrator). When connected, select the
Create Metadata Tables command. This is the preferred method and is also the method to use
when updating the metadata objects as new releases become available.
 Export to SQL Server – You can also create the required metadata tables during bulk loading when
using the IMPORT.BAT command file created by the Export to SQL Server command in GeoMedia
Page 15
 SQL Server Spatial Data Server
User Guide

Professional by setting the sixth parameter to Y. The METADATA.SQL file generated by Export to
SQL Server can also be run directly in SQL Server's Management Console.

Scalar Functions
Internally, GeoMedia uses a binary data type when reading WKB data so it converts SQL Server's
GEOMETRY/GEOGRAPHY data type to/from binary when reading/writing native geometry records. It
uses the following four scalar functions to do the data type conversion:
 Binary2SqlGeography – Converts GeoMedia's binary data type to the native GEOGRAPHY data
type when writing WKB geographic data.
 Binary2SqlGeometry – Converts GeoMedia's binary data type to the native GEOMETRY data type
when writing WKB geometry data.
 SqlGeography2Binary – Converts a native GEOGRAPHY data type to GeoMedia's binary data type
when reading WKB data.
 SqlGeometry2Binary – Converts a native GEOMETRY data type to GeoMedia's binary data type
when reading WKB data.
Execute privileges are required on these four functions for any login to a SQL Server database that does
not have the DB_OWNER role. These functions only convert the data type used to store the data; they
do not convert data between WKB and GDO formats.

AttributeProperties Table
The ATTRIBUTEPROPERTIES metadata table describes the attribute types for the columns listed in the
FIELDLOOKUP table. The common link between this table and FIELDLOOKUP is the INDEXID column.
The ATTRIBUTEPROPERTIES table is defined, as follows:

 INDEXID – Uniquely identifies the column being described. The INDEXID value comes from the
FIELDLOOKUP table.
 ISKEYFIELD – Determines whether a column is a primary key field. The default value is 0 for FALSE.
Use -1 (TRUE) if the column is a primary key.
 ISFIELDDISPLAYABLE – Determines whether a column is displayed in GeoMedia Professional. The
default value is -1 for TRUE. Use 0 (FALSE) to hide the column.

Page 16
 SQL Server Spatial Data Server
User Guide

 FIELDTYPE – Determines how GeoMedia interprets the data type used in the column definition.
These are based on the conversion from SQL Server to GeoMedia data types. The field type values
correspond to the following:
1 – Boolean 8 – Date
2 – Byte 10 – Text
3 – Integer 11 – Binary
4 – Long 12 – Memo
5 – Currency 15 – GUID
6 – Single 32 – Spatial geometry
7 – Double 33 – Graphic geometry
 FIELDPRECISION – Represents the number of decimal places displayed in GeoMedia Professional.
For numeric data types, the default is 6. Usually, this is the same as the scale defined for the
number field.
 FIELDFORMAT – Determines the general format of the data being displayed. Format types include
General Number, Date/Time, and Currency.
 FIELDDESCRIPTION – A user-provided description of the column.

FieldLookup Table
The FIELDLOOKUP metadata table provides a unique identifier (INDEXID) for every column in every
table/view in the database. The table definition is, as follows:

 INDEXID – This key column contains a unique identifier for every column in every table in the
database. It is populated using an identity increment.
 FEATURENAME – The table name.
 FIELDNAME – Stores each column name for the associated feature name.
 The INDEXID is used as a reference by other metadata tables like ATTRIBUTEPROPERTIES and
GEOMETRYPROPERTIES, which are used to describe the columns and their contents.

Page 17
 SQL Server Spatial Data Server
User Guide

GAliasTable
The GALIASTABLE metadata table determines the names of the other metadata tables used by
GeoMedia Professional. The GALIASTABLE is the only metadata table whose name is hard coded. This
table must exist and cannot be modified or altered in any way. The table definition is, as follows:

 TABLETYPE – This key column contains an internal reference name used by GeoMedia applications.
 TABLENAME – This is the table name used by the associated table type. A table or view is required
for each table type.

GCoordSystem Table
The GCOORDSYSTEM metadata table stores GeoMedia's coordinate system definitions. If this table is
not present, no coordinate system transformation will occur, and the GeoWorkspace coordinate
system will be used. This table is not user editable and is not listed due to the large number of
columns and types of parameters required to define a coordinate system. This table should never be
populated manually. There are three columns worth noting:

 NAME – The name the user has assigned to this coordinate system. This is an optional parameter,
but it should be used because it makes the coordinate system easier to identify, particularly if
multiple coordinate systems are used in the database.
 DESCRIPTION – A user-provided description of the coordinate system. This is optional, but like the
name, it can also be useful.
 CSGUID – The CSGUID is a special value used to uniquely identify the coordinate system
parameters. The CSGUID IS used to associate a geometry object to a GeoMedia coordinate system.
The CSGUID is also referenced in GEOMETRYPROPERTIES and in GFIELDMAPPING.
Coordinate systems should be created using GeoMedia Professional's Define Coordinate System
command. When a defined coordinate system is assigned to a feature class, the parameters that make
up the coordinate system are inserted into the database table. Any feature class that uses the
coordinate system is assigned the CSGUID for that coordinate system.

Coordinate systems are defined on a per-feature-class basis. Each feature class can have its own
coordinate system. If the database has a default coordinate system defined using the
DEFAULTCOORDINATESYSTEM parameter in the GParameters table, feature classes created using the
Feature Class Definition, Output to Feature Classes, or Export to SQL Server commands will
automatically use the default. Outside of GeoMedia Professional, you will need to use the Database
Utilities command, which is available in the GeoMedia Professional program group. If you have
incorrectly assigned a coordinate system to a feature class, you can also use the Database Utilities to
correct the assigned coordinate system.

If you plan to use multiple coordinate systems in your SQL Server database, you need to assign one
coordinate system to use as a default. Default coordinate systems can be assigned using Database
Page 18
 SQL Server Spatial Data Server
User Guide

Utilities or Feature Class Definition. Only one default coordinate system is allowed per database. The
CSGUID of the default coordinate system is stored in the DEFAULTCOORDINATESYSTEM parameter in
the GPARAMETERS metadata table.

When digitizing in GeoMedia Professional, you should ensure that the GeoWorkspace coordinate
system matches the coordinate system of the feature class into which you are digitizing. This is not
always required, but depending on the coordinate transformation used, conversion errors can occur
when the coordinates are written to the database. GeoMedia Professional will compare the
GeoWorkspace coordinate system to the coordinate system of the feature you select for editing and
will warn you if there is a mismatch. It will be up to the user to rectify or ignore the mismatch. One
example where a difference is required is when editing geographic data in the Polar Regions; in this
case, your workspace should be set to either north or south polar stereographic.

GeometryProperties Table
The GEOMETRYPROPERTIES metadata table stores the geometry type, primary geometry flag, and the
coordinate system ID for geometry columns contained by feature classes. The common link between
this table and FIELDLOOKUP is the INDEXID column. The table definition is, as follows:

 INDEXID – This key field links the information to the actual column defined in the FIELDLOOKUP
table.
 PRIMARYGEOMETRYFLAG – A feature class can contain multiple geometry fields, but only one field
is allowed to be primary. The primary geometry field is the field that allows for editing. A value of -1
means the geometry column is the primary geometry. All other geometry columns in the feature
class should be assigned 0. Only one primary geometry field is allowed.
 GEOMETRYTYPE – This field determines how the data server maps the geometry:
1 – Line 2 – Area
3 – AnySpatial 4 – Coverage
5 – GraphicsText 10 – Point
 GCOORDSYSTEMGUID – This field contains the CSGUID from the GCOORDSYSTEM table. It tells the
data server what coordinate system is assigned to the geometry.
 FIELDDESCRIPTION – A user-provided description of the column.

Page 19
 SQL Server Spatial Data Server
User Guide

GFeatures Table
The GFEATURES metadata table stores the table names of all user tables (feature classes). By
manipulating the tables listed here, you can make feature classes visible or invisible in GeoMedia. The
table definition is, as follows:

 FEATURENAME – This key column contains the name of the table that will be exposed as a feature
class in GeoMedia applications. This table is used by every command in GeoMedia Professional that
lists the available feature classes, for example, Add Legend Entries.
 GEOMETRYTYPE – This field determines how the data server maps the geometry.
1 – Line 2 – Area
3 – AnySpatial 4 – Coverage
33 – GraphicsText 10 – Point
-1 – Attribute only (no geometry field)
 PRIMARYGEOMETRYFIELDNAME – The name of the primary geometry column.
 FEATUREDESCRIPTION – A user-provided description of the column.

GFieldMapping Table
The GFIELDMAPPING metadata table is used to override various aspects of column definitions.
Information stored here typically consists of the primary key column and the primary geometry with
their associated GeoMedia data types, the coordinate system ID, and any assigned autonumber types.
This table also defines the relationship between the native geometry storage column and the
GeoMedia binary geometry column. The table definition is, as follows:

 TABLE_NAME – The name of the table.

 COLUMN_NAME – The column in the table that this information apples to.
 The TABLE_NAME/COLUMN_NAME combination makes up the primary key.
Page 20
 SQL Server Spatial Data Server
User Guide

 DATA_TYPE – Determines how GeoMedia interprets the data type used in the column definition.
Field type values include the following types (these are derived from the SQL Server to GeoMedia
data type matching table):
1 – Boolean 8 – Date
2 – Byte 10 – Text
3 – Integer 11 – Binary
4 – Long 12 – Memo
5 – Currency 15 – GUID
6 – Single 32 – Spatial geometry
7 – Double 33 – Graphic geometry
 DATA_SUBTYPE – Used when the DATA_TYPE is 32 or 33; the subtype determines the graphic type:
1 – Line 2 – Area
3 – AnySpatial 4 – Coverage
5 – GraphicsText 10 – Point
 CSGUID – The coordinate system assigned to the primary geometry field.
 AUTOINCREMENT – A Boolean field indicating that the field is set to auto-increment. Use -1 for
True; otherwise, the value is NULL.
 NATIVE_GEOMETRY – This column is used to match the native geometry column with its associated
GeoMedia binary geometry column.
 NATIVE_SRID – This column contains the SRID of the native geometry field. Typically it will be 0 for
GEOMETRY type fields. For GEOGRAPHY types, it should reflect an SRID value that is defined in SQL
Server's SYS.SPATIAL_REFERENCE_SYSTEMS table.

GIndexColumns Table
The GINDEXCOLUMNS metadata table is used to specify the column or columns in a view that can act
as primary or unique key fields. This table is populated using Database Utilities. The table definition
is, as follows:

Page 21
 SQL Server Spatial Data Server
User Guide

 The primary key is a combination of the OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, and

COLUMN_NAME fields.
 OBJECT_SCHEMA – The owner of the view (the default is 'dbo').
 OBJECT_NAME – The name of the view.
 INDEX_NAME – The primary key index name from the base table.
 COLUMN_NAME – The name of a column in the view that will use the index in INDEX_NAME.
 INDEX_TYPE – The type of the index: ‘P’ for primary, ‘U’ for unique. The default value is ‘P’. If this
field is missing, the first index will be assumed to be the primary index. If a view does not have a key
defined in the GINDEXCOLUMNS, it will be read-only, and no DML operations will be allowed.
 COLUMN_POSITION – This field is the order of the column within the index. The default value is 1.
 BASE_OBJECT_SCHEMA – This field is the owner of the table (view) on which the view is based. If
this field contains NULL (empty string), notification will not be supported. Only triggers can support
notification in this case.
 BASE_OBJECT_NAME – This field is the name of the table (view) on which the view is based. If this
field is missing or contains NULL (empty string), notification will not be supported. Only triggers can
support notification in this case.
 BASE_COLUMN_NAME – This field is the name of the corresponding field of the base table/view.
This field is used for name aliasing. If this field contains NULL (empty string), column name aliasing
will not be supported.

GParameters Table
The GPARAMETERS metadata table contains the default values for the parameters needed to create
new tables using GeoMedia Professional as well as other miscellaneous information, such as the
default warehouse coordinate system.

Page 22
 SQL Server Spatial Data Server
User Guide

This table contains two fields, GPARAMETER and GVALUE. Currently, the following values are used by
default:

NEVER modify the values in the GPARAMETER column. The values used in the GVALUE column are
user editable and these control how GeoMedia Professional creates tables in the database. These
values mainly affect Feature Class Definition, but any GeoMedia Professional command that creates a
table in the database will use these as defaults. Typically, you would edit the following:

 TYPEFORNATIVEGEOMETRYSTORAGE – This controls whether tables will be created using the

GEOMETRY or the GEOGRAPHY data type. If you are using projected data, use GEOMETRY. If your
data is GPS or longitude/latitude based, use GEOGRAPHY.
 DEFAULTNATIVESTORAGESRID – This assigns the default SRID to use. If the
TYPEFORNATIVEGEOMETRYSTORAGE is set to GEOMETRY, this value must be set to 0. If
TYPEFORNATIVEGEOMETRYSTORAGE is set to GEOGRAPHY, this value must be set to an SRID that
is currently supported by SQL Server. Use the following query for a list of the SRIDs currently
supported by SQL Server:
SELECT * FROM SYS.spatial_reference_systems

 DEFAULTCOORDINATESYSTEM – This parameter contains the CSGUID from the GCOORDSYSTEM

table that corresponds to a GeoMedia coordinate system that is to be used as the default for all
feature classes created through the GeoMedia Professional environment. If the
TYPEFORNATIVEGEOMETRYSTORAGE is set to GEOGRAPHY, this value should correspond to the
CSGUID of a coordinate system in the GCOORDSYSTEM table that matches the coordinate system
for the SRID used for the DEFAULTNATIVESTORAGESRID.
 XUPPERBOUND, YUPPERBOUND, XLOWERBOUND, YLOWERBOUND – These parameters control
the MBR or bounding box range used by GeoMedia Professional when creating spatial indexes on
geometry data types. Spatial indexes will perform better when the range used here more closely
matches the range of the data indexed. Data that falls outside the range will not be indexed but will
still be included in the results of the second pass filter, if applicable. Optimizing the spatial indexes
for each feature class will improve performance and should be done at the database level.
GeoMedia Professional will only create a default spatial index, and it may or may not be optimal.
Page 23
 SQL Server Spatial Data Server
User Guide

If you need to modify any of the other GPARAMETER/GVALUE pairs, you should first consult
GeoMedia customer support.

GPickLists Table
The GPICKLISTS metadata table contains the Picklist assignments used by both the Properties dialog
box and the data window in GeoMedia Professional. Also known as domain lists, Picklists allow for a
predefined list of values to be used when updating attribute fields. GPICKLISTS is defined, as follows:

 The primary key is a combination of the FEATURENAME and FIELDNAME columns. These columns
refer to the feature class and the specific attribute field for which the Picklist is to be used.
 PICKLISTTABLENAME – Specifies a table in the schema containing the PickList values.
 VALUEFIELDNAME and DESCRIPTIONFIELDNAME – Refers to the name of the columns in the table
containing the Picklist values.
 VALUEFIELDNAME – Specifies the field in the Picklist table that contains the values to be stored in
the database. The data type of the field in the Picklist table specified here must match the data type
of the attribute assigned in the FIELDNAME.
 DESCRIPTIONFIELDNAME – Specifies the field that contains Picklist descriptions to be displayed in
the pop-up menu on the Properties dialog box.
 The values stored in VALUEFIELDNAME and DESCRIPTIONFIELDNAME could be the same when the
displayed values are the same as the stored values.
 FILTERCLAUSE – Is optional and may contain a SQL WHERE clause that will be used to filter the
records in the Picklist. The filter allows a single Picklist table to be used when creating multiple
Picklists.
Picklist tables can be any tables that contain the required information, including existing feature
classes. You can implement a Picklist as a code list (using separate value and description entries) or as
a domain list (when value and description entries are the same). Ranges are not supported.

The Picklist metadata table can either be populated manually or by using the Picklist Manager utility.
This utility is available from Intergraph Customer Support. For more information, visit the SG&I
Support page (http://support.intergraph.com/).

The following is an example of tables, columns, and values that could be defined for Picklists:

GPickLists

Page 24
 SQL Server Spatial Data Server
User Guide

FEATURENAME FIELDNAME PICKLIST VALUE DESCRIPTION FILTERCLAUSE

TABLENAME FIELDNAME FIELDNAME
BUILDINGS NAME PL_BUILDING CODE_VALUE VAL_DESCRIPTION BLD_TYPE = 'NAME'

BUILDINGS STATE PL_STATE STATE_NAME DESC

BUILDINGS TYPE PL_BUILDING CODE_VALUE VAL_DESCRIPTION BLD_TYPE = 'TYPE'

PL_Building

CodeValue ValDescription Bld_Type

0 MOTEL TYPE
1 MARRIOT NAME
2 HOLIDAY INN NAME
3 BED AND BREAKFAST TYPE
4 DAYS INN NAME
PL_State

StateName Desc
Alabama ALABAMA
Arkansas ARKANSAS
Colorado COLORADO
Texas TEXAS
Florida FLORIDA

Queue Table
The GQUEUE metadata table is used to store the static queues for the Queued Edit command. The
columns in GQUEUE are populated through commands in GeoMedia Professional and are used solely
by the Queued Edit command. This table is not user editable and should not be modified in any way.

ModifiedTables
MODIFIEDTABLES is a join view that provides the object ID for each table/view. The view uses an
inner join between the SYSOBJECTS table and the SYSINDEXES table in conjunction with a union on
GINDEXCOLUMNS. The MODIFIEDTABLEID in this view provides the values for the
MODIFIEDTABLEID used in the MODIFICATIONLOG table. This value is used to identify the edited
table in the MODIFICATIONLOG table. This view is not user editable and should not be modified in
any way.

Page 25
 SQL Server Spatial Data Server
User Guide

ModificationLog Table
The MODIFICATIONLOG metadata table tracks modifications made from the GeoMedia environment
for all feature classes in the connected schema. Specifically, it is used to track all inserts, updates, and
changes made to tables/views listed in MODIFIEDTABLES. The MODIFIEDTABLEID is the common link
between MODIFICATIONLOG and MODIFIEDTABLES. The definition of the MODIFICATIONLOG table
is, as follows:

 MODIFICATIONNUMBER – The auto-increment key filed for the table.

 TYPE – The type of edit that has occurred: 1 for insert, 2 for update, and 3 for delete.
 MODIFIEDTABLEID – The column identifier from MODIFIEDTABLES.
 KEYVALUE1 to KEYVALUE10 – These fields store the primary key column values for the edited row.
If there is only one primary key column, only KEYVALUE1 is used. For multi-column primary keys,
the values from each field that makes up the key are stored here. A primary key can be made up of
a maximum of 10 columns.
 SESSIONID – Identifies the SQL Server session making the edit. This field is populated automatically
from a function-based default value.
 MODIFIEDDATE – Identifies the date and time of the edit. This field is populated automatically from
a function-based default value.
The MODIFICATIONLOG table is part of the GeoMedia notification system. All edits made to feature
classes within the connected SQL Server database are tracked in the MODIFICATIONLOG table. Over
time, this table can grow very large very quickly. Because the size of the MODIFICATIONLOG table can
negatively affect editing performance in GeoMedia applications, the table should be periodically
truncated. However, do not clear this table while there are open GeoMedia sessions. The Clear
Modification Log command in Database Utilities will truncate this table. You can also use the
following SQL to clear this table:
Truncate Table dbo.ModificationLog
Page 26
 SQL Server Spatial Data Server
User Guide

You could also set up a SQL Server job to do this automatically; just make sure it runs when there are
no active GeoMedia sessions.

The MODIFICATIONLOG table is currently only configured to track modifications made through the
GeoMedia environment. Modifications to the data made outside of GeoMedia do not update the
MODIFICATIONLOG table; thus, GeoMedia sessions are not notified of those changes.

To solve this issue, you can create triggers that will automatically provide modification logging. To
prevent insert events from happening twice, the triggers must have names that are recognized by the
SQL Server data server:

 The trigger for insert must have a name that corresponds to the feature class name appended by
GMTI.
 The trigger for update must have a name that corresponds to the feature class name appended by
GMTU.
 The trigger for delete must have a name that corresponds to the feature class name appended by
GMTD.
For example, if the feature class is STATES, the triggers must have the name STATESGMTI,
STATESGMTU, and STATESGMTD. This rule holds true regardless of whether the feature class is a
table or a view. When the triggers are detected, GeoMedia will offload all the modification logging for
the specific feature class to the trigger.

Each trigger fires on the specific editing event and writes an entry into the MODIFICATIONLOG table:

 TYPE is populated with the following constants: 1 - Insert, 2 – Update, or 3 – Delete.

 MODIFIEDTABLEID is populated with the object ID of the object for which the entry is created. This
field comes from the MODIFIEDTABLES view.
 KEYVALUE1 to KEYVALUE10 are populated by converting the primary key value to NVARCHAR(255).
For a single column primary key, only KEYVALUE1 is populated. If the primary key consists of
multiple columns, the additional columns can be added to KEYVALUE2 through KEYVALUE10.
Primary keys consisting of more than 10 columns are not supported.
If the primary key is user editable (non-composite or does not contain an identity field), all
modifications must create two entries, one for the old key value and one for the new key value.

The following are examples of the insert, update, and delete triggers for a feature class (table) called
STATES, whose primary key column is ID:

CREATE TRIGGER [dbo].[StatesGMTI] ON [dbo].[States] FOR INSERT AS

DECLARE @TableID INT
IF object_id('tempdb..#DisableModificationLog') is null
BEGIN
SELECT @TableID=id FROM SYSOBJECTS WHERE name='States';
INSERT INTO ModificationLog([Type], [ModifiedTableID], KeyValue1)
SELECT 1, convert(nvarchar(20),@TableID), convert(nvarchar(255), inserted.[ID])
FROM inserted;

Page 27
 SQL Server Spatial Data Server
User Guide

END;
GO
--

CREATE TRIGGER [dbo].[StatesGMTU] ON [dbo].[States] FOR UPDATE AS

DECLARE @TableID INT
IF object_id('tempdb..#DisableModificationLog') is null
BEGIN
SELECT @TableID=id FROM SYSOBJECTS WHERE name='States'
INSERT INTO ModificationLog([Type], [ModifiedTableID], KeyValue1)
SELECT 2, convert(nvarchar(20),@TableID),convert(nvarchar(255),deleted.[ID])
FROM deleted;
INSERT INTO ModificationLog([Type], [ModifiedTableID], KeyValue1)
SELECT 2, convert(nvarchar(20),@TableID), convert(nvarchar(255), inserted.[ID])
FROM inserted;
END;
GO
--

CREATE TRIGGER [dbo].[StatesGMTD] ON [dbo].[States] FOR DELETE AS

DECLARE @TableID INT
if object_id('tempdb..#DisableModificationLog') is null
BEGIN
SELECT @TableID=id FROM SYSOBJECTS WHERE name='States'
INSERT INTO ModificationLog([Type], [ModifiedTableID], KeyValue1)
SELECT 3, convert(nvarchar(20),@TableID), convert(nvarchar(255), deleted.[ID])
FROM deleted
END;
GO
--

To make notification work with views, you need to add an entry to the base table trigger that handles
the modification to the view. For example, if you have a simple view on STATES called STATES_VIEW,
you could use the following trigger to handle notification for inserts:

CREATE TRIGGER [dbo].[StatesGMTI] ON [dbo].[States] FOR INSERT AS

DECLARE @TableID INT
IF object_id('tempdb..#DisableModificationLog') is null
BEGIN
SELECT @TableID=id FROM SYSOBJECTS WHERE name='States';
INSERT INTO ModificationLog([Type], [ModifiedTableID], KeyValue1)
SELECT 1, convert(nvarchar(20),@TableID), convert(nvarchar(255), inserted.[ID])
FROM inserted;
SELECT @TableID=id FROM SYSOBJECTS WHERE name='States_View';
INSERT INTO ModificationLog([Type], [ModifiedTableID], KeyValue1)
SELECT 1, convert(nvarchar(20),@TableID), convert(nvarchar(255), inserted.[ID])
FROM inserted;

Page 28
 SQL Server Spatial Data Server
User Guide

END;
GO

The trigger in the above example will handle edit notification for both the table and the view, but
GeoMedia will still attempt to write another notification for the view itself. To stop this second
notification event, another trigger needs to be created on the view using the view name:
CREATE TRIGGER dbo.States_View_GMTI ON dbo.States FOR INSERT AS
DECLARE @TableID INT
if object_id('tempdb..#DisableModificationLog') is null
BEGIN
SELECT @TableID=1
END
GO

The trigger itself is still on the base table STATES; it is only the name of the trigger that refers to
the view. This is essentially a dummy trigger; it does not do anything other than telling GeoMedia not
to write directly to the MODIFICATIONLOG table.

When you edit through a view, it is the underlying base table that is actually edited, and in that case, a
modification log trigger is required. This becomes more complicated as more views are added on the
same base table. Every update to the base table should also update the MODIFICATIONLOG table for
every view that is dependent on the base table. For join views, you will need to take into account all
the base tables and associated views. In the case of join views, most editing would be handled
through INSTEAD OF triggers. In this case, you could embed the insert into the MODIFICATIONLOG
table directly using the INSTEAD OF trigger as long as the trigger name adheres to the rules listed
above.

Data Server Required Triggers in SQL Server Spatial

To maintain the relationship between the native geometry and GeoMedia's binary GDO geometry, an
after-insert and an after-update trigger are required for every table. When a native geometry column
is inserted/updated outside of GeoMedia, this trigger sets GeoMedia's binary GDO geometry to NULL.
The next time the data is read by the data server, the binary GDO column value will be reconstructed
from the native value. These triggers are automatically created for every feature class created by
GeoMedia Professional. If you create your tables outside of the GeoMedia Professional environment,
you will need to manually add these triggers. The general format for each of these triggers is shown
below:
CREATE TRIGGER <tablename>_<native_geom_col>_INS ON <tablename>
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.<native_geom_col> IS NULL
AND INSERTED.<gdo_geom_col> IS NOT NULL)
BEGIN
RAISERROR (N'Unsupported. Cannot specify value for GDO column only,
native column value must also be provided.', 0, 1)
ROLLBACK TRANSACTION
Page 29
 SQL Server Spatial Data Server
User Guide

END;
END;
GO

CREATE TRIGGER <tablename>_<native_geom_col>_UPG

AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(<native_geom_col>)
BEGIN
IF NOT UPDATE(<gdo_geom_col>)
BEGIN
UPDATE <tablename> SET <gdo_geom_col> = NULL
WHERE EXISTS (SELECT NULL FROM INSERTED
WHERE INSERTED.<primaryKeyColumn> = <tablename>.<primaryKeyColumn>
END
END
ELSE IF UPDATE(<gdo_geom_col>)
BEGIN
RAISERROR ('Unsupported. Cannot specify value for GDO column only,
native column value must also be provided.', 0, 1)
ROLLBACK TRANSACTION
END
END;
GO

The use of these triggers can mean data loss is possible for some types of geometries. For example, if
an OrientedPointGeometry is stored in SQL Server, GeoMedia's binary GDO geometry field contains
the actual oriented point geometry, while the native geometry field stores only the point location.
When a non-GeoMedia client updates the native POINT (x y z) geometry, the orientation vector is lost.
A more complicated scenario occurs when arcs are stored in the binary GDOgeometry and
corresponding stroked polylines are stored in the native geometry. In this case, information
referencing the geometry as an arc will be lost, and its stroked PolylineGeometry will remain.

SQL Server Spatial Indexing

Spatial filtering in GeoMedia will use standard SQL Server spatial filtering operations. These rely on
spatial indexes on the native geometry fields. For tables created using GeoMedia Professional, the
spatial indexes are created automatically using the syntax below.

For native geometries using the GEOGRAPHY data type, the spatial index is created using the following
syntax:
CREATE SPATIAL INDEX <TABLE_NAME>_<NATIVE_COLUMN_NAME>_sindx
ON <TABLE_NAME> (<NATIVE_COLUMN_NAME>);

For native geometries using the GEOMETRY data type, the spatial index is created using the following
syntax:
CREATE SPATIAL INDEX <TABLE_NAME>_<NATIVE_COLUMN_NAME>_sindx
ON <TABLE_NAME> (<NATIVE_COLUMN_NAME>)
Page 30
 SQL Server Spatial Data Server
User Guide

WITH (BOUNDING_BOX = (<XLowerBound>, <YLowerBound>, <XUpperBound>, <YUpperBound>))

where <XLowerBound>, <YLowerBound>, <XUpperBound>, <YUpperBound> are taken from

the GPARAMETERS table.

For existing tables that contain native geometries, the database administrator should ensure that the
spatial indexes exist and are up to date. Spatial indexes can only be created on tables that have a
clustered primary key. The maximum number of key columns allowed on any given table is 15, and the
maximum size of the index key records is 895 bytes. The primary key definition cannot be changed
while the spatial index exists. For best performance, use a single integer-based primary key populated
by an identity increment.

The parameters used to create a spatial index, particularly for projected data, can have a large effect
on overall query performance. Refer to SQL Server Books Online for more information on creating and
optimizing spatial indexes.

A default spatial index is automatically created whenever a table is created via GeoMedia, but
there is no guarantee that this index will be optimal. For best performance, you should optimize every
spatial index for the specific geometry stored and periodically rebuild the index as new data is entered.

Page 31
 SQL Server Spatial Data Server
User Guide

WORKING WITH SQL SERVER SPATIAL

GeoMedia Professional's SQL Server native spatial data server utilizes two distinct columns in a SQL
Server table to store the binary representation of spatial vector data. Its stores SQL Server's native
spatial format (WKB – well known binary) in a column using the native GEOMETRY/GEOGRAPHY data
type and stores GeoMedia's GDO format in a column using the VARBINARY(max) data type (for those
geometries that are not directly supported by native spatial). For every native spatial column in a
table, there must be a corresponding GDO column, and the association must be stored in GeoMedia's
GFieldMapping metadata table.

For existing native spatial data, the GDO column will have to be added and the association made via
Database Utilities. Initially, the GDO column will only contain NULL rows. During GeoMedia INSERT
and UPDATE operations, the data server will automatically populate records in both columns using
GDO format for the varbinary column and WKB for the native geometry. This ensures that geometry
types that are currently not supported by SQL Server can still be retrieved from the GDO column while
a close approximation is stored in the native geometry column (see the section GeoMedia's Binary
Geometry to Native Geometry Type Matching for more information).

During an INSERT operation, a trigger fires to verify that if a new geometry record is inserted into the
GDO column, the same (or a close approximation) geometry is also inserted into the corresponding
row of the native geometry column. Every geometry record in the GDO column must always have a
corresponding geometry record in the native geometry column (the native geometry record cannot be
NULL if there is a GDO record). However, a new geometry record can be inserted into the native
geometry column without a corresponding record being inserted into the GDO column (the GDO
record can be NULL).

During an UPDATE operation, a trigger fires to verify that both columns are being updated, if only the
row in the native geometry column is being updated, the corresponding row in the GDO column is set
to NULL. This situation would only happen if the native geometry record was edited outside of the
GeoMedia environment. If GeoMedia detects a NULL row in the GDO column, it will read the
corresponding row from the native geometry column. The next time an update on this same row
occurs in GeoMedia, the corresponding row in the GDO column will again be populated. While this
ensures that the geometry records remain in sync, it can lead to loss of data in some cases. For
example, if you have a rotated point that is used for symbology and the native geometry for that point
is edited outside of GeoMedia, the rotation value will revert to zero because the corresponding row in
the GDO column will be set to NULL and GeoMedia will only read the native geometry the next time
the point is displayed. You would have to reset the point's rotation using GeoMedia which would
replace the NULL record in the GDO column with the current native geometry and the updated point
rotation.

Using Existing Native Spatial Data

If you have existing native spatial data, it must be 3D; 2D geometries are not supported. You will also
need to add a VARBINARY(MAX) column to support GeoMedia's binary GDO geometry format used
for unsupported geometries (for example, arcs, oriented points, text, and raster). This column can be
named anything, but it is best to make it similar to the column name of the native geometry. This will

Page 32
 SQL Server Spatial Data Server
User Guide

make associating the two columns a lot easier when metadata is assigned using Database Utilities. For
example, if the table ROADS contains a native geometry column called CENTERLINE and an identity-
based primary key called ID, you need to add a new column called CENTERLINE_GDO:
ALTER TABLE ROADS ADD COLUMN CENTERLINE_GDO VARBINARY(MAX)
GO

Each table also requires two maintenance triggers for the additional GDO column: an after insert and
an after update trigger. See the Data Server Required Triggers in SQL Server Spatial section for more
information. For the example ROADS table listed above, the triggers would look like the following:
CREATE TRIGGER [dbo].[ROADS_GEOMETRY_INS] ON [dbo].[ROADS]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[CENTERLINE] IS NULL
AND INSERTED.[CENTERLINE_GDO] IS NOT NULL)
BEGIN
RAISERROR(N'Unsupported. Cannot specify value for GDO column only,
native column value must also be provided.', 0, 1)
ROLLBACK TRANSACTION
END
END;
GO
CREATE TRIGGER [ROADS_GEOMETRY_UPG] ON [dbo].[ROADS]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
IF UPDATE([CENTERLINE])
BEGIN
IF NOT UPDATE([CENTERLINE_GDO])
BEGIN
UPDATE [ROADS] SET [CENTERLINE_GDO] = NULL
WHERE EXISTS (SELECT NULL FROM INSERTED
WHERE INSERTED.[ID] = [ROADS].[ID])
END
END
ELSE IF UPDATE([CENTERLINE_GDO])
BEGIN
RAISERROR('Unsupported. Cannot specify value for GDO column only,
native column value must also be provided.', 0, 1)
ROLLBACK TRANSACTION
END
END;
GO

Once the binary column and the triggers have been added, you will need to add the metadata tables
required by GeoMedia applications and then add the metadata entries for each table you want to use
as a feature class. You can use Database Utilities for both of these operations.

Page 33
 SQL Server Spatial Data Server
User Guide

Importing Spatial Data

The easiest way to get started using the SQL Server Spatial data server is by bulk importing data from
another data source. GeoMedia Professional has two commands that will move data to SQL Server
warehouses, Export to SQL Server and Output to Feature Classes.

Export to SQL Server creates a set of files and an IMPORT.BAT script that will load a SQL Server
database from any data source that GeoMedia Professional is connected to. The process will use the
coordinate system of the GeoWorkspace for the output. The resulting export files use SQL Server’s
CMDSQL and BCP to load the data. For this reason, imports can only be run on a SQL Server
Administrative Client or on the system where SQL Server is installed. This method is very fast and is
ideal for bulk loading large amounts of data (>1000000 rows per table). This method also allows you
to optionally create the required metadata tables.

Output to Feature Classes requires that metadata tables already exist in the target SQL Server
database. To manually create the metadata, use GeoMedia Professional’s Database Utilities, connect
to the new database using an account with the DB_OWNER role, and then click Create Metadata
Tables. Once the metadata is created, you will be able to connect to the warehouse through
GeoMedia Professional and then use Output to Feature Classes to create feature classes. This
command is very flexible and allows you to make modifications to the table/column definitions, key
definitions, and coordinate system assignments. The drawback is in performance; this command is
considerably slower than Export to SQL Server and is best used for smaller datasets (<1000000 rows
per table).

Existing Standard SQL Server Data

If you have databases that currently use GeoMedia's standard SQL Server data server, the best way to
migrate them is to use one of the methods discussed in the previous section. You cannot convert a
GeoMedia Standard SQL Server dataset directly to the native spatial model at the database level.

Feature Class Definition

The Feature Class Definition command in GeoMedia Professional works the same as it does with other
read-write data servers. You can add tables and columns and edit existing tables and columns as long
as the user account you are connected with has the correct permissions. Just remember, if a login that
does not have the DB_OWNER assigned creates tables, they will only be accessible to the login that
created the tables. For best results, create tables while connected as the database administrator or
database owner.

When editing tables using Feature Class Definition, the following caveats apply:

 Never change the primary key column of a table after the table has been created. This could make
the table inaccessible by GeoMedia Professional. If you need to do this, use Database Utilities to
drop the metadata before altering the table using SQL Server’s own tools. You can re-add the
metadata after making the table change. If the table has a spatial index, you will need to drop it
before modifying the primary key.
Page 34
 SQL Server Spatial Data Server
User Guide

 Do not change data types on existing columns using Feature Class Definition. If you need to do this,
drop the metadata first, make the data type change using SQL Server's Management Studio, and
then re-insert the metadata using Database Utilities.
 Renaming a table can take a long time if the table contains data, and by default, SQL Server will
disallow this operation. The rename process creates a copy of the existing table, deletes the
original, re-creates the table with a new name, and then populates the data back to the newly
named table. If you need this capability, you may need to activate the capability using SQL Server's
Management Studio.
 Copying an existing table will not work due to the way the metadata for the GDO to native geometry
relationship is handled. If you need to copy a table, use SQL Server's Management Console.

Undo/Redo
If you use the Undo/Redo commands while editing the geometry or attributes associated with tables
that contain an identity column, be aware that the numeric sequence is not preserved. Auto-
increment identity columns are usually assigned as primary key columns, and they should not be used
as part of a foreign key. Failure to heed this warning could invalidate view-join definitions.

For example, a row of your data consists of an identity field called ID that contains the value 10, and
there are 300 total records in this table such that MAX(ID)=300. If you accidentally delete the row
ID=10 and use the Undo command to get it back, ID will now be assigned the next available number in
the auto-increment sequence, in this case 301. The original ID=10 is not recoverable. In all cases, the
next available autonumber value will be obtained on an undo/redo operation; the previous
autonumber value will not be preserved. This is actually by design; it is how Microsoft intends the
auto-increment field to be used.

Default Values
Default values can simplify data entry and supply values for columns that are either required or just
need to have a specific entry. Default values are honored by GeoMedia but not directly. When
inserting a new record with the option to display the Attribute Properties dialog box turned on, the
default values are not shown in the dialog box even though they are available at the database level.
They will only be used when the insert occurs. If the fields are required, you will not see an error;
instead, the insert will pick up the default values. However, if the option Copy attribute values from
previous feature is enabled (from the Placement and Editing tab on the Options dialog box), you will
no longer be able to use the default value. Instead, the value used in the previous insert will be used.
If you delete the previous value used in a required field, the default value will still not be used, and you
will get an error message.

For best results with defaults, either disable Copy attribute values from previous feature option, or do
not make the fields required. Function based defaults also will work, but again, you must disable the
Copy attribute values from previous feature option. This same problem will occur if you are using
triggers to populate required fields.

Page 35
 SQL Server Spatial Data Server
User Guide

Spatial Filtering
Spatial filtering will use the spatial index created on the tables' native geometry columns. The
performance of spatial indexes can vary widely, so if performance is an issue, consider re-creating the
spatial indexes using different parameters. GeoMedia applications have four different spatial filter
options (listed in order of performance):

 Coarse Overlap – This method uses SQL Server's FILTER method to return the results. This is
generally the fastest performing filter because it only uses the spatial index; however, it will always
pick up extraneous values.
 Overlap – This method uses SQL Server's STINTERSECTS method.
 Inside - This method uses SQL Server's STWITHIN method.
 Entirely Inside - This method uses SQL Server's STWITHIN method and then processes the final
results on the client. It will generally be the slowest filter method.
Simple rectangular polygons will return the quickest results; the more complicated the filter area, the
slower the process. For geographic data, SQL Server only supports FILTER and STINTERSECTS. For
these cases, GeoMedia will perform the final filtering on the client.

Views and Join Views

GeoMedia Professional makes no distinction between views and tables; it treats a view just like any
other feature class. The SQL Server Spatial data server handles read-write access to most types of
views as long as the views are key preserved. This means that the status of the primary key in the base
table is preserved in the view. One way to ensure this is true is to include and preserve the primary
key in the view definition. In a join-view, the primary key can come from either the left or the right
side as long as it retains its key status. It cannot come from both sides of the join and still be
updatable. In addition, only the side of the join containing the key will be updatable. For full editing
capability on a join view, an INSTEAD OF trigger is required.

For a view to be treated as a spatial feature class, the view definition must include both the native
geometry column and GeoMedia's binary GDO column. Metadata is also required to see the view in
GeoMedia applications, and Database Utilities can be used to insert the required metadata. For views
to be updatable in GeoMedia Professional, there must also be an entry for the key column in the
GINDEXCOLUMNS metadata table. This is required for both spatial and non-spatial feature classes
that are view based.

Page 36
 SQL Server Spatial Data Server
User Guide

DATABASE UTILITIES
Database Utilities consist of several utilities for managing and updating Access, Oracle, and SQL Server
databases for use with GeoMedia products. These utilities are delivered with GeoMedia Professional
and are accessible from the Start menu.

See the Database Utilities online Help for complete information.

Database Utilities includes seven separate database tools, but only six of these are available for SQL
Server. Here are the six basic tools:

 You can connect to a SQL Server spatial databases using either Windows domain authentication or
SQL Server authentication. For best results, all Database Utilities operations should be performed
by a database administrator login or by any user who has been assigned the DB_OWNER role.
 For new databases, select the Create Metadata Tables command before attempting any other
GeoMedia operation. Subsequent use of this command will update existing metadata with the
changes for the given release (if any).
 For tables or views created in SQL Server, use the Insert Feature Class Metadata command to add
the metadata required to see these as feature classes in GeoMedia. The primary difference between
the SQL Server spatial data server and the standard SQL Server data server is the assignment of the
native geometry field and the native SRID value:

Note: For the Geographic data type, you must select or enter a SRID (EPSG code) that matches the
coordinate system being used and the code must exist in SQL Server. For the Geometry data type,
the default SRID is 0 because SQL Server does not use or store SRID’s for projected data. You can
enter any SRID here and it will be stored in metadata but neither SQL Server not GeoMedia will
utilize it for coordinate system operations.
 To alter metadata already entered for existing feature classes, use the Edit Feature Class Metadata
command.
 To delete the metadata for an existing feature class, use the Delete Feature Class Metadata
command. If significant DDL modifications are going to be made to a table or view, the metadata
should first be deleted and then re-inserted after the modifications have been made.

Page 37
 SQL Server Spatial Data Server
User Guide

 To assign a default coordinate system to a new database or to re-assign coordinate systems for
existing feature classes, use the Assign Coordinate System command. For existing feature classes,
this command changes the coordinate system assignment without changing the data. Use discretion
here; assigning an incorrect coordinate system can cause problems when editing. Make sure the
correct coordinate system is assigned.

Page 38
 SQL Server Spatial Data Server
User Guide

EXPORTING TO SQL SERVER

Use the Export to SQL Server command to export data from a legacy data store to a SQL Server 2008
or later database for use with the GeoMedia product suite. This command is intended for bulk loading
large amounts of data into a database by generating ASCII files that can be used by SQL Server’s Bulk
Loader application. This command is NOT intended to be used as an update tool. Before using this
command, you must verify that the GeoWorkspace coordinate system is the appropriate target
coordinate system for the data you want to export.

To use this command, you first select the data to be exported from a treeview of all feature
classes/queries, including categories and reference features. You can select any mixture of feature
classes, queries, categories, and reference features, across any number of connections.

This command offers the following two modes:

 SQL Server non-Spatial – for SQL Server 2005 or later. Geometry storage uses VARBINARY(MAX).
 SQL Server Spatial – SQL Server 2008 or later. Geometry storage uses native spatial
GEOMETRY/GEOGRAPHY combined with VARBINARY(MAX) for unsupported geometry types.
SQL Server 2000 was de-supported starting from GeoMedia 6.1.11. Only SQL Server 2005 and
later version are supported starting from GeoMedia 6.1.11. Native spatial storage is only available in
SQL Server 2008 or later.

You can write the command output in SQL Server native spatial format for either a projected or non-
projected target coordinate system. For a non-projected target coordinate system, you select the
appropriate spatial reference system from all the supported spatial reference systems.

The export process takes the source data as is with no modification. Source data that is not
from another database may have problems once it is imported into the SQL Server spatial
environment. Column names may be illegal, primary key columns may not exist, and there may be
data issues. After import, verify that the data model conforms to the requirements of SQL Server
spatial, and make any necessary corrections before using the data in the GeoMedia environment. You
should also make sure that spatial indexes are created on the spatial geometry columns after any
import operation.

In the Exporting options on the Export to SQL Server dialog box, the Export native spatial format
check box is enabled whether the active target coordinate system is projected or non-projected.
When this check box is checked and a non-projected target coordinate system is active, the Spatial
reference system identifier drop-down list is enabled and populated with all supported spatial
reference systems, from which you choose the appropriate one. Export to SQL Server creates the
following files based on the coordinate system of the GeoWorkspace:

 METADATA.SQL—Creates GeoMedia's required metadata objects.

 FEATURECLASSNAME_PRE.SQL (one for each feature class or query exported)—Creates the table
using defaults. You can also create the table or edit the delivered script file for more control.
 FEATURECLASSNAME.BCP (one for each feature class or query exported)—Data file for loading
data.

Page 39
 SQL Server Spatial Data Server
User Guide

 FEATURECLASSNAME_POST.SQL (one for each feature class or query exported)—Populates the SQL
Server metadata table and all GeoMedia metadata tables.
 FEATURECLASSNAME.DAT—Contains the attribute and geometry data for use with the bulk load
processor.
 IMPORT.BAT—Script file with all of the above files, which uses common defaults and can be edited
for handling specific options.
 EXPORT.LOG—Log file that contains either the cause of failure if error conditions arise or the
number of features successfully exported per selected feature class during the export process.
By default, the data is exported to the \WAREHOUSES folder. You can change this location on the
dialog box, and this location is then remembered as a session preference.

Error conditions are not reported to you while the Export to SQL Server command is being run.
This is to improve performance and to ensure uninterrupted exports of large sets of data. You should
review the EXPORT.LOG file at the completion of the export to determine if any errors occurred during
the export process.
To export data to SQL Server:
1. Connect to the existing warehouse from which you want to export data.
2. Verify that the GeoWorkspace coordinate system is the appropriate target coordinate system for
the data that is to be exported.
3. Select Warehouse > Export to > SQL Server to open the Export to SQL Server dialog box.

4. Select the appropriate items from the Features to export tree view.
Holding the cursor over an entry displays a tooltip with the geometry type of the entry.
5. OPTIONAL: Check the Export to native spatial format check box.

Page 40
 SQL Server Spatial Data Server
User Guide

6. With the check box in the previous step checked, and using an active non-projected target
coordinate system, select the appropriate reference system from the Spatial reference system
identifier drop-down list,

When you select a spatial reference system identifier for the first time with an active non-
projected coordinate system, the following warning message is displayed:
Verify that the GeoWorkspace coordinate system is identical to the selected spatial system
before proceeding.
This warning is also displayed when the default selection is restored from session preferences and
you have not changed it.
7. Select the appropriate Export folder.
8. Click Apply to export the data.
The following error message is displayed when there is no selected spatial reference system
identifier for a non-projected active coordinate system, and you click Apply:
Select a spatial reference system identifier that matches the GeoWorkspace coordinate
system before proceeding.
9. Examine the output ASCII files, and modify them if necessary.
10. Run the output script file.
11. Use Bulk Loader to create SQL Server tables and to load the geometry and attributes to the SQL
Server database.

Page 41
 SQL Server Spatial Data Server
User Guide

The information contained in this document (the “Work”) is the exclusive property of Intergraph Corporation (“Intergraph”). The Work is protected under
United States copyright law and other international copyright treaties and conventions, to include the Berne and Geneva Phonograms Conventions, the
WIPO Copyright Treaty, and the World Trade Organization.

No part of the Work may be reproduced, stored, or transmitted in any form or by any means, electronic or mechanical, including photocopying and
recording, or by any information storage or retrieval system, except as expressly permitted in writing by Intergraph. All such requests should be sent to
the attention of Manager of Customer Education of the Hexagon Geospatial Division of Intergraph (“Hexagon Geospatial”) at the following address:

Hexagon Geospatial
5051 Peachtree Corners Circle
Norcross, Georgia 30092-2500 USA

Phone: 770 / 776-3651

Fax: 770 / 776-3694

Support Services: 800 / 953-6643

Customer Education: 800 / 373-2713
Web: www.hexagongeospatial.com

Page 42