dave harris 45446 (1/9/2013)

---

Hi all
Can anyone advise please, I have a TSQL 2008r2 headache...
I'm calculating the geo distance between a previous geo point and current geo point.
The code below, as a SELECT statement, returns 1.1 M rows and seems to be good.
When run as an UPDATE it sets all 1.8 M rows and with a zero value

WITH tblCTE AS -- create common table expression
(SELECT 
   Row_Number() OVER (ORDER BY varVehicleReg, dteEvent) AS RowNumber
  ,dteEvent, varVehicleReg, geoPoint, fltDistanceMoved
  FROM stageCoAvlsLog WHERE intFix > 0 -- all geoPoint are populated.
 )
 SELECT Cur.dteEvent, Cur.varVehicleReg, Cur.fltDistanceMoved,
    -- or ...
 --UPDATE stageCoAvlsLog SET fltDistanceMoved = 
 Cur.geoPoint.STDistance(Prv.geoPoint)-- AS fltMetres
FROM tblCTE AS Cur
LEFT OUTER JOIN tblCTE AS Prv
 ON Cur.RowNumber = Prv.RowNumber + 1 -- join on current and previous row
 AND Cur.varVehicleReg=Prv.varVehicleReg

-- the table defs are 
 [dteEvent] [datetime] NOT NULL,
 [varVehicleReg] [varchar](32) NOT NULL,
 [geoPoint] [geography] NULL,
 [fltDistanceMoved] [real] NULL,

The http://msdn.microsoft.com/en-us/library/ms177523(v=sql.105).aspx says "Only the rows returned by the common table expression are modified." Hmm... 1 issue or 2?


Dave

dave harris 45446 (1/9/2013)

---

Hi all
Can anyone advise please, I have a TSQL 2008r2 headache...
I'm calculating the geo distance between a previous geo point and current geo point.
The code below, as a SELECT statement, returns 1.1 M rows and seems to be good.
When run as an UPDATE it sets all 1.8 M rows and with a zero value

WITH tblCTE AS -- create common table expression
(SELECT 
   Row_Number() OVER (ORDER BY varVehicleReg, dteEvent) AS RowNumber
  ,dteEvent, varVehicleReg, geoPoint, fltDistanceMoved
  FROM stageCoAvlsLog WHERE intFix > 0 -- all geoPoint are populated.
 )
 SELECT Cur.dteEvent, Cur.varVehicleReg, Cur.fltDistanceMoved,
    -- or ...
 --UPDATE stageCoAvlsLog SET fltDistanceMoved = 
 Cur.geoPoint.STDistance(Prv.geoPoint)-- AS fltMetres
FROM tblCTE AS Cur
LEFT OUTER JOIN tblCTE AS Prv
 ON Cur.RowNumber = Prv.RowNumber + 1 -- join on current and previous row
 AND Cur.varVehicleReg=Prv.varVehicleReg

-- the table defs are 
 [dteEvent] [datetime] NOT NULL,
 [varVehicleReg] [varchar](32) NOT NULL,
 [geoPoint] [geography] NULL,
 [fltDistanceMoved] [real] NULL,

The http://msdn.microsoft.com/en-us/library/ms177523(v=sql.105).aspx says "Only the rows returned by the common table expression are modified." Hmm... 1 issue or 2?


Dave