[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: RE: [postgis-users] Union of 7 datasets
From: "Obe, Regina" <robe.dnd () cityofboston ! gov>
Date: 2007-09-26 13:20:52
Message-ID: 53F9CF533E1AA14EA1F8C5C08ABC08D20283C841 () ZDND ! DND ! boston ! cob
[Download RAW message or body]
I didn't read your last message before I sent this one. Anyrate below I made a typo \
should be
st_intersects(ffh, spas) and st_is_empty(st_difference(ffh, spa)) = false
Kevin's I suspect is more accurate since with mine, you will probably get extra \
records if it returns at all the right result and if it does return somewhat accurate \
you would probably have to do an additional collect - wrap the whole set of unions \
into something like
SELECT ffhs, spas, st_collect(DISTINCT newset.geomcollection) as newgeom
FROM
(big old set of unions goes here) as newset
GROUP BY newset.ffhs, newset.spas (excluding the geom column)
-----Original Message-----
From: postgis-users-bounces@postgis.refractions.net \
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of \
Obe, Regina
Sent: Wednesday, September 26, 2007 9:11 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Union of 7 datasets
Sorry Andreas,
I would have expected if anything that the parts that intersect would have attributes \
from spa and ffh Some parts that don't intersect but are part of \
overlapping/intersecting polygons would have attributes from both (which is wrong) \
And the others that are not part of intersecting polygons would have only ffh or spa \
attributes.
Perhaps you can send us some sample datasets results.
So the only problem join I see would be the first one (our intersection one). That \
one I would think we would want to break out further into (psuedo syntax below)
ffhs , spas
, st_intersection(ffh, spa)
where st_intersects(ffh,spas)
union all
ffh, null(spas), st_difference(ffh, spa)
where st_intersects(ffh, spas) && st_is_empty(st_difference(ffh, spa)) = false
union all
null(ffhs), spas, st_difference(spa, ffh)
where st_intersects(ffh, spas) && st_is_empty(st_difference(spa, ffh)) = false
Note if you are using an older version of postgis you may need to replace
st_intersects with ffh && spa AND intersects(ffh, spas)
and get rid of the other st_
Also I must add that my geometry pinnings are a bit weak so I probably have all my \
definitions about overlaps and intersects mixed up. Here is my basic understanding \
of geometric concepts so perhaps someone can correct me if I am wrong in any part \
which most likely I am
1) A intersects B if some part of A and B are shared.
2) A overlaps B only if the intersection of A and B is not A or B or the empty set - \
(meaning A can't be fully contained in B and B can't be fully contained in A but \
parts of A or B are shared).
Of course this throws out my concept of why A && B returns true when A and B have the \
same bounding box but st_overlaps(A,B) is false when A and B are the same polygon. - \
I always thought A&&B means overlaps boundary boxes which seems closer match the \
definition of intersects. So I am thoroughly confused.
3) difference(A,B) will return that part of A that is not within B.
Thanks,
Regina
-----Original Message-----
From: postgis-users-bounces@postgis.refractions.net \
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of \
Andreas Laggner
Sent: Tuesday, September 25, 2007 9:41 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Union of 7 datasets
Hi Regina,
your last hint with collect brings me closer to my goal!!
I ran this query now - my geometries are exactly what i wanted to have!
There only problem left is: If polygons intersect, they always have the
attributes from spa only, never from ffh. Parts, that contain spa AND
ffh only have the spa-attributes, and this parts from a overlapping
polygon, that are only ffh, have no attributes at all.
Can you find the error in the query?
cheers Andreas
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@postgis.refractions.net \
> [mailto:postgis-users-bounces@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@postgis.refractions.net
> > [mailto:postgis-users-bounces@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@postgis.refractions.net
> > >
> > [mailto:postgis-users-bounces@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@postgis.refractions.net
> > > >
> > [mailto:postgis-users-bounces@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@postgis.refractions.net
> > > > >
> > [mailto:postgis-users-bounces@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@postgis.refractions.net
> > > > > >
> > [mailto:postgis-users-bounces@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@fal.de
> > Homepage: http://www.lr.fal.de/
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@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@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@fal.de
Homepage: http://www.lr.fal.de/
_______________________________________________
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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic