[postgis-users] finding duplicate value for the_geom field

[Andy Anderson]

  && only tells if they overlap. I think you want to use:

	a.the_geom ~= b.the_geom

 From the manual: "The '~=' operator is the 'same as' operator. It  
tests actual geometric equality of two features. So if A and B are  
the same feature, vertex-by-vertex, the operator returns true."

I would also use > rather than <>:

	a.gid > b.gid

because <> allows duplicate results (e.g. 2 <> 3 and 3 <> 2):

It might also be faster to ensure the testing of the gid statement  
first. I'll again note that the Postgres SQL AND statement is not  
automatically processed left-to-right as in some other languages. It  
seems to me that it's best to use the CASE statement to ensure the  
order of tests:

	WHERE CASE WHEN a.gid > b.gid THEN a.the_geom ~= b.the_geom ELSE  
false END

http://www.postgresql.org/docs/8.3/interactive/sql- 
expressions.html#SYNTAX-EXPRESS-EVAL>

However, the manual does say "A CASE construct used in this fashion  
will defeat optimization attempts, so it should only be done when  
necessary." So perhaps I should relax about this and let Postgres  
decide the best order? (I've never completely trusted optimizing  
compilers :-)

-- Andy

On May 27, 2008, at 8:35 AM, Benedetto Porfidia wrote:

> build a gist index on your table and run something like this:
>
> SELECT a.gid,b.gid,a.the_geom, b.the_geom
> FROM my_table as a,my_table as b
> WHERE  a.the_geom && b.the_geom and a.gid<>b.gid;
>
> Antonio Di Marco ha scritto:
>> Hi, how can I find duplicate value in a PostgreSQL table for the  
>> geometry field (the_geom)
>> Thanks a lot
>> Antonio
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users