CODEWITHSUNDEEP
sqlsql-serverspatial
Dnl_
Dnl_

Reputation: 65

Multiple points on multiple polygons SQL Server Spatial

I am a bit newbie on SQL server spatial.

I've been running a job on SQL server using 2 spatial databases "points"(>1m entries) and "polygons"(>800 entries)

what I want to do is simply create a Boolean field that tells me if a point falls in a polygon. I have tried a few things but nothing conclusive or productive. The Query I have tried was:

SELECT
    points_id,
    CASE
        WHEN p1.GEOM.STWithin((p2.GEOM))=1 
        THEN 'yes' 
        ELSE 'no'
    END as results
FROM [pointsdb] p1, [polydb] p2

the results I get are basically all the possible combinations of 1 point to all polygons and vice versa. I tried to use a group by but the effiency gone down a lot.

Could you help me on that? cheers

Upvotes: 0

Views: 317

Answers (1)

GMB
GMB

Reputation: 222482

Do not cross join the tables. Instead, you can use an exists condition with correlated subquery, as follows:

SELECT
    points_id,
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM [polydb] p2 
            WHERE p1.GEOM.STWithin((p2.GEOM))=1 
        ) 
        THEN 'yes' 
        ELSE 'no'
    END as results
FROM [pointsdb] p1

Upvotes: 1

Related Questions