Geography, SQL Server and Big Updates!

Ran into a situation today where I had the following problem.

A database was given to me that had places (with Geography types in it) but I needed to be able to search based on a “Region”. A region, for this case, would be something like United States, Poland, Canada, etc. I also had a table of these Regions and a place holder in my “Places” table to assign a Place to a Region.

What did I do? I took advantage of these awesome types!

My SQL queries looked like this:

declare @area geography

-- ROUGH USA Border
select @area = geography::STGeomFromText('Polygon((-126.9075399967648 47.68027463274793,
-125.5933469520973 41.87061363994702,
 -124.5566677584721 38.34492930185334,
 -121.622900826232 34.85168794468432,
 -118.3275314955308 32.43508337044441,
 -115.5103796044553 32.19705726589449,
 -113.0654003277932 31.77687371296225,
 -110.9163553600406 31.16613810081651,
 -107.8958428990167 31.21725190814499,
 -105.3582829747764 30.3390488806813,
 -103.6229070416005 28.49450717252408,
 -101.6198812206448 29.23270393932756,
 -100.7263754686541 28.00898678954991,
 -99.49318812859124 26.32947301175079,
 -97.60389066822228 25.60910944803409,
 -96.63744452913633 25.93235911342899,
 -96.66023831081218 27.52354912193902,
 -95.02257544177812 28.59771331453454,
 -93.634596213173 29.23882980485253,
 -92.19747252871773 29.18153225747653,
 -90.40642605344159 28.7293375847932,
 -88.6334266135873 28.88384249687773,
 -87.73643471760506 29.47029719641609,
 -85.87623029108073 29.53341980232942,
 -84.08101685380154 29.52982504860095,
 -83.45417798638813 29.01842045470445,
 -82.38844010677963 24.24859712043354,
 -79.93327843767648 24.20260368451352,
 -79.46386162873068 25.97644839336022,
 -79.41762247493236 27.57497473469773,
 -79.91028751745607 29.10473903971489,
 -80.72068003661178 30.76965704143833,
 -79.97159623672641 32.13755430502794,
 -78.56208210583684 33.05511984133363,
 -77.67426061440897 33.75436948960927,
-75.22615439141757 34.85613954463283,
-74.25971143052236 36.63662366903175,
-73.53523756113569 38.47662633954932,
-72.62489706944318 39.64529717376119,
-69.30794873940781 41.15751725724563,
-68.44618965381588 43.16536523423157,
-67.08150916498779 44.0404291840986,
-66.1853049222763 45.88453459280221,
-66.37599482234019 47.69293757149508,
-70.97725783051568 48.40288239949412,
-73.7478063212333 46.77463218866193,
-76.50777828662675 44.75251441663305,
-78.25661258474381 44.01667413433834,
-80.38458637693319 43.06328850460273,
-81.86159747540829 42.11343964044789,
-81.5698248277223 43.05972264831926,
-81.68301357631532 44.43194956690464,
-82.47213088304741 45.78012745632746,
-83.55860543115054 47.03329808948071,
-87.06682093928856 47.65337611719893,
-89.34057911681968 47.9782542785637,
-91.04822120347217 48.64900196116928,
-94.56095082763974 49.72287463654621,
-96.54901877261908 49.20586850215418,
-126.4638084250994 49.34574515284749,
-126.9075399967648 47.68027463274793 ))', 4326)

UPDATE Place
SET RegionKey = 123 -- USA
WHERE RegionKey IS NULL
AND
@area.STIntersects(PlaceGeography) = 1

This does the following:

  1. Sets up a new variable called @area
  2. Defines that area as a polygon that roughly covers the good ol’ US of A
  3. Then updates my “Place” table with a region code that matches the RegionKey of the Region table with items that intersect (or actually, lie within) the area I defined

Pretty easy but believe it or not I had trouble finding an example like this any where on the interwebs so I thought I’d share.

Happy geographic coding!