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

[Paul Norman]

On 9/15/2016 6:53 PM, Rhys A.D. Stewart wrote:
> =====================================================================
> select premises, st_astext(g), g from service.location
> where st_astext(g) = 'POINT(727895.4 663599.3)'
> ---------------------------------------------------------------------
> premisesst_astextg
> 267077POINT(727895.4 
> 663599.3)0101000020780D0000CDCCCCCCAE3626419A9999995E402441
> 267053POINT(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?

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 other.

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 
https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT 
and the links from there. I haven't tested this.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160915/eabff54e/attachment.html>