[postgis-users] Union of 7 datasets
Obe, Regina
robe.dnd at cityofboston.gov
Mon Sep 10 07:41:39 PDT 2007
Andreas,
What error are you getting with the intersection?
I'm guessing that what your problem here is more of a presentation issue than anything else and you are trying to use one layer to present multiple layers.
What software are you using to display the geometries?
I'm guessing that what you want to do is keep these as 3 separate geometry layers and then overlay them
on your map as needed. I think you are trying to get postgis to store symbology in with the geometry (e.g. colors etc.) which is really not what its designed for.
With that said, there are 2 ways that come to mind for dealing with this
1) You could keep your uber table - but have 3 separate geometry fields for each thing you want to have different symbology for. In your map you would just treat these as 3 separate layers even though you are selecting from the same table.
2) The more flexible way is to just keep them in 3 or more separate tables and don't try to mix apples with oranges.
Then in your map you just overlay them as separate layers. Your map should automagically do the right thing since it would be grabbing the data that is in the extent and (if smarter reproject if it is in the wrong projection) of the section of the map you are looking at. No need for join or anything. Just blindly overlay them on the map.
Now your non-presentation questions are a bit trickier to handle. I'm sensing that you are trying answer too much with one uber table.
One of the main benefits of a relational system over a single flat table is that, (to take a page from my Mechanical Engineering education), it allows you to have more joints.
You can think of each table as representing a different joint. The more joints you have the move movements ,in this case - questions, you can answer. Of course there is a point at which having too many joints makes movement clumsy and hard to control so you have to balance the 2 opposing forces.
What you are creating with your uber table - is a very rigid system that is as complicated to maintain as the most complicated question it was designed to answer and that can answer only the questions it was specifically designed for. With a multi-joint system you can answer even unthought of questions by moving your joints in different directions.
- lets take one question you posed to me off-list.
How many hectares are in germany with forest on it AND good soils AND more than 600mm per year precipitation AND in one of the 7 reserve areas?
If you had your set split into study_area, forest, soils, reserve_areas
1) Study_area - has the most number of polygons and very granular (they could be very small quads) - basically a grid of germany and for each square it has precipitation among other interesting things.
now to answer the above, you would do something like
SELECT sum(area(study_area.the_geom)) As totarea, collect(study_area.the_geom) As geomspaceofinterest,
extent(study_area.the_geom) As extentofinterest
FROM study_area INNER JOIN reserve_area ON study_area.the_geom && reserve_area.the_geom AND within(study_area.the_geom, reserve_area)
WHERE EXISTS(SELECT forest.the_geom FROM forest
WHERE study_area.the_geom && forest.the_geom AND overlaps(study_area.the_geom, forest.the_geom))
AND EXISTS(SELECT soils.the_geom FROM soils
WHERE study_area.the_geom && soils.the_geom AND overlaps(study_area.the_geom, soils.the_geom) AND soils.quality = 'good')
The above would give you the study area space of interest (the extent would let you know the smallest area to zoom into your map) and you can also display on the map and if you overlayed the other areas in the map
you would get the attributes from the other tables if you layer each of those.
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: Monday, September 10, 2007 8:28 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Union of 7 datasets
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/
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
From MAILER-DAEMON Mon Sep 10 07:43:34 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 08DC22C198E
for <postgis-users at postgis.refractions.net>;
Mon, 10 Sep 2007 07:43:33 -0700 (PDT)
Received: from [195.4.92.18] (helo=mx8.freenet.de)
by mout1.freenet.de with esmtpa (Exim 4.68-dev) id 1IUkW7-0006aF-1G
for postgis-users at postgis.refractions.net;
Mon, 10 Sep 2007 16:45:35 +0200
Received: from p549c2050.dip0.t-ipconnect.de ([84.156.32.80]:35982
helo=dvise.alpstein.de)
by mx8.freenet.de with esmtpa (ID alpstein1 at freenet.de) (port 25) (Exim
4.68-dev #12) id 1IUkVu-0000GL-5W
for postgis-users at postgis.refractions.net;
Mon, 10 Sep 2007 16:45:34 +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 14:45:21 +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: <0004B7E2.46E5749A 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 14:43:34 -0000
Sehr geehrter Absender,
vielen Dank für Ihre Email.
Leider kann ich Ihre Nachricht im Moment nicht persönlich
beantworten, da ich mich bis zum 21.09.2007 im Urlaub befinden.
Nach meiner Rückkehr am 24.09.2007 werde ich gerne Ihre Anfrage beantworten.
Bitte wenden Sie sich in dringenden Fällen an Herrn Jens Schwarz, jens.schwarz at alpstein.de oder per Telefon 08323-8006-0.
Mit freundlichen Grüßen
Hubert Burger
Alpstein GmbH
Missener Str. 18
87509 Immenstadt
fon +49 8323 8006-0
fax +49 8323 8006-50
web www.alpstein.de
Andreas,
What error are you getting with the intersection?
I'm guessing that what your problem here is more of a presentation issue than anything else and you are trying to use one layer to present multiple layers.
What software are you using to display the geometries?
I'm guessing that what you want to do is keep these as 3 separate geometry layers and then overlay them
on your map as needed. I think you are trying to get postgis to store symbology in with the geometry (e.g. colors etc.) which is really not what its designed for.
With that said, there are 2 ways that come to mind for dealing with this
1) You could keep your uber table - but have 3 separate geometry fields for each thing you want to have different symbology for. In your map you would just treat these as 3 separate layers even though you are selecting from the same table.
2) The more flexible way is to just keep them in 3 or more separate tables and don't try to mix apples with oranges.
Then in your map you just overlay them as separate layers. Your map should automagically do the right thing since it would be grabbing the data that is in the extent and (if smarter reproject if it is in the wrong projection) of the section of the map you are looking at. No need for join or anything. Just blindly overlay them on the map.
Now your non-presentation questions are a bit trickier to handle. I'm sensing that you are trying answer too much with one uber table.
One of the main benefits of a relational system over a single flat table is that, (to take a page from my Mechanical Engineering education), it allows you to have more joints.
You can think of each table as representing a different joint. The more joints you have the move movements ,in this case - questions, you can answer. Of course there is a point at which having too many joints makes movement clumsy and hard to control so you have to balance the 2 opposing forces.
What you are creating with your uber table - is a very rigid system that is as complicated to maintain as the most complicated question it was designed to answer and that can answer only the questions it was specifically designed for. With a multi-joint system you can answer even unthought of questions by moving your joints in different directions.
- lets take one question you posed to me off-list.
How many hectares are in germany with forest on it AND good soils AND more than 600mm per year precipitation AND in one of the 7 reserve areas?
If you had your set split into study_area, forest, soils, reserve_areas
1) Study_area - has the most number of polygons and very granular (they could be very small quads) - basically a grid of germany and for each square it has precipitation among other interesting things.
now to answer the above, you would do something like
SELECT sum(area(study_area.the_geom)) As totarea, collect(study_area.the_geom) As geomspaceofinterest,
extent(study_area.the_geom) As extentofinterest
FROM study_area INNER JOIN reserve_area ON study_area.the_geom && reserve_area.the_geom AND within(study_area.the_geom, reserve_area)
WHERE EXISTS(SELECT forest.the_geom FROM forest
WHERE study_area.the_geom && forest.the_geom AND overlaps(study_area.the_geom, forest.the_geom))
AND EXISTS(SELECT soils.the_geom FROM soils
WHERE study_area.the_geom && soils.the_geom AND overlaps(study_area.the_geom, soils.the_geom) AND soils.quality = 'good')
The above would give you the study area space of interest (the extent would let you know the smallest area to zoom into your map) and you can also display on the map and if you overlayed the other areas in the map
you would get the attributes from the other tables if you layer each of those.
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: Monday, September 10, 2007 8:28 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Union of 7 datasets
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/
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
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