Acquiring ArcGIS-like speed in Postgis
Different approach. Knowing that the pain is in ST_Intersection, and that true/false tests are fast, trying to minimize the amount of geometry passing through the intersection might speed things up. For example, parcels that are totally contained in a jurisdiction don't need to be clipped, but ST_Intersection will still probably go to the trouble of building part of the intersection overlay before realizing it doesn't have to generate any new geometry. So this
INSERT INTO parcel_jurisdictions(parcel_gid,jurisdiction_gid,isect_geom)
SELECT
a.orig_gid AS parcel_gid,
b.orig_gid AS jurisdiction_gid,
st_multi(st_intersection(a.geom,b.geom)) AS geom
FROM
valid_parcels a, valid_jurisdictions b
WHERE
st_intersects(a.geom, b.geom) and not st_within(a.geom, b.geom)
UNION ALL
SELECT
a.orig_gid AS parcel_gid,
b.orig_gid AS jurisdiction_gid,
a.geom AS geom
FROM
valid_parcels a, valid_jurisdictions b
WHERE
st_within(a.geom, b.geom);
Or even terser
INSERT INTO parcel_jurisdictions(parcel_gid,jurisdiction_gid,isect_geom)
SELECT
a.orig_gid AS parcel_gid,
b.orig_gid AS jurisdiction_gid,
CASE
WHEN ST_Within(a.geom,b.geom)
THEN a.geom
ELSE ST_Multi(ST_Intersection(a.geom,b.geom))
END AS geom
FROM valid_parcels a
JOIN valid_jurisdictions b
ON ST_Intersects(a.geom, b.geom)
Might even be faster w/o the UNION.
What would happen if you omit the "st_multi(st_intersection(a.geom,b.geom))"
part?
Doesn't the below query mean the same thing without it? I ran it on the data you provided.
INSERT INTO parcel_jurisdictions(parcel_gid,jurisdiction_gid,isect_geom)
SELECT
a.orig_gid parcel_gid,
b.orig_gid jurisdiction_gid,
a.geom
FROM
valid_parcels a, valid_jurisdictions b
WHERE
st_intersects(a.geom,b.geom);
Configuration
Processor: AMD Athlon II X4 635 2.9 GHz
Memory: 4 GB
Platform: Windows 7 Professional
Potgres Version: 8.4
Postgis Version: "POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER"
Analyze Results
"Nested Loop (cost=0.00..7505.18 rows=217489 width=1580) (actual time=1.994..248405.616 rows=329150 loops=1)"
" Join Filter: _st_intersects(a.geom, b.geom)"
" -> Seq Scan on valid_jurisdictions b (cost=0.00..37.25 rows=525 width=22621) (actual time=0.054..1.732 rows=525 loops=1)"
" -> Index Scan using valid_parcels_index on valid_parcels a (cost=0.00..11.63 rows=2 width=1576) (actual time=0.068..6.423 rows=1366 loops=525)"
" Index Cond: (a.geom && b.geom)"
"Total runtime: 280087.497 ms"