GeoMedia Discussions

Search for an answer, post a question, or answer other users' questions in our GeoMedia support discussions. This discussion board is a great way to collaborate with industry peers around the world. It is intended for discussion and support of the GeoMedia Desktop and Add-on applications.
Showing results for 
Search instead for 
Do you mean 
Reply
Highlighted
Occasional Contributor
Posts: 6
Registered: ‎04-26-2017

Inserting a new object creates a binary geometry but no native geometry

Hello,

 

i converted a GM-project from Access to SQL-Server using the 'Export to' function in GM. Then i used the sql-files to create the table and insert the data from Access to SQL-Server. In Access there are 2 geometry columns. One with a binary and one with a native geometry. These 2 columns where also created in the SQL-Server table. There was also a trigger created which prevents the insert of data without a native geometry. When i want to insert a new object using GM, this trigger prevents the insert, because GM only creates a binary geometry and the native geometry is null. I added the SQL-scripts created by GM as an attachment.

 

Version: GeoMedia Desktop Professional 2016

 

Thanks in advance,

Jens Keller

Highlighted
Regular Contributor
Posts: 226
Registered: ‎05-25-2016

Re: Inserting a new object creates a binary geometry but no native geometry

Hi Jens

 

First a question. What you mean with 'native geometry' in an Access Warehouse?

 

Usually there is only one binary geometry field in Access Warehouses. The second column (by default called Geometry_SK) is only for the index.

 

hesrah

Highlighted
Staff
Posts: 156
Registered: ‎05-26-2016

Re: Inserting a new object creates a binary geometry but no native geometry

Jens

 

Not sure I understand why you have run Output to Feature class and also run a script, unless your trying two methods of creating your database.

 

Normally you would create your database using Microsoft SQL Server. Then you would go into Geomedia  Desktop 2016 > Database Utilities

Open the database as “SQL Server Spatial”, Create Metadata Tables and Assign Coordinate system.

 

Then in Geomedia you would Output to Feature Class, which would build your feature schema as per the Access database. Functions and triggers would all be built and operational.

 

==

 

On the other hand if you have Access Geomedia data and you built a similar schema, you would have to Add the SQL Native Geometry column yourself to each spatial feature.

 

ie ALTER TABLE <table_name> ADD GEOMETRY_SPA <Geometry or Geography>;

Then Add metadata tables to database using GeoMedia Database Utilities. Ensure when you login that you select Database Type as ‘SQL Server Spatial’.

 

Select Create Metadata Tables to build. When complete select Insert Feature Class Metadata and assign geometry columns and coordinate system to the spatial features. Assign Native Geometry Names and change the Geometry Type and Native SRID

 

Next you should build Spatial Indexes, as the Access ones are not used , the <feature>_SK column is of no use.

ie CREATE SPATIAL INDEX [<feature>_Geometry_SPA_sindx] ON [dbo].[ Feature] (GEOMETRY_SPA);

 

Now , Create database triggers for Insert and Update operations on geometry Tables for use by GeoMedia.

 

ie - Create_Triggers.sql

CREATE TRIGGER [dbo].[ADDRESS_DEFAULT_GEOCODE_Geometry_SPA_ins] ON [dbo].[ADDRESS_DEFAULT_GEOCODE] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[Geometry_SPA] IS NULL AND INSERTED.[Geometry] 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 [dbo].[ADDRESS_DEFAULT_GEOCODE_Geometry_SPA_upd] ON [dbo].[ADDRESS_DEFAULT_GEOCODE] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE([Geometry_SPA]) BEGIN IF NOT UPDATE([Geometry]) BEGIN UPDATE [ADDRESS_DEFAULT_GEOCODE] SET [Geometry] = NULL WHERE EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[address_default_geocode_pid] = [ADDRESS_DEFAULT_GEOCODE].[address_default_geocode_pid]) END END ELSE IF UPDATE([Geometry]) BEGIN RAISERROR ('Unsupported. Cannot specify value for GDO column only, native column value must also be provided.', 0, 1) ROLLBACK TRANSACTION END END

GO

 

It is not unusual to see NULL data in Native and GDO Geometry spatial columns. Initially data will only be in GDO column in your case, until that point where you run a function which needs to update or insert the data. Then you will get Native populated. Similarly if new data is added to Native, you don’t necessarily get data in GDO column. Only oriented point feature data like Point, Text or some complex compound geometry will get GDO geometry column populated if database uses SQL Spatial database.

 

See GeoMedia Help, Contents  > Using SQL Server Spatial Connections for more info.

Highlighted
Occasional Contributor
Posts: 6
Registered: ‎04-26-2017

Re: Inserting a new object creates a binary geometry but no native geometry

Hello Roland,

 

sorry for the delay but i was on vacation. I tried the "SQL Server Spatial" connection type and now i can create new objects.

 

This is what i did:

 

  • create a new GM workspace and connect to the Access-DB
  • used the 'Export to' function in GM to export to SQL
  • this creates 2 sql-files and 1 dat-file for every feature and a batch-file to start the import in a SQL-DB
  • the sql-files contain the sql to create the table and insert the GM-metadata for every feature
  • starting the batch-file creates the table, inserts the data from the dat-file and creates the metadata and the triggers

The geometries in the Access-DB were in GK4. I transfomed the geometries in the converted SQL-DB to UTM-32 with an external tool. Then i created a new workspace in GM that uses UTM-32 and modified the metadata for GM in the DB to use UTM-32 for every feature. This worked fine for 5 Access-DBs i converted to SQL. But in 1 project i have the problem, that GM seems to transform the UTM-32 coordinates to UTM-32, when showing the features in the workspace. For some reason GM seems to think these coordinates are in GK4. Every setting i checked uses UTM-32 as the coordinate system. I even tried to connect to this SQL-DB from another GM-Installation and there GM shows the features in UTM-32 without a tranformation. The GM installation i'm having this problem with is version 15 (i have to use version 15 because the customer is using this version). The installation where it works is version 16. Do you have an idea what can cause this problem?

 

Thanks for your help,

Jens Keller

Highlighted
Staff
Posts: 156
Registered: ‎05-26-2016

Re: Inserting a new object creates a binary geometry but no native geometry

Your workflow seems a bit complex, and I have not used GM version 15 for quite some time.

 

This is what I would probably have done.

 

1. I would create an SQL Server Spatial database and then add GM Metadata and Assign default coordinate system , UTM32 using GM Database Utilities.

 

2. Then I would get into gws with Access GK4 database and make a connection to the UTM-32 SQL database.

 

3. Use the ‘Output to Feature Classes, command which creates the new Tables in the SQL database and transforms the data from GK4 to UTM32 at the same time. This builds the Triggers and Primary and Spatial indexes automatically.

 

Job done.

 

Highlighted
Occasional Contributor
Posts: 6
Registered: ‎04-26-2017

Re: Inserting a new object creates a binary geometry but no native geometry

The problem with this solution is, that the transformation with GM to UTM-32 ist not exact enough. If you layer the transformed objects with some rasterdata you can see that it is not on the exact position as it should be. Exporting the original GK4 coordinates to SQL-Server and then tranforming them with our tool, the objects are at the exact position as they should be. For several Access-projects this works fine, but only this one project just won't work (with GM version 15) and i can't find a way (maybe a wrong setting...) to make it work.

Highlighted
Super Contributor
Posts: 335
Registered: ‎03-10-2016

Re: Inserting a new object creates a binary geometry but no native geometry

Hi Jens,

 

may I ask where you work, respectively which federal state / Bundesland?

Maybe I have an idea how to solve your precision problem, as we in Baden-Wuerttemberg had the same issue and could solve with an exacter transformation grid for GK3 in our state.

 

Regards

Stefan

GAUSS Ingenieurtechnik GmbH | Germany
Highlighted
Occasional Contributor
Posts: 6
Registered: ‎04-26-2017

Re: Inserting a new object creates a binary geometry but no native geometry

Hi Stefan,

 

the data is from Thueringen.

 

Regards,

Jens Keller