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

[Michael Fuhr]

On Mon, Sep 04, 2006 at 03:12:50PM -0600, Bill Thoen wrote:
> I'm trying to reduce a collection of farm fields to only those that fall 
> within a county border and so I'm trying to figure out the SQL to delete 
> all records in one table whose polygons fall outside a particular 
> polygon in another table. For example, I've got farms (farm_id integer, 
> the_geom geometry) and I've got counties (fips varchar(5), the_geom 
> geometry). I want to remove everything from farms only where not 
> intersects(farms.the_geom, counties.the_geom) and counties.fips='17001'. 

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