nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Trouver plus de publications taguées avec

Map Authoring

Implemented

Status: Implemented

Réponses

PiergiorgioRoveda1

Spatial Relationship Class

RamonGallegos

This seems like a terribly useful function. for example I have a polygon layer and a point layer. the spatial extent of the points is beyond the polygons. I want to create a map that only displays points that are inside the polygons and exclude points outside of the polygons. I can't manipulate the point attribute table and I don't want to create a layer based on selection. This seems like such an elementary aspect of spatial analysis that it should be as available to users as a definition query is in the layer properties dialog.

Chris Fox

Hi ramong, I believe you can accomplish this workflow by going to the Data Frame Properties > Data Frame > Clipping Options > Clip to Shape. Next click specify shape and in the Data Frame Clipping Dialog select Outline of Features and select your polygon layer. Next you can click exclude layers to exclude all other layers than the points and the output will be showing only the points inside the polygon.

BerendVeldkamp

Chris,

Clipping may work in some cases, but some tools do not recognize clipped features. For instance, when creating a mobile cache from an mxd, the definition query can limit the number of exported features, whereas clipping does not.

RobertKlein3

This might come in handy; 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?

LenWilson

Was just about to write the exact same thing but you got in before me 🙂 Awesome idea......

Trying to temporary highlight problem bridges only on certain roads.... but not affect the original source... or create extra redundant copies of data.

The clip suggestion isn't quiet the same

spatialcase

Can't belive that this basic functionality still isn't available!

RaymondBrunner

Using the result of a Spatial Definition Query would be helpful for publishing regions with ArcGIS for Server.

It would be useful to publish a geographic portion of the production enterprise geodatabase, negating the need to perform a distributed geodatabase replica.

JosephMcGehan1

This function would be good as it could be called with ArcPy. Therefore avoiding the 9 click ArcGIS Desktop dataframe properties manual method. It already exists at within production mapping (old PLTS), maybe it should be made apart of the mapping module.

### arcpyproduction EXAMPLE:
    arcpyproduction.mapping.ClipDataFrameToGeometry (data_frame, clip_geometry, {exclusion_layers})

MorganGrundy

Please esri.

JayGuarneri

I would really love to be able to use this functionality in SearchCursors to minimize the number of features I need to loop through.

MikeJones6

You can't publish services with the "Clip to Shape" function turned on. The work around doesn't even work for everything. AutoCAD has had this for a few years now, maybe more.

KiraNelson1

I would love to be able to use this for editing, for example finding all points that aren't connected to lines, and having the selection refresh after edits.

GreggRoemhildt1

This idea is a good start to a similar idea I had.

Instead of simplifying it to Clipping it would be awesome to allow definition queries that implement the arcpy Geometry api, or the SQL relational functions for ArcSDE.

Arcpy Geometry API:

Geometry—Help | ArcGIS for Desktop

Select features that are with 10 units to another:

where: SHAPE.distanceTo(other_table.SHAPE) = 10

Select features contained in the other:

where: SHAPE.contains(other_table.SHAPE)

SQL Relational Functions:

A quick tour of SQL functions used with ST_Geometry—Help | ArcGIS for Desktop

For example, select features that touch another:

Where: SHAPE@.ST_Touches(other_table.SHAPE@)

features that are equal to another (geometrically):

Where: SHAPE@.ST_Equals(other_Table.SHAPE@)

JoshuaBixby

While I agree that such functionality would be handy, it does come with some risk. Spatial predicates are much more expensive than other predicates in DBMSs, and that is true when spatial indexes exist and are well tuned. If data sets either don't have spatial indexes, don't have them well tuned, or the DBMS chooses not to use them; well, the performance can be downright miserable.

Given there is a very real chance the performance of a spatial predicate will be quite poor, the software maker takes a beating for something they have little control over. What if a spatial predicate takes 2+ seconds to execute, possibly even 5+ seconds? Every time a user goes to zoom or pan, the screen just hangs for protracted periods of time. What if a map service is made out of that layer, how will that look to people consuming the service?

I can understand the desire for such functionality, I just think there is too much risk involved for Esri. Over time, the risk may diminish and make it more feasible, but I don't see it happening in the near term. That said, if one is working with enterprise DBMSs, then some of this functionality can be implemented at the storage level and brought into ArcGIS.

AlexZhuk

Also, clipping clips labels. Can look awkward.

KumaitMuhammed

While this can be achieved by using a normal definition query and utilize the used RDBMS spatial capabilities, it will however require disabling the standardized queries on the ArcGIS server to be able to spatially filter against a map service.

Providing such function by ArcGIS would be much cleaner.

DWR_BuckEhler

This workaround does not limit the data in the database. It only limits what is drawn in the clipping extent.

JeffThomasILM

I understand performance is a problem, but would it be possible to cache the selection set? E.g., You open the map and the layer with the spatial query grabs an up-to-date snapshot and caches it (or a portion of it, like Feature Cache in ArcMap) and remains static for the rest of the session or until the user chooses to manually refresh. This wouldn't work for constantly changing data, but it seems like it would work fine if the data only needs to be refreshed daily or weekly, and it's being used as a reference more than for active editing. Maybe that's too big of a caveat to bother explaining or implementing, but I think it would be worthwhile.

20200207_155022987_iOS.jpg

JoshuaBixby

I see what you are getting at, and it isn't a bad idea, but it comes with risks as well. Caching addresses some aspects but introduces new issues that either need to be mitigated or documented and communicated to users. As good as documentation may be, we know most users take a cursory look if they bother to look at all. When new functionality performing well depends on numerous fine-print details that few will read or understand, users start complaining "it doesn't work" whether it is warranted or not.

Anonymous User

Working with large eGIS datasets, I often find myself working with only a subset of the data in a feature class, generally in a specific part of the province. Harvest data is tied spatially to clients in "districts" and, depending on the task, I either export/clip the subset of harvest data into a new layer or run a layer query to narrow my search and reduce how much is displayed on the map. The former is an extra step and increases the number of intermediate layers to manage (which I try to keep to a minimum), while the latter is limited to tabular queries based on attribute data. 90% of the time, I use both together because I need to query the data over a specific area (clip) and over a specific time period (layer query).

I would LOVE if we could have both steps incorporated into one, i.e. by having "Select by Location" built into Layer Queries, combining spatial and tabular queries together. That is the idea I am suggesting.

TanuHoque

@Anonymous User

I have two questions for you:

1. is your data stored in an enterprise database or file gdb?

2. >> Harvest data is tied spatially to clients in "districts" <<
are these district polygons coming from a separate Feature Layer?

Anonymous User

Hello!

1. The Harvest data can come from either a gdb or enterprise database, usually eGIS.

2. Yes, the polygons are in a separate enterprise database layer.

In retrospect, I realize that I could easily create a model and ultimately my own tool for this; it is but one of many different scenarios where I have wished that ArcGIS would enable spatial and tabular queries. Sometimes I need to conduct multiple spatial and tabular queries on 2 or more eGIS/gdb layers before I can narrow down my search and begin working. This scenario is the simplest I could think of to describe my idea.

Thanks,

Ellie

TanuHoque

hi @Anonymous User,

As you see, after @KoryKramer merged your post here, we don't have any out of the box capability at this point.

For now, I can give you a workaround and it will ONLY work if:

if both of your layers are coming from the same enterprise database (doesn't need to geodatabase).
- won't work for file geodatabase
both of them are in the same projection (on the fly projection won't work)

Steps to filter out features from layerA using feature(s) from layerB

First get the source FeatureClass name for layerA
- do the same for layerB
  - let's assume they are named DS-A and DS-B respectively
Open layerA's Proeprty page
Switch to Definition Query page
Create a new one
Switch to SQL mode by clicking the SQL toggle button from the upper right corner
Paste one of the following two sql clauses.
- even though the send one looks pretty straight forward, I found it didn't perform well in my quick test.
  - I'm not a SQL guru, therefore I can't explain why 😞
Click OK

The syntax provided below are for SQL Server. The STIntersects() function syntax will be different on PostgreSQL or Oracle or other databases.
SQL#1

objectid in (select DS-A.objectid from DS-A
inner join
(select shape from DS-B
where aField = 'aString_Value' ) a
on a.shape.STIntersects(DS-A.shape) = 1)

SQL#2

exists (select 1 from DS-B a where aField = 'aString_Value' and a.shape.STIntersects(DS-A.shape) = 1)

Here is animation showing how I did that on my side:

Anonymous User

Thank you! I will give it a try at the next available opportunity.

Boyang_Wang

I have a quick question regarding this:

I am trying to display this feature layer in ArcGIS Online Map Viewer. I only want to show the points in a certain county. As the data does not contain a county field, is there any way to do it in definition query other than typing the IDs of individual features?

https://idpgis.ncep.noaa.gov/arcgis/rest/services/NWS_Observations/ahps_riv_gauges/MapServer/0

Thanks,

Boyang

KoryKramer

Hi @Boyang_Wang It sounds like your question is about how to accomplish that working in the ArcGIS Online map viewer. I would recommend posting a question to https://community.esri.com/t5/arcgis-online-questions/bd-p/arcgis-online-questions

Boyang_Wang

@KoryKramer Sorry I did not realize this was a Pro forum. I do want to ask this question in Pro as well. For a map service with query enabled but export disabled, how do I display a subset of features in a given geographical area?

Thanks!

TanuHoque

@Boyang_Wang

Unfortunately this is the supported at this point. Please up vote this idea as it appears important use case for you.

Bud

@JeffThomasILM

Regarding your comment about caching:

I understand performance is a problem, but would it be possible to cache the selection set? E.g., You open the map and the layer with the spatial query grabs an up-to-date snapshot and caches it (or a portion of it, like Feature Cache in ArcMap) and remains static for the rest of the session or until the user chooses to manually refresh. This wouldn't work for constantly changing data, but it seems like it would work fine if the data only needs to be refreshed daily or weekly, and it's being used as a reference more than for active editing. Maybe that's too big of a caveat to bother explaining or implementing, but I think it would be worthwhile.

Alternatively, running a scheduled job to precompute a field or a related table might work in some cases: What levels of the ArcGIS Enterprise stack support scheduled jobs?

Bud

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

[removed and cleaned up in a later comment]

Bud

@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

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

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

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

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.

avillamo

I will like to select by coordinates my layers.

Bud

[removed and cleaned up in a later comment]

Rejoindre la conversation

Connectez-vous pour publier, suivre du contenu, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.