Can STWithin or STContains be used to update colum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can STWithin or STContains be used to update colum

Author

Topic

tpcolson
Starting Member

5 Posts

Posted - 2012-02-11 : 13:30:40

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)

and

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-12 : 13:18:38

sorry didnt understand how the two tables are related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources