Spatial definition query

HenkZwols

I would like to be able to apply a spatial definition query to a layer.

I.e. Select features from this pointlayer that are within a feature from buildingpolygons.

Defining a query must be as easy as it can be done in the Select By Location dialog.

The result of the query is updated when features that are involved are modified.

Bud · ‎03-26-2023

Regarding @JoshuaBixby’s valid concerns about spatial query performance:

If anyone comes across this post and wants to set up a spatial definition query themselves on an EGDB FC, using spatial database functions like @TanuHoque suggested, then there are mechanisms in some DBs that can help with precomputing those expensive operations. For example, Oracle SDO_Geometry has function-based spatial indexes. They’re pretty tricky to set up so you might want to get your DBA to do it for you. But they do work and make spatial queries as fast as querying a FC/table. And they don’t require any extra database privileges other than CREATE INDEX, which you would have OOTB in the data creator role. If you know you’ll be performing the spatial query often, then function-based indexes might be worth the trouble.

I don’t know if other enterprise databases offer similar functionality or not. I think SQL Server has something called Indexed Views. But I don’t know if that helps in this case.
File geodatabases don’t have equivalent functionality, that’s for sure. I’d be surprised if mobile geodatabases/SQLite did.

Alternatively, I wonder if precomputing the data in a field or related table would work in some cases. For example, use an attribute rule to populate a field in a point FC that has the ID of an intersecting polygon FC. Although that might get tricky logistically. What if the polygon gets edited? Will the point FC become out-of-date?

Related: Options for computing fields

Of course all of these options are about a hundred times more difficult than what the original idea proposes: spatial definition queries that are as easy as using Select By Location (no code or setup required; just use the UI).

Bud · ‎03-26-2023

@RobertKlein3 Regarding your comment:

…would it also make sense to expand the functionality to turn any selection subset into a definition query, and retain that definition query once you clear selected features?

You might find this idea interesting: Selection to Definition Query

And it looks like there is Python workaround: Creating definition query from selected features using ArcGIS Pro

You can also just do it with a few lines of code. This has the advantage over the make layer from selected feature, in that you can see the query definition.
def qdef_selected_features(lyr):
    desc = arcpy.Describe(lyr)
    # Get a semicolon-delimited string of selected feature IDs 
    fid_list = desc.FIDSet.split(";")
    # build the query definition
    query = '{} IN ({})'.format(desc.OIDFieldName, ",".join(fid_list))
    # apply the query definition back to the layer   
    lyr.definitionQuery = query

Bud · ‎03-27-2023

I suppose it's worth noting that this idea wouldn't work for file geodatabases, since FGDBs don't have spatial SQL functions in the database (unlike enterprise and mobile geodatabases).

I talk about an alternative idea here:
“Select By Location” Feature Layer — Spatial query that is fast, easy, modifiable, and refreshable
That idea is based on ArcObjects, so I think it would work for all datatypes, including file geodatabases.

Bud · ‎03-27-2023

One thing to keep in mind:

With Select By Location in ArcGIS Pro, the tool only has one row in the attribute table per feature. So if I run this Select By Location, it simply selects the features (1:1):

3,800 selected of 3,904

But with a spatial SQL operation like this:

--Oracle 18c, SDE.ST_GEOEMTRY
   SELECT r.*
     FROM road_row_st_geom r,  --cross join
          strln_st_geom s
    WHERE sde.st_intersects (r.shape, s.shape) = 1
 ORDER BY r.objectid

...it produces rows with duplicate objectids, whenever the polygons in the first FC intersect multiple lines in the second FC.

That's a typical difference between ArcGIS Pro vs database queries. ArcGIS usually performs only does 1-to-1 or 1-to-first queries. Whereas databases queries will propagate duplicate rows via the join.

10,614 rows selected.

I often forget about that quirk.

But I suppose, now that I think about it, if Esri were to implement the Spatial Definition Query idea, they'd do what they always do: only perform a 1:1 selection. So the quirk I mentioned above wouldn't be an issue for the user.

If I were to mock up the 1:1 logic in an Oracle query, it might look like this:

  SELECT objectid 
    FROM (SELECT r.objectid,
                 row_number() over(partition by r.objectid order by r.objectid desc) rn
            FROM road_row_st_geom r,
                 strln_st_geom s
           WHERE sde.st_intersects (r.shape, s.shape) = 1
         )
   WHERE rn = 1
ORDER BY objectid

3,879 rows selected.

But I doubt ArcGIS Pro uses that syntax. It might use some sort of generic ANSI SQL subquery or something like that?

It's interesting that Select By Location selected 3,800 rows. But my Oracle SDE.ST_GEOMETRY query selected more rows: 3,879. I wonder why there's a difference?

Bud · ‎03-30-2023

I did a related test (Oracle 18c):

Select road right-of-way polygons (~3,000) that have at least one intersecting point (road midpoints; ~3,000). Only select one row per polygon; I want a unique list of polygons that intersect at least one point.

1. ArcGIS Pro Select By Location tool (processes in RAM): 1 second

2. SDO_GEOMETRY database query: 10 seconds

  SELECT poly_objectid,
         pnt_objectid
    FROM (
          SELECT poly.objectid as poly_objectid,
                 pnt.objectid  as pnt_objectid,
                 row_number() over(partition by poly.objectid order by null) rn
            FROM polygons poly
      CROSS JOIN points pnt
           WHERE sdo_anyinteract(poly.shape, pnt.shape) = 'TRUE'
         )
   WHERE rn = 1

Related (uses different data): Select polygons that intersect points and a parallel post here.

3. SDE.ST_GEOMTERY database query: 50 seconds

  SELECT poly_objectid,
         pnt_objectid
    FROM (
          SELECT poly.objectid as poly_objectid,
                 pnt.objectid as pnt_objectid,
                 row_number() over(partition by poly.objectid order by null) rn
            FROM polygons poly
      CROSS JOIN points pnt
           WHERE sde.st_intersects (poly.shape, pnt.shape) = 1
         )
   WHERE rn = 1

It surprises me that my mediocre office computer with ArcGIS Pro —> Select By Location (processes in RAM) out-performs enterprise databases so spectacularly. Why are databases so bad at spatial queries?

“Select By Location” Feature Layer — Spatial query that is fast, modifiable, and refreshable

Bud · ‎04-06-2023

In the old ArcMap docs, there are some examples of manually writing SQL spatial definition queries:

Workflow: Use SQL with existing feature classes (scroll to the bottom of the page)
Create an expression to identify the particular features in the layer you want to display. The following queries find all the buildings within 25 meters of a water main. Type the expression appropriate to your DBMS in the Layer Properties Definition Query box.
For Oracle:
objectid IN (SELECT b.objectid FROM BUILDINGS b, WATER w
 WHERE w.watertype = 'MAIN' 
 AND sde.st_intersects (b.shape, sde.st_buffer (w.shape, 25)) = 1)
For PostgreSQL:
objectid IN (SELECT b.objectid FROM buildings b, water w
 WHERE w.watertype = 'MAIN' 
 AND sde.st_intersects (b.shape, sde.st_buffer (w.shape, 25)) = 't')
For Db2:
objectid IN (SELECT b.objectid FROM BUILDINGS b, WATER w
 WHERE w.watertype = 'MAIN' 
 AND db2gse.st_intersects (b.shape, db2gse.st_buffer (w.shape, 25)) = 1)
For Informix:
objectid IN (SELECT b.objectid FROM buildings b, water w
 WHERE w.watertype = 'MAIN' 
 AND st_intersects (b.shape, st_buffer (w.shape, 25)))

That requires manual coding. So it's not the same as this idea. But is still interesting.

The subquery approach looks useful. It solves the problem where the inner query might propagate duplicate rows via the join. Although it might not be the most efficient approach. EXISTS might be more efficient in some cases:

IN vs. EXISTS
The IN clause scans all records fetched from the given subquery column, whereas EXISTS clause evaluates true or false, and the SQL engine quits the scanning process as soon as it has found a match.

Bud · ‎04-24-2023

When it comes to Oracle SDO_GEOMETRY spatial queries, the following query performs extremely well. I imagine it could be used in a subquery in a definition query.

    select distinct
        c.objectid as objectid
    from 
        sdo_join('PNT','SHAPE','PLY','SHAPE','MASK=anyinteract') a,
        pnt b,
        ply c
    where 
        a.rowid1 = b.rowid
        and a.rowid2 = c.rowid

https://stackoverflow.com/a/75923159/5576771

The MASK=anyinteract argument in the sdo_join() function can be modified for different spatial relationships:

MASK
The topological relationship of interest.Valid values are 'mask=<value>' where <value> is one or more of the mask values valid for the SDO_RELATE operator (TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT, ON), or FILTER, which checks if the MBRs (the filter-level approximations) intersect. Multiple masks are combined with the logical Boolean operator OR (for example, 'mask=inside+touch'); however, FILTER cannot be combined with any other mask.
If this parameter is null or contains an empty string, mask=FILTER is assumed.

Related: https://forums.oracle.com/ords/apexds/post/select-polygons-that-intersect-points-9091

Bud · ‎01-02-2024

For what it's worth, the Select Layer by Location GP tool has an "Intersect (DBMS)" option, which creates a database view that has a spatial SQL query:

View name: SELECTIONOUTPUTNAME

select "OBJECTID" from ( SELECT a.OBJECTID FROM INFRASTR.ACTIVE_TRANSPORTATION a,
INFRASTR.AGOL_PARKINGLOT b WHERE SDE.ST_INTERSECTS(a.SHAPE, b.SHAPE) = 1 ) esri_sql

In a pinch, I wonder if a person could use the SQL from that view to write a spatial definition query:

objectid in ( select a.objectid from infrastr.active_transportation a,
infrastr.agol_parkinglot b where sde.st_intersects(a.shape, b.shape) = 1 )

Related: Select Layer by Location — Intersect (DBMS) — Create a query layer instead of a database view

ElaineBryant · ‎01-12-2024

I can't believe that this functionality isn't there yet. GeoMedia has had it for 20 years. But it also has offered other types of querying capabilities that Esri just hasn't done in their products. At present, outputting a new feature class isn't acceptable, but that is all we have.