jbe@0: 
jbe@0: CREATE EXTENSION latlon;
jbe@0: 
jbe@0: CREATE TABLE "test" (
jbe@0:         "id"            SERIAL4         PRIMARY KEY,
jbe@0:         "location"      EPOINT          NOT NULL,
jbe@10:         "surrounding"   ECIRCLE         NOT NULL,
jbe@10:         "multipoint"    ECLUSTER        NOT NULL,
jbe@46:         "triangle"      ECLUSTER        NOT NULL,
jbe@46:         "votes"         INT4            NOT NULL );
jbe@0: 
jbe@10: CREATE INDEX "test_location_key"    ON "test" USING gist ("location");
jbe@10: CREATE INDEX "test_surrounding_key" ON "test" USING gist ("surrounding");
jbe@10: CREATE INDEX "test_multipoint_key"  ON "test" USING gist ("multipoint");
jbe@10: CREATE INDEX "test_triangle_key"    ON "test" USING gist ("triangle");
jbe@46: CREATE INDEX "test_vote_key" ON "test" ("votes");
jbe@0: 
jbe@46: 
jbe@46: -- Below follows an example of how to perform a nearest-neighbor search with
jbe@46: -- weighted geometric objects (distance scaled anti-proportionally through
jbe@46: -- "votes" column).
jbe@46: --
jbe@46: -- NOTE: The approach may be speeded up by providing new data types like
jbe@46: --       "weighted_ecluster" with corresponding GiST index support in future
jbe@46: --       versions of pgLatLon.
jbe@46: 
jbe@46: CREATE TYPE "test_with_relevance" AS (
jbe@46:         "id"            INT4,
jbe@46:         "location"      EPOINT,
jbe@46:         "surrounding"   ECIRCLE,
jbe@46:         "multipoint"    ECLUSTER,
jbe@46:         "triangle"      ECLUSTER,
jbe@46:         "votes"         INT4,
jbe@46:         "relevance"     FLOAT8 );
jbe@46: 
jbe@46: CREATE FUNCTION "get_by_relevance" (epoint, int4 = 1, int4 = 10000)
jbe@46:   RETURNS SETOF "test_with_relevance"
jbe@46:   LANGUAGE plpgsql STABLE AS $$
jbe@46:     DECLARE
jbe@46:       "max_votes" INT4;
jbe@46:       "tries"     INT4 = 2;
jbe@46:       "all"       INT4;
jbe@46:       "matches"   INT4;
jbe@46:     BEGIN
jbe@46:       SELECT "votes" INTO "max_votes" FROM "test" ORDER BY "votes" DESC LIMIT 1;
jbe@46:       IF "max_votes" > 0 THEN
jbe@46:         LOOP
jbe@46:           RAISE DEBUG 'Considering % entries', "tries";
jbe@46:           SELECT
jbe@46:             count(1),
jbe@46:             count(CASE WHEN "relevance" < "worst_case" THEN 1 ELSE NULL END)
jbe@46:             INTO "all", "matches"
jbe@46:             FROM (
jbe@46:               SELECT
jbe@46:                 CASE
jbe@46:                   WHEN "votes" = 0
jbe@46:                   THEN 'inf'::FLOAT8
jbe@46:                   ELSE "fair_distance" / "votes"
jbe@46:                 END AS "relevance",
jbe@46:                 max("fair_distance") OVER () / "max_votes" AS "worst_case"
jbe@46:               FROM (
jbe@46:                 SELECT fair_distance("triangle", $1, $3), "votes" FROM "test"
jbe@46:                 ORDER BY fair_distance
jbe@46:                 LIMIT "tries"
jbe@46:               ) AS "subquery1"
jbe@46:             ) AS "subquery2";
jbe@46:           EXIT WHEN "matches" >= $2 OR "all" < "tries";
jbe@46:           "tries" := "tries" * 2;
jbe@46:         END LOOP;
jbe@46:         RETURN QUERY SELECT * FROM (
jbe@46:           SELECT
jbe@46:             *,
jbe@46:             CASE
jbe@46:               WHEN "votes" = 0
jbe@46:               THEN 'inf'::FLOAT8
jbe@46:               ELSE fair_distance("triangle", $1, $3) / "votes"
jbe@46:             END AS "relevance"
jbe@46:             FROM "test" ORDER BY fair_distance("triangle", $1, $3) LIMIT "tries"
jbe@46:         ) AS "subquery" ORDER BY "relevance", "id" LIMIT $2;
jbe@46:       ELSE
jbe@46:         RETURN QUERY SELECT * FROM "test" ORDER BY "id" LIMIT $2;
jbe@46:       END IF;
jbe@46:       RETURN;
jbe@46:     END;
jbe@46:   $$;
jbe@46: