[postgis-users] 2 Questions

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users