Dylan, For question 1) Sorry not quite sure what you are asking here. Below is a guess
SELECT ST_Difference(A.the_geom, B.the_geom) FROM A INNER JOIN B ON ST_Overlaps(A.the_geom,B.the_geom) UNION ALL SELECT A.the_geom FROM A LEFT JOIN B ON ST_Intersections(A.the_geom,B.the_geom) WHERE B.gid IS NULL where A is your table A and B is your country boundary. That will give you the portion of A not inside an intersecting Country boundary. Although I think you will need to do a ST_Union on B by collecting all the country boundaries that overlap a specific polygon (for the overlaps part if your As can straddle multiple countries. For question 2) The ST_Union aggregate form is more commonly used than the non-aggregate you have below. For example if you want to combine all polygons that fall inside of a country boundary into a single MULTIPOLYGON, you would use ST_Union or ST_Collect. Something of the form SELECT b.country_name, ST_Union(a.the_geom) as the_geom FROM poly a INNER JOIN country b ON ST_Intersects(a.the_geom, b.the_geom) GROUP BY b.country_name; ST_Collect is generally faster, but will not dissolve and may result in geometry collections. Hope that helps, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dylan Lorimer Sent: Friday, April 18, 2008 5:47 PM To: PostGIS Users Discussion Subject: [postgis-users] 2 Questions Sorry for the barrage of questions lately! But suddenly PostGIS is becoming extremely useful to my day-to-day activities. So congrats on a fantastic product that is letting me (thus far) skip on purchasing any ESRI software. Questions: 1) Given N polygons in a table called A, and another table of country borders, how do I generate a set of polygons that represent where the N polygons of table A do NOT exist? Sort of a mask of the polygons in A clipped by a country border. Actually, I know how to clip by borders, so I'm really just looking for how to find the area not occupied by polygons in A. 2) I know this one has been beaten to death on this list, but yet in the archives I still can't exactly find what I want. I've got absurd amounts of polygons, millions. But for the sake of argument let's just say I have a lot. I've also got country borders. I can easily find which polys fall within certain countries, but what I want to do is then generalize those polys that overlap into single polygons. It appears as if ST_Union is my candidate, but I can't see to get it working. Do I need to join against the same table? Here's what I've so far come up with but that I don't think is correct: select ST_Union(a.the_geom,b.the_geom) as the_geom from the_table a, the_table b where ST_Intersects(a.the_geom, b.the_geom); Any thoughts on this would be helpful. Cheers! -dylan _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
