Performing a Query Based on a the results of a previous query.

649
3
Jump to solution
04-01-2014 11:03 AM
PaulWilner_II
New Contributor
I have a point layer, with a related table. In this case I'm mapping schools in the point layer and each school has many programs which are recorded in the related table. I'd like to query the related table for specific programs, and return the school ID's that have these programs, then I'd like to use the school ID's returned from the query of the related table, to query the point layer and display a graphics layer based on that second query.

Originally I thought maybe a query related records would work, but since it doesn't allow for a traditional "where clause, and since I"m going from the "many" table back to the "one" table, I'm not sure how that would work. Again, I'm a novice at this.

Here are my conceptual steps as to how I would do this. I'd like some input from some more experienced people as to if this is possible/wise, before I spend a lot of time banging my head against the wall.

Step 1: Query related table for desired programs for example a query task based around query where program = biology, not returning geometry(since there is none) and only returning the outfield that corresponds to the school ID in the point layer. This would return a feature set which only has the school IDs with a biology program in it.

Step 2: Use the results of the first query in the "where" clause of a second query, which queries the point layer to return the schools that have biology programs.

Step 3: return graphics for the second query.


If this design is wise, I think my main hangup is...........how to use the results of the first query in the "where" clause of the second query.

Any input on this would be great, specifically with regard to how to use the results of the first query in the where clause of the second query. Is there any, or could you provide sample code for this type of procedure.
0 Kudos
1 Solution

Accepted Solutions
KellyHutchins
Esri Frequent Contributor
I think the problem with your code being unable to find the function is due to some mis-placed curly braces. In your example the closing brace for getSchoolId is outside the addPointsToMap function.  Another issue in your code is that your second query task isn't waiting for the first to finish so it may execute before you have the school id. Here's a quick test case that shows one way of executing a query after another has finished.

<!DOCTYPE html> <html> <head>   <title>Create a Map</title>   <meta http-equiv="Content-Type" content="text/html; charset=utf-8">   <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no">   <link rel="stylesheet" href="http://js.arcgis.com/3.7/js/dojo/dijit/themes/claro/claro.css">   <link rel="stylesheet" href="http://js.arcgis.com/3.7/js/esri/css/esri.css">   <style>     html, body, #mapDiv {       padding: 0;       margin: 0;       height: 100%;     }   </style>      <script src="http://js.arcgis.com/3.8/"></script>   <script>     var map;      require([       "esri/map",        "esri/tasks/query",        "esri/tasks/QueryTask",        "esri/symbols/SimpleMarkerSymbol",        "esri/InfoTemplate",        "dojo/_base/Color",        "dojo/domReady!"       ],        function(         Map,          Query,          QueryTask,          SimpleMarkerSymbol,          InfoTemplate,         Color       ) {          map = new Map("mapDiv", {             basemap: "streets",             center: [-122,47], // long, lat             zoom: 6,             sliderStyle: "small"         });           //Contrived sample first lets query the census layer to find a state then use that value to query          //another service            var queryTask = new QueryTask("http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/3");         var query = new Query();         query.where = "STATE_FIPS = '53'"; //Washington         query.returnGeometry = false;         query.outFields = ["STATE_ABBR"];         queryTask.execute(query).then(getDetails);                                     function getDetails(response){           //response is the value from query 1 we can use this to query another service and find            //all the values where state abbreviation is WA           if(response.features.length > 0){             var queryVal = response.features[0].attributes.STATE_ABBR;             var query = new Query();             query.where = "State = '" + queryVal + "'";             query.returnGeometry = true;             query.outFields = ["*"];             var queryTask = new QueryTask("http://services.arcgis.com/V6ZHFr6zdgNZuVG0/arcgis/rest/services/CascadeCrest/FeatureServer/0");             queryTask.execute(query, addPointsToMap);           }           }                  function addPointsToMap(featureSet) {          var symbol =  new SimpleMarkerSymbol().setColor(new Color([255,255,128, .85]));          var featuresX = featureSet.features;          console.log(featuresX);          var featureX;          for (var i=0, il=featuresX.length; i<il; i++) {             featureX = featuresX;             map.graphics.add(featuresX.setSymbol(symbol));         }                }               });          </script>  </head> <body class="claro">   <div id="mapDiv"></div> </body> </html> 

View solution in original post

0 Kudos
3 Replies
PaulWilner_II
New Contributor
Here is code I came up with to execute my conceptual design above. Right now it is throwing a reference error on the queryX callback function. stating that "addPointsToMapX is undefined"

Any thoughts?

!DOCTYPE html>
<html>
<head>
  <title>Create a Map</title>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no">
  <link rel="stylesheet" href="http://js.arcgis.com/3.7/js/dojo/dijit/themes/claro/claro.css">
  <link rel="stylesheet" href="http://js.arcgis.com/3.7/js/esri/css/esri.css">
  <style>
    html, body, #mapDiv {
      padding: 0;
      margin: 0;
      height: 100%;
    }
  </style>
  
  <script src="http://js.arcgis.com/3.8/"></script>
  <script>
  
  // Initializes map
    var map, defPopSymbol;
    require(["esri/map", "esri/tasks/query", "esri/tasks/QueryTask", "esri/symbols/SimpleMarkerSymbol", "esri/InfoTemplate", "dojo/_base/Color", "dojo/domReady!"], 
      function(Map, Query, QueryTask, SimpleMarkerSymbol, InfoTemplate, Color) { 
        map = new Map("mapDiv", {
            basemap: "streets",
            center: [-75.498,42.981], // long, lat
            zoom: 6,
            sliderStyle: "small"
        });
        
        // Delcare the output array of query 1
        var SchoolID = new Array ();
        
        SchoolPopSymbol = new SimpleMarkerSymbol().setColor(new Color([255,255,128, .85])); 
  // Query 1 the related to get campus IDs of program
  
        var queryTask = new QueryTask("http://w7hp348/arcgis/rest/services/Testservice/XTest/MapServer/1"+ "?returnDistinctValues=true");
        var query = new Query();
        query.where = "Academic_Program_Name = 'Biology'";
        query.returnGeometry = false;
        query.outFields = ["Primary_SUNY_Institution_ID"];
        queryTask.execute(query, GetSchoolID);
        
 // Initialize query 2 which takes IDs obtained from query 1 and queries the Campus layer
 
        var queryTaskX = new QueryTask("http://w7hp348/arcgis/rest/services/Testservice/XTest/MapServer/0");
        var queryX = new Query();
        queryX.returnGeometry = true;
        queryX.outfields = ["CampusName"];
        queryX.where = "SUNY_INSTITUTION_CODE  = " + SchoolID;
        queryTaskX.execute(queryX, addPointsToMapX);
        
        
        
        

    
      
      // callback of query 1 populates "SchoolID" and fires query 2
        function GetSchoolID(featureSet) {
        var features = featureSet.features;
        var feature;
        for (var i=0, il=features.length; i<il; i++) {
          feature = features;
          SchoolID =  featureSet.features.attributes["Primary_SUNY_Institution_ID"];  
          
          
         
          
        }


       function addPointsToMapX(featureSet) {
       var featuresX = featureSet.features;
       var featureX;
       for (var i=0, il=featuresX.length; i<il; i++) {
          featureX = featuresX;
           map.graphics.add(featuresX.setSymbol(SchoolPopSymbol));
            console.log(featureX.length);
          
          
          
      }
        
        
      }


        
      }
  });
      
  </script>

</head>
<body class="claro">
  <div id="mapDiv"></div>
</body>
</html>
0 Kudos
KellyHutchins
Esri Frequent Contributor
I think the problem with your code being unable to find the function is due to some mis-placed curly braces. In your example the closing brace for getSchoolId is outside the addPointsToMap function.  Another issue in your code is that your second query task isn't waiting for the first to finish so it may execute before you have the school id. Here's a quick test case that shows one way of executing a query after another has finished.

<!DOCTYPE html> <html> <head>   <title>Create a Map</title>   <meta http-equiv="Content-Type" content="text/html; charset=utf-8">   <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no">   <link rel="stylesheet" href="http://js.arcgis.com/3.7/js/dojo/dijit/themes/claro/claro.css">   <link rel="stylesheet" href="http://js.arcgis.com/3.7/js/esri/css/esri.css">   <style>     html, body, #mapDiv {       padding: 0;       margin: 0;       height: 100%;     }   </style>      <script src="http://js.arcgis.com/3.8/"></script>   <script>     var map;      require([       "esri/map",        "esri/tasks/query",        "esri/tasks/QueryTask",        "esri/symbols/SimpleMarkerSymbol",        "esri/InfoTemplate",        "dojo/_base/Color",        "dojo/domReady!"       ],        function(         Map,          Query,          QueryTask,          SimpleMarkerSymbol,          InfoTemplate,         Color       ) {          map = new Map("mapDiv", {             basemap: "streets",             center: [-122,47], // long, lat             zoom: 6,             sliderStyle: "small"         });           //Contrived sample first lets query the census layer to find a state then use that value to query          //another service            var queryTask = new QueryTask("http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/3");         var query = new Query();         query.where = "STATE_FIPS = '53'"; //Washington         query.returnGeometry = false;         query.outFields = ["STATE_ABBR"];         queryTask.execute(query).then(getDetails);                                     function getDetails(response){           //response is the value from query 1 we can use this to query another service and find            //all the values where state abbreviation is WA           if(response.features.length > 0){             var queryVal = response.features[0].attributes.STATE_ABBR;             var query = new Query();             query.where = "State = '" + queryVal + "'";             query.returnGeometry = true;             query.outFields = ["*"];             var queryTask = new QueryTask("http://services.arcgis.com/V6ZHFr6zdgNZuVG0/arcgis/rest/services/CascadeCrest/FeatureServer/0");             queryTask.execute(query, addPointsToMap);           }           }                  function addPointsToMap(featureSet) {          var symbol =  new SimpleMarkerSymbol().setColor(new Color([255,255,128, .85]));          var featuresX = featureSet.features;          console.log(featuresX);          var featureX;          for (var i=0, il=featuresX.length; i<il; i++) {             featureX = featuresX;             map.graphics.add(featuresX.setSymbol(symbol));         }                }               });          </script>  </head> <body class="claro">   <div id="mapDiv"></div> </body> </html> 
0 Kudos
PaulWilner_II
New Contributor
Kelly, this was a truly awesome answer; Not only did you help me fix a syntax error that was bugging me, and also explain the execution order problem(that I didn't even know was a problem), but you also provided a concrete example to help me work through programming my application myself. I was able to do so, and I'm now able to programmatically find which of our schools have specific programs. For a beginner like me, it is a thing of beauty, thank you so much for your help! 

I consider this question answered and thread closed.
0 Kudos