[postgis-users] correcting polygons

Malm Paul paul.malm at saabgroup.com
Wed Jun 6 23:04:13 PDT 2007


Regina, 
SELECT distinct srid(buffer(poli,0.0)) as the_srid FROM Depare_area2
where isvalid(poli) = false and isvalid(buffer(poli,0.0)) = true 
answer:
the_srid integer = -1



expanding the table(I think this is what you ment)
Depare_area2
If I expand the table I will see (dependents):
Type			Name				Restriction
Type			depare_area2		Internal
Sequense		depare_area2_gid_seq	auto
Index			idx_depare_area2_poli	auto
Check			enforce_dims_poli		auto
Check			enforce_geotype_poli	auto
Check			enforce_srid_poli		auto
Primary Key		pk_depare_area2		auto
Check			enforce_dims_poli		normal
Check			enforce_geotype_poli	normal
Check			enforce_srid_poli		normal


(dependencies):
Type			geometry			normal
Schema		public			normal

(properties):
Name				depare_area2
OID				33213
Owner				postgres_owner
ACL		
Primary key			gid
rows(est)			271
rows(counted)		271
inherits tables		no
inherits tables count	0
Has OIDs 			no
system table?		no


And for depare_area:

If I expand the table I will see (dependents):
Type			Name
Restriction
Type			depare_area				Internal
Sequense		depare_area2_gid_seq		auto
Index			idx_depare_area2_poli		auto
Check			enforce_dims_the_geom		auto
Check			enforce_geotype_the_geom	auto
Check			enforce_srid_the_geom		auto
Primary Key		depare_area_pkey			auto
Check			enforce_dims_the_geom		normal
Check			enforce_geotype_the_geom	normal
Check			enforce_srid_the_geom		normal


(dependencies):
Type			geometry			normal
Schema		public			normal

(properties):
Name				depare_area
OID				33160
Owner				postgres_owner
ACL		
Primary key			gid
rows(est)			271
rows(counted)		271
inherits tables		no
inherits tables count	0
Has OIDs 			no
system table?		no


Kind Regards,
Paul 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: den 4 juni 2007 16:16
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

What do you get if you do

SELECT distinct srid(buffer(poli,0.0)) as the_srid FROM Depare_area2
where isvalid(poli) = false and isvalid(buffer(poli,0.0)) = true

If you are using pgadmin3 or something like that - you can look at the
create table statement of your table to see the constraints or expand
the table and go to the constraints section to see the constraints and
associated definition.  Unfortunately I don't know off hand how to see
this information by typing in and sql statement next to writing a fairly
convoluted sql statement against the catalog tables.

Hope this helps,
Regina 

 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Malm
Paul
Sent: Monday, June 04, 2007 9:56 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Regina,
It is still the same even if I don't use multi()  

If I enter:
SELECT distinct (srid(poli)) as the_srid From Depare_area2;

   I will get only -1

If I enter:
SELECT distinct (srid(the_geom)) as the_srid From Depare_area;

   I will get only -1


I did not understand what you wanted me to do with:
"What does your enforce_srid_poli constraint look like?

It should look something like

 CONSTRAINT enforce_srid_poli CHECK (srid(poli) = -1)"




-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: den 4 juni 2007 15:27
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Paul,

Hmm strange I would expect your update to be failing for
enforce_geotype_poli since you are trying to update a POLYGON
constrained field (poli) to a MULTIPOLYGON.  Try getting rid of the
multi function call so change to 

UPDATE deparea2 SET poli = buffer(poli, 0.0) WHERE isvalid(poli) = false
AND isvalid(buffer(poli, 0.0)) = true;

OR alternatively change your add to
SELECT AddGeometryColumn('public','deparea2', 'poli', -1,
'MULTIPOLYGON',2);

I'm still puzzled why you are getting an "enforce_srid_poli" error.
What does your enforce_srid_poli constraint look like?

It should look something like

 CONSTRAINT enforce_srid_poli CHECK (srid(poli) = -1)




-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Malm
Paul
Sent: Monday, June 04, 2007 8:49 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Regina,
This is how I created deparea:


CREATE TABLE "public"."deparea" (gid serial PRIMARY KEY,

"id" int4,

"lnam" varchar(17),

"name" varchar(12),

"prim" varchar(1),

"grup" int2,

"rver" int2,

"ruin" varchar(1),

"drval1" float8,

"drval2" float8,

"quasou" varchar(70),

"souacc" float8,

"verdat" int2);

SELECT
AddGeometryColumn('public','deparea','the_geom','-1','MULTIPOLYGON',2);


It is this SQL row that generates the error:

UPDATE deparea2 SET poli = multi(buffer(poli, 0.0)) WHERE isvalid(poli)
= false AND isvalid(buffer(poli, 0.0)) = true;

Regards,
Paul

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: den 4 juni 2007 13:47
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

The error means that you are trying to insert geometries with a
different SRID than the SRID that you specified for your new table.
Note your new table has an SRID = -1

My guess is that your original deparea table has an SRID different from
-1.

To check try 

SELECT distinct(srid(the_geom)) as the_srid FROM deparea

You should only get 1 record unless you have a mixed bag of SRIDs.  Now
whatever that srid is, your add geometry should use that instead of the
-1 you have in place.  So the line

SELECT AddGeometryColumn('public','deparea2', 'poli', '<WHATEVER SRID
YOU GOT ABOVE GOES HERE>', 'POLYGON',2);

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 Malm
Paul
Sent: Monday, June 04, 2007 1:54 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Thanks Regina,

If you, or someone else, got the time I would be pleased if you can
explain this error code for me. I've tried to do some random changes :)
since I'm not very good at SQL.

ERROR (appears on the last SQL row):
org.postgresql.util.PSQLException: ERROR: new row for relation
"depare_area2" violates check constraint "enforce_srid_poli"


This is what I do:

SELECT geometryN(the_geom, generate_series(1, numgeometries(the_geom)))
AS poli FROM deparea; CREATE TABLE deparea2 (gid serial, CONSTRAINT
pk_deparea2 PRIMARY KEY(gid));

SELECT AddGeometryColumn('public','deparea2', 'poli', '-1',
'POLYGON',2);

INSERT INTO deparea2(poli) SELECT geometryn(the_geom, generate_series(1,
numgeometries(the_geom))) As poli FROM deparea; 

CREATE INDEX idx_deparea2_poli  ON deparea2  USING gist (poli); 

UPDATE deparea2 SET poli = multi(buffer(poli, 0.0)) WHERE isvalid(poli)
= false AND isvalid(buffer(poli, 0.0)) = true;


Kind regards,
Paul

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: den 1 juni 2007 17:05
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Forgot to mention before updating the field, its probably best to only
if you really need to and if updating will help so something like

UPDATE sometable
SET the_geom = multi(buffer(the_geom, 0.0)) WHERE isvalid(the_geom) =
false and isvalid(buffer(the_geom,0.0)) = true
 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: Friday, June 01, 2007 8:28 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Paul,

Not sure if this quite answers your question, but you would use an
UPDATE statement to update a geometry

So for example

UPDATE sometable SET the_geom = multi(buffer(the_geom, 0.0))

or
UPDATE sometable SET the_geom = buffer(the_geom, 0.0)


Now generate_series explodes a row so in an update statement - doesn't
really make sense to use unless you want to do something with each
individual geometry and then collapse it back using something like
collect or geomunion.  As I recall from using buffer before, I think it
acts on each individual geometry in a multi set e..g in a multipolygon.
So doing a generate series is really not necessary to use buffer - it
sort of implicitly does that whole thing for you.  Someone correct me if
I am wrong on that assumption :)

The only thing to be careful of is that if you have a constraint on your
table that requires the_geom to be say a multipolygon,  when you apply a
buffer operation to it and there is only one geometry within the
multigeometry, buffer will convert that multipolygon to a polygon.  To
prevent that from happening, you would then apply the multi function.
multi function in essence wraps your single polygon into a multipolygon
encasement.

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 Malm
Paul
Sent: Friday, June 01, 2007 4:50 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons

Thanks, Paul!

I'm not to good in SQL and have looked at old userGroup qestions and
found this (Regina Obe):
(converting multi polygons to polygons by creating a new tabel)
/////////////////////////////////////////
String table = "depare_area";
String qq;
myStat = con.createStatement();

qq= "SELECT geometryN(the_geom, generate_series(1,
numgeometries(the_geom))) AS poli FROM " + table; Statement myStat2 =
con.createStatement(); ResultSet myResult2 = myStat2.executeQuery(qq);

qq = "CREATE TABLE " + table + "2 " + "(gid serial, CONSTRAINT " + "pk_"
+ table + "2 " + " PRIMARY KEY(gid))";
myStat.execute(qq);

qq = "SELECT AddGeometryColumn('public','" + table + "2', 'poli', '-1',
'POLYGON',2)"; myStat.execute(qq);

qq = "INSERT INTO " + table + "2(poli) SELECT geometryn(the_geom,
generate_series(1, numgeometries(the_geom))) As poli FROM " + table;
myStat.execute(qq);
			
qq = "CREATE INDEX idx_" + table + "2_poli  ON " + table + "2  USING
gist (poli)"; myStat.execute(qq);
//////////////////////////////////////////////

Du you know how I at the same time can use your suggestion to use
buffer(xxxx, 0.0) to correct the polygons?

I have not found anything of how to update objects, only how to create
new objects.

Kind regards,
Paul
  

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: den 30 maj 2007 20:01
To: PostGIS Users Discussion
Subject: Re: [postgis-users] correcting polygons

buffer(polygon,0.0) should re-build it with correct structure

On 30-May-07, at 7:29 AM, Malm Paul wrote:

> Hi list,
> Is there a simple way to correct a polygon that has a hole and where 
> one of the coordinates of that hole is exactly the same as one 
> coordinate in the external ring?
>
> Kind regards,
> Paul
>
> _______________________________________________
> 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
_______________________________________________
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
_______________________________________________
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
_______________________________________________
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
_______________________________________________
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
_______________________________________________
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