[postgis-users] correcting polygons
Malm Paul
paul.malm at saabgroup.com
Thu Jun 7 22:08:28 PDT 2007
You are right, what would I do without you!
But now I'm getting:
Notice: Self intersection
Error: new row for relation "depare_area2" violates check constraint
"enforce_geotype_poli"
SQL state: 23514
I know that the original shape file has a very bad status, but this is
all I will get from our supplier.
That's why I'm trying to correct the file with buffer(0.0).
Thanks again,
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 7 juni 2007 16:31
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons
Yes that is what I meant. All of it looks right to me so not sure why
you are getting the
ERROR
org.postgresql.util.PSQLException: ERROR: new row for relation
"depare_area2" violates check constraint "enforce_srid_poli". Are you
sure that is coming from your update line and not from some other place
like your insert line. Based on the fact it says new row - I would
guess its coming from your insert.
What does
SELECT DISTINCT srid(geometryn(the_geom, generate_series(1,
numgeometries(the_geom)))) As the_srid FROM deparea;
return?
Just to be absolutely sure run the below from a query window instead of
from your java application (actually run each step of your sql
statements separately from query window).
UPDATE deparea2 SET poli = buffer(poli, 0.0) WHERE isvalid(poli) = false
AND isvalid(buffer(poli, 0.0)) = true;
and if you run just that statement from a query window you still get
that error correct?
and get the same error correct?
My only other guess is that there is some dangling corruption in your
database somewhere. Did you try
vacuum analyze
and then try to run the statement again.
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: Thursday, June 07, 2007 9:08 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons
Regina,
I hope this is what you mean:
-- Table: depare_area
-- DROP TABLE depare_area;
CREATE TABLE depare_area
(
gid serial NOT NULL,
id integer,
lnam character varying(17),
name character varying(12),
prim character varying(1),
grup smallint,
rver smallint,
ruin character varying(1),
drval1 double precision,
drval2 double precision,
quasou character varying(70),
souacc double precision,
verdat smallint,
the_geom geometry,
CONSTRAINT depare_area_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE depare_area OWNER TO postgres_owner;
-- Table: depare_area2
-- DROP TABLE depare_area2;
CREATE TABLE depare_area2
(
gid serial NOT NULL,
poli geometry,
CONSTRAINT pk_depare_area2 PRIMARY KEY (gid),
CONSTRAINT enforce_dims_poli CHECK (ndims(poli) = 2),
CONSTRAINT enforce_geotype_poli CHECK (geometrytype(poli) =
'POLYGON'::text OR poli IS NULL),
CONSTRAINT enforce_srid_poli CHECK (srid(poli) = -1)
)
WITHOUT OIDS;
ALTER TABLE depare_area2 OWNER TO postgres_owner;
-- Index: idx_depare_area2_poli
-- DROP INDEX idx_depare_area2_poli;
CREATE INDEX idx_depare_area2_poli
ON depare_area2
USING gist
(poli);
Hope you can find something out of it
/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 7 juni 2007 13:19
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons
Paul,
That wasn't quite what I meant. Are you using PgAdmin III? - It looks
like you are but can't absolutely tell - could be another editor.
At least in PgAdmin III
If you click a table name - there is an SQL Pane that shows usually to
the right and gives you the whole DDL of your table. One of mine for
example looks like this (this probably isn't the best example, but
hopefully you get the idea)
-- Table: parceltime_2006
-- DROP TABLE parceltime_2006;
CREATE TABLE parceltime_2006
(
-- Inherited: pid character(10) NOT NULL,
-- Inherited: cm_id character(10),
-- Inherited: st_num character varying(10),
-- Inherited: st_name character varying(75),
-- Inherited: name_suf character varying(20),
-- Inherited: unit_num character varying(10),
-- Inherited: zipcode character varying(10),
-- Inherited: ptype integer,
-- Inherited: lu character varying(5),
-- Inherited: exmpt_code character varying(20),
-- Inherited: ownocc character varying(1),
-- Inherited: "owner" character varying(150),
-- Inherited: address character varying(150),
-- Inherited: city_state character varying(75),
-- Inherited: mail_zip character varying(10),
-- Inherited: landval bigint,
-- Inherited: bldgval bigint,
-- Inherited: totalval bigint,
-- Inherited: gross_tax numeric(31,15),
-- Inherited: legal_area bigint,
-- Inherited: year_built integer,
-- Inherited: year_remod integer,
-- Inherited: gross_area bigint,
-- Inherited: area bigint,
-- Inherited: num_floors numeric(31,15),
-- Inherited: "class" character varying(15),
-- Inherited: r_bldg_stl character varying(50),
-- Inherited: r_roof_typ character varying(50),
-- Inherited: r_ext_fin character varying(100),
-- Inherited: total__rms integer,
-- Inherited: r_bdrms integer,
-- Inherited: r_full_bth integer,
-- Inherited: r_half_bth integer,
-- Inherited: r_kitch integer,
-- Inherited: r_heat_typ character varying(1),
-- Inherited: r_ac character varying(1),
-- Inherited: r_fplace integer,
-- Inherited: s_num_bldg integer,
-- Inherited: s_bldg_sty character varying(5),
-- Inherited: s_unit_res integer,
-- Inherited: s_unit_com integer,
-- Inherited: s_unit_rc integer,
-- Inherited: s_ext_fin character varying(2),
-- Inherited: u_base_flr integer,
-- Inherited: u_num_prk integer,
-- Inherited: u_corner character varying(1),
-- Inherited: u_orient character varying(2),
-- Inherited: u_num_rms integer,
-- Inherited: u_num_beds integer,
-- Inherited: u_bths integer,
-- Inherited: u_half_bth integer,
-- Inherited: u_kitch character varying(2),
-- Inherited: u_heat character varying(2),
-- Inherited: u_ac character varying(1),
-- Inherited: u_fplaces integer,
-- Inherited: xcoord numeric(31,15),
-- Inherited: the_geom geometry,
-- Inherited: the_point geometry,
-- Inherited: entrytime timestamp without time zone NOT NULL,
-- Inherited: pid_year smallint NOT NULL,
-- Inherited: neighborhood character varying(50),
-- Inherited: wpd character varying(20),
-- Inherited: councildist character varying(15),
-- Inherited: ycoord numeric(31,15),
CONSTRAINT pk_parceltime_2006 PRIMARY KEY (pid, pid_year),
CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 2249),
CONSTRAINT pid_year_cc CHECK (pid_year = 2006::smallint)
)
INHERITS (parceltime)
WITH (OIDS=TRUE)
;
ALTER TABLE parceltime_2006 OWNER TO postgres; GRANT ALL ON TABLE
parceltime_2006 TO postgres; GRANT ALL ON TABLE parceltime_2006 TO
dnddtsadmins WITH GRANT OPTION;
-- Index: idx_stnameonly_2006
-- DROP INDEX idx_stnameonly_2006;
CREATE INDEX idx_stnameonly_2006
ON parceltime_2006
USING btree
(upper(COALESCE(st_name, ''::character varying)::character
varying(75)::text));
-- Index: idx_stnameonly_soundex_2006
-- DROP INDEX idx_stnameonly_soundex_2006;
CREATE INDEX idx_stnameonly_soundex_2006
ON parceltime_2006
USING btree
(soundex(st_name::text));
-- Index: idx_stnamesndex_2006
-- DROP INDEX idx_stnamesndex_2006;
CREATE INDEX idx_stnamesndex_2006
ON parceltime_2006
USING btree
(soundex(COALESCE(st_name, ''::character varying)::character
varying(75)::text));
-- Index: idx_stnumonly_2006
-- DROP INDEX idx_stnumonly_2006;
CREATE INDEX idx_stnumonly_2006
ON parceltime_2006
USING btree
(upper(btrim(st_num::text)));
-- Index: idx_the_geom_2006
-- DROP INDEX idx_the_geom_2006;
CREATE INDEX idx_the_geom_2006
ON parceltime_2006
USING gist
(the_geom);
ALTER TABLE parceltime_2006 CLUSTER ON idx_the_geom_2006;
-- Index: idx_the_point_parceltime_2006
-- DROP INDEX idx_the_point_parceltime_2006;
CREATE INDEX idx_the_point_parceltime_2006
ON parceltime_2006
USING gist
(the_point);
-- Index: idx_zip_2006
-- DROP INDEX idx_zip_2006;
CREATE INDEX idx_zip_2006
ON parceltime_2006
USING btree
(fn_chartonumber(zipcode, '999999'::character varying));
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Malm
Paul
Sent: Thursday, June 07, 2007 2:04 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] correcting polygons
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
_______________________________________________
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