liquid_feedback_core
changeset 577:3536fb4148dc
Merged experimental workaround for missing spatio-temporal index as commented-out section
author | jbe |
---|---|
date | Thu Feb 22 11:24:55 2018 +0100 (2018-02-22) |
parents | eb5f4e4f17d8 f28662013308 |
children | 02a6149822e0 |
files | core.sql |
line diff
1.1 --- a/core.sql Mon Oct 23 15:14:11 2017 +0200 1.2 +++ b/core.sql Thu Feb 22 11:24:55 2018 +0100 1.3 @@ -4554,6 +4554,86 @@ 1.4 1.5 1.6 1.7 +------------------------ 1.8 +-- Geospatial lookups -- 1.9 +------------------------ 1.10 + 1.11 +/* 1.12 +CREATE FUNCTION "closed_initiatives_in_bounding_box" 1.13 + ( "bounding_box_p" EBOX, 1.14 + "limit_p" INT4 ) 1.15 + RETURNS SETOF "initiative" 1.16 + LANGUAGE 'plpgsql' STABLE AS $$ 1.17 + DECLARE 1.18 + "limit_v" INT4; 1.19 + "count_v" INT4; 1.20 + BEGIN 1.21 + "limit_v" := "limit_p" + 1; 1.22 + LOOP 1.23 + SELECT count(1) INTO "count_v" 1.24 + FROM "initiative" 1.25 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.26 + WHERE "issue"."closed" NOTNULL 1.27 + AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.28 + LIMIT "limit_v"; 1.29 + IF "count_v" < "limit_v" THEN 1.30 + RETURN QUERY SELECT "initiative".* 1.31 + FROM ( 1.32 + SELECT 1.33 + "initiative"."id" AS "initiative_id", 1.34 + "issue"."closed" 1.35 + FROM "initiative" 1.36 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.37 + WHERE "issue"."closed" NOTNULL 1.38 + AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.39 + ) AS "subquery" 1.40 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.41 + ORDER BY "subquery"."closed" DESC 1.42 + LIMIT "limit_p"; 1.43 + RETURN; 1.44 + END IF; 1.45 + SELECT count(1) INTO "count_v" 1.46 + FROM ( 1.47 + SELECT "initiative"."id" AS "initiative_id" 1.48 + FROM "initiative" 1.49 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.50 + WHERE "issue"."closed" NOTNULL 1.51 + ORDER BY "closed" DESC 1.52 + LIMIT "limit_v" 1.53 + ) AS "subquery" 1.54 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.55 + WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.56 + LIMIT "limit_p"; 1.57 + IF "count_v" >= "limit_p" THEN 1.58 + RETURN QUERY SELECT "initiative".* 1.59 + FROM ( 1.60 + SELECT 1.61 + "initiative"."id" AS "initiative_id", 1.62 + "issue"."closed" 1.63 + FROM "initiative" 1.64 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.65 + WHERE "issue"."closed" NOTNULL 1.66 + ORDER BY "closed" DESC 1.67 + LIMIT "limit_v" 1.68 + ) AS "subquery" 1.69 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.70 + WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.71 + ORDER BY "subquery"."closed" DESC 1.72 + LIMIT "limit_p"; 1.73 + RETURN; 1.74 + END IF; 1.75 + "limit_v" := "limit_v" * 2; 1.76 + END LOOP; 1.77 + END; 1.78 + $$; 1.79 + 1.80 +COMMENT ON FUNCTION "closed_initiatives_in_bounding_box" 1.81 + ( EBOX, INT4 ) 1.82 + IS 'TODO'; 1.83 +*/ 1.84 + 1.85 + 1.86 + 1.87 --------------------------- 1.88 -- Transaction isolation -- 1.89 ---------------------------