From: migurus@yahoo.com   
      
   I have a table that keeps spatial data of Los Angeles freeways, the data is   
   combined into one record and column type is geography. There is another table   
   that keeps some 8+ million of points. I need to calculate and set the distance   
   from each point to    
   the freeway and keep it in the points table. The distance attribute is   
   actually 1, 2, 3, 4, 5, -1. That mark a point within 1 mile, or 2 mile etc.   
   from the freeway. All points further than 5 miles are marked with -1.   
   --   
   So, I have following:   
   create table XPOINTS (    
     ID int not null primary key   
   , DIST int null   
   , GP geography null   
   )   
   create table HWY (   
     ID int not null primary key   
   , GEOG geography null   
   )   
   -- i can't show data for HWY as it is huge one record   
   update XPOINTS set DIST = 1    
   where ID IN (   
     select ID    
     from XPOINTS p   
     ,    HWY   
     where HWY.GEOG.STDistance( p.GP ) < 1609   
   )   
   go   
   update XPOINTS set DIST = 2   
   where ID IN (   
     select ID    
     from XPOINTS p   
     ,    HWY   
     where HWY.GEOG.STDistance( p.GP ) < 1609*2   
     and   p.DIST is not null   
   )   
   go   
   etc... for up to 5 miles, then I just update remaining nulls with -1.   
      
   I do have a spatial index on the HWY.GEOG and it is used when I just try to   
   run select of a point against GEOG.   
      
   The update statement plan does not show use of the index. How can I hint sql   
   to use it?   
      
   Also, any other ideas would be much appreciated, as I hate five updates run   
   sequentially.    
      
   Thanks in advance.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)