04-18-2017 10:07 AM
Has any one generated a trigger in SQL Server Spatial to fill the SRID in the geometry_SPA? We need to fill dat information, which is fixed, in all geometry when it is cretead or modified by Geomedia.
04-19-2017 01:57 PM
I don't have a trigger to offer you but be aware that if your edits are occuring via GeoMedia then the SRID assignments can come from the GeoMedia metadata.
When working with projected features, GeoMedia assumes a default generic SRID value of zero is being used. SRID value of zero just means "projected" (as opposed to Geographic). Working with the zero for projected should not cause any problem for MS SQL Server or GeoMedia but if you want to work with a specific SRID value, you can manually define GeoMedia’s Default SRID for a new database (after creating the metadata using database utilities, but before features are created) by updating the DefaultNativeGeometrySrid field in the GParameters table.
UPDATE GParameters SET GVALUE= <Your SRID VALUE> WHERE GPARAMETER='DefaultNativeGeometrySrid';
UPDATE GParameters SET GVALUE=26916 where GPARAMETER='DefaultNativeGeometrySrid';
The value from GParameters is then used as the default value for the GFieldMapping metadata NATIVE_SRID for new feature classes created by GeoMedia.
If you have existing features, then you can use GeoMedia's Database Utilities to "assign" a specific SRID value using the Edit Coordinate System option. This will set the NATIVE_SRID in GFieldMapping for the feature class. So as you insert new features, the Spatial GeometrySRID will be as desired.
You must be careful however as you should only have 1 SRID assigned for the geometry! For example the following query could be used to return the SRID and count of records (N) for table. The results should only be 1 SRID value.
Select Geometry_SPA.STSrid, count(*) as N
Group by Geometry_SPA.STSrid;
If you get multiple SRID's then you may see errors such as "Some spatial objects were not displayed since their SRID is different than the SRID of the first object in the record set.
You can run an UPDATE query to "assign" a SRID value on geometry for all rows.
NOTE: This workflow simply assigns SRID values. It does NOT transform from one SRID to another. The numeric SRID value used for the update must match one of the values in the system table sys.spatial_reference_systems. It's also assumed you have done the steps previously mentioned for setting up the GeoMedia metadata to the same SRID value.
UPDATE <table name> SET <Geometry_name>.STSrid=<SRID_VALUE>;
For my database, this would look like the following:
UPDATE roads SET Geometry_SPA.STSrid=26916;
If you have no constraints on the table, all of the rows should now be set with the desired SRID.