Repetitive Attribute Values Automated with SQL

1012
0
12-31-2014 07:29 AM
ThomasColson
MVP Frequent Contributor
2 0 1,012

Most (all actually) of my GIS data has what I call the "Mickey Mouse" attribute value requirement: Each feature class has a couple of attribute fields that contain boring information such as administrative boundaries (what state, county this point occurs in). In the past I've been very proud of the elaborate attribute domains I've created so data editors can choose what (County, State, USGS Topo Quad) their data point occurs in.

No one uses them.

Data entry has a time and a labor cost, and like most of us, we need to focus on what's important (what happened at this point) and don't have a lot of time for administrative data entry.

Until a report is due that requires the partitioning of data values by (state, county, or watershed).

In Using SQL to Auto-Populate XY Values I described using a Trigger to automate the population of GPS coordinates using the well-known text pulled from the geography column. There's no reason why we can't extend that to handling a few more data maintenance tasks.

In this example, I have the following administrative boundary polygon feature classes all configure with the "Geography" storage type and SRID 4629 in the same SDE database.

  1. GRSM_COUNTIES: County name is populated in the County field;
  2. GRSM_States: State name is populated in the State field;
  3. GRSM_Sub_Ranger_Districts: Ranger District name is populated in the PARKDISTRICT field;
  4. USGS_24K_TOPOMAP_BOUNDARIES: USGS Quad Name is populated in the QuadName field;
  5. WATERSHEDS: HUC 12 Watershed Name is populated in the Watershed field.

Our data policy is that each new data point will be attributed with the "Name" value of each of the 5 polygons it occurs in. Imagine the enthusiasm of data editors when told they have to choose one of many values from a pick list, 5 times, for each new point they add or edit!

Extending Using SQL to Auto-Populate XY Values we can "join" the new or updated point spatially with each of the 5 polygons it occurs in and grab the required value from the polygon and write it to the correct attribute field.

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)
  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

There is no limit to the number of joins you can use to automate spatial queries that update attribute values. Just remember that both feature classes participating in the join need to use the same storage type.

But what if you want to apply this to existing data? After creating the trigger you could apply a one-time query to update existing values. This example is using the Geometry storage type:

use GRSM
update GRSM_TRAILS
set CONG_DIST = CONGRESSIONALDISTRICTS.NAMELSAD
from GRSM_TRAILS
inner join CONGRESSIONALDISTRICTS
on grsm_trails.location_id = grsm_trails.location_id
where (CONGRESSIONALDISTRICTS.Shape.STIntersection(grsm_trails.Shape).STDimension() = 1);

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.

About the Author
This is a personal account and does not reflect the view or policies of my org.