1

I'm trying to write a query in SQL Server using 1 set of points and 2 sets of polygons. The polygons overlap, and I only want to select the points that fall inside of 1 polygon but not the other.

I want to duplicate this behavior in ArcGIS for Desktop.

In ArcGIS:

  1. New Selection by Location Points that Intersect Polygon layer 1
  2. Remove from the Current Selection by Location Points that Intersect Polygon layer 2

Attempt in SQL Server:

SELECT DISTINCT --MY FIELDS--

FROM

Points, Polygons1, Polygons2

WHERE

Polygons1.Shape.STIntersects(Points.Shape) = 1
AND Polygons2.Shape.STDisjoint(Points.Shape) = 1

Am I missing something? I think the logic is sound in the SQL query, but it's not working.

  • Your query will not work because the query compares all rows to all rows which means that when the point intersect 1 point it also disjoint at least most of the polygons in table 2. So you need to work with a subquery in some way. There are many options. – Nicklas Avén Oct 14 '14 at 6:03
1

I'd try

   SELECT DISTINCT --MY FIELDS--

    FROM 

    Points

    WHERE 

    EXISTS (SELECT 1 FROM Polygons1 WHERE Polygons1.Shape.STIntersects(Points.Shape) = 1)

    AND NOT EXISTS (SELECT 1 FROM Polygons2 WHERE Polygons2.Shape.STIntersects(Points.Shape) = 1)
0

The solution in PostGIS would be something like below that might lead you in the right direction also in SQL Server.

I don't know if SQL Server have ST_Difference http://www.postgis.org/docs/ST_Difference.html

SELECT point FROM pointTable INNER JOIN (SELECT COALESCE(ST_Difference(a.polygon,b.polygon),a.polygon) diffPolygon FROM polygonTableA as a LEFT JOIN polygonTableB as b on ST_Intersects(a.polygon,b.polygon)) poly ON ST_Intersects(pointTable.point,poly.diffPolygon)

The left join and coalesce is to get the polygons from polygonTableA that doesn't intersect at alm with the other polygon table.

Haven't tested it though but should give the idea

0

Try intersecting the two polygons first using STIntersection as a sub-query, then run an STIntersects on the points against the Intersection of the two polygons.

Something like this:

    select points.geom
, points.OtherFields

 from 
    (
    select 
    p1.geom.STIntersection(p2.geom) as geom
    from
       dbo.polygonLayer1 as p1
    , dbo.polygonLayer2 as p2

     ) as polyInt --gets you the intersection of the 2 polygon inputs as a sub query

    , dbo.pointsLayer as points

    where points.geom.STIntersects(polyInt.geom) = 1

Let me know if that works for you.

  • That doesn't seem like it would be performant if the polygon tables are very large, especially as no index will be available on the polyInt result set. – Russell at ISC Oct 14 '14 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.