topic Using a sub-query instead of a variable in STIntersection? in Data Management Questions

Using a sub-query instead of a variable in STIntersection?

ArnonTsairi — Sat, 11 Dec 2021 19:35:59 GMT

I'm executing the query bellow to intersect a soil layer with a polygon (a parcel) with no problem.
I would like to replace the @parcel in the select statement with something like a sub-query. I was naive to think that putting a SELECT Shape from another layer would do the trick, but it fails.
Any ideas?
Thanks, Arnon

DECLARE @parcel geometry 
        =geometry::STGeomFromText('POLYGON ((170018 1068876, <more coordinates>))',1);
SELECT Shape.STIntersection(@parcel)
FROM soils
WHERE Shape.STIntersects(@parcel)=1

Re: Using a sub-query instead of a variable in STIntersection?

VinceAngelo — Wed, 06 Apr 2011 11:13:45 GMT

The OGC intent was certainly to permit subqueries, but there's still the SRID issue with
which to contend. A Microsoft forum would probably be a better place to find experts on
GEOMETRY behavior in SQL-Server 2008.

ArcSDE just uses an envelope search as a filter and then uses Esri's libraries for topological
comparison (whether it's GEOMETRY/GEOGRAPHY, ST_GEOMETRY, SDO_GEOMETRY,
PG_GEOMETRY , SDEBINARY, or SDELOB storage). Shape library comparisons are done at
the precision specified by the layer coordinate reference (inverse of xyscale).

- V

Re: Using a sub-query instead of a variable in STIntersection?

ArnonTsairi — Tue, 12 Apr 2011 10:48:18 GMT

Eventually I abandoned (for now) the subquery idea. Instead I'm using a join with STIntersects in the join criteria. This makes the shapes of both layers available for intersection.
Basic, I guess...