liquid_feedback_core

annotate geoindex_install.sql @ 624:82387194519b

find changesets by author, revision, files, or words in the commit message
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
rev   line source
jbe@622 1 BEGIN;
jbe@622 2
jbe@622 3
jbe@622 4 CREATE EXTENSION IF NOT EXISTS latlon;
jbe@622 5
jbe@622 6
jbe@622 7 ------------------------
jbe@622 8 -- Geospatial indices --
jbe@622 9 ------------------------
jbe@622 10
jbe@622 11 CREATE INDEX IF NOT EXISTS "member_location_idx"
jbe@622 12 ON "member"
jbe@622 13 USING gist ((GeoJSON_to_ecluster("location")));
jbe@622 14 CREATE INDEX IF NOT EXISTS "unit_location_idx"
jbe@622 15 ON "unit"
jbe@622 16 USING gist ((GeoJSON_to_ecluster("location")));
jbe@622 17 CREATE INDEX IF NOT EXISTS "area_location_idx"
jbe@622 18 ON "area"
jbe@622 19 USING gist ((GeoJSON_to_ecluster("location")));
jbe@622 20 CREATE INDEX IF NOT EXISTS "initiative_location_idx"
jbe@622 21 ON "initiative"
jbe@622 22 USING gist ((GeoJSON_to_ecluster("location")));
jbe@622 23 CREATE INDEX IF NOT EXISTS "draft_location_idx"
jbe@622 24 ON "draft"
jbe@622 25 USING gist ((GeoJSON_to_ecluster("location")));
jbe@622 26 CREATE INDEX IF NOT EXISTS "suggestion_location_idx"
jbe@622 27 ON "suggestion"
jbe@622 28 USING gist ((GeoJSON_to_ecluster("location")));
jbe@622 29
jbe@622 30
jbe@622 31 ------------------------
jbe@622 32 -- Geospatial lookups --
jbe@622 33 ------------------------
jbe@622 34
jbe@622 35 /*
jbe@622 36 CREATE OR REPLACE FUNCTION "closed_initiatives_in_bounding_box"
jbe@622 37 ( "bounding_box_p" EBOX,
jbe@622 38 "limit_p" INT4 )
jbe@622 39 RETURNS SETOF "initiative"
jbe@622 40 LANGUAGE 'plpgsql' STABLE AS $$
jbe@622 41 DECLARE
jbe@622 42 "limit_v" INT4;
jbe@622 43 "count_v" INT4;
jbe@622 44 BEGIN
jbe@622 45 "limit_v" := "limit_p" + 1;
jbe@622 46 LOOP
jbe@622 47 SELECT count(1) INTO "count_v"
jbe@622 48 FROM "initiative"
jbe@622 49 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622 50 WHERE "issue"."closed" NOTNULL
jbe@622 51 AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622 52 LIMIT "limit_v";
jbe@622 53 IF "count_v" < "limit_v" THEN
jbe@622 54 RETURN QUERY SELECT "initiative".*
jbe@622 55 FROM (
jbe@622 56 SELECT
jbe@622 57 "initiative"."id" AS "initiative_id",
jbe@622 58 "issue"."closed"
jbe@622 59 FROM "initiative"
jbe@622 60 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622 61 WHERE "issue"."closed" NOTNULL
jbe@622 62 AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622 63 ) AS "subquery"
jbe@622 64 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
jbe@622 65 ORDER BY "subquery"."closed" DESC
jbe@622 66 LIMIT "limit_p";
jbe@622 67 RETURN;
jbe@622 68 END IF;
jbe@622 69 SELECT count(1) INTO "count_v"
jbe@622 70 FROM (
jbe@622 71 SELECT "initiative"."id" AS "initiative_id"
jbe@622 72 FROM "initiative"
jbe@622 73 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622 74 WHERE "issue"."closed" NOTNULL
jbe@622 75 ORDER BY "closed" DESC
jbe@622 76 LIMIT "limit_v"
jbe@622 77 ) AS "subquery"
jbe@622 78 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
jbe@622 79 WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622 80 LIMIT "limit_p";
jbe@622 81 IF "count_v" >= "limit_p" THEN
jbe@622 82 RETURN QUERY SELECT "initiative".*
jbe@622 83 FROM (
jbe@622 84 SELECT
jbe@622 85 "initiative"."id" AS "initiative_id",
jbe@622 86 "issue"."closed"
jbe@622 87 FROM "initiative"
jbe@622 88 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622 89 WHERE "issue"."closed" NOTNULL
jbe@622 90 ORDER BY "closed" DESC
jbe@622 91 LIMIT "limit_v"
jbe@622 92 ) AS "subquery"
jbe@622 93 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
jbe@622 94 WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622 95 ORDER BY "subquery"."closed" DESC
jbe@622 96 LIMIT "limit_p";
jbe@622 97 RETURN;
jbe@622 98 END IF;
jbe@622 99 "limit_v" := "limit_v" * 2;
jbe@622 100 END LOOP;
jbe@622 101 END;
jbe@622 102 $$;
jbe@622 103
jbe@622 104 COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
jbe@622 105 ( EBOX, INT4 )
jbe@622 106 IS 'TODO';
jbe@622 107 */
jbe@622 108
jbe@622 109
jbe@622 110 COMMIT;

Impressum / About Us