Skip to main content
  • EN

Hi all,

I want to figure out the best way of summarising data by date.

I have a workspace that creates routes for journeys using the ShortestPathFinder, and then explodes the routes into their constituent segments, with the attributes below plus Road Name, SegmentID:

What I need to do is sum the total number of vehicles per day on each segment.

I have a solution that works but is slow and I need to optimise it. Currently I merge a date list to each route, then explode the list so there is a sepreate feature per day per route - and then test to see if it is within the date range. Finally I sum the number of vehicles using an aggregator (most efficient?) per road segment, per day.

The problem is the journeys occur over a number of years, and over a large area so this results in many many features and is not quick.

I would be grateful for any suggestions!

Thanks,

Owen

Hi @owen, this workflow creates copies with the number of days between Start and Finish for each record, and adds Date attribute to every resulting record. Hopefully it might be efficient to aggregate the resulting records by the Date. Besides, it's better to remove unnecessary attributes before copying.

Number of Copies = (Interval between Start and Finish) + 1 Cdays]

0684Q00000ArLN9QAN.png

Hope this helps.

An idea could be to try the InlineQuerier Transformer. If you are not familiar with it - it let's you handle any data as SQL Databases. Then you could create a SQL statement by using group and count on the attributes. I think this could have some impact (but you never now before testing).

 

 

(see more here if you are not familiar with SQL: http://www.dofactory.com/sql/group-by)

 

 

 

Hi @owen, this workflow creates copies with the number of days between Start and Finish for each record, and adds Date attribute to every resulting record. Hopefully it might be efficient to aggregate the resulting records by the Date. Besides, it's better to remove unnecessary attributes before copying.

Number of Copies = (Interval between Start and Finish) + 1 Cdays]

0684Q00000ArLN9QAN.png

Hope this helps.

Thats great thanks Takashi I can see that that will certainly speed things up

 

 

An idea could be to try the InlineQuerier Transformer. If you are not familiar with it - it let's you handle any data as SQL Databases. Then you could create a SQL statement by using group and count on the attributes. I think this could have some impact (but you never now before testing).

 

 

(see more here if you are not familiar with SQL: http://www.dofactory.com/sql/group-by)

 

 

 

Thanks Ulf, I'm not too hot on SQL but will be worth working it out if I can speed things up

 

 

Reply


Community Stats

30,189
Posts
114,582
Replies
37,853
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.