[postgis-users] Union of 7 datasets

[Andreas Laggner]

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/