[postgis-users] Union of 7 datasets
Andreas Laggner
andreas.laggner at fal.de
Mon Sep 10 05:28:18 PDT 2007
Hi Regina,
i still did not reach my aim.....but i am not shure if i put your
suggestions on the right position!?
I replaced the geomunion statement by the collect statement as shown below.
I want to have one table with one geometry_column. The geometry should
look like figure 1 - i did not get this result so far!! (The colors in
figure 1 are symbols for different attributes in the table). The
collect (instead of the geomunion) is a little bit closer to my
intention: geometries that intersect are now not totally combined (as
geomunion did), but in intersecting polygons only these parts are
separated which do not overlap. I want to have three (new) polygonss
(figure 1). Geomunion returns me one polygon, collect returns me two
polygons.........
As i tried to replace the geomunion by the intersection statement you
wrote to me i always get an error (i do not understand all of the query,
so i do not know how to integrate the intersection statement in my query).
drop table natura2000;
create table natura2000
(ffh_name character varying(80),ffh_land character varying(3),ffh
smallint,ffh_id smallint,
spa_name character varying(80),spa_land character varying(3),spa
smallint,spa_id smallint)
with oids;
select
addgeometrycolumn('','natura2000','the_geom','31467','MULTIPOLYGON',2);
alter table natura2000 drop constraint enforce_geotype_the_geom;
insert into natura2000
(ffh_name,ffh_land,ffh,ffh_id,spa_name,spa_land,spa,spa_id,the_geom)
select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
*collect(ffh_rep.the_geom, spa_rep.the_geom) as geomcollection*
from ffh_rep inner join spa_rep
on (ffh_rep.the_geom && spa_rep.the_geom and
intersects(ffh_rep.the_geom, spa_rep.the_geom))
union all
-- the second select gives you ffh_reps that have no spa_reps - your 15
select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
null as spa_name, null as spa_land, null as spa, null as spa_id,
ffh_rep.the_geom as the_geom
from ffh_rep left join spa_rep
on (ffh_rep.the_geom && spa_rep.the_geom and
intersects(ffh_rep.the_geom, spa_rep.the_geom))
where spa_rep.the_geom is null
union all
-- and the 3rd gives you spa_reps that have no ffh_reps. - your 40
select null as ffh_name, null as ffh_land, null as ffh, null as ffh_id,
spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
spa_rep.the_geom AS the_geom
from spa_rep left join ffh_rep
on (ffh_rep.the_geom && spa_rep.the_geom and
intersects(ffh_rep.the_geom, spa_rep.the_geom))
where ffh_rep.the_geom is null;
Obe, Regina schrieb:
> Not sure I quite understand the question. You mean to store these 3 geometries separately so you can color code as needed or overlay as needed?
>
> In that case you would have 3 separate geometry fields
>
> intersection(ffh_rep.the_geom, spa_rep.the_geom) As geomintersection, ffh_rep.the_geom As ffh_geom, spa_rep.the_geom As spa_geom
>
> For the ones where there is only udo or kai then I guess you can fill in the same geometry for all geometry fields.
>
>
> and then overlay them on your map as separate layers as needed
>
> or if you mean you just want it to look like figure 1 but not necessarily color coded, then you would do
>
> collect(ffh_rep.the_geom, spa_rep.the_geom) as geomcollection
>
> 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 Andreas Laggner
> Sent: Thursday, August 30, 2007 7:24 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Union of 7 datasets
>
> Moin Regina,
>
> the query you suggested is a very big step forward, but it does not
> exactly what i want to have ;-) ... it seems to me i will need an
> intersection in one part and not a geomunion!! But i want to ask you:
> what it does correctly: I now have a union of all geometries from both
> source datasets.
> If i have a single udo (ffh_rep) that intersects no kai (spa_rep) the
> row only has the attributes from udo. Same thing i have with single kai.
> That is all correct.
> BUT: If udo and kai intersects i will not have new polygons!! For
> example: blue is udo (or ffh_rep) and yellow is kai (or spa_rep). I want
> to have three polygons than with blue only the attributes from udo,
> yellow only the attributes from kai AND the intersection, polygon number
> 3, the green one (blue with yellow points) with the attributes from udo
> and kai (figure1). And now i have one Polygon having the attributes from
> udo and kai (figure2).
>
> figure1:
> figure1
>
> figure2:
>
> figure2
>
>
> My query:
> drop table natura2000;
> create table natura2000
> (ffh_name character varying(80),ffh_land character varying(3),ffh
> smallint,ffh_id smallint,
> spa_name character varying(80),spa_land character varying(3),spa
> smallint,spa_id smallint)
> with oids;
> select
> addgeometrycolumn('','natura2000','newgeom','31467','MULTIPOLYGON',2);
> alter table natura2000 drop constraint enforce_geotype_newgeom;
>
> insert into natura2000
> (ffh_name,ffh_land,ffh,ffh_id,spa_name,spa_land,spa,spa_id,newgeom)
> select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
> spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
> geomunion(ffh_rep.the_geom, spa_rep.the_geom) as newgeom
> from ffh_rep inner join spa_rep
> on (ffh_rep.the_geom && spa_rep.the_geom and
> intersects(ffh_rep.the_geom, spa_rep.the_geom))
> union all
> -- the second select gives you ffh_reps that have no spa_reps - your 15
> select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
> null as spa_name, null as spa_land, null as spa, null as spa_id,
> ffh_rep.the_geom as newgeom
> from ffh_rep left join spa_rep
> on (ffh_rep.the_geom && spa_rep.the_geom and
> intersects(ffh_rep.the_geom, spa_rep.the_geom))
> where spa_rep.the_geom is null
> union all
> -- and the 3rd gives you spa_reps that have no ffh_reps. - your 40
> select null as ffh_name, null as ffh_land, null as ffh, null as ffh_id,
> spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
> spa_rep.the_geom AS newgeom
> from spa_rep left join ffh_rep
> on (ffh_rep.the_geom && spa_rep.the_geom and
> intersects(ffh_rep.the_geom, spa_rep.the_geom))
> where ffh_rep.the_geom is null;
>
> Cheers Andreas
>
>
>
> Obe, Regina schrieb:
>
>> Andreas,
>>
>> 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 UNION does an implicit distinct) - so I
>> have corrected below.
>>
>> - - I happened to insert comments in between which may have confused
>> you, 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.
>>
>> So would be
>> INSERT INTO sometable(field1, field2,field3,field4, newgeom)
>> SELECT udo.field1, udo.field2, kai.field3, kai.field4,
>> geomunion(udo.the_geom, kai.the_geom) AS newgeom
>> FROM udo INNER JOIN kai ON (udo.the_geom && kai.the_geom AND
>> intersects(udo.the_geom, kai.the_geom))
>> UNION ALL
>> -- the second select gives you udos that have no kais - your 15
>> SELECT udo.field1, udo.field2, NULL As field3, NULL As field4,
>> udo.the_geom AS newgeom
>> FROM udo LEFT JOIN kai ON (udo.the_geom && kai.the_geom AND
>> intersects(udo.the_geom, kai.the_geom))
>> WHERE kai.the_geom IS NULL
>> UNION ALL
>> -- and the 3rd gives you kais that have no udos. - your 40
>> SELECT NULL As field1, null As field2, kai.field3, kai.field4,
>> kai.the_geom AS newgeom
>> FROM kai LEFT JOIN udo ON (udo.the_geom && kai.the_geom AND
>> intersects(udo.the_geom, kai.the_geom))
>> WHERE udo.the_geom IS NULL;
>>
>>
>>
>> ----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 worked and can be easily fixed (but sadly I think the issue is
>> deeper than Postgis and Geos) - you could write the above much simpler as)
>>
>> INSERT INTO sometable(field1, field2,field3,field4, newgeom)
>> 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
>> FROM udo FULL JOIN kai ON (udo.the_geom && kai.the_geom AND
>> intersects(udo.the_geom, kai.the_geom))
>>
>> 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 the built
>> SQL or set of SQL statements or you could do a similar thing in some
>> scripted language like perl or php.
>>
>> 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.
>>
>> I don't get the 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.
>>
>> 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)
>>
>> 1) unioning of record sets (standard ANSI SQL UNION/UNION ALL) - which
>> is simply a way of stringing together a bunch of selects into a
>> single result set as shown above
>>
>> 2) unioning of 2 geometry fields like shown above with geomunion
>>
>> 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).
>>
>> 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.
>>
>> 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
>> Andreas Laggner
>> Sent: Tuesday, August 28, 2007 9:13 AM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] Union of 7 datasets
>>
>> Obe, Regina schrieb:
>>
>>> 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 -
>>
>>> --the first select gives you those records in both tables your 25 udokai
>>> SELECT udo.field1, udo.field2, kai.field3, kai.field4,
>>>
>> geomunion(udo.the_geom, kai.the_geom) AS newgeom
>>
>>> FROM udo INNER JOIN kai ON (udo.the_geom && kai.the_geom AND
>>>
>> intersects(udo.the_geom, kai.the_geom))
>>
>>> UNION
>>> -- the second select gives you udos that have no kais - your 15
>>> SELECT udo.field1, udo.field2, NULL As field3, NULL As field4,
>>>
>> udo.the_geom AS newgeom
>>
>>> FROM udo LEFT JOIN kai ON (udo.the_geom && kai.the_geom AND
>>>
>> intersects(udo.the_geom, kai.the_geom))
>>
>>> WHERE kai.the_geom IS NULL
>>> UNION
>>> -- and the 3rd gives you kais that have no udos. - your 40
>>> SELECT nul As field1, null As field2, kai.field3, kai.field4,
>>>
>> kai.the_geom AS newgeom
>>
>>> FROM kai LEFT JOIN udo ON (udo.the_geom && kai.the_geom AND
>>>
>> intersects(udo.the_geom, kai.the_geom))
>>
>>> WHERE udo.the_geom IS NULL
>>>
>>> Unioned together you should get
>>> 25 + 15 + 40 = ? 80
>>>
>>> Hope that helps,
>>> Regina
>>>
>>>
>>>
>> UFF - that seems to be a complex question, a comprehensive operation and
>> a lot to type too!!! Thanks a million - i would have spent many time to
>> find out this query by myself! Ok..... how can i perform your "UNION": I
>> would just insert my selects one after another in one new table - will
>> that work well?
>> I want to aggregate around 20 datasets this way!? Do you know a method
>> to operate with more datasets (for example 7) in a effektiv manner and
>> not to do this three selects 6 times?
>> AND: Am i totally wrong with my aims? It seems to me that is an exotic
>> think to do with PostGis, but our projects/problems require to aggregate
>> datasets in almost all cases (around 90%) and that is exactly what one
>> of the basic functions "Union" in ArcView already carried out ten years
>> ago. So i would exspect there is a function to perform this operation
>> easier!? .....i just wonder.....
>>
>>
>> cheers Andreas
>>
>>
>>> -----Original Message-----
>>> From: postgis-users-bounces at postgis.refractions.net
>>>
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> Andreas Laggner
>>
>>> Sent: Tuesday, August 28, 2007 7:28 AM
>>> To: PostGIS Users Discussion
>>> Subject: Re: [postgis-users] Union of 7 datasets
>>>
>>> Obe, Regina schrieb:
>>>
>>>
>>>> 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.
>>
>>>>
>>>>
>>> ok - it seems to me i need some coaching......Yes, I want to get all
>>> records with no matching including those that match, but i also want to
>>> dissect the polygons that matches. I will try to explain again just to
>>> be sure you understood my aims: I have 20 polygons in udo and 50 in kai.
>>> 10 from kai are intersecting 5 from udo to 25 new polygons i call udokai
>>> (because they have attributes from udo AND kai). My result should have:
>>> 15 polygons with attributes only from udo, 40 polygons with attributes
>>> only from kai AND 25 with attributes from udo and kai! In most cases i
>>> want to do such a operation because i want to aggregate (spatial
>>> correct) different datasets!
>>> Which example from you fits best for this aim? I did not find any
>>> information on how left join works on the postgis or postgresql
>>>
>> reference...
>>
>>>
>>>
>>>> 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.
>>
>> --------------------------------------------------------------------------
>>
>>>> Simplest case - get all records in g1 one or union of g1 and g2
>>>>
>> that intersect
>>
>>>> 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
>>
>>>> SELECT g1.field1, g1.field2, COALESCE(geomunion(g1.the_geom,
>>>>
>> g2.the_geom), g1.the_geom) AS newgeom
>>
>>>> FROM g1 LEFT JOIN g2 ON (g1.the_geom && g2.the_geom AND
>>>>
>> intersects(g1.the_geom, g2.the_geom))
>>
>>>>
>> -----------------------------------------------------------------------------------------
>>
>>>> Either of case - get all geometries in g1 or g2 or union if there
>>>>
>> is a match - workaround for full joins not working right
>>
>>>> SELECT g1.field1, g1.field2, geomunion(g1.the_geom, g2.the_geom) AS
>>>>
>> newgeom
>>
>>>> FROM g1 INNER JOIN g2 ON (g1.the_geom && g2.the_geom AND
>>>>
>> intersects(g1.the_geom, g2.the_geom))
>>
>>>> UNION
>>>> SELECT g1.field1, g1.field2, g1.the_geom AS newgeom
>>>> FROM g1 LEFT JOIN g2 ON (g1.the_geom && g2.the_geom AND
>>>>
>> intersects(g1.the_geom, g2.the_geom))
>>
>>>> WHERE g2.the_geom IS NULL
>>>> UNION
>>>> SELECT g1.field1, g1.field2, g2.the_geom AS newgeom
>>>> FROM g2 LEFT JOIN g1 ON (g1.the_geom && g2.the_geom AND
>>>>
>> intersects(g1.the_geom, g2.the_geom))
>>
>>>> WHERE g1.the_geom IS NULL
>>>>
>>>>
>>>>
>>>> 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
>> Andreas Laggner
>>
>>>> Sent: Thursday, August 23, 2007 11:32 AM
>>>> To: PostGIS Users Discussion
>>>> Subject: Re: [postgis-users] Union of 7 datasets
>>>>
>>>> Obe, Regina schrieb:
>>>>
>>>>
>>>>
>>>>> Oh the g1 g2 .. was just for example - I don't actually call my
>>>>>
>> tables meaningless names like that.
>>
>>>>> 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.
>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>> I think the records in my targed table must be added (more or less) and
>>>> not multiplied! My Aim is a table that contains the areas of all the 7
>>>> sourcetables and the information which refuges are inside and wich not.
>>>> Perhaps i must use the intersection!? If i do my query with a gist like
>>>> this: where t1.the_geom && t2.the_geom; than the operation is very fast
>>>> (about one minute) but i only have the Polygons covered by BOTH
>>>> datasets, and i want to have as well those, which are covered by one
>>>> dataset only!! But my operation without the where clause runs for 4
>>>> hours now - that shows me there is something wrong ;-)
>>>>
>>>>
>>>>
>>>>> 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.
>>
>>>>> 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.
>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>> Sample question: give me all areas (all polygons) from germany where
>>>> landuse=arable land and soils=good and precipitation>600 and any (of 7)
>>>> reserves and so on.......
>>>> I need the values in my table to calculate the potential yield or other
>>>> things...
>>>> And i want to analyse such questions with a statistical software (SAS),
>>>> so it seems to me i need one table to import in SAS (or to query from
>>>> SAS directly to the postgresql).
>>>>
>>>> Thanks for your help, i will be back in my office in
>>>>
>> Monday.......Andreas
>>
>>>>
>>>>
>>>>
>>>>> 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.
>>
>>>>> 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
>> Andreas Laggner
>>
>>>>> Sent: Thursday, August 23, 2007 10:04 AM
>>>>> To: PostGIS Users Discussion
>>>>> Subject: Re: [postgis-users] Union of 7 datasets
>>>>>
>>>>> Obe, Regina schrieb:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> Andreas,
>>>>>>
>>>>>> It would help to know what your table structure looks like and
>>>>>>
>> why do you want to put them all in a single geometry?
>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> My table structures are a little bit different. I want to have
>>>>>
>> them in a
>>
>>>>> single geometry to intersect them with other data and built a large
>>>>> table to run statistics over it (production site analysis over
>>>>>
>> germany).
>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> I'm imaging you are you doing something like
>>>>>>
>>>>>> SELECT g1.somefield, geomunion(geomunion(g1.the_geom,
>>>>>>
>> g2.the_geom), g3.the_geom)
>>
>>>>>> FROM g1 INNER JOIN g2 on g1.somefield = g2.somefield INNER JOIN
>>>>>>
>> g3 on g2.somefield = g3.somefield
>>
>>>>>> GROUP BY g1.somefield
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> That´s an interesting method with inner join..why go you call your
>>>>> tables g1. g2. and so on?
>>>>> That´s my method i am using right now (geomunion 1 to 3 from 6), seems
>>>>> to be a pedestrian method :-(
>>>>>
>>>>> create table natura2000
>>>>> (ffh_name character varying(80), ffh_land character
>>>>>
>> varying(3), ffh
>>
>>>>> smallint, ffh_id smallint,
>>>>> spa_name character varying(80), spa_land character
>>>>>
>> varying(3), spa
>>
>>>>> smallint, spa_id smallint) with oids;
>>>>> select
>>>>>
>>>>>
>> addgeometrycolumn('','natura2000','the_geom','31467','MULTIPOLYGON',2);
>>
>>>>> alter table natura2000 drop constraint enforce_geotype_the_geom;
>>>>> insert into natura2000
>>>>> select
>>>>>
>>>>>
>> t1.ffh_name,t1.ffh_land,t1.ffh,t1.ffh_id,t2.spa_name,t2.spa_land,t2.spa,t2.spa_id,
>>
>>>>> geomunion(t1.the_geom, t2.the_geom)
>>>>> from ffh_rep t1, spa_rep t2;
>>>>>
>>>>> create table sg71
>>>>> (ffh_name character varying(80), ffh_land character
>>>>>
>> varying(3), ffh
>>
>>>>> smallint, ffh_id smallint,
>>>>> spa_name character varying(80), spa_land character
>>>>>
>> varying(3), spa
>>
>>>>> smallint, spa_id smallint,
>>>>> bio_name character varying(70), bio smallint, bio_id
>>>>>
>> smallint) with
>>
>>>>> oids;
>>>>> select
>>>>>
>> addgeometrycolumn('','sg71','the_geom','31467','MULTIPOLYGON',2);
>>
>>>>> alter table sg71 drop constraint enforce_geotype_the_geom;
>>>>> insert into sg71
>>>>> select t1.ffh_name, t1.ffh_land, t1.ffh, t1.ffh_id, t1.spa_name,
>>>>> t1.spa_land, t1.spa, t1.spa_id,
>>>>> t2.name,t2.bio,t2.bio_id,geomunion(t1.the_geom,
>>>>>
>> t2.the_geom)
>>
>>>>> from natura2000 t1, biosphere t2;
>>>>>
>>>>> create table sg72
>>>>> (ffh_name character varying(80), ffh_land character varying(3),
>>>>> ffh smallint, ffh_id smallint,
>>>>> spa_name character varying(80), spa_land character varying(3), spa
>>>>> smallint, spa_id smallint,
>>>>> bio_name character varying(70), bio smallint, bio_id smallint,
>>>>> np_name character varying(60), np smallint, np_id smallint)
>>>>>
>> with oids;
>>
>>>>> select
>>>>>
>> addgeometrycolumn('','sg72','the_geom','31467','MULTIPOLYGON',2);
>>
>>>>> alter table sg72 drop constraint enforce_geotype_the_geom;
>>>>> insert into sg72
>>>>> select t1.ffh_name, t1.ffh_land, t1.ffh, t1.ffh_id, t1.spa_name,
>>>>> t1.spa_land, t1.spa, t1.spa_id,
>>>>> t1.bio_name,t1.bio,t1.bio_id,t2.np_name,t2.np,t2.np_id,
>>>>> geomunion(t1.the_geom, t2.the_geom)
>>>>> from sg71 t1, np t2;
>>>>> AND SO ON......
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> or
>>>>>>
>>>>>> SELECT g1.somefield, geomunion(gt.the_geom)
>>>>>> FROM (SELECT somefield, the_geom FROM g1 UNION SELECT somefield,
>>>>>>
>> the_geom FROM g2 ...) gt
>>
>>>>>> GROUP BY gt.somefield
>>>>>>
>>>>>>
>>>>>> 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.
>>
>>>>>> Then instead of the above I can simply do
>>>>>>
>>>>>> SELEG myparenttable.somefield, geomunion(myparenttable.the_geom)
>>>>>> FROM myparenttable
>>>>>> GROUP by gh.somefield
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> ok - i have to think about your suggestions......that´s my second week
>>>>> with postgis.
>>>>> Can you tell me from my SQL-Statements which method will be best? So i
>>>>> try to understand that one.....
>>>>>
>>>>> Thanks for your reply!!!
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> 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
>> Andreas Laggner
>>
>>>>>> Sent: Thursday, August 23, 2007 9:11 AM
>>>>>> To: PostGis_Mailinglist
>>>>>> Subject: [postgis-users] Union of 7 datasets
>>>>>>
>>>>>> Hi users,
>>>>>>
>>>>>> i want to put together 7 datasets to have all the different
>>>>>>
>> refuges in
>>
>>>>>> one table (and in one geometry). Am i doing right with 6 times
>>>>>>
>> geomunion
>>
>>>>>> (that´s much to type with all the attributes) or is there a more
>>>>>> effective way?
>>>>>>
>>>>>> cheers Andreas
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>> --
>> Dipl. Geoökologe Andreas Laggner
>> Institut für Ländliche Räume (LR)
>> Bundesforschungsanstalt für Landwirtschaft (FAL)
>>
>> Institute of Rural Studies
>> Federal Agricultural Research Centre (FAL)
>>
>> Bundesallee 50
>> D-38116 Braunschweig
>>
>> Tel.: (+49) (0)531 596 5515
>> Fax: (+49) (0)531 596 5599
>> E-mail: andreas.laggner at fal.de
>> Homepage: http://www.lr.fal.de/
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>> ------------------------------------------------------------------------
>>
>> *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. *
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
>
--
Dipl. Geoökologe Andreas Laggner
Institut für Ländliche Räume (LR)
Bundesforschungsanstalt für Landwirtschaft (FAL)
Institute of Rural Studies
Federal Agricultural Research Centre (FAL)
Bundesallee 50
D-38116 Braunschweig
Tel.: (+49) (0)531 596 5515
Fax: (+49) (0)531 596 5599
E-mail: andreas.laggner at fal.de
Homepage: http://www.lr.fal.de/
From MAILER-DAEMON Mon Sep 10 05:28:14 2007
Return-Path: <>
X-Original-To: postgis-users at postgis.refractions.net
Delivered-To: postgis-users at netnation.refractions.net
Received: from mout1.freenet.de (mout1.freenet.de [195.4.92.91])
by netnation.refractions.net (Postfix) with ESMTP id C98872C1950
for <postgis-users at postgis.refractions.net>;
Mon, 10 Sep 2007 05:28:09 -0700 (PDT)
Received: from [195.4.92.18] (helo=mx8.freenet.de)
by mout1.freenet.de with esmtpa (Exim 4.68-dev) id 1IUiP4-0002ad-Fr
for postgis-users at postgis.refractions.net;
Mon, 10 Sep 2007 14:30:10 +0200
Received: from p549c2050.dip0.t-ipconnect.de ([84.156.32.80]:36672
helo=dvise.alpstein.de)
by mx8.freenet.de with esmtpa (ID alpstein1 at freenet.de) (port 25) (Exim
4.68-dev #12) id 1IUiP3-0008Uf-LV
for postgis-users at postgis.refractions.net;
Mon, 10 Sep 2007 14:30:10 +0200
From: hubert.burger at alpstein.de
Subject: AUTOREPLY Re: [postgis-users] Union of 7 datasets
To: postgis-users at postgis.refractions.net
Date: Mon, 10 Sep 2007 12:30:08 +0000
Priority: normal
X-Priority: 3 (Normal)
Importance: normal
X-Mailer: DvISE by Tobit Software, Germany (0244.444B44464847494F524A),
Mime Converter 101.20
X-David-Sym: 0
X-David-Flags: 0
Message-ID: <0004B7B7.46E554EF at dvise.alpstein.de>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-BeenThere: postgis-users at postgis.refractions.net
X-Mailman-Version: 2.1.5
Precedence: list
Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
List-Id: PostGIS Users Discussion <postgis-users.postgis.refractions.net>
List-Unsubscribe: <http://postgis.refractions.net/mailman/listinfo/postgis-users>,
<mailto:postgis-users-request at postgis.refractions.net?subject=unsubscribe>
List-Archive: <http://lists.refractions.net/pipermail/postgis-users>
List-Post: <mailto:postgis-users at postgis.refractions.net>
List-Help: <mailto:postgis-users-request at postgis.refractions.net?subject=help>
List-Subscribe: <http://postgis.refractions.net/mailman/listinfo/postgis-users>,
<mailto:postgis-users-request at postgis.refractions.net?subject=subscribe>
X-List-Received-Date: Mon, 10 Sep 2007 12:28:14 -0000
Sehr geehrter Absender,=20
vielen Dank f=FCr Ihre Email.
Leider kann ich Ihre Nachricht im Moment nicht pers=F6nlich
beantworten, da ich mich bis zum 21.09.2007 im Urlaub befinden.=20
Nach meiner R=FCckkehr am 24.09.2007 werde ich gerne Ihre Anfrage =
beantworten.=20
Bitte wenden Sie sich in dringenden F=E4llen an Herrn Jens Schwarz, =
jens.schwarz at alpstein.de oder per Telefon 08323-8006-0.
Mit freundlichen Gr=FC=DFen
Hubert Burger
=20
=20
Alpstein GmbH =20
Missener Str. 18 =20
87509 Immenstadt =20
=20
fon +49 8323 8006-0 =20
fax +49 8323 8006-50 =20
=20
web www.alpstein.de
Hi Regina,
i still did not reach my aim.....but i am not shure if i put your=20
suggestions on the right position!?
I replaced the geomunion statement by the collect statement as shown =
below.
I want to have one table with one geometry_column. The geometry should=20
look like figure 1 - i did not get this result so far!! (The colors in=20
figure 1 are symbols for different attributes in the table). The=20
collect (instead of the geomunion) is a little bit closer to my=20
intention: geometries that intersect are now not totally combined (as=20
geomunion did), but in intersecting polygons only these parts are=20
separated which do not overlap. I want to have three (new) polygonss=20
(figure 1). Geomunion returns me one polygon, collect returns me two=20
polygons.........
As i tried to replace the geomunion by the intersection statement you=20
wrote to me i always get an error (i do not understand all of the query,=20
so i do not know how to integrate the intersection statement in my =
query).
drop table natura2000;
create table natura2000
(ffh_name character varying(80),ffh_land character varying(3),ffh=20
smallint,ffh_id smallint,
spa_name character varying(80),spa_land character varying(3),spa=20
smallint,spa_id smallint)
with oids;
select=20
addgeometrycolumn('','natura2000','the_geom','31467','MULTIPOLYGON',2);
alter table natura2000 drop constraint enforce_geotype_the_geom;
insert into natura2000=20
(ffh_name,ffh_land,ffh,ffh_id,spa_name,spa_land,spa,spa_id,the_geom)
select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
*collect(ffh_rep.the_geom, spa_rep.the_geom) as geomcollection*
from ffh_rep inner join spa_rep
on (ffh_rep.the_geom && spa_rep.the_geom and=20
intersects(ffh_rep.the_geom, spa_rep.the_geom))
union all
-- the second select gives you ffh_reps that have no spa_reps - your 15
select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
null as spa_name, null as spa_land, null as spa, null as spa_id,
ffh_rep.the_geom as the_geom
from ffh_rep left join spa_rep
on (ffh_rep.the_geom && spa_rep.the_geom and=20
intersects(ffh_rep.the_geom, spa_rep.the_geom))
where spa_rep.the_geom is null
union all
-- and the 3rd gives you spa_reps that have no ffh_reps. - your 40
select null as ffh_name, null as ffh_land, null as ffh, null as =
ffh_id,
spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
spa_rep.the_geom AS the_geom
from spa_rep left join ffh_rep
on (ffh_rep.the_geom && spa_rep.the_geom and=20
intersects(ffh_rep.the_geom, spa_rep.the_geom))
where ffh_rep.the_geom is null;
Obe, Regina schrieb:
> Not sure I quite understand the question. You mean to store these 3 =
geometries separately so you can color code as needed or overlay as =
needed?
>
> In that case you would have 3 separate geometry fields
>
> intersection(ffh_rep.the_geom, spa_rep.the_geom) As geomintersection, =
ffh_rep.the_geom As ffh_geom, spa_rep.the_geom As spa_geom
>
> For the ones where there is only udo or kai then I guess you can fill in =
the same geometry for all geometry fields.
>
>
> and then overlay them on your map as separate layers as needed
>
> or if you mean you just want it to look like figure 1 but not =
necessarily color coded, then you would do
>
> collect(ffh_rep.the_geom, spa_rep.the_geom) as geomcollection
>
> 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 =
Andreas Laggner
> Sent: Thursday, August 30, 2007 7:24 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Union of 7 datasets
>
> Moin Regina,
>
> the query you suggested is a very big step forward, but it does not=20
> exactly what i want to have ;-) ... it seems to me i will need an=20
> intersection in one part and not a geomunion!! But i want to ask you:
> what it does correctly: I now have a union of all geometries from both=20
> source datasets.
> If i have a single udo (ffh_rep) that intersects no kai (spa_rep) the=20
> row only has the attributes from udo. Same thing i have with single kai. =
> That is all correct.
> BUT: If udo and kai intersects i will not have new polygons!! For=20
> example: blue is udo (or ffh_rep) and yellow is kai (or spa_rep). I want =
> to have three polygons than with blue only the attributes from udo,=20
> yellow only the attributes from kai AND the intersection, polygon number =
> 3, the green one (blue with yellow points) with the attributes from udo =
> and kai (figure1). And now i have one Polygon having the attributes from =
> udo and kai (figure2).
>
> figure1:
> figure1
>
> figure2:
>
> figure2
>
>
> My query:
> drop table natura2000;
> create table natura2000
> (ffh_name character varying(80),ffh_land character varying(3),ffh=20
> smallint,ffh_id smallint,
> spa_name character varying(80),spa_land character varying(3),spa=20
> smallint,spa_id smallint)
> with oids;
> select=20
> addgeometrycolumn('','natura2000','newgeom','31467','MULTIPOLYGON',2);
> alter table natura2000 drop constraint enforce_geotype_newgeom;
>
> insert into natura2000=20
> (ffh_name,ffh_land,ffh,ffh_id,spa_name,spa_land,spa,spa_id,newgeom)
> select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
> spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
> geomunion(ffh_rep.the_geom, spa_rep.the_geom) as newgeom
> from ffh_rep inner join spa_rep
> on (ffh_rep.the_geom && spa_rep.the_geom and=20
> intersects(ffh_rep.the_geom, spa_rep.the_geom))
> union all
> -- the second select gives you ffh_reps that have no spa_reps - your =
15
> select ffh_rep.ffh_name,ffh_rep.ffh_land,ffh_rep.ffh,ffh_rep.ffh_id,
> null as spa_name, null as spa_land, null as spa, null as spa_id,
> ffh_rep.the_geom as newgeom
> from ffh_rep left join spa_rep
> on (ffh_rep.the_geom && spa_rep.the_geom and=20
> intersects(ffh_rep.the_geom, spa_rep.the_geom))
> where spa_rep.the_geom is null
> union all
> -- and the 3rd gives you spa_reps that have no ffh_reps. - your 40
> select null as ffh_name, null as ffh_land, null as ffh, null as =
ffh_id,
> spa_rep.spa_name,spa_rep.spa_land,spa_rep.spa,spa_rep.spa_id,
> spa_rep.the_geom AS newgeom
> from spa_rep left join ffh_rep
> on (ffh_rep.the_geom && spa_rep.the_geom and=20
> intersects(ffh_rep.the_geom, spa_rep.the_geom))
> where ffh_rep.the_geom is null;
>
> Cheers Andreas
>
>
>
> Obe, Regina schrieb:
> =20
>> Andreas,
>>
>> You would use the SQL UNION predicate like shown below (actually=20
>> slight correction - it is speedier to use UNION ALL especially when=20
>> you know there will not be dupiclates since it saves the processing of =
>> sorting to get a distinct UNION does an implicit distinct) - so I=20
>> have corrected below.
>>
>> - - I happened to insert comments in between which may have confused=20
>> you, 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.
>>
>> So would be
>> INSERT INTO sometable(field1, field2,field3,field4, newgeom)
>> SELECT udo.field1, udo.field2, kai.field3, kai.field4,=20
>> geomunion(udo.the_geom, kai.the_geom) AS newgeom
>> FROM udo INNER JOIN kai ON (udo.the_geom && kai.the_geom AND=20
>> intersects(udo.the_geom, kai.the_geom))
>> UNION ALL
>> -- the second select gives you udos that have no kais - your 15
>> SELECT udo.field1, udo.field2, NULL As field3, NULL As field4,=20
>> udo.the_geom AS newgeom
>> FROM udo LEFT JOIN kai ON (udo.the_geom && kai.the_geom AND=20
>> intersects(udo.the_geom, kai.the_geom))
>> WHERE kai.the_geom IS NULL
>> UNION ALL
>> -- and the 3rd gives you kais that have no udos. - your 40
>> SELECT NULL As field1, null As field2, kai.field3, kai.field4,=20
>> kai.the_geom AS newgeom
>> FROM kai LEFT JOIN udo ON (udo.the_geom && kai.the_geom AND=20
>> intersects(udo.the_geom, kai.the_geom))
>> WHERE udo.the_geom IS NULL;
>>
>> =20
>>
>> ----If FULL JOIN were to work (which in theory it should, but doesn't=20
>> seem to with Postgis functions (HINT HINT: would be nice if=20
>> that worked and can be easily fixed (but sadly I think the issue is=20
>> deeper than Postgis and Geos) - you could write the above much simpler =
as)
>>
>> INSERT INTO sometable(field1, field2,field3,field4, newgeom)
>> SELECT udo.field1, udo.field2, kai.field3, kai.field4,=20
>> COALESCE(geomunion(udo.the_geom, kai.the_geom), udo.the_geom,=20
>> kai.the_geom) AS newgeom
>> FROM udo FULL JOIN kai ON (udo.the_geom && kai.the_geom AND=20
>> intersects(udo.the_geom, kai.the_geom))
>>
>> Now if you have a lot of these and the tables are very similar in=20
>> 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=20
>> statement to execute either via a FOR loop and then executes the built =
>> SQL or set of SQL statements or you could do a similar thing in some=20
>> scripted language like perl or php.
>>
>> I take it SQL and pgsql and all that is fairly new to you so it might=20
>> be worthwhile (even though its a lot of typing) to do it the long cut=20
>> and paste way if nothing more than an exercise to get a feel of how=20
>> this all works and visualize the patterns at play.
>>
>> I don't get the sense that I comprehend your full problem. =20
>> Unfortunately I don't have any experience with ArcGIS/ArcView ways of=20
>> doing things, so I'm not quite sure if there is an equivalent way in=20
>> PostGIS/PostgreSQL world of doing the same kind of thing and what=20
>> exactly that thing is you are doing in ArcGIS.
>>
>> Union has 3 meanings in PostGIS/PostgreSQL (actually stuff your=20
>> favorite spatial/DB here - all non-trivial spatial relational dbs=20
>> behave more or less the same) =20
>>
>> 1) unioning of record sets (standard ANSI SQL UNION/UNION ALL) - which =
>> is simply a way of stringing together a bunch of selects into a=20
>> single result set as shown above
>>
>> 2) unioning of 2 geometry fields like shown above with geomunion
>>
>> 3) Aggregate geomunion - aggregate variant of the above geomunion=20
>> function that groups and unions a whole setof geometries together but=20
>> requires you are grouping by some field or set of fields).
>>
>> I must also mention there is collect (non-aggregate and aggregate=20
>> function) which often times is just as effective as the geomunion and=20
>> in general much faster processor wise.
>>
>> Hope that helps,
>>
>> Regina
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net=20
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of=20
>> Andreas Laggner
>> Sent: Tuesday, August 28, 2007 9:13 AM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] Union of 7 datasets
>>
>> Obe, Regina schrieb:
>> =20
>>> Sounds like you would have to go with a full join type thing with=20
>>> =20
>> workaround I described below (last example). So If I understand you=20
>> correctly then something like this -=20
>> =20
>>> --the first select gives you those records in both tables your 25 =
udokai
>>> SELECT udo.field1, udo.field2, kai.field3, kai.field4,=20
>>> =20
>> geomunion(udo.the_geom, kai.the_geom) AS newgeom
>> =20
>>> FROM udo INNER JOIN kai ON (udo.the_geom && kai.the_geom AND=20
>>> =20
>> intersects(udo.the_geom, kai.the_geom))
>> =20
>>> UNION
>>> -- the second select gives you udos that have no kais - your 15
>>> SELECT udo.field1, udo.field2, NULL As field3, NULL As field4,=20
>>> =20
>> udo.the_geom AS newgeom
>> =20
>>> FROM udo LEFT JOIN kai ON (udo.the_geom && kai.the_geom AND=20
>>> =20
>> intersects(udo.the_geom, kai.the_geom))
>> =20
>>> WHERE kai.the_geom IS NULL
>>> UNION
>>> -- and the 3rd gives you kais that have no udos. - your 40
>>> SELECT nul As field1, null As field2, kai.field3, kai.field4,=20
>>> =20
>> kai.the_geom AS newgeom
>> =20
>>> FROM kai LEFT JOIN udo ON (udo.the_geom && kai.the_geom AND=20
>>> =20
>> intersects(udo.the_geom, kai.the_geom))
>> =20
>>> WHERE udo.the_geom IS NULL
>>>
>>> Unioned together you should get
>>> 25 + 15 + 40 =3D ? 80
>>>
>>> Hope that helps,
>>> Regina
>>>
>>> =20
>>> =20
>> UFF - that seems to be a complex question, a comprehensive operation =
and
>> a lot to type too!!! Thanks a million - i would have spent many time =
to
>> find out this query by myself! Ok..... how can i perform your "UNION": =
I
>> would just insert my selects one after another in one new table - will
>> that work well?
>> I want to aggregate around 20 datasets this way!? Do you know a method
>> to operate with more datasets (for example 7) in a effektiv manner and
>> not to do this three selects 6 times?
>> AND: Am i totally wrong with my aims? It seems to me that is an exotic
>> think to do with PostGis, but our projects/problems require to =
aggregate
>> datasets in almost all cases (around 90%) and that is exactly what one
>> of the basic functions "Union" in ArcView already carried out ten =
years
>> ago. So i would exspect there is a function to perform this operation
>> easier!? .....i just wonder.....
>>
>>
>> cheers Andreas
>>
>> =20
>>> -----Original Message-----
>>> From: postgis-users-bounces at postgis.refractions.net=20
>>> =20
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of=20
>> Andreas Laggner
>> =20
>>> Sent: Tuesday, August 28, 2007 7:28 AM
>>> To: PostGIS Users Discussion
>>> Subject: Re: [postgis-users] Union of 7 datasets
>>>
>>> Obe, Regina schrieb:
>>> =20
>>> =20
>>>> BASIC TRICK: If you want to get all records with no matching=20
>>>> =20
>> including those that match - put what you would normally put in your=20
>> WHERE clause in the JOIN clause and use a LEFT JOIN.
>> =20
>>>> =20
>>>> =20
>>> ok - it seems to me i need some coaching......Yes, I want to get all
>>> records with no matching including those that match, but i also want =
to
>>> dissect the polygons that matches. I will try to explain again just =
to
>>> be sure you understood my aims: I have 20 polygons in udo and 50 in =
kai.
>>> 10 from kai are intersecting 5 from udo to 25 new polygons i call =
udokai
>>> (because they have attributes from udo AND kai). My result should =
have:
>>> 15 polygons with attributes only from udo, 40 polygons with =
attributes
>>> only from kai AND 25 with attributes from udo and kai! In most cases =
i
>>> want to do such a operation because i want to aggregate (spatial
>>> correct) different datasets!
>>> Which example from you fits best for this aim? I did not find any
>>> information on how left join works on the postgis or postgresql=20
>>> =20
>> reference...
>> =20
>>> =20
>>> =20
>>>> Unfortunately as I have come across before if you need an either or=20
>>>> =20
>> (if in table 1 or table 2 - ideally you would use a FULL JOIN but for=20
>> 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=20
>> workaround using a set of UNIONS.
>> =20
>> =
--------------------------------------------------------------------------=
>> =20
>>>> Simplest case - get all records in g1 one or union of g1 and g2=20
>>>> =20
>> that intersect
>> =20
>>>> NOTE: COALESCE is an ANSI SQL function that will return the first=20
>>>> =20
>> non-null - when you do a geomunion of a geometry and null you get null =
>> which is why we need COALESCE
>> =20
>>>> SELECT g1.field1, g1.field2, COALESCE(geomunion(g1.the_geom,=20
>>>> =20
>> g2.the_geom), g1.the_geom) AS newgeom
>> =20
>>>> FROM g1 LEFT JOIN g2 ON (g1.the_geom && g2.the_geom AND=20
>>>> =20
>> intersects(g1.the_geom, g2.the_geom))
>> =20
>>>> =20
>> =
---------------------------------------------------------------------------=
--------------
>> =20
>>>> Either of case - get all geometries in g1 or g2 or union if there=20
>>>> =20
>> is a match - workaround for full joins not working right
>> =20
>>>> SELECT g1.field1, g1.field2, geomunion(g1.the_geom, g2.the_geom) AS=20
>>>> =20
>> newgeom
>> =20
>>>> FROM g1 INNER JOIN g2 ON (g1.the_geom && g2.the_geom AND=20
>>>> =20
>> intersects(g1.the_geom, g2.the_geom))
>> =20
>>>> UNION
>>>> SELECT g1.field1, g1.field2, g1.the_geom AS newgeom
>>>> FROM g1 LEFT JOIN g2 ON (g1.the_geom && g2.the_geom AND=20
>>>> =20
>> intersects(g1.the_geom, g2.the_geom))
>> =20
>>>> WHERE g2.the_geom IS NULL
>>>> UNION
>>>> SELECT g1.field1, g1.field2, g2.the_geom AS newgeom
>>>> FROM g2 LEFT JOIN g1 ON (g1.the_geom && g2.the_geom AND=20
>>>> =20
>> intersects(g1.the_geom, g2.the_geom))
>> =20
>>>> WHERE g1.the_geom IS NULL
>>>>
>>>>
>>>>
>>>> Hope that helps,
>>>> Regina
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: postgis-users-bounces at postgis.refractions.net=20
>>>> =20
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of=20
>> Andreas Laggner
>> =20
>>>> Sent: Thursday, August 23, 2007 11:32 AM
>>>> To: PostGIS Users Discussion
>>>> Subject: Re: [postgis-users] Union of 7 datasets
>>>>
>>>> Obe, Regina schrieb:
>>>> =20
>>>> =20
>>>> =20
>>>>> Oh the g1 g2 .. was just for example - I don't actually call my=20
>>>>> =20
>> tables meaningless names like that.=20
>> =20
>>>>> You should be doing a join on something or have a where clause=20
>>>>> =20
>> unless one of your tables has only one record. Otherwise you are=20
>> 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=20
>> and m is the number in second table. This is generally a big NO NO. =20
>> In certain rare cases you do want to do something like that, but is=20
>> usually the exception.
>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>> I think the records in my targed table must be added (more or less) =
and
>>>> not multiplied! My Aim is a table that contains the areas of all the =
7
>>>> sourcetables and the information which refuges are inside and wich =
not.
>>>> Perhaps i must use the intersection!? If i do my query with a gist =
like
>>>> this: where t1.the_geom && t2.the_geom; than the operation is very =
fast
>>>> (about one minute) but i only have the Polygons covered by BOTH
>>>> datasets, and i want to have as well those, which are covered by one
>>>> dataset only!! But my operation without the where clause runs for 4
>>>> hours now - that shows me there is something wrong ;-)
>>>> =20
>>>> =20
>>>> =20
>>>>> Its hard for me to tell if you need a cartesian product in this=20
>>>>> =20
>> case since I'm not quite sure what for example nature and biosphere=20
>> represent. I would guess that is wrong and you should first figure=20
>> out which sets of say nature records you need to geomunion with=20
>> biosphere and then join by that field or set of fields.
>> =20
>>>>> It would help a bit if you could provide some sample questions you=20
>>>>> =20
>> expect to answer with your statistical analysis. My guess is you may=20
>> be better off with more than one table.
>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>> Sample question: give me all areas (all polygons) from germany where
>>>> landuse=3Darable land and soils=3Dgood and precipitation>600 and any =
(of 7)
>>>> reserves and so on.......
>>>> I need the values in my table to calculate the potential yield or =
other
>>>> things...
>>>> And i want to analyse such questions with a statistical software =
(SAS),
>>>> so it seems to me i need one table to import in SAS (or to query =
from
>>>> SAS directly to the postgresql).
>>>>
>>>> Thanks for your help, i will be back in my office in=20
>>>> =20
>> Monday.......Andreas
>> =20
>>>> =20
>>>> =20
>>>> =20
>>>>> Which structure is best really boils down to what questions you=20
>>>>> =20
>> hope to answer because one approach may make one question easy and=20
>> fast and another question slow and cumbersome.
>> =20
>>>>> Hope that helps,
>>>>> Regina
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: postgis-users-bounces at postgis.refractions.net=20
>>>>> =20
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of=20
>> Andreas Laggner
>> =20
>>>>> Sent: Thursday, August 23, 2007 10:04 AM
>>>>> To: PostGIS Users Discussion
>>>>> Subject: Re: [postgis-users] Union of 7 datasets
>>>>>
>>>>> Obe, Regina schrieb:
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>>> Andreas,
>>>>>>
>>>>>> It would help to know what your table structure looks like and=20
>>>>>> =20
>> why do you want to put them all in a single geometry?
>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>> My table structures are a little bit different. I want to have=20
>>>>> =20
>> them in a
>> =20
>>>>> single geometry to intersect them with other data and built a large
>>>>> table to run statistics over it (production site analysis over=20
>>>>> =20
>> germany).
>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>>> I'm imaging you are you doing something like
>>>>>>
>>>>>> SELECT g1.somefield, geomunion(geomunion(g1.the_geom,=20
>>>>>> =20
>> g2.the_geom), g3.the_geom)
>> =20
>>>>>> FROM g1 INNER JOIN g2 on g1.somefield =3D g2.somefield INNER JOIN=20
>>>>>> =20
>> g3 on g2.somefield =3D g3.somefield
>> =20
>>>>>> GROUP BY g1.somefield
>>>>>>
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>> That=B4s an interesting method with inner join..why go you call =
your
>>>>> tables g1. g2. and so on?
>>>>> That=B4s my method i am using right now (geomunion 1 to 3 from 6), =
seems
>>>>> to be a pedestrian method :-(
>>>>>
>>>>> create table natura2000
>>>>> (ffh_name character varying(80), ffh_land character=20
>>>>> =20
>> varying(3), ffh
>> =20
>>>>> smallint, ffh_id smallint,
>>>>> spa_name character varying(80), spa_land character=20
>>>>> =20
>> varying(3), spa
>> =20
>>>>> smallint, spa_id smallint) with oids;
>>>>> select
>>>>>
>>>>> =20
>> =
addgeometrycolumn('','natura2000','the_geom','31467','MULTIPOLYGON',2);
>> =20
>>>>> alter table natura2000 drop constraint enforce_geotype_the_geom;
>>>>> insert into natura2000
>>>>> select
>>>>>
>>>>> =20
>> =
t1.ffh_name,t1.ffh_land,t1.ffh,t1.ffh_id,t2.spa_name,t2.spa_land,t2.spa,t2.=
spa_id,
>> =20
>>>>> geomunion(t1.the_geom, t2.the_geom)
>>>>> from ffh_rep t1, spa_rep t2;
>>>>>
>>>>> create table sg71
>>>>> (ffh_name character varying(80), ffh_land character=20
>>>>> =20
>> varying(3), ffh
>> =20
>>>>> smallint, ffh_id smallint,
>>>>> spa_name character varying(80), spa_land character=20
>>>>> =20
>> varying(3), spa
>> =20
>>>>> smallint, spa_id smallint,
>>>>> bio_name character varying(70), bio smallint, bio_id=20
>>>>> =20
>> smallint) with
>> =20
>>>>> oids;
>>>>> select=20
>>>>> =20
>> addgeometrycolumn('','sg71','the_geom','31467','MULTIPOLYGON',2);
>> =20
>>>>> alter table sg71 drop constraint enforce_geotype_the_geom;
>>>>> insert into sg71
>>>>> select t1.ffh_name, t1.ffh_land, t1.ffh, t1.ffh_id, =
t1.spa_name,
>>>>> t1.spa_land, t1.spa, t1.spa_id,
>>>>> t2.name,t2.bio,t2.bio_id,geomunion(t1.the_geom,=20
>>>>> =20
>> t2.the_geom)
>> =20
>>>>> from natura2000 t1, biosphere t2;
>>>>>
>>>>> create table sg72
>>>>> (ffh_name character varying(80), ffh_land character =
varying(3),
>>>>> ffh smallint, ffh_id smallint,
>>>>> spa_name character varying(80), spa_land character varying(3), =
spa
>>>>> smallint, spa_id smallint,
>>>>> bio_name character varying(70), bio smallint, bio_id smallint,
>>>>> np_name character varying(60), np smallint, np_id smallint)=20
>>>>> =20
>> with oids;
>> =20
>>>>> select=20
>>>>> =20
>> addgeometrycolumn('','sg72','the_geom','31467','MULTIPOLYGON',2);
>> =20
>>>>> alter table sg72 drop constraint enforce_geotype_the_geom;
>>>>> insert into sg72
>>>>> select t1.ffh_name, t1.ffh_land, t1.ffh, t1.ffh_id, =
t1.spa_name,
>>>>> t1.spa_land, t1.spa, t1.spa_id,
>>>>> t1.bio_name,t1.bio,t1.bio_id,t2.np_name,t2.np,t2.np_id,
>>>>> geomunion(t1.the_geom, t2.the_geom)
>>>>> from sg71 t1, np t2;
>>>>> AND SO ON......
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>>> or
>>>>>>
>>>>>> SELECT g1.somefield, geomunion(gt.the_geom)
>>>>>> FROM (SELECT somefield, the_geom FROM g1 UNION SELECT somefield,=20
>>>>>> =20
>> the_geom FROM g2 ...) gt
>> =20
>>>>>> GROUP BY gt.somefield
>>>>>>
>>>>>>
>>>>>> If I have 7 different tables that have pretty much the same=20
>>>>>> =20
>> structure, but for logistical or other technical reasons (such as each =
>> has additional attributes distinct from one another), I need to keep=20
>> 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=20
>> the parent table and it will automatically drill down to the child=20
>> tables. Not sure if that helps more than it confuses your situation.
>> =20
>>>>>> Then instead of the above I can simply do
>>>>>>
>>>>>> SELEG myparenttable.somefield, geomunion(myparenttable.the_geom)
>>>>>> FROM myparenttable
>>>>>> GROUP by gh.somefield
>>>>>>
>>>>>>
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>> ok - i have to think about your suggestions......that=B4s my second =
week
>>>>> with postgis.
>>>>> Can you tell me from my SQL-Statements which method will be best? So =
i
>>>>> try to understand that one.....
>>>>>
>>>>> Thanks for your reply!!!
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>>> Hope that helps,
>>>>>> Regina
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: postgis-users-bounces at postgis.refractions.net=20
>>>>>> =20
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of=20
>> Andreas Laggner
>> =20
>>>>>> Sent: Thursday, August 23, 2007 9:11 AM
>>>>>> To: PostGis_Mailinglist
>>>>>> Subject: [postgis-users] Union of 7 datasets
>>>>>>
>>>>>> Hi users,
>>>>>>
>>>>>> i want to put together 7 datasets to have all the different=20
>>>>>> =20
>> refuges in
>> =20
>>>>>> one table (and in one geometry). Am i doing right with 6 times=20
>>>>>> =20
>> geomunion
>> =20
>>>>>> (that=B4s much to type with all the attributes) or is there a more
>>>>>> effective way?
>>>>>>
>>>>>> cheers Andreas
>>>>>>
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>>> =20
>>>> =20
>>>> =20
>>>> =20
>>> =20
>>> =20
>> --
>> Dipl. Geo=F6kologe Andreas Laggner
>> Institut f=FCr L=E4ndliche R=E4ume (LR)
>> Bundesforschungsanstalt f=FCr Landwirtschaft (FAL)
>>
>> Institute of Rural Studies
>> Federal Agricultural Research Centre (FAL)
>>
>> Bundesallee 50
>> D-38116 Braunschweig
>>
>> Tel.: (+49) (0)531 596 5515
>> Fax: (+49) (0)531 596 5599
>> E-mail: andreas.laggner at fal.de
>> Homepage: http://www.lr.fal.de/
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>> =
------------------------------------------------------------------------
>>
>> *The substance of this message, including any attachments, may be=20
>> confidential, legally privileged and/or exempt from disclosure=20
>> pursuant to Massachusetts law. It is intended solely for the=20
>> addressee. If you received this in error, please contact the sender=20
>> and delete the material from any computer. *
>>
>> =
------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> =20
>> =20
>
>
> =20
--=20
Dipl. Geo=F6kologe Andreas Laggner
Institut f=FCr L=E4ndliche R=E4ume (LR)
Bundesforschungsanstalt f=FCr Landwirtschaft (FAL)
Institute of Rural Studies
Federal Agricultural Research Centre (FAL)
Bundesallee 50
D-38116 Braunschweig
Tel.: (+49) (0)531 596 5515
Fax: (+49) (0)531 596 5599
E-mail: andreas.laggner at fal.de
Homepage: http://www.lr.fal.de/=20
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list