[postgis-users] How to Delete Records Outside a Polygon?

[Bill Thoen]

Thnaks! I should have remembered how to do a subquery like that, but
sometimes a little help is just what I need. However, one clause you
include is something I wouldn't have expected, and is particularly useful
to me since my farms table is very large.

So when you perform the join by:
JOIN counties AS c ON c.the_geom && f.the_geom
    AND Intersects(c.the_geom, f.the_geom)

does the SQL optimizer really organize things so that it does all the
comparisions by bounding boxes first before attempting the polygon
intersection comparisions? Or do you have to write the '&&' comparision
first to make this happen?



On Mon, Sep 04, 2006 at 06:46:19PM -0600, Michael Fuhr wrote:
> 
> Something like the following should work.  Execute the subquery by
> itself before doing the delete to make sure it selects the right
> farms; then start a transaction, do the delete, make a few queries
> to verify that the delete worked correctly, then commit or roll
> back the transaction.
> 
> DELETE FROM farms
> WHERE farm_id NOT IN (
>   SELECT f.farm_id
>   FROM farms AS f
>   JOIN counties AS c ON c.the_geom && f.the_geom
> 		    AND Intersects(c.the_geom, f.the_geom)
>   WHERE c.fips = '17001'
> );
> 
> The above is standard SQL; the following uses the nonstandard
> PostgreSQL USING clause:
> 
> DELETE FROM farms
> USING counties
> WHERE counties.fips = '17001'
>   AND NOT (farms.the_geom && counties.the_geom
> 	   AND Intersects(farms.the_geom, counties.the_geom));
> 
> -- 
> Michael Fuhr
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>