| rev |
line source |
|
jbe@0
|
1
|
|
jbe@0
|
2 CREATE EXTENSION latlon;
|
|
jbe@0
|
3
|
|
jbe@0
|
4 CREATE TABLE "test" (
|
|
jbe@0
|
5 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
6 "location" EPOINT NOT NULL,
|
|
jbe@10
|
7 "surrounding" ECIRCLE NOT NULL,
|
|
jbe@10
|
8 "multipoint" ECLUSTER NOT NULL,
|
|
jbe@46
|
9 "triangle" ECLUSTER NOT NULL,
|
|
jbe@46
|
10 "votes" INT4 NOT NULL );
|
|
jbe@0
|
11
|
|
jbe@10
|
12 CREATE INDEX "test_location_key" ON "test" USING gist ("location");
|
|
jbe@10
|
13 CREATE INDEX "test_surrounding_key" ON "test" USING gist ("surrounding");
|
|
jbe@10
|
14 CREATE INDEX "test_multipoint_key" ON "test" USING gist ("multipoint");
|
|
jbe@10
|
15 CREATE INDEX "test_triangle_key" ON "test" USING gist ("triangle");
|
|
jbe@46
|
16 CREATE INDEX "test_vote_key" ON "test" ("votes");
|
|
jbe@0
|
17
|
|
jbe@46
|
18
|
|
jbe@46
|
19 -- Below follows an example of how to perform a nearest-neighbor search with
|
|
jbe@46
|
20 -- weighted geometric objects (distance scaled anti-proportionally through
|
|
jbe@46
|
21 -- "votes" column).
|
|
jbe@46
|
22 --
|
|
jbe@46
|
23 -- NOTE: The approach may be speeded up by providing new data types like
|
|
jbe@46
|
24 -- "weighted_ecluster" with corresponding GiST index support in future
|
|
jbe@46
|
25 -- versions of pgLatLon.
|
|
jbe@46
|
26
|
|
jbe@46
|
27 CREATE TYPE "test_with_relevance" AS (
|
|
jbe@46
|
28 "id" INT4,
|
|
jbe@46
|
29 "location" EPOINT,
|
|
jbe@46
|
30 "surrounding" ECIRCLE,
|
|
jbe@46
|
31 "multipoint" ECLUSTER,
|
|
jbe@46
|
32 "triangle" ECLUSTER,
|
|
jbe@46
|
33 "votes" INT4,
|
|
jbe@46
|
34 "relevance" FLOAT8 );
|
|
jbe@46
|
35
|
|
jbe@46
|
36 CREATE FUNCTION "get_by_relevance" (epoint, int4 = 1, int4 = 10000)
|
|
jbe@46
|
37 RETURNS SETOF "test_with_relevance"
|
|
jbe@46
|
38 LANGUAGE plpgsql STABLE AS $$
|
|
jbe@46
|
39 DECLARE
|
|
jbe@46
|
40 "max_votes" INT4;
|
|
jbe@46
|
41 "tries" INT4 = 2;
|
|
jbe@46
|
42 "all" INT4;
|
|
jbe@46
|
43 "matches" INT4;
|
|
jbe@46
|
44 BEGIN
|
|
jbe@46
|
45 SELECT "votes" INTO "max_votes" FROM "test" ORDER BY "votes" DESC LIMIT 1;
|
|
jbe@46
|
46 IF "max_votes" > 0 THEN
|
|
jbe@46
|
47 LOOP
|
|
jbe@46
|
48 RAISE DEBUG 'Considering % entries', "tries";
|
|
jbe@46
|
49 SELECT
|
|
jbe@46
|
50 count(1),
|
|
jbe@46
|
51 count(CASE WHEN "relevance" < "worst_case" THEN 1 ELSE NULL END)
|
|
jbe@46
|
52 INTO "all", "matches"
|
|
jbe@46
|
53 FROM (
|
|
jbe@46
|
54 SELECT
|
|
jbe@46
|
55 CASE
|
|
jbe@46
|
56 WHEN "votes" = 0
|
|
jbe@46
|
57 THEN 'inf'::FLOAT8
|
|
jbe@46
|
58 ELSE "fair_distance" / "votes"
|
|
jbe@46
|
59 END AS "relevance",
|
|
jbe@46
|
60 max("fair_distance") OVER () / "max_votes" AS "worst_case"
|
|
jbe@46
|
61 FROM (
|
|
jbe@46
|
62 SELECT fair_distance("triangle", $1, $3), "votes" FROM "test"
|
|
jbe@46
|
63 ORDER BY fair_distance
|
|
jbe@46
|
64 LIMIT "tries"
|
|
jbe@46
|
65 ) AS "subquery1"
|
|
jbe@46
|
66 ) AS "subquery2";
|
|
jbe@46
|
67 EXIT WHEN "matches" >= $2 OR "all" < "tries";
|
|
jbe@46
|
68 "tries" := "tries" * 2;
|
|
jbe@46
|
69 END LOOP;
|
|
jbe@46
|
70 RETURN QUERY SELECT * FROM (
|
|
jbe@46
|
71 SELECT
|
|
jbe@46
|
72 *,
|
|
jbe@46
|
73 CASE
|
|
jbe@46
|
74 WHEN "votes" = 0
|
|
jbe@46
|
75 THEN 'inf'::FLOAT8
|
|
jbe@46
|
76 ELSE fair_distance("triangle", $1, $3) / "votes"
|
|
jbe@46
|
77 END AS "relevance"
|
|
jbe@46
|
78 FROM "test" ORDER BY fair_distance("triangle", $1, $3) LIMIT "tries"
|
|
jbe@46
|
79 ) AS "subquery" ORDER BY "relevance", "id" LIMIT $2;
|
|
jbe@46
|
80 ELSE
|
|
jbe@46
|
81 RETURN QUERY SELECT * FROM "test" ORDER BY "id" LIMIT $2;
|
|
jbe@46
|
82 END IF;
|
|
jbe@46
|
83 RETURN;
|
|
jbe@46
|
84 END;
|
|
jbe@46
|
85 $$;
|
|
jbe@46
|
86
|