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?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
E. Wright
  • 31
  • 1
  • 2

3 Answers3

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

Ben S Nadler
  • 1,604
  • 10
  • 12
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

Community
  • 1
alaybourn
  • 430
  • 4
  • 11
  • 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 '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
-1

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

Venessa
  • 1