topic Re: Related Table Queries in ArcGIS API for Silverlight Questions

Related Table Queries

JasonKnisley — Wed, 18 Jan 2012 15:14:10 GMT

I'm trying to query related tables and finding the process cumbersome. I'm hoping someone can shed light on a better way to accomplish this.

General scenario:
I have a collection of layers (lets say 5 for this example) for various structures, and a single table of special equipment that is related to all 5 layers. Entries in the equipment table have no specific spatial component since Equipment Type A may be located in a number of places across the 5 layers to which it is related. A user wants to find locations for equipment that matches certain parameters (the specific equipment model doesn't matter as long as it meets height and weight requirements, for example). Within the current API I see a few possible methods to accomplish this, but all of them present problems. Hopefully there is a much easier solution that I am missing.

Method 1:
Query the equipment table first to find all equipment that matches the specified parameters. Then use those object ids in a RelationshipParameter to perform 5 more queries (one for each layer) to get features related to the equipment. Unfortunately, even though I'm preforming a related query against layers with geometry, for some reason the API doesn't seem to allow for a spatial filter at this point, so now I assume I have to call a GeometryService for each result in order to determine which final locations are actually within the user specified boundary.
Total cost: 6 queries (1 to the table + 5 to the associated layers) + 5 geometry service calls
Disadvantage: There is no good way to apply a spatial filter, and resources are wasted by obtaining full sets of results and the relying on several geometry service calls to obtain the final data set.

Method 2:
Since the previous method makes it complicated to apply a spatial filter in any of the queries, an alternate solution is to query the layers first, with the result of 5 different feature sets that are within the user specified boundary. With each feature set I can make a related query where the DefinitionExpression includes the equipment-related parameters for further filtering. I then compile each of the separate results into a single result set.
Total cost: 10 queries
Disadvantage: A large number of queries are needed... the equipment table must now be queried 5 times instead of just once.

Method 3:
Use the same strategy as the first method, and query the equipment table first. However, instead of using a RelationshipParameter and executing a RelationshipQuery against each of the 5 layers, execute a regular query (with the appropriate spatial filter) against each of the 5 layers and programmatically define the relationships.
Total cost: 6 queries
Disadvantage: Essentially ignore relationships defined in ArcGIS and instead hard code relationships.

This would all be much easier if there was a way to apply a spatial filter to a related query. Is there a way to accomplish this that I'm simply missing?
------------------
If looking at actual services help to visualize this problem, consider the services at http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/Petroleum/KSPetro/MapServer. Let's say that the Tops table was related to 5 different layers, and a user wants to find locations in any layer (but within a specific boundary) where ELEVATION > x. What, if any, is an effective way to accomplish this?

Thanks!

Re: Related Table Queries

JenniferNery — Sat, 11 Dec 2021 23:58:14 GMT

FeatureLayer (and QueryTask) support spatial filter through its Geometry property.

Here's are few examples:
FeatureLayer.Geometry
http://help.arcgis.com/en/webapi/silverlight/samples/start.htm#FeatureLayerFiltering

    <esri:FeatureLayer ID="SpatialFilterFeatureLayer"
                    Url="http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer/3" 
                    Renderer="{StaticResource MySimplePolygonRenderer}">
     <esri:FeatureLayer.Geometry>
      <esri:Envelope XMin="-100" YMin="42" XMax="-95" YMax="47" >
       <esri:Envelope.SpatialReference>
        <esri:SpatialReference WKID="4326"/>
       </esri:Envelope.SpatialReference>
      </esri:Envelope>
     </esri:FeatureLayer.Geometry>
    </esri:FeatureLayer>

Query.Geometry
http://help.arcgis.com/en/webapi/silverlight/samples/start.htm#SpatialQuery

Query query = new ESRI.ArcGIS.Client.Tasks.Query();

            // Specify fields to return from query
            query.OutFields.AddRange(new string[] { "STATE_NAME", "SUB_REGION", "STATE_FIPS", "STATE_ABBR", "POP2000", "POP2007" });
            query.Geometry = args.Geometry;

            // Return geometry with result features
            query.ReturnGeometry = true;
            query.OutSpatialReference = MyMap.SpatialReference;

            queryTask.ExecuteAsync(query);

Re: Related Table Queries

JasonKnisley — Sat, 11 Dec 2021 23:58:17 GMT

I don't see how those can be used in this case, however. Consider the Tops table for example (http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/Petroleum/KSPetro/MapServer/2). It has no geometry directly associated with it, and if you try to query it with a spatial filter you get an error. It's spatial information is actually obtained by performing a related query on the Wells layer, providing the object ids for the Tops that you are interested in.

As a more concrete example, say that I want to find all of the Tops locations where FORMATION = 'Heebner Shale Member', but I'm only interested in an area bounded by -75, 0, 0, 30 (spatial reference 4267). This is just an arbitrary boundary within the full extent of the Wells layer, but in real life users are often only interested in areas that are a subset of the full extent of a layer. How can I go about this?

Relating to the first two methods I described above, here are the solutions (and problems) that I see.

Method 1:
Start by querying Tops...

Query query = new Query() { Where = "FORMATION = 'Heebner Shale Member'" };

Now I have a bunch of results with no geometry because the table has no geometry. That's fine, it's a related table, I can get the geometry from a relationship query. The problem is that in the relationship query, while I can get results with geometry, I can't apply a spatial filter as part of the relationship query.
In the QueryTask_ExecuteCompleted for my Tops query, I can do:

RelationshipParameter relationshipParameter = new RelationshipParameter() {
    ObjectIds = (List<int>)e.FeatureSet.ObjectIDs,
    RelationshipId = 3,
    OutFields = new String[] { "*" }
};

but now I still have results from the full extent of the Wells layer, rather than the spatial boundary that I am interested in, and in the Silverlight API I don't know of a good way to perform the filter from this point. Note that if there are multiple related layers, a separate relationship query must be performed for each. This takes a total of 1 + n queries, where n is the number of layers related to the table of interest.

Method 2:
Start by querying Wells... (in my real life example this means a separate query for each layer related to the table of interest).

Query query = new Query() {
    Geometry = spatialFilter  // user specified boundary
    ReturnGeometry = true
};
query.OutFields.Add("OBJECTID");

Now I have all of the Well features within the boundary, but the user doesn't really care about this. They want to know the locations where Tops FORMATION = 'Heebner Shale Member', so I do a related query against wells (and again in real life this involved a separate query for each related layer). So, for each layer I do:

RelationshipParameter relationshipParameter = new RelationshipParameter() {
    ObjectIds = (List<int>)e.FeatureSet.ObjectIDs,
    RelationshipId = 3,
    DefinitionExpression = "FORMATION = 'Heebner Shale Member'",
    OutFields = new String[] { "*" }
};

As a result I now get a RelatedRecordsGroup which is of type IDictionary<int,IEnumerable<Graphic>>, but since none of these graphics have geometry I have to iterate through each of them and set their geometry based on the results of the original query.
The total number of queries that it takes to perform this search is 2*n where n is the number of layers related to the table of interest.

In both cases I'm also wasting resources by returning geometry for features that I don't care about. In Method 1 I'm returning geometry for only those features with Tops FORMATION = 'Heebner Shale Member' but those features are for the entire extent of the layer rather than the smaller extent of interest. In Method 2 I'm returning geometry for only those features in the extent of interest, but I will still end up discarding all of them except where Tops FORMATION = 'Heebner Shale Member'.

If a RelationshipParameter was able to take a spatial filter then I could apply Method 1 while still making relatively efficient use of resources.

Going back to the problem with method 1, where the results I am given span the entire extent since I can't specify Geometry for a RelationshipParameter, how can I go about applying a spatial filter to those results?

Re: Related Table Queries

JenniferNery — Wed, 18 Jan 2012 18:36:31 GMT

This seem related thread: forums.arcgis.com/threads/8680-FeatureLayer-and-SpatialRelationship-for-selection-geometry

The RelationshipResult can be filtered by geometry from the client API using FindGraphicsInHostCoordinates() or Intersects(). See post# 4 in this thread: forums.arcgis.com/threads/43496-Select-and-Delete-feature-overlapped-polygon-problem. These are the client-side operations.

Re: Related Table Queries

JasonKnisley — Wed, 18 Jan 2012 19:00:29 GMT

Thanks for the response. It looks like FindGraphicsInHostCoordinates() would work for instances where the user specifies a rectangle as an area of interest, but if they wanted a polygon or a circle (i.e. radius from an incident point) then I guess it's sounding like I have to do something like my Method 2 from above.

A few questions...
Is there a reason why a spatial filter can't be applied to a related query? Will there be support for this in the future?

Even better, are there plans for other client-side geometry operations for the Silverlight API? I know that the ArcGIS API for Android has a GeometryEngine class with very helpful methods such as contains() and crosses() that can take any type of geometries as input, but so far I haven't come across any of its counterparts in Silverlight.

Re: Related Table Queries

DaveTimmins — Wed, 18 Jan 2012 19:16:27 GMT

Hi,

there were some client side geometry methods added at the 2.3 release http://help.arcgis.com/en/webapi/silverlight/2.3/help/index.html#/What_s_new_in_2_3/0166000000mm000000/ though at the moment it only supports a subset of operations. Also some geometry types have some spatial operations such as Envelope -> intersection, union etc.

Should be possibe to create your own helper methods though.

Cheers,

Re: Related Table Queries

DaveTimmins — Sat, 11 Dec 2021 23:58:20 GMT

Hi,

forgot to add this example

public static bool Contains(this Envelope envelope, Envelope contained)
    {
        if (envelope == null || contained == null)
        {
            return false;
        }
        if (!envelope.SpatialReference.Equals(contained.SpatialReference))
        {
            return false;
        }
        if (envelope.XMin <= contained.XMin && envelope.XMax >= contained.XMax && envelope.YMin <= contained.YMin)
        {
            return envelope.YMax >= contained.YMax;
        }
        return false;
    }

Cheers,