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