liquid_feedback_core
view geoindex_install.sql @ 624:82387194519b
Added code to suppress compiler warning (writing one byte into region of size 0)
| author | jbe |
|---|---|
| date | Fri Feb 05 13:50:45 2021 +0100 (2021-02-05) |
| parents | 5b7c4082d7b3 |
| children |
line source
1 BEGIN;
4 CREATE EXTENSION IF NOT EXISTS latlon;
7 ------------------------
8 -- Geospatial indices --
9 ------------------------
11 CREATE INDEX IF NOT EXISTS "member_location_idx"
12 ON "member"
13 USING gist ((GeoJSON_to_ecluster("location")));
14 CREATE INDEX IF NOT EXISTS "unit_location_idx"
15 ON "unit"
16 USING gist ((GeoJSON_to_ecluster("location")));
17 CREATE INDEX IF NOT EXISTS "area_location_idx"
18 ON "area"
19 USING gist ((GeoJSON_to_ecluster("location")));
20 CREATE INDEX IF NOT EXISTS "initiative_location_idx"
21 ON "initiative"
22 USING gist ((GeoJSON_to_ecluster("location")));
23 CREATE INDEX IF NOT EXISTS "draft_location_idx"
24 ON "draft"
25 USING gist ((GeoJSON_to_ecluster("location")));
26 CREATE INDEX IF NOT EXISTS "suggestion_location_idx"
27 ON "suggestion"
28 USING gist ((GeoJSON_to_ecluster("location")));
31 ------------------------
32 -- Geospatial lookups --
33 ------------------------
35 /*
36 CREATE OR REPLACE FUNCTION "closed_initiatives_in_bounding_box"
37 ( "bounding_box_p" EBOX,
38 "limit_p" INT4 )
39 RETURNS SETOF "initiative"
40 LANGUAGE 'plpgsql' STABLE AS $$
41 DECLARE
42 "limit_v" INT4;
43 "count_v" INT4;
44 BEGIN
45 "limit_v" := "limit_p" + 1;
46 LOOP
47 SELECT count(1) INTO "count_v"
48 FROM "initiative"
49 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
50 WHERE "issue"."closed" NOTNULL
51 AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
52 LIMIT "limit_v";
53 IF "count_v" < "limit_v" THEN
54 RETURN QUERY SELECT "initiative".*
55 FROM (
56 SELECT
57 "initiative"."id" AS "initiative_id",
58 "issue"."closed"
59 FROM "initiative"
60 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
61 WHERE "issue"."closed" NOTNULL
62 AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
63 ) AS "subquery"
64 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
65 ORDER BY "subquery"."closed" DESC
66 LIMIT "limit_p";
67 RETURN;
68 END IF;
69 SELECT count(1) INTO "count_v"
70 FROM (
71 SELECT "initiative"."id" AS "initiative_id"
72 FROM "initiative"
73 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
74 WHERE "issue"."closed" NOTNULL
75 ORDER BY "closed" DESC
76 LIMIT "limit_v"
77 ) AS "subquery"
78 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
79 WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
80 LIMIT "limit_p";
81 IF "count_v" >= "limit_p" THEN
82 RETURN QUERY SELECT "initiative".*
83 FROM (
84 SELECT
85 "initiative"."id" AS "initiative_id",
86 "issue"."closed"
87 FROM "initiative"
88 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
89 WHERE "issue"."closed" NOTNULL
90 ORDER BY "closed" DESC
91 LIMIT "limit_v"
92 ) AS "subquery"
93 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
94 WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
95 ORDER BY "subquery"."closed" DESC
96 LIMIT "limit_p";
97 RETURN;
98 END IF;
99 "limit_v" := "limit_v" * 2;
100 END LOOP;
101 END;
102 $$;
104 COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
105 ( EBOX, INT4 )
106 IS 'TODO';
107 */
110 COMMIT;