[postgis-users] Strange behaviour of = operator

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Steffen Macke
> Sent: 07 February 2006 12:38
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Strange behaviour of = operator

(cut)

> Now I would like to report the little example that the geometries are
> unique.
> However it doesn't work out. What's wrong?
> 
> create table test (id int);
> select addgeometrycolumn('test','the_geom',-1,'MULTIPOLYGON',2);
> insert into test values (1, GeometryFromText('MULTIPOLYGON(((1 1,1 0,0
> 0, 1 1)))'));
> insert into test values (2, GeometryFromText('MULTIPOLYGON(((1 1, 0
> 0,0 1,1 1)))'));
> select the_geom,count(*) from test group by the_geom;


Hi Steffen,

The problem occurs because the btree_geometry_ops operator class is only
used when the table is accessed via an index. If the table is accessed via a
sequential scan, then the conventional = operator is used which again will
only compare bounding boxes in its current implementation. Since your test
table is so small then PostgreSQL automatically defaults to using a
sequential scan.

I think the results you want can be obtained using the following:


BEGIN;

DROP OPERATOR CLASS btree_geometry_ops USING btree;
DROP OPERATOR = (geometry, geometry);

create or replace function btree_equals(geometry, geometry) returns boolean
as $$
begin
	return equals($1, $2);
end;
$$
language plpgsql;

create or replace function btree_equals_opclass(geometry, geometry) returns
integer as $$
  begin
  if equals($1,$2) then
    return 1;
  else
    return 0;
  end if;
end;
$$
language plpgsql;

CREATE OPERATOR = (
   LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = btree_equals,
   COMMUTATOR = '=', -- we might implement a faster negator here
   RESTRICT = contsel, JOIN = contjoinsel
);

CREATE OPERATOR CLASS btree_geometry_ops DEFAULT
   FOR TYPE geometry USING btree AS
   OPERATOR 1  <,
   OPERATOR 2  <=,
   OPERATOR 3  =,
   OPERATOR 4  >=,
   OPERATOR 5  >,
   FUNCTION 1  btree_equals_opclass(geometry, geometry);

COMMIT;


Once you have entered this, you'll need to exit psql using \q and then
re-connect to the database as it seems PostgreSQL caches the reference to
the old operator definitions until you reconnect. This then gives the
following:


dcmms=# select astext(the_geom),count(*) from test group by the_geom;
              astext               | count
-----------------------------------+-------
 MULTIPOLYGON(((1 1,0 0,0 1,1 1))) |     1
 MULTIPOLYGON(((1 1,1 0,0 0,1 1))) |     1
(2 rows)


...compared to the original results I got which were:


dcmms=# select astext(the_geom),count(*) from test group by the_geom;
              astext               | count
-----------------------------------+-------
 MULTIPOLYGON(((1 1,0 0,0 1,1 1))) |     2
(1 row)


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.