Using SQL to Auto-Populate XY Values

[ThomasColson]

I have an incredible amount of point data in hundreds of feature classes for which one user demand is constant: they want to "see" the "GPS Coordinates" of the point for a variety of reasons, including proprietary (non-GIS) applications which report the GIS coordinate.

I could perhaps build into a data editing workflow which allows data editors to perform an XY calculation task, or even trick out some custom Pynthon or an ArcObjects toolbar, but those solutions require custom code which, frankly, I dropped out of computer programming (C++) after the first class.

SQL Server is ideally suited to handle this type of repetitive data maintenance task when you're using the Microsoft Spatial Storage types.

The following assumes you're working with a Point Feature Class using the "Geography" storage type using GCS_North_American_1983 as the coordinate system (SRID 4269) and there is a GLOBALID unique identifier.

One way to accomplish this is with a Trigger, in this case, a trigger that fires when the edit or update hits the base table. Lets write a trigger:

CREATE TRIGGER [dbo].[TEST_GEOGRAPHY]
ON [dbo].[TEST]
/****** fire on inserts and updates  ******/
/****** disable trigger when SQL replication or mirroring is enabled  ******/
after INSERT,UPDATE NOT FOR REPLICATION
AS   
BEGIN    
  SET NOCOUNT ON;  
   UPDATE p SET  
/****** hypothetically we could enter the lat/lon as text and create a geography object  ******/
         SHAPE = CASE WHEN i.SHAPE IS NOT NULL   
        THEN p.SHAPE ELSE Geography::STPointFromText('POINT('  
          + CAST(p.LON AS VARCHAR(20)) + ' '   
          + CAST(p.LAT AS VARCHAR(20)) + ')', 4269) END,  
/****** usual case point is created with ARC and casts the LAT/LON as text  ******/
/****** from the geography object  ******/
      LON = CASE WHEN p.SHAPE IS NULL THEN p.LON ELSE p.SHAPE.Long END,  
      LAT = CASE WHEN p.SHAPE IS NULL THEN p.LAT ELSE p.SHAPE.Lat END
  FROM  TEST
     AS p 
/****** allow upate of lat/lon on update ******/
  INNER JOIN  
    inserted AS i 
    ON i.globalid = p.globalid 
  ; 
END
GO

How this works when:

1. Versioned without moving edits to base checked: The LAT and LON column will be populated with the WKT (Well-known Text) value of the geographic coordinates upon data entry and update AFTER your edits have been compressed to the default version. Personally I'm not a big fan of this versioning scenario;
2. Versioned with moving edits to base checked: The LAT and LON column will be populated with the WKT (Well-known Text) value of the geographic coordinates upon data entry and update as soon as the edit is saved;
3. Not versioned: The LAT and LON column will be populated with the WKT (Well-known Text) value of the geographic coordinates upon data entry and update as soon as the edit is made.

If you are using the Geometry Storage Type:

  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,  

Or a polygon:

   UPDATE p SET
   SHAPE = CASE WHEN p.SHAPE IS NOT NULL   
        THEN p.SHAPE ELSE Geography::STPointFromText('POINT('
          + CAST(p.LON AS VARCHAR(20)) + ' '   
          + CAST(p.LAT AS VARCHAR(20)) + ')', 4269) END,  
      LON = CASE WHEN p.SHAPE IS NULL THEN p.LON ELSE p.SHAPE.EnvelopeCenter().Long  END,  
      LAT = CASE WHEN p.SHAPE IS NULL THEN p.LAT ELSE p.SHAPE.EnvelopeCenter().Lat  END,

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.