ArcGIS JSAPI Query Related Records/Relationship Table

GeoffreyWest · ‎06-22-2016

I am using this example https://developers.arcgis.com/javascript/3/jssamples/fl_query_related.html

to develop an application which queries related features. I have two feature classes and have created a N-M relationship class. The objective is to click on an electric feeder and query the related customers. There are 26 electrical feeders and over 400 customers. My relationship class was based on the OBJECTIDs being the primary keys and the foreign keys being the "feederID" that the structures have and the that the customers are assigned to.

Ideally, I'd expect to see several customers per feeder, but based on my logic in this code sample, I am only returning the matching ObjectIDs per feeder.

What should be changed to return each customer which shares the same feeder?

JakeSkinner · ‎06-23-2016

Hi Geoffrey,

Your code looks correct from what I can tell. Is it possible to share your REST service URL?

GeoffreyWest · ‎06-23-2016

Hi Jake,

This REST service is not exposed to the public. When creating the relationship class, should it be N-M? My original was 1-M. Can you please explain the logic in the code which relates one feature to many related records? That is what I am missing here, as when I click on one feeder, the returned record is the customer site with the same objectID.

JakeSkinner · ‎06-23-2016

1-M should work. The code will find the associated OBJECTID of a feature, use the realtionshipID and query related records. Below is code and sample data you can test with to help trouble shoot the issue.

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no">
    <title>Query RelatedRecords Example</title>

    <link rel="stylesheet" href="http://js.arcgis.com/3.16/dijit/themes/tundra/tundra.css">
    <link rel="stylesheet" href="http://js.arcgis.com/3.16/dojox/grid/resources/Grid.css">
    <link rel="stylesheet" href="http://js.arcgis.com/3.16/dojox/grid/resources/tundraGrid.css">
    <link rel="stylesheet" href="http://js.arcgis.com/3.16/esri/css/esri.css">
    <style>
        html, body, #mapDiv {
          height: 100%;
          width: 100%;
          margin: 0;
          padding: 0;
        }

        body {
          background-color:#777;
          overflow:hidden;
          font-family: "Trebuchet MS";
        }

        #header {
          background-color:#444;
          color:orange;
          font-size:15pt;
          text-align:center;
          font-weight:bold;
        }

        #footer {
          background-color:#444;
          color:orange;
          font-size:10pt;
          text-align:center;
        }
    </style>

    <script src="http://js.arcgis.com/3.16/"></script>
    <script>
    var map, parcelFeatureLayer, grid, store;

      require([
        "esri/map",
        "esri/layers/FeatureLayer",
        "esri/layers/ArcGISDynamicMapServiceLayer",
        "esri/tasks/query",
        "esri/tasks/QueryTask",
        "esri/tasks/RelationshipQuery",
        "esri/toolbars/draw",
        "dojox/grid/DataGrid",
        "dojo/data/ItemFileReadStore",
        "esri/layers/ImageParameters",
        "dojo/parser",
        "esri/geometry/Extent",
        "dojo/_base/array",
        "esri/symbols/SimpleMarkerSymbol",
        "esri/symbols/SimpleFillSymbol",
        "esri/symbols/SimpleLineSymbol",
        "dojo/_base/Color",
        "esri/tasks/FindTask",
        "esri/tasks/FindParameters",
        "esri/InfoTemplate",
        "dijit/registry",
        "dojo/_base/array",
        "dojo/on",
        "dojo/dom",
        "dijit/form/Button",
        "dijit/layout/BorderContainer",
        "dijit/layout/ContentPane",
        "dojo/domReady!"
      ], function(
        Map,
        FeatureLayer,
        DynamicMapServiceLayer,
        Query,
        QueryTask,
        RelationshipQuery,
        Draw,
        DataGrid,
        ItemFileReadStore,
        ImageParameters,
        parser,
        Extent,
        array,
        SimpleMarkerSymbol,
        SimpleFillSymbol,
        SimpleLineSymbol,
        Color,
        FindTask,
        FindParameters,
        InfoTemplate,
        registry,
        arrayUtils,
        on,
        dom,
        Button
      ) {
        var findTask, findParams;

        parser.parse();

        map = new Map("mapDiv", {
          basemap: "satellite",
          center: [-75.3917, 38.6902],
          zoom: 17
        });

        findTask = new FindTask("https://ags/arcgis/rest/services/ParcelsRelate/MapServer");

        on(dom.byId("search"), "click", doFind);

        map.on("load", function () {
          //Create the find parameters
          findParams = new FindParameters();
          findParams.returnGeometry = true;
          findParams.layerIds = [0];
          findParams.searchFields = ["Name"];
          findParams.outSpatialReference = map.spatialReference;
        });

        var symbol = new SimpleFillSymbol(
            SimpleFillSymbol.STYLE_SOLID,
            new SimpleLineSymbol(SimpleLineSymbol.STYLE_SOLID, new Color([98, 194, 204]), 2),
            new Color([98, 194, 204, 0.5])
          );

        function doFind() {
          map.graphics.clear();
          parcelFeatureLayer.clearSelection();
          //Set the search text to the value in the box
          findParams.searchText = dom.byId("PIN").value;
          findTask.execute(findParams, showResults);
        }

        function showResults(results) {
          //This function works with an array of FindResult that the task returns
          map.graphics.clear();

          var items = arrayUtils.map(results, function (result) {
            var graphic = result.feature;
            graphic.setSymbol(symbol);
            map.graphics.add(graphic);
            return result.feature.attributes;
          });

        }


        var dynamicLayer = new DynamicMapServiceLayer("https://ags/arcgis/rest/services/ParcelsRelate/MapServer");
        map.addLayer(dynamicLayer);

        parcelFeatureLayer = new FeatureLayer("https://ags/arcgis/rest/services/ParcelsRelate/MapServer/0", {
          mode: FeatureLayer.MODE_ONDEMAND
        });


        parcelFeatureLayer.setSelectionSymbol(symbol);
        parcelFeatureLayer.on("selection-complete", findRelatedRecords);
        findTask.on("complete", findRelatedRecords2);
        map.addLayer(parcelFeatureLayer);

        map.on("click", findOwnerInfo);

        function findRelatedRecords(evt) {
            var features = evt.features;
            var relatedTopsQuery = new RelationshipQuery();
            relatedTopsQuery.outFields = ["*"];
            relatedTopsQuery.relationshipId = 0;
            relatedTopsQuery.objectIds = [features[0].attributes.OBJECTID];
            parcelFeatureLayer.queryRelatedFeatures(relatedTopsQuery, function(relatedRecords) {
              var fset = relatedRecords[features[0].attributes.OBJECTID];
              var items = array.map(fset.features, function(feature) {
                return feature.attributes;
              });
              //Create data object to be used in store
              var data = {
                identifier: "OBJECTID",  //This field needs to have unique values
                label: "OBJECTID", //Name field for display. Not pertinent to a grid but may be used elsewhere.
                items: items
              };

              //Create data store and bind to grid.
              store = new ItemFileReadStore({ data:data });
              grid.setStore(store);
              grid.setQuery({ OBJECTID: "*" });
            });
        }

        function findRelatedRecords2(evt){
            var features = evt.result[0].feature;
            console.log(features);
            var relatedTopsQuery = new RelationshipQuery();
            relatedTopsQuery.outFields = ["*"];
            relatedTopsQuery.relationshipId = 0;
            relatedTopsQuery.objectIds = [features.attributes.OBJECTID];
            parcelFeatureLayer.queryRelatedFeatures(relatedTopsQuery, function(relatedRecords) {
              console.log("related recs: ", relatedRecords);
              if ( ! relatedRecords.hasOwnProperty(features.attributes.OBJECTID) ) {
                console.log("No related records for ObjectID: ", features.attributes.OBJECTID);
                return;
              }
              var fset = relatedRecords[features.attributes.OBJECTID];
              var items = array.map(fset.features, function(feature) {
                return feature.attributes;
              });
              //Create data object to be used in store
              var data = {
                identifier: "OBJECTID",  //This field needs to have unique values
                label: "OBJECTID", //Name field for display. Not pertinent to a grid but may be used elsewhere.
                items: items
              };

              //Create data store and bind to grid.
              store = new ItemFileReadStore({ data:data });
              grid.setStore(store);
              grid.setQuery({ OBJECTID: "*" });
            });
        }

        function findOwnerInfo(evt) {
            map.graphics.clear();
            grid.setStore(null);
            var selectionQuery = new Query();
            var tol = map.extent.getWidth()/map.width * 2;
            var x = evt.mapPoint.x;
            var y = evt.mapPoint.y;
            var queryExtent = new Extent(x-tol,y-tol,x+tol,y+tol,evt.mapPoint.spatialReference);
            selectionQuery.geometry = queryExtent;
            parcelFeatureLayer.selectFeatures(selectionQuery,FeatureLayer.SELECTION_NEW);
        }
    });

    </script>
  </head>
  <body class="tundra">
    <!--TOPMOST LAYOUT CONTAINER-->
    <div id="mainWindow" data-dojo-type="dijit/layout/BorderContainer" data-dojo-props="design:'headline',gutters:false" style="width:100%; height:100%;">
      <!--HEADER SECTION-->
      <div id="header" data-dojo-type="dijit/layout/ContentPane" data-dojo-props="region:'top'" style="height:75px;">
      </div>
      <!--CENTER SECTION-->
      <!--CENTER CONTAINER-->
      <div id="mapDiv" data-dojo-type="dijit/layout/ContentPane" data-dojo-props="region:'center'" style="margin:5px;"></div>
      <!--RIGHT CONTAINER-->

        <div data-dojo-type="dijit/layout/ContentPane" data-dojo-props="region:'right'" style="width:450px;margin:5px;background-color:whitesmoke;">
          <div data-dojo-type="dijit/layout/AccordionContainer">
            <div data-dojo-type="dijit/layout/ContentPane" id="searchPane" data-dojo-props="title:'Search', selected:true">
              <div id="searchDiv">
                 PIN: <input type="text" id="PIN" size="60" value="135-14.20-134.00" /><br><br>
                 <input type="button" name="search" value="Search" id="search"/>
              </div>
            </div>
            <div data-dojo-type="dijit/layout/ContentPane" id="gridPane" data-dojo-props="title:'Related Records'">
              <table data-dojo-type="dojox/grid/DataGrid" jsid="grid" id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px'" style="height:300px; width:300px">
              <thead>
                <tr>
                  <th field="PIN">PIN</th>
                  <th field="LastName">Owner Name</th>
                  <th field="MAILINGADDRESS">Address</th>
                  <th field="LANDUSE">Landuse</th>
                </tr>
              </thead>
            </table>
           </div>
          </div>
      </div>
    </div>
  </body>
</html>

GeoffreyWest · ‎06-23-2016

The thing is however, I don't want to find an exact objectID match, for instance objectID 1 of my feeders can have objectIDs 1,20,59,100 for the customers, so that is where I am thrown off with the logic of using objectID. Like I said in my original iteration I used objectID and it was just a 1-1 match between objectIDs of both feature classses, i.e. I'd click OID 1 for feeder and the related feature was OID1 for customers when there should be several OIDs related to this one feeder. I will take a look at this though. Thanks, Jake.

JakeSkinner · ‎06-23-2016

You'll see with the data that the relationship is actually created from the Parcels 'Name' attribute, and the table's PIN attribute. The OBJECTID is simply used to select the parcel and find the related records using the relationship that was created. So in the example I provide, a parcel is searched/selected. It obtains the OBJECTID and finds the related records by using the Name field in the parcel feature class, and the PIN in the table.

GeoffreyWest · ‎06-23-2016

Got it, can you provide a service exposed to the public aside from these so I can see the functionality on my end?

var dynamicLayer = new DynamicMapServiceLayer("https://ags/arcgis/rest/services/ParcelsRelate/MapServer");

map.addLayer(dynamicLayer);

parcelFeatureLayer = new FeatureLayer("https://ags/arcgis/rest/services/ParcelsRelate/MapServer/0", {

mode: FeatureLayer.MODE_ONDEMAND

});

JakeSkinner · ‎06-24-2016

I don't have a public facing server to expose the service to. You can publish the attached data to ArcGIS Server/ArcGIS Online, and then update the 3 URLs and the application will work.

GeoffreyWest · ‎06-28-2016

Should the PIN be unique per feature? My PK and FK is STRUCTURENAME and there are many features which have the same value for STRUCTURENAME. My use case is that I have several customers which are related to one structure, I would like to return each customers info that is related to a particular vale of STRUCTURENAME.

JakeSkinner · ‎07-05-2016

Would you be able to provide a small sample of your data? I believe you can send me a personal message with a zipped attachment.