3

I have a point dataset (SDE Feature Class) of locations in a city that receive inspections. I also have a table of data (File GeoDatabase Table) where there are many inspections for each point. I have created a relate between the two in ArcMap and want to perform a definition query on the points to filter them based on the results of these inspections. After struggling and researching for a while it seems that Relates don't allow this type of functionality.

Can anyone indicate how I might filter the spatial data based on the related table?

3 Answers 3

5

You can use a sub-query in the definition query as long as the data is not versioned or archive enabled (then you have to query the default view). Since you have key fields already defined from your relate you can use something like:

OBJECTID in (SELECT JOINIDFIELD FROM RELATEDTABLE WHERE INSPECTIONRESULT = 'Failed')

Read more about sub queries here

It will be faster than a Join...

3
  • Hi Ben, I think this may be what I'm after but may need help constructing the query.
    – E. Wright
    Oct 17, 2016 at 15:35
  • I will get back to you on this.
    – E. Wright
    Oct 17, 2016 at 15:35
  • Include more detail on the table names, common fields, and the inspection result field and values you are trying to query. Oct 17, 2016 at 17:07
0

Try a 'join' instead of a 'relate', which will add the table columns to your point dataset and then you can filter based on them.

ESRIs help on the topic: http://desktop.arcgis.com/en/arcmap/10.3/manage-data/relationships/deciding-between-relationship-classes-joins-and-re.htm

Here's a similar issue: Querying through Relations

3
  • 1
    From what I understand, using a join for one-to-many relationships can be sketchy. In ArcMap help, it gives this caution: "In all cases of 1:M joins, only the first matching record is joined and displayed in the layer's attribute table." If this is the case, I'm not sure how you would query the other matching records, if they aren't visible in the joined table. Maybe ArcMap supports querying on the hidden attributes?
    – Baltok
    Oct 17, 2016 at 13:44
  • Good point, @Baltok 52. I missed that part. Depending on OP's goal, might be able to summarize the inspections table and make it a 1:1 join.
    – alaybourn
    Oct 17, 2016 at 14:52
  • Adding a definition query to the standalone table may give you the results you want. Definitely worth testing. Oct 17, 2016 at 17:22
-1

There's a query builder tool in ArcToolbox that is meant for hitting related tables and complex tabular data.

0

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.