[postgis-users] RE: PostGIS cookie cutter

[Kevin Neufeld]

First Last wrote:

>Thanks for the inputs Kevin. I'll give it a try when I come back tomorrow at the office. Before I went home, I managed to make a snippet work but I don't know how it fares (with regard to speed and reliability) to yours. Anyways, here it is:
>
>SELECT bndry.id, (intersection(bndry.the_geom,cutter.the_geom))INTO newtable FROM bndry, cutter where intersects(bndry.the_geom,cutter.the_geom) and cutter.id=1;
>
>It takes a couple of seconds to finish though. I might need to use an index (I supposed that's the && you used eh?).
>  
>
That's exactly right. To speed up your query, I'd highly recommend 
creating spatial indexes on bndry.the_geom and cutter.the_geom. This can 
be done quickly with a few SQL statements:
CREATE INDEX bndry_the_geom_idx ON bndry USING GIST (the_geom);
CREATE INDEX cutter_the_geom_idx ON cutter USING GIST (the_geom);

Also, remember to analyze the index afterwards, or the query planner 
won't be accurate.
ANALYZE bndry;
ANALYZE cutter;

Cheers,
Kevin

-- 
Kevin Neufeld,
Refractions Research Inc.,
kneufeld at refractions.net
Phone: (250) 383-3022 
Fax:   (250) 383-2140