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?).

As a follow up question, how can i give the apache user (i.e., www-data) access to the pgsql2shp command? I'm asking this because I'd be doing everything from a php page. The objective is to present a webpage to a user where he can download a shapefile from a given list. There will be an option to trim it to his desired boundary, after that, the php page will convert the output table into a shapefile. I just realized that "www-data" doesn't have access to pgsql2shp but I'm quite hesitant to connect to the database as another user.  


--- Kevin Neufeld <kneufeld@refractions.net> wrote:

From: Kevin Neufeld <kneufeld@refractions.net>
Date: Mon, 16 Jan 2006 16:04:16 -0800
To: y2kdis@atenista.net, postgis-users@postgis.refractions.net
Subject: RE: PostGIS cookie cutter

Hi,

First Last wrote:

> i'm a novice PostGIS user, can anybody help me do a cookie-cutter 
> technique in PostGIS? basically, i have a polygon table of political 
> boundary and i want to extract all the polygons that lie within the 
> cookie cutter (which can be a single polygon, or another polygon 
> table). it seems the intersection command works only when the 
> operation is between two polygons but not when a collection of 
> polygons (e.g., table) is involved. by the way, i'm assuming the 
> intersection command is what you use for cookie-cutting?


CREATE TABLE boundary_cutter_relationships AS
  SELECT a.boundary_id,
         b.cutter_id
  FROM   political boundary a,
         cookie_cutter b
  WHERE  a.the_geom && b.the_geom
  AND    intersects(a.the_geom, b.the_geom);

This will create a relationship table between your two polygon tables 
... provided your boundaries and "cookie cutter" polygons have unique ids.
Now, if you wanted to know what boundaries intersect your cutter of 
interest ...
SELECT boundary_id
FROM boundary_cutter_relationships
WHERE cutter_id = ??;

> on the same note, how do i combine two tables of the same geometry type? 

Create a new table with a geometry column.
Insert into the new table values from both tables.

OR

SELECT attribute1, attribute2, the_geom
FROM table1
UNION
SELECT attribute1, attribute2, the_geom
FROM table2;

> also can they be added together even if they don't have the same 
> attribute columns (except for the geometry type). 

No. (http://www.postgresql.org/docs/8.1/static/queries-union.html)

> i'm coming from a ESRI/shapefile background so please bear with me.
>
> any help will be appreciated. thank you.
>
>
>
> _____________________________________________________________
> Check out Atenista.Net (www.atenista.net)- new design, regular content 
> and additional services!


Hope this helps.
Cheers,
Kevin

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



_____________________________________________________________
Check out Atenista.Net (www.atenista.net)- new design, regular content and additional services!

