1

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?

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...

| improve this answer | |
  • Hi Ben, I think this may be what I'm after but may need help constructing the query. – E. Wright Oct 17 '16 at 15:35
  • I will get back to you on this. – E. Wright Oct 17 '16 at 15:35
  • Include more detail on the table names, common fields, and the inspection result field and values you are trying to query. – Ben S Nadler Oct 17 '16 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

| improve this answer | |
  • 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 '16 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 '16 at 14:52
  • Adding a definition query to the standalone table may give you the results you want. Definitely worth testing. – Ben S Nadler Oct 17 '16 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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