[postgis-users] ERROR: ExteriorRing: geom is not a polygon

Andreas Laggner andreas.laggner at fal.de
Tue Oct 2 05:20:45 PDT 2007


Hi Regina,

the first query gave me 595 hits (from 27303 total). 594 times there are 
2 polygons in the multipolygon, 1 time there are 3.
So i have 26708 polygons and 595 Multipolygons, right?

should this query run without any other statements?
SELECT ST_ExterionRing(ST_GeometryN(the_geom, generate_series(1, 
ST_NumGeometries(the_geom)))
FROM ffh_rep

I get the Error at the FROM statement:
ERROR: syntax error at or near "FROM"
SQL Status:42601
Zeichen:96

cheers      Andreas


Obe, Regina schrieb:
> Andreas,
>
> You can use ST_NumGeometries
>
> If you just want to know the count of records that have more than one polygon in the multipolygon - then do something like - note for below I am also including ST_GeometryType which will tell you if the geometry field is a Multipolygon or geometry collection or multilinestring etc.  If it's a multilinestring then the ST_NumGeometries tells you the number of line strings, if it's a geometry collection - then it could be a mish mash of anything like opther multipolygons etc.  Hopefully you've only got multipolygons or polygons in there.
>
> SELECT count(gid) As totrecords, ST_GeometryType(the_geom) as the_type
> FROM sometable
> WHERE ST_NumGeometries(the_geom) > 1
> GROUP BY ST_GeometryType(the_geom)
>
>
> If you want to know the actual records
>
> SELECT * , ST_NumGeometries(the_geom) As numPolygons
> FROM sometable
> WHERE ST_NumGeometries(the_geom) > 1
>
> Will tell you what multipolygons have more than one polygon in each multipolygon
>
>
> 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, October 02, 2007 6:45 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] ERROR: ExteriorRing: geom is not a polygon
>
> Hi Regina,
>
> how can i be sure to have only one polygon in each multipolygon in a 
> dataset with thousends of polygons? Exists a function to verify that?
>
> hasta luego      Andreas
>
>
> Obe, Regina schrieb:
>   
>> Yes you do.  ST_ExteriorRing only deals with Polygons not multipolygons or geometry collections.
>>
>> There are 2 ways to do it.
>>
>> 1) If you have a multipolygon and you are sure you only have one polygon in each multipolygon, then you can do
>>
>> SELECT ST_ExteriorRing(ST_GeometryN(the_geom,1))
>> FROM ffh_rep 
>>
>> 2) If you really have multipolygons and you want the exterior ring of each polygon - it's a bit trickier.  Try
>>
>> SELECT ST_ExterionRing(ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))
>> FROM ffh_rep
>>
>> If you have a set of GeometryCollection hmm that's even trickier.  I would guess 2 layers of series - let me know if that is the case.
>>
>> 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: Wednesday, September 26, 2007 7:00 AM
>> To: PostGIS Users Discussion
>> Subject: [postgis-users] ERROR: ExteriorRing: geom is not a polygon
>>
>> Moin users,
>>
>> i get an error starting with this query (i want to overlay tables 
>> according to this 
>> http://postgis.refractions.net/support/wiki/index.php?ExamplesOverlayTables
>>
>> CREATE TEMP TABLE all_lines AS
>>    SELECT St_ExteriorRing (the_geom) AS the_geom FROM ffh_rep
>>    UNION ALL
>>    SELECT St_ExteriorRing (the_geom) AS the_geom FROM spa_rep;
>>
>> ERROR: ExteriorRing: geom is not a polygon
>>
>> do i have to force my source data to be polygons? How can i do that?
>>
>> cheers      Andreas
>>
>>
>>
>>
>>
>> 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/ 




More information about the postgis-users mailing list