[postgis-users] Spatial query Help: points not in set of polygons

[Stephen Woodbridge]

Sorry, replying to my self again!

Looks like the LEFT JOIN is working just fine. I was tripped up by the 
fact that I have previously NULL'd some of the polygons that were 
invalid and so all their respective points were listed as would be 
expected. And secondly, by the fact that there are a lot more results 
that I was expecting, but spot checking a few of the files in 
ArcExplorer verified the results.

And it looks like this query does not support geomcollection in the 
distance function, but only fails with some obsure message in the 
message window of pgadmin3, but if I change the collect() to geomunion() 
it works, but is much slower than the left join as would be expected.

>> select file, id, name
>>   from points
>>  where distance(the_geom, (select collect(the_geom) from polygons)) > 0.0';

-Steve

Stephen Woodbridge wrote:
> Regina,
> 
> I think I spoke too soon. On closer inspection of the results it looks 
> like the query is returning all the points in the points table.
> 
> select a.file, a.id, a.name
>   from points a LEFT JOIN polygons b
>     ON (a.the_geom && b.the_geom
>         and distance(a.the_geom, b.the_geom) = 0.0)
>  where b.the_geom IS NULL
> 
> I think this has a logic issue, IF b.the_geom is NULL then the ON clause 
> must also be NULL. Does that work?
> 
> I also tried this query, which returned no points:
> 
> select file, id, name
>   from points
>  where distance(the_geom, (select collect(the_geom) from polygons)) > 0.0';
> 
> Any thoughts why this does not appear to work?
> 
> I'm going to try your other query and see if I get different results 
> with that.
> 
> Thanks,
>   -Steve
> 
> Obe, Regina wrote:
>> SELECT a.*
>> FROM point a LEFT JOIN polygons b
>>     ON (a.the_geom && b.the_geom
>>     and ST_distance(a.the_geom, b.the_geom) = 0.0)
>> WHERE b.the_geom IS NULL
>>
>> or
>>
>> SELECT a.*
>> FROM point a LEFT JOIN polygons b
>>     ON ST_Within(a.the_geom, b.the_geom)
>> WHERE b.the_geom IS NULL
>>
>> I think the second one should be more efficient, but I haven't done any
>> benchmarks.
>>
>> Hope that helps,
>> Regina
>>
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> Stephen Woodbridge
>> Sent: Wednesday, January 23, 2008 10:27 PM
>> To: PostGIS Users Discussion
>> Subject: [postgis-users] Spatial query Help: points not in set of
>> polygons
>>
>> Hi all,
>>
>> I'm drawing a blank on setting up a query for this.
>>
>> I have a tables points and a table of polygons. I need to find all the 
>> points that are not in any of the polygons.
>>
>> select a.*
>>    from points a, polygons b
>>   where a.the_geom && b.the_geom
>>     and distance(a.the_geom, b.the_geom) > 0.0;
>>
>> The problem with this is that if a point is in polygon A it will have 
>> a distance to polygon B, so this in no good.
>>
>> I thought of doing something like:
>>
>> select * from points
>>   where distance(the_geom, union((select the_geom from polygons))) >
>> 0.0;
>>
>> or
>>
>> select * from points
>>   where distance(the_geom, collect((select the_geom from polygons))) >
>> 0.0;
>>
>> So is there a better way to do this. Seems like there should be. If 
>> not which of these would you suggest.
>>
>> Thanks,
>>    -Steve
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> -----------------------------------------
>> The substance of this message, including any attachments, may be
>> confidential, legally privileged and/or exempt from disclosure
>> pursuant to Massachusetts law. It is intended
>> solely for the addressee. If you received this in error, please
>> contact the sender and delete the material from any computer.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users