ST_INTERSECTS as a query def

3708
8
05-31-2011 04:49 AM
AnthonyFarndon
Occasional Contributor
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?
0 Kudos
8 Replies
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
AnthonyFarndon
Occasional Contributor
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
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
AnthonyFarndon
Occasional Contributor
Yes, I think I need to spend a bit more time with sql....
0 Kudos
ChrisStrobl
New Contributor II
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.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
feralcatcolonist
New Contributor II

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?

Reports that say that something hasn't happened are always interesting to me, because as we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns—the ones we don't know we don't know. And if one looks throughout the history of our country and other free countries, it is the latter category that tends to be the difficult ones. -----Excuse me, but is this an unknown unknown?-----
0 Kudos
VinceAngelo
Esri Esteemed Contributor

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

0 Kudos