3

I have two layers in CartoDB, one with spatial data, school_count, and one without, heisman_winners. I want to visualize my spatial layer but when a user clicks on a feature I want it to display data from my non-spatial table. I have a common column between the two tables: 'school'. On my non-spatial tables I have multiple records that correspond to each 'school' (i.e. 'class', 'name', 'position', 'percentage', 'points', 'year'). Can I set a SQL query that runs when a user clicks a feature that will return each corresponding record from another table? So when they click it will look like this:

'name' , 'position', 'points', 'percentage', 'year', 'class'
'name' , 'position', 'points', 'percentage', 'year', 'class'
'name' , 'position', 'points', 'percentage', 'year', 'class'
etc...

I fond this block but I am not sure exactly how to tweak it correctly.

Improve this question

2 Answers 2

Reset to default
1

You can use a SQL join in your visualization, like:

select a.the_geom_webmercator, a.column_1, b.column_2, b.column_3 from school_count a, heisman_winners b where a.school = b.school;

(in this case you need to do the appropriate join)

You can set the query in the visulalization using CartoDB UI or using setSQL in the sublayer (using javascript)

Improve this answer
3
  • DO I need to preform the join prior to inserting this SQL statement into my javascript?
    – cbunn
    Commented Jan 12, 2015 at 18:21
  • just change the SQL you apply to the sublayer If you give me more context on how you are visualizing your map I could guide you with more details
    – javisantana
    Commented Jan 13, 2015 at 8:21
  • I am displaying a points layer "school_count" that shares a field 'school' with my other non-spatial table "heisman_winners". In the info window I want to display fields (name, position, points, percentage, class) from "heisman_winners". Also in some cases there are multiple records per 'school'.
    – cbunn
    Commented Jan 13, 2015 at 20:39
1

I think you have a pretty similar example to what you want to build here: http://bl.ocks.org/xavijam/378c6dc0d43654280757

As @javisantana says, it's applying a JOIN in the query in order to get the layer and information from both tables, which will be displayed in the infowindow.

If you share with us the code you have at the moment we could give you more specific details if the approach isn't working for you.

Copying the code here as a backup:

<html>
  <head>
<title>Custom infowindow example | CartoDB.js</title>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<link rel="shortcut icon" href="http://cartodb.com/assets/favicon.ico" /> 
<style>html, body, #map { height: 100%; padding: 0; margin: 0 }</style>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" />


<!-- include cartodb.js library -->
<script src="http://libs.cartocdn.com/cartodb.js/v3/cartodb.js"></script>

<script type="text/javascript">
  var layer;

  function main() {

    cartodb.createVis(map, 'http://documentation.cartodb.com/api/v2/viz/2b13c956-e7c1-11e2-806b-5404a6a683d5/viz.json')
      .on('done', function(vis, layers) {

        var lyr = layers[1].getSubLayer(0);

        lyr.setSQL("SELECT e.cartodb_id, e.area, w.subregion, w.un, e.the_geom, e.the_geom_webmercator FROM european_countries_e e LEFT JOIN world_borders w ON e.iso_2_code = w.iso2");

        lyr.infowindow.set(
          "fields",
          [
            { name: 'area', position: 0, title: true },
            { name: 'un', position: 0, title: true },
            { name: 'subregion', position: 0, title: true }
          ]);

      }).on('error', function() {
        console.log("some error occurred");
      });
  }

  window.onload = main;

</script>

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.