Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
How to use hint in the update statement
11 views
Skip to first unread message
migurus
Apr 9, 2013, 2:27:34 PM4/9/13
to
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.
--
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.
Erland Sommarskog
Apr 9, 2013, 4:38:53 PM4/9/13
to
migurus (mig...@yahoo.com) writes:
> 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.
These speci^H^H^Hatial data types go well over my head. I would suggest
> 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.
that you try the MSDN forum for Spatial:
http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/threads
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
0 new messages