On Fri, Apr 25, 2014 at 4:54 PM, Peter Geoghegan <p...@heroku.com> wrote:
> On Fri, Apr 25, 2014 at 4:47 PM, Paul Ramsey <pram...@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),
FUNCTION 1 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
ON ST_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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
