Use query as source for Torque

203 views
Skip to first unread message

Jesús Del Bosque

unread,
Jul 2, 2013, 1:27:50 PM7/2/13
to car...@googlegroups.com
Hi all,

Been using torque for a while now and I was wondering if it is possible to use a query defining a sub-section of my table as the source material for the torque implementation. Instead of using table name and date column lets say I want to filter my table and use the result of that filtering as the source material for torque. Is this possible without creating a new table every time?

Thanks..

Andrew Hill

unread,
Jul 2, 2013, 1:30:15 PM7/2/13
to cartodb
Uhm. I've never tried it, but you can try one thing really quick. 

Say you have a table,

my_temporal_data

And instead of giving hte table name, you want to just select the first 100 for torque. Without getting into the code, you might be able to give this as a table name

"(SELECT * FROM my_temporal_data LIMIT 100) a " 

I'm not totally sure that will work out of the box, but it is worth a shot :)


--
 
---
You received this message because you are subscribed to the Google Groups "cartodb" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cartodb+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 



--

Andrew W. Hill, PhD, Senior Scientist 
www.vizzuality.com | @andrewxhill

Map, analyze and build applications with your data

Jesús Del Bosque

unread,
Jul 2, 2013, 2:02:21 PM7/2/13
to car...@googlegroups.com, and...@vizzuality.com
Thanks Andrew...
Unfortunately I had no luck with your suggestion, I keep recieving errors from the sql api regarding aliases and other interpretations, maybe I can work my way around through this errors.

As of right now Im going through the code to see if I can figure out a way to do this.

Thanks again....

Jesús Del Bosque

unread,
Jul 4, 2013, 11:13:13 AM7/4/13
to car...@googlegroups.com, and...@vizzuality.com
Just to let you know (and if anybody else comes around with the same question) I managed to use a query instead of a table by changing some things.
First, as you suggested, instead of a table name you can use a filtered query with an alias, the alias I used was the letter "i".

"(SELECT * FROM my_temporal_data LIMIT 100) i " 

Then the function "TimePlayer.prototype.get_time_data" on "grid_layer.js" had to be altered by deleting the appearance of "i" on the sql statement, on both cases where the table name is used, resulting on something like this.

 "hgrid, {0} ".format(this.table) +"

Thats it...

On Tuesday, July 2, 2013 12:30:15 PM UTC-5, Andrew Hill wrote:

James David Smith

unread,
Aug 14, 2013, 10:08:12 AM8/14/13
to car...@googlegroups.com, and...@vizzuality.com
Hi Jesus,

I wonder if you would mind posting some more details about your edits to the grid_layer.js file to allow select statement to be used with torque? Or were the edits to the .js file specific to your example?

I would like to do something like this ideally.

(SELECT * FROM hundred_houses_journeys_v2 WHERE point_time < "2012-01-01 02:00:00") i

But just doing this edit in the HTML page doesn't work.

Thanks
James

Andrew Thompson

unread,
Aug 14, 2013, 11:57:43 AM8/14/13
to car...@googlegroups.com, and...@vizzuality.com
Hi James,

As luck would have it I was trying to do the same thing today with a date-limiting query and came across Jesus's post. Also by coincidence I am using a 9 month old version of grid_layer.js, and I see the current one in the Torque repo is heavily modified from what I have and what Jesus posted.

And the actual relevant commit with both the HTML TorqueOptions change and the other one: https://github.com/andrewbt/philly-trees/commit/e3f4f4bce53ba4cc10e3ed75379016bc1373f963

Perhaps you want to revert to an older version of grid_layer.js? It works fine and I'm successfully "Torquing" a filtered set of dates from my table. If I have time I'll go experiment with adding this change to the newer version of the file.

Good luck to you - I just had it!

Jesús Del Bosque

unread,
Aug 14, 2013, 12:10:21 PM8/14/13
to car...@googlegroups.com, and...@vizzuality.com
Hi James,

From what I see, A.H. and the guys have been updating the torque library, so as of today the grid_layer.js file you see on github is different from the one I use. I think you can accomplish the same functionality by looking at this line. This part of the code is the one building the sql statement, and I think youll be able to use your query by removing "i\n" (the {0} variable is replaced by using .format(this.options.table) where this.options.table should be your query, thus using your select statement as a subquery source for the torque library). I havent tried this so Im just guessing. Try console logging the sql variable and see what you get.

If you want to see my working example, take a look at http://cic.mx/tehuan-beta/ (upper part, select "Mapa Animado" and use the lower menu to filter your view).

If you need any more help feel free to ask. If you want I can send you the files Im currently using.

Jesus...

James David Smith

unread,
Aug 14, 2013, 12:14:56 PM8/14/13
to car...@googlegroups.com
Thanks Jesus and Andrew. I'm going to have another crack at this tomorrow.
> You received this message because you are subscribed to a topic in the
> Google Groups "cartodb" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/cartodb/Dya3Hg2ZJj0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

Andrew Thompson

unread,
Aug 15, 2013, 10:53:59 AM8/15/13
to car...@googlegroups.com
Jesus,

I saw that it seems you've been able to change the CartoCSS of the points on your "Mapa Animado". I like the heatmap effect it gives. Any pointers you can give on where and how to do that?

Gracias y buena suerte,

Andrew


--
 
---
You received this message because you are subscribed to a topic in the Google Groups "cartodb" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cartodb/Dya3Hg2ZJj0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cartodb+u...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 



--
Andrew Thompson, Community Evangelist

Azavea |  340 N 12th St, Ste 402, Philadelphia, PA
atho...@azavea.com  | T 215.701.7689  | F 215.925.2663
Web azavea.com  |  Blog azavea.com/blogs  | Twitter @azavea

Join our community! Google Groups:

J BG

unread,
Aug 22, 2013, 6:37:19 PM8/22/13
to car...@googlegroups.com
Hi Andrew,

Sorry for the belated response, havent checked the forum in a while.
You can change the css style by changing the values for the variable named colors (inside TimePlayer.prototype.render_time) on the grid.js file. As an example, this is what im using.

var colors = [ "#4CAA32",
    "#FFBF00",
    "#DF0101",
    "#DF0101"  ];

Hope this helps you out.
Reply all
Reply to author
Forward
0 new messages