<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: [postgis-users] Union of 7 datasets</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<P><FONT size=2>Andreas, </FONT></P>
<P><FONT size=2>You would use the SQL UNION predicate like shown below (actually
slight correction - it is speedier to use UNION ALL especially when you know
there will not be dupiclates since it saves the processing of sorting to get a
distinct<SPAN class=421335016-28082007> UNION does an implicit
distinct</SPAN>) - so I have corrected below. </FONT></P>
<P><FONT size=2>- - I happened to insert comments in between<SPAN
class=421335016-28082007> which may have confused you</SPAN>, but you should be
able to run the whole thing as one statement or if you prefer because of speed
issues run each insert separately.<BR><BR>So would be<BR>INSERT INTO
sometable(field1, field2,field3,field4, newgeom)<BR> SELECT udo.field1,
udo.field2, kai.field3, kai.field4, geomunion(udo.the_geom, kai.the_geom) AS
newgeom<BR> FROM udo INNER JOIN kai ON (udo.the_geom &&
kai.the_geom AND intersects(udo.the_geom, kai.the_geom))<BR> UNION
ALL<BR> -- the second select gives you udos that have no kais - your
15<BR> SELECT udo.field1, udo.field2, NULL As field3, NULL As field4,
udo.the_geom AS newgeom<BR> FROM udo LEFT JOIN kai ON (udo.the_geom
&& kai.the_geom AND intersects(udo.the_geom, kai.the_geom))<BR>
WHERE kai.the_geom IS NULL<BR> UNION ALL<BR> -- and the 3rd gives you
kais that have no udos. - your 40<BR> SELECT NULL As field1, null As
field2, kai.field3, kai.field4, kai.the_geom AS newgeom<BR> FROM kai LEFT
JOIN udo ON (udo.the_geom && kai.the_geom AND intersects(udo.the_geom,
kai.the_geom))<BR> WHERE udo.the_geom IS NULL;</FONT></P>
<P><FONT size=2></FONT> </P>
<P><FONT size=2>----If FULL JOIN were to work (which in theory it should, but
doesn't seem to with Postgis functions (HINT HINT: would be nice if
that <SPAN class=421335016-28082007>worked</SPAN> and can be easily fixed
(but sadly I think the issue is deeper than Postgis and Geos) - you could write
the above <SPAN class=421335016-28082007>much simpler as</SPAN>)</FONT></P>
<P><FONT size=2>INSERT INTO sometable(field1, field2,field3,field4,
newgeom)<BR> SELECT udo.field1, udo.field2, kai.field3, kai.field4,
COALESCE(geomunion(udo.the_geom, kai.the_geom), udo.the_geom,
kai.the_geom) AS newgeom<BR> FROM udo FULL JOIN kai ON (udo.the_geom
&& kai.the_geom AND intersects(udo.the_geom,
kai.the_geom))<BR></FONT></P>
<P><FONT size=2>Now if you have a lot of these and the tables are very similar
in nature and named in a predictable way then the way I usually handle
it is to write a pgsql function that dynamically generates the SQL statement to
execute either via a FOR loop and then executes <SPAN
class=421335016-28082007>the built SQL or set of SQL statements </SPAN>or you
could do a similar thing in some scripted language like perl or php.</FONT></P>
<P><FONT size=2>I take it SQL and pgsql and all that is fairly new to you so it
might be worthwhile (even though its a lot of typing) to do it the long cut and
paste way if nothing more than an exercise to get a feel of how this all works
and visualize the patterns at play.</FONT></P>
<P><FONT size=2>I don't get <SPAN class=421335016-28082007>the</SPAN> sense
that I comprehend your full problem. Unfortunately I don't have any
experience with ArcGIS/ArcView ways of doing things, so I'm not quite sure if
there is an equivalent way in PostGIS/PostgreSQL world of doing the same
kind of thing and what exactly that thing is you are doing in ArcGIS.</FONT></P>
<P><FONT size=2>Union has 3 meanings in PostGIS/PostgreSQL (actually
stuff your favorite spatial/DB here - all non-trivial spatial relational
dbs behave more or less the same) </FONT></P>
<P><FONT size=2>1) unioning of record sets (standard ANSI SQL <SPAN
class=421335016-28082007>UNION/UNION ALL</SPAN>) - which is simply <SPAN
class=421335016-28082007>a </SPAN>way of stringing together a bunch of selects
into a single <SPAN class=421335016-28082007>result </SPAN>set as shown
above</FONT></P>
<P><FONT size=2>2) unioning of 2 geometry fields like shown
above with geomunion</FONT></P>
<P><FONT size=2>3) Aggregate geomunion - aggregate variant of the above
geomunion function that groups and unions a whole setof geometries together but
requires you are grouping by some field or set of fields).</FONT><BR></P>
<P>I must also mention there is collect (non-aggregate and aggregate function)
which often times is just as effective as the geomunion and in general much
faster processor wise.</P>
<P><SPAN class=421335016-28082007></SPAN><FONT face=Arial><FONT size=2>H<SPAN
class=421335016-28082007>ope that helps,</SPAN></FONT></FONT></P>
<P><FONT><FONT size=2><SPAN class=421335016-28082007></SPAN></FONT></FONT><SPAN
class=421335016-28082007></SPAN><FONT face=Arial><FONT size=2>R<SPAN
class=421335016-28082007>egina</SPAN></FONT></FONT><BR><BR><FONT
size=2>-----Original Message-----<BR>From:
postgis-users-bounces@postgis.refractions.net [</FONT><A
href="mailto:postgis-users-bounces@postgis.refractions.net"><FONT
size=2>mailto:postgis-users-bounces@postgis.refractions.net</FONT></A><FONT
size=2>] On Behalf Of Andreas Laggner<BR>Sent: Tuesday, August 28, 2007 9:13
AM<BR>To: PostGIS Users Discussion<BR>Subject: Re: [postgis-users] Union of 7
datasets<BR><BR>Obe, Regina schrieb:<BR>> Sounds like you would have to
go with a full join type thing with workaround I described below (last
example). So If I understand you correctly then something like this
- <BR>><BR>> --the first select gives you those records in both
tables your 25 udokai<BR>> SELECT udo.field1, udo.field2, kai.field3,
kai.field4, geomunion(udo.the_geom, kai.the_geom) AS newgeom<BR>> FROM
udo INNER JOIN kai ON (udo.the_geom && kai.the_geom AND
intersects(udo.the_geom, kai.the_geom))<BR>> UNION<BR>> -- the
second select gives you udos that have no kais - your 15<BR>> SELECT
udo.field1, udo.field2, NULL As field3, NULL As field4, udo.the_geom AS
newgeom<BR>> FROM udo LEFT JOIN kai ON (udo.the_geom &&
kai.the_geom AND intersects(udo.the_geom, kai.the_geom))<BR>> WHERE
kai.the_geom IS NULL<BR>> UNION<BR>> -- and the 3rd gives you kais
that have no udos. - your 40<BR>> SELECT nul As field1, null As field2,
kai.field3, kai.field4, kai.the_geom AS newgeom<BR>> FROM kai LEFT JOIN
udo ON (udo.the_geom && kai.the_geom AND intersects(udo.the_geom,
kai.the_geom))<BR>> WHERE udo.the_geom IS NULL<BR>><BR>> Unioned
together you should get<BR>> 25 + 15 + 40 = ? 80<BR>><BR>> Hope that
helps,<BR>> Regina<BR>><BR>> <BR>UFF - that seems to be a
complex question, a comprehensive operation and<BR>a lot to type too!!! Thanks a
million - i would have spent many time to<BR>find out this query by myself!
Ok..... how can i perform your "UNION": I<BR>would just insert my selects one
after another in one new table - will<BR>that work well?<BR>I want to aggregate
around 20 datasets this way!? Do you know a method<BR>to operate with more
datasets (for example 7) in a effektiv manner and<BR>not to do this three
selects 6 times?<BR>AND: Am i totally wrong with my aims? It seems to me that is
an exotic<BR>think to do with PostGis, but our projects/problems require to
aggregate<BR>datasets in almost all cases (around 90%) and that is exactly what
one<BR>of the basic functions "Union" in ArcView already carried out ten
years<BR>ago. So i would exspect there is a function to perform this
operation<BR>easier!? .....i just
wonder.....<BR><BR><BR>cheers Andreas<BR><BR>>
-----Original Message-----<BR>> From:
postgis-users-bounces@postgis.refractions.net [</FONT><A
href="mailto:postgis-users-bounces@postgis.refractions.net"><FONT
size=2>mailto:postgis-users-bounces@postgis.refractions.net</FONT></A><FONT
size=2>] On Behalf Of Andreas Laggner<BR>> Sent: Tuesday, August 28, 2007
7:28 AM<BR>> To: PostGIS Users Discussion<BR>> Subject: Re:
[postgis-users] Union of 7 datasets<BR>><BR>> Obe, Regina
schrieb:<BR>> <BR>>> BASIC TRICK: If you want to get
all records with no matching including those that match - put what you would
normally put in your WHERE clause in the JOIN clause and use a LEFT
JOIN.<BR>>> <BR>> ok - it seems to me i need
some coaching......Yes, I want to get all<BR>> records with no matching
including those that match, but i also want to<BR>> dissect the polygons that
matches. I will try to explain again just to<BR>> be sure you understood my
aims: I have 20 polygons in udo and 50 in kai.<BR>> 10 from kai are
intersecting 5 from udo to 25 new polygons i call udokai<BR>> (because they
have attributes from udo AND kai). My result should have:<BR>> 15 polygons
with attributes only from udo, 40 polygons with attributes<BR>> only from kai
AND 25 with attributes from udo and kai! In most cases i<BR>> want to do such
a operation because i want to aggregate (spatial<BR>> correct) different
datasets!<BR>> Which example from you fits best for this aim? I did not find
any<BR>> information on how left join works on the postgis or postgresql
reference...<BR>> <BR>>> <BR>>><BR>>>
Unfortunately as I have come across before if you need an either or (if in table
1 or table 2 - ideally you would use a FULL JOIN but for some reason Postgres
chokes when you use postgis functions in the FULL JOIN clause for the cases I
have tried). In that case you need a workaround using a set of
UNIONS.<BR>>>
--------------------------------------------------------------------------<BR>>>
Simplest case - get all records in g1 one or union of g1 and g2 that
intersect<BR>>> NOTE: COALESCE is an ANSI SQL function that will return
the first non-null - when you do a geomunion of a geometry and null you get null
which is why we need COALESCE<BR>>><BR>>> SELECT g1.field1,
g1.field2, COALESCE(geomunion(g1.the_geom, g2.the_geom), g1.the_geom) AS
newgeom<BR>>> FROM g1 LEFT JOIN g2 ON (g1.the_geom && g2.the_geom
AND intersects(g1.the_geom, g2.the_geom))<BR>>><BR>>>
-----------------------------------------------------------------------------------------<BR>>>
Either of case - get all geometries in g1 or g2 or union if there is a match -
workaround for full joins not working right<BR>>><BR>>> SELECT
g1.field1, g1.field2, geomunion(g1.the_geom, g2.the_geom) AS newgeom<BR>>>
FROM g1 INNER JOIN g2 ON (g1.the_geom && g2.the_geom AND
intersects(g1.the_geom, g2.the_geom))<BR>>> UNION<BR>>> SELECT
g1.field1, g1.field2, g1.the_geom AS newgeom<BR>>> FROM g1 LEFT JOIN g2 ON
(g1.the_geom && g2.the_geom AND intersects(g1.the_geom,
g2.the_geom))<BR>>> WHERE g2.the_geom IS NULL<BR>>>
UNION<BR>>> SELECT g1.field1, g1.field2, g2.the_geom AS
newgeom<BR>>> FROM g2 LEFT JOIN g1 ON (g1.the_geom && g2.the_geom
AND intersects(g1.the_geom, g2.the_geom))<BR>>> WHERE g1.the_geom IS
NULL<BR>>><BR>>><BR>>><BR>>> Hope that
helps,<BR>>>
Regina<BR>>><BR>>><BR>>><BR>>><BR>>><BR>>><BR>>><BR>>><BR>>><BR>>>
-----Original Message-----<BR>>> From:
postgis-users-bounces@postgis.refractions.net [</FONT><A
href="mailto:postgis-users-bounces@postgis.refractions.net"><FONT
size=2>mailto:postgis-users-bounces@postgis.refractions.net</FONT></A><FONT
size=2>] On Behalf Of Andreas Laggner<BR>>> Sent: Thursday, August 23,
2007 11:32 AM<BR>>> To: PostGIS Users Discussion<BR>>> Subject: Re:
[postgis-users] Union of 7 datasets<BR>>><BR>>> Obe, Regina
schrieb:<BR>>> <BR>>> <BR>>>>
Oh the g1 g2 .. was just for example - I don't actually call my tables
meaningless names like that. <BR>>>><BR>>>> You should be
doing a join on something or have a where clause unless one of your tables has
only one record. Otherwise you are doing what is called a CROSS JOIN
(cartesian product) which gives you an nxm records where n is the number
of records in your first table and m is the number in second table. This
is generally a big NO NO. In certain rare cases you do want to do
something like that, but is usually the
exception.<BR>>>><BR>>>> <BR>>>> <BR>>>> <BR>>>
I think the records in my targed table must be added (more or less)
and<BR>>> not multiplied! My Aim is a table that contains the areas of all
the 7<BR>>> sourcetables and the information which refuges are inside and
wich not.<BR>>> Perhaps i must use the intersection!? If i do my query
with a gist like<BR>>> this: where t1.the_geom && t2.the_geom;
than the operation is very fast<BR>>> (about one minute) but i only have
the Polygons covered by BOTH<BR>>> datasets, and i want to have as well
those, which are covered by one<BR>>> dataset only!! But my operation
without the where clause runs for 4<BR>>> hours now - that shows me there
is something wrong
;-)<BR>>> <BR>>> <BR>>>>
Its hard for me to tell if you need a cartesian product in this case since I'm
not quite sure what for example nature and biosphere represent. I would
guess that is wrong and you should first figure out which sets of say nature
records you need to geomunion with biosphere and then join by that field or set
of fields.<BR>>>><BR>>>> It would help a bit if you could
provide some sample questions you expect to answer with your statistical
analysis. My guess is you may be better off with more than one
table.<BR>>>><BR>>>> <BR>>>> <BR>>>> <BR>>>
Sample question: give me all areas (all polygons) from germany where<BR>>>
landuse=arable land and soils=good and precipitation>600 and any (of
7)<BR>>> reserves and so on.......<BR>>> I need the values in my
table to calculate the potential yield or other<BR>>>
things...<BR>>> And i want to analyse such questions with a statistical
software (SAS),<BR>>> so it seems to me i need one table to import in SAS
(or to query from<BR>>> SAS directly to the
postgresql).<BR>>><BR>>> Thanks for your help, i will be back in my
office in
Monday.......Andreas<BR>>><BR>>> <BR>>> <BR>>>>
Which structure is best really boils down to what questions you hope to answer
because one approach may make one question easy and fast and another question
slow and cumbersome.<BR>>>><BR>>>> Hope that
helps,<BR>>>>
Regina<BR>>>><BR>>>><BR>>>><BR>>>><BR>>>><BR>>>><BR>>>><BR>>>>
-----Original Message-----<BR>>>> From:
postgis-users-bounces@postgis.refractions.net [</FONT><A
href="mailto:postgis-users-bounces@postgis.refractions.net"><FONT
size=2>mailto:postgis-users-bounces@postgis.refractions.net</FONT></A><FONT
size=2>] On Behalf Of Andreas Laggner<BR>>>> Sent: Thursday, August 23,
2007 10:04 AM<BR>>>> To: PostGIS Users Discussion<BR>>>>
Subject: Re: [postgis-users] Union of 7 datasets<BR>>>><BR>>>>
Obe, Regina
schrieb:<BR>>>> <BR>>>> <BR>>>> <BR>>>>>
Andreas,<BR>>>>><BR>>>>> It would help to know what your
table structure looks like and why do you want to put them all in a single
geometry?<BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>
My table structures are a little bit different. I want to have them in
a<BR>>>> single geometry to intersect them with other data and built a
large<BR>>>> table to run statistics over it (production site analysis
over
germany).<BR>>>> <BR>>>> <BR>>>> <BR>>>>>
I'm imaging you are you doing something
like<BR>>>>><BR>>>>> SELECT g1.somefield,
geomunion(geomunion(g1.the_geom, g2.the_geom), g3.the_geom)<BR>>>>>
FROM g1 INNER JOIN g2 on g1.somefield = g2.somefield INNER JOIN g3 on
g2.somefield = g3.somefield<BR>>>>> GROUP BY
g1.somefield<BR>>>>><BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>
That´s an interesting method with inner join..why go you call
your<BR>>>> tables g1. g2. and so on?<BR>>>> That´s my method
i am using right now (geomunion 1 to 3 from 6), seems<BR>>>> to be a
pedestrian method :-(<BR>>>><BR>>>> create table
natura2000<BR>>>> (ffh_name character
varying(80), ffh_land character varying(3), ffh<BR>>>> smallint, ffh_id
smallint,<BR>>>> spa_name character
varying(80), spa_land character varying(3), spa<BR>>>> smallint, spa_id
smallint) with oids;<BR>>>> select<BR>>>>
addgeometrycolumn('','natura2000','the_geom','31467','MULTIPOLYGON',2);<BR>>>>
alter table natura2000 drop constraint enforce_geotype_the_geom;<BR>>>>
insert into natura2000<BR>>>>
select<BR>>>>
t1.ffh_name,t1.ffh_land,t1.ffh,t1.ffh_id,t2.spa_name,t2.spa_land,t2.spa,t2.spa_id,<BR>>>>
geomunion(t1.the_geom,
t2.the_geom)<BR>>>>
from ffh_rep t1, spa_rep t2;<BR>>>><BR>>>> create table
sg71<BR>>>> (ffh_name character varying(80),
ffh_land character varying(3), ffh<BR>>>> smallint, ffh_id
smallint,<BR>>>> spa_name character
varying(80), spa_land character varying(3), spa<BR>>>> smallint, spa_id
smallint,<BR>>>> bio_name character
varying(70), bio smallint, bio_id smallint) with<BR>>>>
oids;<BR>>>> select
addgeometrycolumn('','sg71','the_geom','31467','MULTIPOLYGON',2);<BR>>>>
alter table sg71 drop constraint enforce_geotype_the_geom;<BR>>>>
insert into sg71<BR>>>> select t1.ffh_name,
t1.ffh_land, t1.ffh, t1.ffh_id, t1.spa_name,<BR>>>> t1.spa_land,
t1.spa,
t1.spa_id,<BR>>>>
t2.name,t2.bio,t2.bio_id,geomunion(t1.the_geom,
t2.the_geom)<BR>>>>
from natura2000 t1, biosphere t2;<BR>>>><BR>>>> create table
sg72<BR>>>> (ffh_name
character varying(80), ffh_land character varying(3),<BR>>>> ffh
smallint, ffh_id smallint,<BR>>>> spa_name
character varying(80), spa_land character varying(3), spa<BR>>>>
smallint, spa_id smallint,<BR>>>> bio_name
character varying(70), bio smallint, bio_id
smallint,<BR>>>> np_name character varying(60),
np smallint, np_id smallint) with oids;<BR>>>> select
addgeometrycolumn('','sg72','the_geom','31467','MULTIPOLYGON',2);<BR>>>>
alter table sg72 drop constraint enforce_geotype_the_geom;<BR>>>>
insert into sg72<BR>>>> select t1.ffh_name,
t1.ffh_land, t1.ffh, t1.ffh_id, t1.spa_name,<BR>>>> t1.spa_land,
t1.spa,
t1.spa_id,<BR>>>>
t1.bio_name,t1.bio,t1.bio_id,t2.np_name,t2.np,t2.np_id,<BR>>>>
geomunion(t1.the_geom,
t2.the_geom)<BR>>>>
from sg71 t1, np t2;<BR>>>> AND SO
ON......<BR>>>> <BR>>>> <BR>>>> <BR>>>>>
or<BR>>>>><BR>>>>> SELECT g1.somefield,
geomunion(gt.the_geom)<BR>>>>> FROM (SELECT somefield, the_geom FROM
g1 UNION SELECT somefield, the_geom FROM g2 ...) gt<BR>>>>> GROUP BY
gt.somefield<BR>>>>><BR>>>>><BR>>>>> If I
have 7 different tables that have pretty much the same structure, but for
logistical or other technical reasons (such as each has additional attributes
distinct from one another), I need to keep them as separate tables, then I
usually use inherited tables for that. That way when I need to join all datasets
at once, I can simply query the parent table and it will automatically drill
down to the child tables. Not sure if that helps more than it confuses your
situation.<BR>>>>><BR>>>>> Then instead of the above I
can simply do<BR>>>>><BR>>>>> SELEG
myparenttable.somefield, geomunion(myparenttable.the_geom)<BR>>>>>
FROM myparenttable<BR>>>>> GROUP by
gh.somefield<BR>>>>><BR>>>>><BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>
ok - i have to think about your suggestions......that´s my second
week<BR>>>> with postgis.<BR>>>> Can you tell me from my
SQL-Statements which method will be best? So i<BR>>>> try to understand
that one.....<BR>>>><BR>>>> Thanks for your
reply!!!<BR>>>> <BR>>>> <BR>>>> <BR>>>>>
Hope that helps,<BR>>>>>
Regina<BR>>>>><BR>>>>> -----Original
Message-----<BR>>>>> From:
postgis-users-bounces@postgis.refractions.net [</FONT><A
href="mailto:postgis-users-bounces@postgis.refractions.net"><FONT
size=2>mailto:postgis-users-bounces@postgis.refractions.net</FONT></A><FONT
size=2>] On Behalf Of Andreas Laggner<BR>>>>> Sent: Thursday, August
23, 2007 9:11 AM<BR>>>>> To: PostGis_Mailinglist<BR>>>>>
Subject: [postgis-users] Union of 7
datasets<BR>>>>><BR>>>>> Hi
users,<BR>>>>><BR>>>>> i want to put together 7 datasets
to have all the different refuges in<BR>>>>> one table (and in one
geometry). Am i doing right with 6 times geomunion<BR>>>>> (that´s
much to type with all the attributes) or is there a more<BR>>>>>
effective way?<BR>>>>><BR>>>>> cheers
Andreas<BR>>>>><BR>>>>> <BR>>>>> <BR>>>>> <BR>>>>> <BR>>>> <BR>>>> <BR>>>> <BR>>> <BR>>> <BR>><BR>><BR>> <BR><BR><BR>--<BR>Dipl.
Geoökologe Andreas Laggner<BR>Institut für Ländliche Räume
(LR)<BR>Bundesforschungsanstalt für Landwirtschaft (FAL)<BR><BR>Institute of
Rural Studies<BR>Federal Agricultural Research Centre (FAL)<BR><BR>Bundesallee
50<BR>D-38116 Braunschweig<BR><BR>Tel.: (+49) (0)531 596 5515<BR>Fax: (+49)
(0)531 596 5599<BR>E-mail: andreas.laggner@fal.de<BR>Homepage: </FONT><A
href="http://www.lr.fal.de/"><FONT
size=2>http://www.lr.fal.de/</FONT></A><BR><BR><FONT
size=2>_______________________________________________<BR>postgis-users mailing
list<BR>postgis-users@postgis.refractions.net<BR></FONT><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"><FONT
size=2>http://postgis.refractions.net/mailman/listinfo/postgis-users</FONT></A><BR></P></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>