[postgis-users] ERROR: ExteriorRing: geom is not a polygon
Andreas Laggner
andreas.laggner at fal.de
Tue Oct 2 07:26:05 PDT 2007
ok - it worked now - so i will get my multipolygons in polygons. I
created a new table, but so far i only have the polygons in there that
were multipolygons before. How can i select my polygons from ffh_rep to
insert them in the new table?
I tried this, but result is empty.......
SELECT count(gid) As totrecords, ST_GeometryType(the_geom) as the_type
FROM ffh_rep
WHERE ST_NumGeometries(the_geom) = 1
GROUP BY ST_GeometryType(the_geom)
cheers Andreas
Obe, Regina schrieb:
> Oops I guess I was missing a ) and had a typo in the name. Try the below instead
>
> SELECT ST_ExteriorRing(ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom))))
> FROM ffh_rep
>
> Yes it should run without any other statements.
>
> -----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 8:21 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] ERROR: ExteriorRing: geom is not a polygon
>
> 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