[postgis-users] what does it really mean for one geometry to be equal to another

[Rhys A.D. Stewart]

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>