[postgis-users] Landtype minus parcels ?

[Brian Hamlin]

Hi All-

   maybe I am missing an easier solution.. I would like to subtract  
the sum of all parcels in a county, from a "landtypes" table for the  
same county.
Landtypes are non-overlapping areas with a category..  Although this  
is straightforward to describe, I am writing things that take a long  
time, and are not simple.

note that I use python as much or more than SQL. Most jobs are in  
python using psycopg2 to execute the SQL. I have no problem with  
looping in python to get a desired result, but if there is something  
straightforward in SQL than of course the database can do a better  
job of optimizing..

The best I have so far is as follows:

   in python, loop through all landtype records
     for that landtype record, find all parcels that intersect it
        ST_UNION() those parcels
        subtract the ST_UNION'd result from that landtype record, and  
store it

this is how I wrote it -- suggestions and improvements requested
(yes I left the DISTINCT ON in there.. I was getting multiple,  
identical rows back instead of one row ??)

-----------
SELECT distinct on (tgeom)
      st_difference(
        l.wkb_geometry,

        (select st_union(p.wkb_geometry )
        from
          big_landtypes l,
          small_parcels p
        where st_intersects(
          l.wkb_geometry, p.wkb_geometry) AND
          l.pkey = 1)
        ) tgeom
      FROM
        big_landtypes l,
        small_parcels p
      WHERE
        l.pkey = 1
      ORDER BY
        tgeom;
---
  Unique  (cost=3044213.34..3047941.15 rows=1 width=9069) (actual  
time=190578.880..190865.979 rows=1 loops=1)
    InitPlan 1 (returns $1)
      ->  Aggregate  (cost=29.92..29.93 rows=1 width=3203) (actual  
time=5.276..5.277 rows=1 loops=1)
            ->  Nested Loop  (cost=0.00..29.83 rows=35 width=3203)  
(actual time=0.765..2.317 rows=11 loops=1)
                  Join Filter: _st_intersects(l.wkb_geometry,  
p.wkb_geometry)
                  ->  Index Scan using big_landtypes_pkey on  
big_landtypes l  (cost=0.00..8.27 rows=1 width=9069) (actual  
time=0.004..0.006 rows=1 loops=1)
                        Index Cond: (pkey = 1)
                  ->  Index Scan using small_parcels_geom_idx on  
small_parcels p  (cost=0.00..20.51 rows=4 width=3203) (actual  
time=0.075..0.649 rows=55 loops=1)
                        Index Cond: (l.wkb_geometry && wkb_geometry)
    ->  Sort  (cost=3044183.42..3046047.32 rows=745562 width=9069)  
(actual time=190578.878..190616.465 rows=745808 loops=1)
          Sort Key: (st_difference(l.wkb_geometry, $1))
          Sort Method: quicksort  Memory: 59536kB
          ->  Nested Loop  (cost=0.00..74035.41 rows=745562  
width=9069) (actual time=5.732..189597.837 rows=745808 loops=1)
                ->  Index Scan using big_landtypes_pkey on  
big_landtypes l  (cost=0.00..8.27 rows=1 width=9069) (actual  
time=0.011..0.015 rows=1 loops=1)
                      Index Cond: (pkey = 1)
                ->  Seq Scan on small_parcels p  (cost=0.00..64707.62  
rows=745562 width=0) (actual time=0.004..491.980 rows=745808 loops=1)