[postgis-users] Comparison of geometries

[strk at refractions.net]

On Tue, Jan 31, 2006 at 08:34:21AM +0100, Düster Horst wrote:
> I try to compare two geometry tables with the intension to detect the
> geometrical and attributive differences between these two tables. For
> example I attach two tables new and old. Now I want to detect all geometries
> of the table old which are not in table new. For this issue I use the
> following query
> 
> select the_geom, art from "old"
> except 
> select the_geom, art from "new"
> 
> but the query doesn't supply the correct result. The correct result should
> contain two records but the query results 0 records. Now I'm doubt about
> whether  it's allowed to use geometries in an except clause like other
> datatypes?? Otherwise in other tables 100% of different geometries will be
> detected by the query above!

'except' will use the binary tree opclass.
For postgis this just checks bounding boxes equality.
Reformulate your query to use the ~= operator if you
want vertex-by-vertex equality, or use the equals()
function for topological equality.

--strk;