topic Re: SQL Trigger to Auto-Calculate Coordinate Values in Data Management Questions

SQL Trigger to Auto-Calculate Coordinate Values

mpboyle — Mon, 05 May 2014 14:44:28 GMT

Does anyone know if it's possible to create a SQL trigger which would auto-calculate coordinate values for a feature class?

Example: For a point feature class, when a point is added or updated, the fields 'XCoordinate' and 'YCoordinate' would be auto-updated.

If this is possible, would it also be possible to calculate the coordinate values based on another coordinate system from what is used for the feature class, like WGS84 for longitude and latitude?

Example Environment:
DBMS: SQL Server 2008 R2
Geometry: SQL Geometry

Thanks in advance!

Re: SQL Trigger to Auto-Calculate Coordinate Values

VinceAngelo — Mon, 05 May 2014 16:14:59 GMT

Sure, both are possible. Microsoft might even have code posted in one of their forums.
Since this is SQL-Server specific, and doesn't involve Esri software (in implementation,
at least), there might not be much to find in these forums.

- V

Re: SQL Trigger to Auto-Calculate Coordinate Values

mpboyle — Mon, 05 May 2014 17:08:58 GMT

I guess I was curious if anyone had implemented such a routine into their workflow...and if so...if they'd be willing to share a sample of their trigger.

Re: SQL Trigger to Auto-Calculate Coordinate Values

ThomasColson — Sat, 11 Dec 2021 20:01:55 GMT

This works when using SQL Geometry as the storage type, I'm not sure you can do what you're asking when using SDE binary, unless you cook up a really complicated stored procedure. As always, ESRI will disown you if you call tech support with any SDE issue and they discover you're using triggers, SP's, and such "under the hood".

This allows to either populate UTM XY on insert from a GIS client, or create the geometry object on insert from a non-gis client (using a SP that also honors the Object ID creation!). In addition, it is also performing a number of intersect operations to populate some attributes that we adminstratively in all of our data per local protocol.

Finally, it's allowing us to set the GlobalID (LocationID Field) as a sequential GUID, which has done wonders to improve performance over the non-sequential default ESRI GUID.

Use at your own risk!


 
CREATE TRIGGER [dbo].[INSERT_VM_PLOTS_Location_ID]
ON [dbo].[VM_PLOTS]
INSTEAD OF INSERT NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON; 
INSERT dbo.VM_PLOTS(
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,X_Coord, Y_Coord, Shape
)
SELECT 
a.OBJECTID, a.VError, a.MapSource, a.SourceDate, a.MapMethod, b.NAME, a.UnitCode, a.EditDate, a.UnitName, a.IsExtant, a.HError, c.NAME, d.NAME, a.LAT, a.LON, e.HU_12_Name, a.PlaceName, a.TrailName, a.StreamName, a.Landform, a.Meta_MID, a.Road, a.Elevation, a.Year, a.Notes, a.Coord_Units, a.Coord_System, a.Datum, a.UTM_Zone, g.FCSubtype, f.District, a.EditBy, a.CreateBy, a.CreateDate, a.RegionCode, a.Restriction, j.MUNAME, k.GLG_SYM, l.CLASSES, h.VitalName, p.EPA_REGION, q.RANGE, a.MeanDailyTemp, a.Loc_Name, a.Directions, a.Description, a.Loc_Notes,a.X_Coord, a.Y_Coord, a.Shape
From
(SELECT 
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,
 SHAPE = CASE WHEN SHAPE IS NOT NULL   
        THEN SHAPE ELSE Geometry::STPointFromText('POINT('  
          + CAST(X_Coord AS VARCHAR(20)) + ' '   
          + CAST(Y_Coord AS VARCHAR(20)) + ')', 26917) END,  
      X_Coord = CASE WHEN SHAPE IS NULL THEN X_Coord ELSE SHAPE.STX END,  
      Y_Coord = CASE WHEN SHAPE IS NULL THEN Y_Coord ELSE SHAPE.STY END
FROM inserted) 
AS a 
LEFT OUTER JOIN grsm.dbo.USGS_24K_TOPOMAP_BOUNDARIES AS b 
ON b.Shape.STContains(a.Shape) = 1 
LEFT OUTER JOIN grsm.dbo.GRSM_COUNTIES AS c 
ON c.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_STATES AS d 
ON d.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.WBD_12 AS e 
ON e.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.RANGER_DISTRICTS AS f 
ON f.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_VEG AS h 
ON h.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_GMP_ZONE AS g 
ON g.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN iandm.DBO.GRSM_Soil_Taxonomy AS j
ON j.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.grsmglg AS k
ON k.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_DISTURBANCE_HISTORY AS l
ON l.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_LEVEL_IV_ECOREGIONS AS p
ON p.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.AVERAGERAINFALL as q
ON q.Shape.STContains(a.Shape) = 1;
end

Re: SQL Trigger to Auto-Calculate Coordinate Values

ThomasColson — Sat, 11 Dec 2021 20:01:58 GMT

....and here's a another one that handles updates to the XY or the geometry....

CREATE TRIGGER    [dbo].[GRSM_POINTS_OF_INTEREST_Update_Geo]
ON    [dbo].[GRSM_POINTS_OF_INTEREST]
after  UPDATE NOT FOR REPLICATION
AS   
BEGIN    
  SET NOCOUNT ON;  
 
  UPDATE p SET  
    X_Coord = CASE WHEN i.shape.STDimension() = 2  
      THEN i.shape.STCentroid().STX  
      ELSE i.shape.STEnvelope().STCentroid().STX  
    END, 
    Y_Coord = CASE WHEN i.shape.STDimension() = 2  
      THEN i.shape.STCentroid().STY  
      ELSE i.shape.STEnvelope().STCentroid().STY  
    END,  
    QuadName = COALESCE(b.name, p.QuadName),
    Watershed = COALESCE(c.HU_12_Name, p.Watershed),
    County = COALESCE(d.Name, p.County), 
    State= COALESCE(e.Name, p.State),
    SubRangerDistrict = COALESCE(f.District, p.SubrangerDistrict)
  FROM  
    dbo.GRSM_POINTS_OF_INTEREST AS p 
  INNER JOIN  
    inserted AS i 
    ON i.Location_ID = p.Location_ID 
  LEFT OUTER JOIN grsm.dbo.USGS_24K_TOPOMAP_BOUNDARIES AS b 
    ON b.Shape.STContains(i.Shape) = 1 
      LEFT OUTER JOIN grsm.dbo.WBD_12 AS c
    ON c.Shape.STContains(i.Shape) = 1 
          LEFT OUTER JOIN grsm.dbo.GRSM_COUNTIES AS d
    ON d.Shape.STContains(i.Shape) = 1 
              LEFT OUTER JOIN grsm.dbo.GRSM_States AS e
    ON e.Shape.STContains(i.Shape) = 1
    LEFT OUTER JOIN dbo.GRSM_SUB_RANGER_DISTRICTS AS f 
ON f.Shape.STContains(i.Shape) = 1 
  WHERE EXISTS  
  ( 
    SELECT 1 FROM inserted AS i2 
      INNER JOIN deleted AS d 
      ON i2.Location_ID = d.Location_ID 
      WHERE i2.Location_ID = i.Location_ID 
      AND i2.Shape.STEquals(d.Shape) = 0 
      -- ...and NULL handling if necessary 
  ); 
END
GO

Re: SQL Trigger to Auto-Calculate Coordinate Values

mpboyle — Thu, 08 May 2014 18:19:24 GMT

Tom,

Thanks for the replies! Most of that code is over my head as I'm not a heavy SQL user, just enough to be dangerous, but by no means an expert 🙂

Is there a 'template' sql trigger that could be written up for calculating x,y?

Thanks again for the knowledge!

Re: SQL Trigger to Auto-Calculate Coordinate Values

ChrisMathers1 — Tue, 06 Sep 2016 19:29:19 GMT

Reviving an old thread but I know but I was just looking into this same thing and there are templates on how to set up a trigger if you're using MSSQL as your DBMS on MSDN: CREATE TRIGGER (Transact-SQL). It looks like the syntax is very similar across major DBs: Database trigger - Wikipedia, the free encyclopedia.

Re: SQL Trigger to Auto-Calculate Coordinate Values

RichardFairhurst — Tue, 06 Sep 2016 19:58:31 GMT

If this behavior is always triggered in an ArcMap Desktop edit session whenever you create a feature or modify its geometry, you can use Attribute Assistant to do this using the X_COORDINATE and Y_COORDINATE methods. You need to create an entry for each method and each target feature class in the dynamicValues table and add that table to your map, then make sure the Attribute Assistant add-in is turned on. You can get Attribute Assistant here by pressing the Download button.