On Fri, Apr 25, 2014 at 4:54 PM, Peter Geoghegan <pg@heroku.com> wrote:
> On Fri, Apr 25, 2014 at 4:47 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:
>> Is it possible to make custom types hashable? There's no hook in the
>> CREATE TYPE call for a hash function, but can one be hooked up
>> somewhere else? In an operator?
>
> See 35.14.6., System Dependencies on Operator Classes
Coming back to this, I created an appropriate opclass...
CREATE OR REPLACE FUNCTION geometry_hash_eq(geom1 geometry, geom2 geometry)
RETURNS boolean
AS '$libdir/postgis-2.2', 'lwgeom_hash_eq'
LANGUAGE 'c' IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION geometry_hash(geom1 geometry)
RETURNS integer
AS '$libdir/postgis-2.2', 'lwgeom_hash'
LANGUAGE 'c' IMMUTABLE STRICT;
-- Availability: 0.9.0
CREATE OPERATOR == (
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_hash_eq,
COMMUTATOR = '==',
RESTRICT = contsel, JOIN = contjoinsel
);
CREATE OPERATOR CLASS hash_geometry_ops DEFAULT FOR TYPE geometry USING hash AS OPERATOR 1 == (geometry, geometry),
FUNCTION1 geometry_hash(geometry);
I even tested that it as an index!
> create index hashidx on points using hash ( the_geom_webmercator); CREATE INDEX
But when I run my recursive query...
WITH RECURSIVE find_cluster(cartodb_id, cluster_id, geom) AS (
(SELECT points.cartodb_id, points.cartodb_id as cluster_id,
points.the_geom_webmercator as geom FROM points WHERE points.cartodb_id in (select cartodb_id from points)) UNION
(SELECT pts.cartodb_id, n.cluster_id, pts.the_geom_webmercator as geom FROM points pts JOIN find_cluster n
ONST_DWithin(n.geom, pts.the_geom_webmercator, 2) WHERE n.cartodb_id <> pts.cartodb_id)
)
SELECT * FROM find_cluster;
It still says I lack the secret sauce...
ERROR: could not implement recursive UNION
DETAIL: All column datatypes must be hashable.
What's the sauce?
Thanks!
P
>
>
> --
> Peter Geoghegan