<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
On 9/15/2016 6:53 PM, Rhys A.D. Stewart wrote:<br>
<blockquote
cite="mid:CACg0vTkjgR1ZhuDsHK1dZOKi=K=RBXbxa+qmq6E46Q0ZurK2=w@mail.gmail.com"
type="cite">
<div class="gmail_default"><font face="trebuchet ms, sans-serif">=====================================================================</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">select
premises, st_astext(g), g from service.location </font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">where
st_astext(g) = 'POINT(727895.4 663599.3)'</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">---------------------------------------------------------------------</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">premises<span class="Apple-tab-span" style="white-space:pre"> </span>st_astext<span class="Apple-tab-span" style="white-space:pre"> </span>g</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">267077<span class="Apple-tab-span" style="white-space:pre"> </span>POINT(727895.4
663599.3)<span class="Apple-tab-span" style="white-space:pre"> </span>0101000020780D0000CDCCCCCCAE3626419A9999995E402441</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">267053<span class="Apple-tab-span" style="white-space:pre"> </span>POINT(727895.4
663599.3)<span class="Apple-tab-span" style="white-space:pre"> </span>0101000020780D0000CDCCCCCCAE362641999999995E402441</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">=====================================================================</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif"><br>
</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">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.</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif"><br>
</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif"><br>
</font></div>
<div class="gmail_default"><font face="trebuchet ms, sans-serif">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?</font></div>
</blockquote>
<br>
A safe bet is that you're hitting floating point issues. EWKB is the
canonical format for geometries, but the conversion to a text
representation could lose some precision. If you did want to require
unique geometries, you could do it with a btree index on the
geometry, not st_astext of the geometry.<br>
<br>
As a general rule, comparing two floating point numbers for equality
is tricky. What you probably want is an exclusion constraint which
prevents two points from being within a small distance of each
other.<br>
<br>
I don't know of a great way to do this, but a bad way that might
work is EXCLUDE USING GIST (ST_Buffer(geom, 0.1) WITH &&).
See
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT">https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT</a>
and the links from there. I haven't tested this.<br>
</body>
</html>