Greetings all, I maintain a medium size table of customer locations, which, for business purposes now needs to not have any coincident points. Table definition follows: ===================================================================== service.location ( premises text NOT NULL, matchtype text, matchdate date, connectedtransformer text, g geometry(Point,3448), CONSTRAINT servicelocation_pkey PRIMARY KEY (premises), CONSTRAINT servicelocation_premisesnumber_check CHECK (char_length(premises) = 6 OR char_length(premises) = 7), CONSTRAINT servicelocation_premisesnumber_is_a_number_check CHECK (premises !~* '[A-z]+'::text) ) =====================================================================
There are approximately 866k rows, and a gist index on g. I update the table so that no geometries are coincident ( see https://gist.github.com/rhysallister/bcb4bb07a99d69938fff88f150883bee for the sql to remove the coincident geoms) I ran the sql in the gist until it said 0 rows affected. To prevent one from inserting or updating a coincident geometry I try to create a unique index on g. Since gist doesn't support unique indices I do: ===================================================================== CREATE unique INDEX unique_g ON service.location (st_astext(g) ); --------------------------------------------------------------------- ERROR: could not create unique index "unique_g" DETAIL: Key (st_astext(g))=(POINT(727895.4 663599.3)) is duplicated. ===================================================================== This makes me slightly flummoxed. I'm pretty sure the query in the gist returned 0 affected rows. But, maybe I missed a step. I try to find the offending rows with: ===================================================================== select premises, st_astext(g), g from service.location where st_equals(g, 'SRID=3448;POINT(727895.4 663599.3)'::geometry) --------------------------------------------------------------------- premises st_astext g 267077 POINT(727895.4 663599.3) 0101000020780D0000CDCCCCCCAE3626419A9999995E402441 ===================================================================== Strange. I now move to being slightly perturbed. I'm very sure the previous error message made mention of duplicity. I then run ===================================================================== select premises, st_astext(g), g from service.location where st_astext(g) = 'POINT(727895.4 663599.3)' --------------------------------------------------------------------- premises st_astext g 267077 POINT(727895.4 663599.3) 0101000020780D0000CDCCCCCCAE3626419A9999995E402441 267053 POINT(727895.4 663599.3) 0101000020780D0000CDCCCCCCAE362641999999995E402441 ===================================================================== Now I'm just confused, the 2 premises have the same st_astext, but different wkb representations and as such are not being caught in the st_equals call. Is there some gotcha that I don't know about, maybe something in the docs that I missed or is this not supposed to happen? Rhys Peace & Love|Live Long & Prosper
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users