Re: what does it really mean for one geometry to be equal to another
On Thu, Sep 15, 2016 at 08:53:25PM -0500, Rhys A.D. Stewart wrote:
> 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?
What you describe is expected:
the text representation is approximated/truncated.
You could create your index on ST_AsBinary(geometry) if you wanted
to check binary-level equality, but as your subject asks: what's
your concept of equality ? Do you really want those practically
identical points in your db ? Or you could create an index on
ST_SnapToGrid(geom, <tolerance>), for points to be no closer
than <tolerance>. Or (for lines) you could use ST_HausdorffDistance.
Many ways to interpret equality, which is why the equality operator
is currently just checking for approximated minimum bounding box equality
(probably still equal for your two almost-identical points).
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.
there some gotcha that I don't know about, maybe something in
the docs that I missed or is this not supposed to happen?
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.
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
On 9/15/2016 10:11 PM, Sandro Santilli wrote:
> you could create an index on
> ST_SnapToGrid(geom, <tolerance>), for points to be no closer
> than <tolerance>.
This won't check that points are no closer than <tolerance>, nor will it
solve floating point issues. It's the same as rounding. If two points
are on either side of the line where they round to different grid
points, even if they are arbitrarily close, up to the limit of precision.
It does mean that you will see fewer values where you get floating point
equality issues, but the difference between the rounded floating points