Avoid Cursor in SQL Server

Question

I'm trying to get a dataset put together to feed a GIS application and looking for a SQL Guru to help me avoid using a cursor and temp tables since we have about 30 million records to process (I'm using SQL Server 2012). The main points of the dataset consist of a VehicleID, PositionTime, Latitude, Longitude, and Speed. We need to have the records ordered by VehicleID and PositionTime in order to map the Lat/Long points and track movement. If the Speed is greater than or equal to 0.2, then it's an acceptable record. However, if the speed is less than 0.2, then it's considered as not moving and needs special processing. In the example below, for records one and two, I would need to average the lat, long, and speed, and also keep the maximum position time and put that into a single record. The third and fourth records are fine as is. Records five and six would also need to be combined into one record, so vehicle 1 would go from 6 records to 4 records. After the records are "normalized", we then need to get the date difference between records for each vehicle and position time. The same process would start over again for VehicleID 2 (we have about 7,000 vehicles).

VehicleID  PositionTime         Latitude    Longitude    Speed
1          11/20/2013 18:09:27  29.54608    -95.04444    0.1
1          11/20/2013 18:47:35  29.54608    -95.04444    0
1          11/20/2013 20:34:45  29.546105   -95.04442    5
1          11/20/2013 20:46:44  29.54607833 -95.04443167 3
1          11/20/2013 21:01:44  29.54606667 -95.04442833 0
1          11/20/2013 21:16:43  29.546095   -95.04443167 0.1
2          11/20/2013 21:31:44  29.54609    -95.04441    5
2          11/20/2013 21:46:44  29.54607667 -95.04443    0

It is not very clear what you are trying to do here. It seems a case expression should get you what you want but I can't decipher through all the unformatted text to figure out the business rules. It is also not really clear what column(s) you expect as output. — Sean Lange
– Sean Lange, Commented Feb 3, 2015 at 16:57

Hugh Jones · Accepted Answer · 2015-02-03 18:07:47Z

1

I think all you are trying to do is aggregate the slow/non-moving vehicles

Does this help?

Create or replace view vehicles_view as 
 select VehicleId, 
        Max(PositionTime) as PositionTime, 
        avg(Latitude) as Latitude, 
        Avg(Longitude) as Longitude, 
        avg(Speed) as speed
 from vehicles
 where Speed < 0.2
 group by vehicleId

union

 select VehicleId, 
  PositionTime , 
  Latitude, 
  Longitude, 
  Speed 
 from vehicles
 where Speed >= 0.2

answered Feb 3, 2015 at 18:07

Hugh Jones

2,7641 gold badge20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1408057 Over a year ago

Hugh, sorry it took me so long to get back to this, I'm still working on this project. I actually started the process in a similar manner as you have above, but I'm not getting the max/avg values on the whole data set for each vehicle. In my example above, I need to get the max/avg values for the first 2 rows since they are below 0.2 (but there could be any number of contiguous rows that are below 0.2), and I take rows 3 and 4 as is since they are >= 0.2. That pattern can happen multiple times for each vehicle; in my example, it starts again for rows 5 and 6 for vehicle 1.

Giorgi Nakeuri · Accepted Answer · 2015-02-04 09:50:27Z

Ok, here is some code:

DECLARE @t TABLE
    (
      VehicleID INT ,
      PositionTime DATETIME ,
      Latitude DECIMAL(20, 10) ,
      Longitude DECIMAL(20, 10) ,
      Speed DECIMAL(20, 10)
    )

INSERT  INTO @t
VALUES  ( 1, '11/20/2013 18:09:27', 29.54608, -95.04444, 0.1 ),
        ( 1, '11/20/2013 18:47:35', 29.54608, -95.04444, 0 ),
        ( 1, '11/20/2013 20:34:45', 29.546105, -95.04442, 5 ),
        ( 1, '11/20/2013 20:46:44', 29.54607833, -95.04443167, 3 ),
        ( 1, '11/20/2013 21:01:44', 29.54606667, -95.04442833, 0 ),
        ( 1, '11/20/2013 21:16:43', 29.546095, -95.04443167, 0.1 ),
        ( 2, '11/20/2013 21:31:44', 29.54609, -95.04441, 5 ),
        ( 2, '11/20/2013 21:46:44', 29.54607667, -95.04443, 0 );
WITH    cte1
          AS ( SELECT   VehicleID ,
                        PositionTime ,
                        Latitude ,
                        Longitude ,
                        CASE WHEN Speed <= 0.2 THEN 0
                             ELSE Speed
                        END AS Speed
               FROM     @t
             ),
        cte2
          AS ( SELECT   * ,
                        SUM(Speed) OVER ( PARTITION BY VehicleID ORDER BY PositionTime ) AS s
               FROM     cte1
             ),
        cte3
          AS ( SELECT   * ,
                        RANK() OVER ( PARTITION BY VehicleID ORDER BY Speed, s ) AS r
               FROM     cte2
             ),
        cte4
          AS ( SELECT   VehicleID ,
                        MAX(PositionTime) AS PositionTime ,
                        AVG(Latitude) AS Latitude ,
                        AVG(Longitude) AS Longitude ,
                        MAX(Speed) AS Speed
               FROM     cte3
               GROUP BY VehicleID ,
                        r
             )
    SELECT  * ,
            DATEDIFF(ss,
                     LAG(PositionTime) OVER ( PARTITION BY VehicleID ORDER BY PositionTime ),
                     PositionTime) AS DiffInSeconds
    FROM    cte4
    ORDER BY PositionTime

And output:

VehicleID   PositionTime            Latitude         Longitude      Speed           DiffInSeconds
1           2013-11-20 18:47:35.000 29.5460800000   -95.0444400000  0.0000000000    NULL
1           2013-11-20 20:34:45.000 29.5461050000   -95.0444200000  5.0000000000    6430
1           2013-11-20 20:46:44.000 29.5460783300   -95.0444316700  3.0000000000    719
1           2013-11-20 21:16:43.000 29.5460808350   -95.0444300000  0.0000000000    1799
2           2013-11-20 21:31:44.000 29.5460900000   -95.0444100000  5.0000000000    NULL
2           2013-11-20 21:46:44.000 29.5460766700   -95.0444300000  0.0000000000    900

Let's go through it. In cte1 I just cast all values of Speed <=0.2 to 0 In cte2 I summ Speed values, so I get same values where Speed = 0

Speed           s
0.0000000000    0.0000000000
0.0000000000    0.0000000000
5.0000000000    5.0000000000
3.0000000000    8.0000000000
0.0000000000    8.0000000000
0.0000000000    8.0000000000
5.0000000000    5.0000000000
0.0000000000    5.0000000000

Then in cte3 I am applying ranking function to combination of Speed and incremental sum s. So I get:

VehicleID   Speed           s               r
1           0.0000000000    0.0000000000    1
1           0.0000000000    0.0000000000    1
1           0.0000000000    8.0000000000    3
1           0.0000000000    8.0000000000    3
1           3.0000000000    8.0000000000    5
1           5.0000000000    5.0000000000    6
2           0.0000000000    5.0000000000    1
2           5.0000000000    5.0000000000    2

Notice that you have same r where Speed is 0, so you can apply grouping here by VehicleID and r. I do that in cte4 to get distinct rows and apply aggregation like Max on date and Avg on lat and long.

And finally there is select from cte4 that calculates difference between current row and the row before it using LAG function.

Collectives™ on Stack Overflow

Avoid Cursor in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related