topic Re: Filter features based on a no-geom table in ArcGIS Pro Questions

Filter features based on a no-geom table

CarstenHogertz — Fri, 17 Jun 2022 09:32:46 GMT

Hello,

I've got two datasets in my current map:

1. a FeatureLayer (locations of measuring points for air quality data)

2. a table without geom (holds the air quality measured data for each feature)

What I'd like do is the following:

I use a definition query to filter the no-geom table (for example only want to see the measured ozon data)

As not all measuring points have ozon data I'd like the FeatureLayer to be filter based on no-geom table.

How could I achive that. In SQL would be something like: where location in (select distinct (column) from no-geom table).

But I could find a solution in APRO.

Every hint is welcome.

Thanks alot!

Re: Filter features based on a no-geom table

RobertBorchert — Fri, 17 Jun 2022 11:56:31 GMT

there is a definition query that can query out related data. I use it quite often for just your purpose.

The field names need to be database names and not alias

ORIGINFIELD IN (SELECT DESTINATIONFIELD FROM <RELATED NAME> where <FIELDNAME> <QUERY>)

In this example I have some 9000 points. I only want to see points that have a related table value where location is a positive integer

LOCATION in (Select LOCATION FROM SitePhotos_WORKGROUPS where LOCATION > 0)

The first location is the LOCATION field in my point feature this should be the same as the Origin Key in your relationship

The second LOCATION is the Destination Key field in my related table

SitePhotos_WORKGROUPS is the name of the related table

Re: Filter features based on a no-geom table

CarstenHogertz — Fri, 17 Jun 2022 12:12:11 GMT

Thanks @RobertBorchert Does your solution work on services as well?

Re: Filter features based on a no-geom table

RobertBorchert — Fri, 17 Jun 2022 12:26:28 GMT

The project I use this solution for there are some 9000 point features with Location Numbers for our company. The project allows field crews to take photos of various sites for a variety of reasons. In a Dashboard the people in the office can find that site and see the photos, with a variety of other filters.

This definition query only shows the points with photos. Currently some 900

However, I do not know if this will work with AGOL or services hosted in the Cloud. We are an Enterprise Portal

Re: Filter features based on a no-geom table

CarstenHogertz — Mon, 20 Jun 2022 13:16:02 GMT

One thing is missing in your proposed solution (maybe because I didn't communicate my requirements clearly):

- My no-geom table itself uses definition queries in APRO to filter it's content
- Your SQL communicates directly with the DB

Well, the consequence is that I can see all locations that have an entry in the no-geom db-table. But what I'd like to do is seeing only that locations that are in the APRO filtered no-geom table.

Do you have a solution for that?