SQL Trigger to Auto-Calculate Coordinate Values

1889
7
05-05-2014 07:44 AM
Highlighted
Regular Contributor
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!
0 Kudos
7 Replies
Highlighted
Esri Esteemed Contributor
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
0 Kudos
Highlighted
Regular Contributor
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.
0 Kudos
Highlighted
MVP Frequent Contributor
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



0 Kudos
Highlighted
MVP Frequent Contributor
....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
0 Kudos
Highlighted
Regular Contributor
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!
0 Kudos
Highlighted
Occasional Contributor II

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.

0 Kudos
Highlighted
MVP Honored Contributor

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.

0 Kudos