topic ST_INTERSECTS as a query def in Data Management Questions

ST_INTERSECTS as a query def

AnthonyFarndon — Sun, 12 Dec 2021 02:35:16 GMT

Hi

Not too sure which forum to post this question in, so had a stab in here.....

I have two featureclasses in a 10 geodatabase in Oracle, stored with the defailt SDO_GEOMETRY, lets say they are called "MYSCHEMA.LAYER1" and "MYSCHEMA.LAYER2"

I would like to to put a query def in ArcMap on layer 1 to only show those features that intersect layer 2.

I tried a couple things like:

sde.st_intersects(shape, layer2.shape)
or
sde.st_intersects(layer1.shape, layer2.shape)

but get invalid syntax. Have had a look around the web help but it is a little light and concentrates on sql statements as if run from a sql client i.e. with all the select from where etc

Any ideas?

Re: ST_INTERSECTS as a query def

VinceAngelo — Sun, 12 Dec 2021 02:35:19 GMT

You can't use Esri's ST_GEOMETRY operators on Oracle's SDO_GEOMETRY datatype.
You need to either use SDE.ST_GEOMETRY storage (which is the usual default for
ArcSDE 10) or use Oracle's operators.

While it's possible to use query definition to establish this sort of query, it would be
far easier to get the SQL right in a view first, then either register the view or port
it to ArcGIS layer definition, or use Query Layers to access the view (or SQL).

Any way you cook it, the SQL would have to test the result of the spatial operator
function, like this SELECT I created for a functionality test at a client site with
corrupted permissions:

 
SELECT objectid 
FROM poly_stg t
WHERE sde.ST_Intersects(
      sde.ST_GeomFromText('POLYGON ((0 0,5 0,5 5,0 5,0 0))',2),
      t.shape) = 1;

In your case you'd need to refer to both tables in a single statement as in this
documentation example:

 
SELECT sa.id SA_ID, hs.id HS_ID
FROM SENSITIVE_AREAS sa, HAZARDOUS_SITES hs
WHERE sde.st_intersects (sde.st_buffer (hs.location, .1), sa.zone) = 1
ORDER BY sa.id;

Note that both tables must share the same SRID, or the comparison operator will
always fail silently.

- V

Re: ST_INTERSECTS as a query def

AnthonyFarndon — Tue, 31 May 2011 15:28:49 GMT

Thanks for reply.

Sorry my bad, the featureclasses have the geom set to default which is ST_GEOMETRY not SDO_(and all are in same SRID)

Had missed out the = 1 in my sql, however as said before am afraid the SELECT, FROM, WHERE examples don't really help much for a layer def query.

Trying

sde.st_intersects(shape, layer2.shape) = 1

throws up a different error now, about an invalid identifier for layer2

I tried

SELECT * FROM MYLAYER1 mla, MYLAYER2 mlb WHERE sde.st_intersects(mla.shape, mlb.shape) = 1

throws up invalid operator error

I have tried other ST operations as a def query which *DO* work e.g.

sde.st_area(shape) >= 10000

so the problem is correct syntax for a def query layer

Creating views is not an option so I'll try the query layer approach in the absence of being able to do this via a layer query def.

Thanks, Tony

Re: ST_INTERSECTS as a query def

VinceAngelo — Tue, 31 May 2011 16:35:09 GMT

I suggested you get comfortable with the SQL first because you need to *really* understand the
query before using the ArcGIS GUI, to intuit what needs to be placed where. The ArcSDE API is
quite clear on SE_QUERYINFO object components, but the Layer Properties form has three tabs
that the components of the query reside -- Joins & Relates, Fields, and Definition Query. The task
is to place the additional table in Joins, the join constraint in Definition Query, and restrict the
result to just one spatial column in fields, but it doesn't look like GUI will give you the flexibility
to use a SQL join constraint (ArcObjects might).

A view is your best bet, but with that off the table, Definition Query is probably next best.
It may be challenging to write your query so that the join will perform well when the spatial
constraint is applied for rendering.

Keep in mind that a "*" column list is pure poison when the result can only contain one
geometry column -- explicitly referring to the columns with table alias will help document
your procedure.

- V

Re: ST_INTERSECTS as a query def

AnthonyFarndon — Tue, 31 May 2011 19:03:34 GMT

Yes, I think I need to spend a bit more time with sql....

Re: ST_INTERSECTS as a query def

ChrisStrobl — Thu, 31 Jan 2013 16:20:53 GMT

I, too, would like to perform a similar operation using a Query Layer against an Oracle SDE instance and ST_Geometry. I want to create a selection set of one feature class that intersects the features of another feature class. The syntax in the documenation has not been helpful. It says: sde.st_intersects (g1 sde.st_geometry, g2 sde.st_geometry). What does g1 and g2 represent?

Here is the statement I have been trying to use with no luck so far:

select * from S_WO_OPS_EGIS.FHP_AERIALSURVEY where sde.st_intersects (S_WO_OPS_EGIS.FHP_AERIALSURVEY, S_WO_OPS_EGIS.ADMINISTRATIVEFOREST) = 1

I'm missing something, but I don't know what. I'm not very SQL saavy and could use some hand holding here.

Thank you.

Re: ST_INTERSECTS as a query def

VinceAngelo — Thu, 31 Jan 2013 21:39:19 GMT

Look at the examples above -- you must specify both tables after the FROM,
and the specific columns in the operator function.

You must also specify all the returned columns (vice '*'), since only one
geometry can be returned in the result.

Please put all SQL in CODE blocks (the '#' pick in the WYSIWYG editor UI)

- V

Re: ST_INTERSECTS as a query def

feralcatcolonist_old — Thu, 18 Feb 2021 15:55:48 GMT

Hey @VinceAngelo, can a spatial SQL query be written against a file geodatabase using a definition query within ArcGIS Pro (obviously not in clause mode)? What would the syntax look like?

Re: ST_INTERSECTS as a query def

VinceAngelo — Mon, 22 Feb 2021 00:21:04 GMT

There are no spatial operators in the basic SQL supported on file geodatabase, so no, that is not an option. It would be an option in a true database source.

- V