[postgis-users] problem creating and exporting a polygon withholeusing pgsql2shp

[Ghislain Geniaux]

I've found the solution : it's the export routine pgsql2shp that  
needs a table with at least 3 variables : a primary key (gid in my  
table), a geometry  (the_geom in my table) and a third variable  
whatever is this variable in order to produce a dbf file that is not  
empty.
With the following code, it's ok :

create table temp as select  r.gid, ST_Difference 
(r.the_geom,i.the_geom) as the_geom,area2D(ST_Difference 
(r.the_geom,i.the_geom)) as area  from recouv as r, recouv as i where  
i.gid=2 and r.gid=1;
ALTER TABLE temp ADD PRIMARY KEY (gID);
\q
pgsql2shp -f ext.shp conversion 'select * from matable2'

Thanks.

Ghislain.

Le 12 mars 08 à 15:15, Obe, Regina a écrit :

> Could be your export routine then.  I don't think ' is legal.  Need  
> to use double-quotes (or at least on windows) for the SQL. You can  
> also leave out the .shp although I guess its harmless.
>
> Try
> pgsql2shp -f ext conversion "select * from matable2"
>
> Hope that helps,
> Regina
>
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis- 
> users-bounces at postgis.refractions.net] On Behalf Of Ghislain Geniaux
> Sent: Wednesday, March 12, 2008 8:47 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] problem creating and exporting a  
> polygon withholeusing pgsql2shp
>
> Thanks for your answer Regina,
>
> but ST_Difference does not solve the problem :
> ST_Difference and ST_Symdifference give the same polygon but the  
> export by pgsql2shp failled.
>
> conversion=# create table temp as
> conversion-# select  r.gid, ST_Difference(r.the_geom,i.the_geom) as  
> the_geom from recouv as r, recouv as i where i.gid=2 and r.gid=1;
> SELECT
> conversion=# ALTER TABLE temp ADD PRIMARY KEY (gID);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index  
> "temp_pkey" for table "temp"
> ALTER TABLE
> conversion=# SELECT geometrytype(the_geom), Nrings(the_geom), area2d 
> (the_geom) from temp;
>  geometrytype | nrings |      area2d
> --------------+--------+------------------
>  POLYGON      |      2 | 2177713.02017212
> (1 row)
>
>
> conversion=# create table temp as
> conversion-# select  r.gid, ST_symDifference(r.the_geom,i.the_geom)  
> as the_geom from recouv as r, recouv as i where i.gid=2 and r.gid=1;
> SELECT
> conversion=# ALTER TABLE temp ADD PRIMARY KEY (gID);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index  
> "temp_pkey" for table "temp"
> ALTER TABLE
> conversion=# SELECT geometrytype(the_geom), Nrings(the_geom), area2d 
> (the_geom) from temp;
>  geometrytype | nrings |      area2d
> --------------+--------+------------------
>  POLYGON      |      2 | 2177713.02017212
> (1 row)
>
>
> The two polygons are goods and well displayed in QGIS but exported  
> shapefiles are empty or corrupted.
>
>
>
> Le 12 mars 08 à 12:51, Obe, Regina a écrit :
>
>> I'm guessing your result may be a Geometrycollection which ESRI  
>> shape has no clue what to do with.
>>
>> If I understand you correctly, I think you really should be using  
>> ST_Difference instead of ST_SymDifference.
>>
>> So ST_Difference(polygon2, polygon1) - will give you that portion  
>> of polygon2 which does not intersect with polygon1 which would  
>> give you a polygon with polygon1 removed (a doughnut so to speak).
>>
>> The ST_Symdifference will give you that portion of polygon2 not in  
>> polygon1 + polygon1 not in polygon2 = the holed polygon you want +  
>> an empty geometry collection -> a geometry collection if taken  
>> strictly.
>>  I'm not sure if PostGIS will throw out the second part (the empty  
>> collection) - and at anyrate is a waste of processing power to  
>> even compute it.
>>
>> Hope that helps,
>> Regina
>>
>> From: postgis-users-bounces at postgis.refractions.net  
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf  
>> Of Ghislain Geniaux
>> Sent: Wednesday, March 12, 2008 6:29 AM
>> To: postgis-users at postgis.refractions.net
>> Subject: [postgis-users] problem creating and exporting a polygon  
>> with holeusing pgsql2shp
>>
>> Using a postgis table with Two polygons, in which polygon 1  
>> entirely contained in polygon 2, I've create a new polygon with a  
>> hole using :
>>
>> ------
>> create table matable2 as
>> select  r.gid, area(symdifference(r.the_geom,i.the_geom)) as area, 
>> (symdifference(r.the_geom,i.the_geom)) as the_geom from matable as  
>> r, matable as i where i.gid=2 and r.gid=1;
>>
>> ALTER TABLE matable2 ADD PRIMARY KEY (GID);
>> \q
>> pgsql2shp -f ext.shp conversion 'select * from matable2'
>> -----
>>
>> Area is correct, display of the polygon in QGIS is also correct,  
>> but the export by pgsl2shp produce an empty shapefile.
>>
>> How to deal with this problem?
>>
>> ------------------------------------------
>> Geniaux Ghislain
>>
>>
>>
>>
>> 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.
>>
>>
>>
>> Help make the earth a greener place. If at all possible resist  
>> printing this email and join us in saving paper.
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------------------
> Geniaux Ghislain
> INRA SAD Ecodéveloppement
> Site Agroparc
> Domaine St Paul
> 84914 Avignon Cedex 9
>
> Tél : 04 32 72 25 64
> Fax : 04 32 72 25 62
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users



------------------------------------------
Geniaux Ghislain
INRA SAD Ecodéveloppement
Site Agroparc
Domaine St Paul
84914 Avignon Cedex 9

Tél : 04 32 72 25 64
Fax : 04 32 72 25 62


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080312/e341506f/attachment.html>