The intention of this article is to show the user how they can create additional metadata with FME so that GeoMedia can interact with the native SQL Server Spatial tables.
This works with SQL Server 2012 and higher.
A note from Chuck at Intergraph: Mixing GeoMedia 2015 and earlier versions on the same native spatial data is not recommended. GeoMedia 2015 uses a different binary format than 2014 and earlier, it is based on Microsoft's binary for SQL Server 2012 or later. Once a feature class is edited in 2015, you cannot use it in an early version of GeoMedia like GeoMedia 2014.
Using " GeoMedia SQL Server Warehouse" Writer in FME:
When FME creates new tables in a SQL Server database FME is capable of generating the necessary GeoMedia metadata, that makes the new table read/write enabled from the GeoMedia application.
Using " Microsoft SQL Server Spatial" writer in FME: To write to a SQL Server Native Spatial table, FME creates the spatial table but does not generated the necessary metadata for GeoMedia as this format is native to Microsoft SQL Server Spatial Database.
In order for GeoMedia to have read/write capabilities it is necessary to add additional metadata to the GeoMedia Metadata tables (GFeatures etc). FME does not do this by default.
SQL Server Spatial - Native Spatial Data Type
If you have recently moved to SQL Server Spatial or are thinking about it and plan to use the native Spatial data with GeoMedia you will need to be aware of a few additional steps.
You may notice when you try to add the GeoMedia Metadata for the new SQL Server Native Spatial table that the Database Utilities won't display the geometry tab in the dialog. Basically Database Utilities isn't aware of the geometry type and ignores it. You can still add the table to the metadata tables but GeoMedia will not display the geographical data from the table as it isn't aware of the geometry data stored with the table. You will be able to view the table, in tabular view, using "New Data Window" in GeoMedia while other 3rd party applications may recognized the spatial table and display it as expected.
This is an indication that the spatial table is missing an additional
varbinary column (required by GeoMedia). This is a unique requirement to GeoMedia.
FME created new tables but I can't see them in GeoMedia
The FME SQL Server Spatial Writer doesn't have the ability to create the <GEOM>_GDO column (data type - varbinary) when it creates a table in SQL Server Spatial database. GeoMedia requires this column, 2 triggers, and metadata in order to interact with a native Spatial table in SQL Server Spatial.
Steps required to make GeoMedia aware of the geometry in any SQL Server Spatial table.
Review the " GeoMedia SQL Server Spatial User Guide" that comes with the SQL Server Spatial AddOn for GeoMedia 2014. It contains a section called "Using Existing Native Spatial Data" that covers the steps required. This is where the following instructions came from and I will refer you to that document for further information.
1) GeoMedia requires a column to be added to the new table...
From within MS SQL Server Studio run the following scripts (please edit the table names and columns to match your table name and column names) ex: NOTE: The following script assumes that the native SQL Server Spatial geometry column is called GEOM and the GeoMedia geometry column is called GEOM_GDO, [ID] column refers to the IDENTITY column of your table.
ALTER TABLE [schema].[tablename] ADD GEOM_GDO VARBINARY(MAX);GOThen further, 2 new triggers to be created on this table... ex:
CREATE TRIGGER [tablename_INS] ON [schema].[tablename] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[GEOM] IS NULL AND INSERTED.[GEOM_GDO] IS NOT NULL) BEGIN RAISERROR ('Unsupported. Cannot specify value for GDO column only, native column value must also be provided.', 0, 1) ROLLBACK TRANSACTION END; END; GO CREATE TRIGGER [tablename_UPG] ON [schema].[tablename] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE([GEOM]) BEGIN IF NOT UPDATE([GEOM_GDO]) BEGIN UPDATE [schema].[tablename] SET [GEOM_GDO] = NULL WHERE EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[ID] = [tablename].[ID] 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
As indicated in this User Guide from Intergraph - once you have the column and triggers in place you can then run the Database Utilities to load the metadata for GeoMedia. You'll need to ensure you run Database Utilities from a system where both GeoMedia and the SQL Server Spatial Software AddOn from Intergraph has been installed.
This has been tested with SQL Server 2008, GeoMedia 2014 and FME 2015.
We've put together a workspace (attached - geom-gdoforgeomedia.zip) that will assist you in updating existing SQL Server Spatial tables. This workspace will add the necessary
varbinary column and the 2 required triggers to native spatial table. The workspace reads a CSV file that contains a list of existing tables and columns.
About the CSV File: The CSV file can be created in the same FME workspace (using a Text Line Writer) that created the new spatial tables and then used in a second workspace (like the one attached) to alter the spatial tables. Alternatively if you are dealing with existing tables it would be possible to use SQL to return a listing of spatial tables that can then be saved to a text file and used in this workspace.
Unfortunately it is still necessary to use
Database Utilities to add the tables to GeoMedia's metadata tables for interaction with Metadata.
Intergraph now has some PL/SQL tools that can help automate and add the necessary metadata to allow for GeoMedia interaction (after the
varbinary column and triggers have been added to the table - see above).
This is supported on SQL Server 2008 and above.
"GMP Spatial Utilities - Stored Procedures for use with GeoMedia's SQL Server Native Spatial Data Server" from Intergraph can be downloaded from Hexagon Support. The download contains database packages.
These database packages will need to be installed in the SQL Server database where the native spatial tables are stored.
*At time of posting the link is working. If the link is broken let us know using the comments section below.
Alternatively, visit Hexagon Support and search for the title "GMP Spatial Utilities - Stored Procedures for use with GeoMedia's SQL Server Native Spatial Data Server".
After installing the packages it is possible to have your FME workspace call the
GMPSetMetadataFor package and populate the necessary metadata for interaction with GeoMedia.
Please let us know if you have any difficulties.
Hope this helps someone! Happy FME'ing.
2 People are following this .