Community
Select to view content in your preferred language

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

306
0
01-02-2024 01:12 PM
Status: Open
Labels (1)
Labels
Bud
by
Honored Contributor

ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB; SDE.ST_Geometry:

Background:

In the Select Layer By Location (Data Management) docs, the Relationship Parameter — Intersect (DBMS) section says:

For the Relationship parameter, the Intersect (DBMS) option may provide better performance than the Intersect option when using enterprise geodatabase data; however, this option is only supported under specific conditions. If all conditions are met, the spatial operation will be performed in the enterprise geodatabase database management system (DBMS) rather than on the client.

...The user connecting to the geodatabase must have privileges to create a view in the database where the feature classes are stored.

In other words, the tool creates a database view.

Bud_1-1704229549155.png

Bud_0-1704229531660.png

Bud_2-1704229563608.png

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

The view is called SELECTIONOUTPUTNAME.

Idea:

It seems strange to me that we'd want to create a database view.

  1. It's awkward to create a persistent database object for ad hoc analysis like this.
  2. What if the user doesn't have CREATE VIEW privileges or doesn't want to create a view?
  3. The user might have CREATE VIEW privileges, but the user may not have GRANT privileges for creating a view on the feature classes. The feature classes might be owned by other owners/users.
  4. There could be issues if multiple users use the same tool in the same EGDB workspace -- the view would get overridden by the other user.

Why not use a query layer (or some other temporary query mechanism) instead of creating a database view? A query layer would solve the issues mentioned above.


Related: