Can STWithin or STContains be used to update columns based on point within polygon relationship?

Question

Given the following tables:CREATE TABLE #USGS_24K_TOPOMAP_BOUNDARIES(
[OBJECTID] [int] NOT NULL,     
[AREA] [numeric](38, 8) NULL,     
[PERIMETER] [numeric](38, 8) NULL,     
[QD24K_GRSM] [numeric](38, 8) NULL,     
[QD24K_GR_1] [numeric](38, 8) NULL,     
[QUADID] [numeric](38, 8) NULL,     
[CENTLAT] [numeric](38, 8) NULL,     
[CENTLONG] [numeric](38, 8) NULL,     
[NAME] [nvarchar](35) NULL,     
[STATE] [nvarchar](2) NULL,    
 [LATLONG] [nvarchar](9) NULL,     
[OHIO_INDEX] [nvarchar](8) NULL,     
[GRID60] [nvarchar](5) NULL,     
[Reviewed] [int] NULL,     
[Corrected] [int] NULL,     
[Verified] [int] NULL,     
[GlobalID] [uniqueidentifier] NOT NULL,     
[SHAPE] [geometry] NULL)

CREATE TABLE #tbl_locations(  
[OBJECTID] [int] NOT NULL, 
[FCategory] [varchar](16) NULL, 
[MapMethod] [varchar](4) NULL, 
[HError] [varchar](50) NULL, 
[MapSource] [varchar](255) NULL, 
[SourceDate] [datetime2](7) NULL, 
[EditDate] [datetime2](7) NULL, 
[Notes] [varchar](255) NULL, 
[Species_Community] [varchar](50) NULL, 
[Location_ID] [uniqueidentifier] NOT NULL, 
[Site_ID] [uniqueidentifier] NULL, 
[GIS_Location_ID] [varchar](50) NULL, 
[Meta_MID] [varchar](50) NULL, 
[X_Coord] [numeric](38, 8) NULL, 
[Y_Coord] [numeric](38, 8) NULL, 
[Coord_Units] [varchar](50) NULL, 
[Coord_System] [varchar](50) NULL, 
[UTM_Zone] [varchar](50) NULL, 
[Accuracy_Notes] [varchar](255) NULL, 
[Unit_Code] [varchar](12) NULL, 
[Loc_Name] [varchar](100) NULL,
 [Loc_Type] [varchar](25) NULL, 
[Updated_Date] [varchar](50) NULL, 
[Loc_Notes] [varchar](255) NULL, 
[Datum] [varchar](5) NULL, 
[Watershed] [varchar](50) NULL, 
[StreamName] [varchar](50) NULL, 
[NHDReachCode] [varchar](14) NULL, 
[TOPO_NAME] [varchar](50) NULL, 
[Trail] [varchar](100) NULL, [Road] 
[varchar](50) NULL, [Elevation] [numeric](38, 8) NULL, 
[LAT] [numeric](38, 8) NULL, 
[LON] [numeric](38, 8) NULL, 
[Population_ID] [uniqueidentifier] NULL, 
[Year_] [varchar](4) NULL, 
[WGS_DAT] [varchar](5) NULL, 
[WGS_CS] [varchar](5) NULL, 
[County] [varchar](20) NULL, 
[State] [varchar](15) NULL, 
[IsExtant] [varchar](3) NULL, 
[IsSenstive] [varchar](3) NULL, 
[SpeciesName] [varchar](125) NULL, 
[SpeciesID] [varchar](50) NULL, 
[Species_ID] [int] NULL, 
[SHAPE] [geometry] NULL) 

I'd like to populate #tbl_locations.Topo_Name with #USGS_24K_TOPOMAP_BOUNDARIES.Name. In other words, I trying to determine the name of the topo map that a point falls within, and programatically write that to the points table. Seems simple in theory, but tbl_locations contains thousands of points which could occur in one of 36 topo map polygon boundaries

I've gotten this far:

Select NAME, Loc_Name, Location_ID From #USGS_24K_TOPOMAP_BOUNDARIES a, 
#TBL_LOCATIONS b where a.Shape.STContains(b.Shape)=1

which returns a neat table that I can cross-walk back to tbl_locations through a join, but I'm stuck on getting this accomplished through a single query-update statement, and I have many similar point-polygon relationships that I'd like to automate this way (e.g what watershed, county, state, etc... the point occurs in). THanks!

Answer 1

Hi there. I think you're looking for this:

UPDATE #tbl_locations
SET Topo_Name = (
  SELECT Name
  FROM #USGS_24K_TOPOMAP_BOUNDARIES t
  WHERE t.Shape.STContains(#tbl_locations) = 1
);

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

that worked perfectly!