07-13-2018 03:37 AM
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,
07-13-2018 02:48 PM
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.
07-15-2018 06:24 PM
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
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
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.
07-30-2018 06:13 AM
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:
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,
07-30-2018 03:12 PM
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.
08-02-2018 01:38 AM
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.
08-02-2018 09:06 AM
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.