Rasters, Pixels and Code Oh My!

[ThomasColson]

In Repetitive Attribute Values Automated with SQL  we added some automation to repetitive data entry using feature classes that are using the Microsoft Geography storage type.

But what about other repetitive data points? Back to our "Data Standard", much of the data we collect also needs to be attributed with discrete or continuous values obtained from a raster, such as elevation. Sure, I could bang out some Python or C# code to handle this, but I skipped that class...

This assumes that you have a raster Digital Elevation Model in SDE that has been FIRST configured using ST_RASTER (ArcGIS Help 10.1 ) then copied over into a ST_PIXEL table with SRID 4269.

In this example, I have a 10-m resolution DEM named DEM10MP. In my naming convention, the "P" indicates that the table is of ST_PIXEL type.

Extending the trigger presented in Repetitive Attribute Values Automated with SQL  I simply add a Get Value By Location query using the LAT/LON value from the inserted or updated Geography column. The entire trigger looks like this. Line 25/26 includes the elevation query.

ALTER 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,
       QuadName = COALESCE(b.name, p.QuadName),
    Watershed = COALESCE(c.HUC_12_Name, p.Watershed),
    County = COALESCE(d.Name, p.County), 
    State= COALESCE(e.Name, p.State),
    PARKDISTRICT = COALESCE(f.District, p.PARKDISTRICT),
  ELEVATION = (SELECT
pdata.getValueByLoc(1,p.SHAPE.Long,p.SHAPE.Lat)  FROM [dbo].[DEM10MP])


  FROM  TEST
     AS p 
/****** allow upate of lat/lon on update ******/
  INNER JOIN  
    inserted AS i 
    ON i.globalid = p.globalid 
   LEFT OUTER JOIN USGS_24K_TOPOMAP_BOUNDARIES AS b 
    ON b.Shape.STIntersects(i.Shape) = 1 
      LEFT OUTER JOIN WATERSHEDS AS c
    ON c.Shape.STIntersects(i.Shape) = 1 
          LEFT OUTER JOIN GRSM_COUNTIES AS d
    ON d.Shape.STIntersects(i.Shape) = 1 
              LEFT OUTER JOIN GRSM_States AS e
    ON e.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN GRSM_SUB_RANGER_DISTRICTS AS f 
ON f.Shape.STIntersects(i.Shape) = 1 
  ; 
END
GO

Now for every new point I add to this feature class, or edit, all but one field is automatically populated or updated.

Every time.

No user input.

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.