[postgis-users] what does it really mean for one geometry to be equal to another
Rhys A.D. Stewart
rhys.stewart at gmail.com
Thu Sep 15 18:53:25 PDT 2016
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160915/313afacc/attachment.html>
More information about the postgis-users
mailing list