1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/geoindex_install.sql	Fri Feb 05 12:53:00 2021 +0100
     1.3 @@ -0,0 +1,110 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +
     1.7 +CREATE EXTENSION IF NOT EXISTS latlon;
     1.8 +
     1.9 +
    1.10 +------------------------
    1.11 +-- Geospatial indices --
    1.12 +------------------------
    1.13 +
    1.14 +CREATE INDEX IF NOT EXISTS "member_location_idx"
    1.15 +  ON "member"
    1.16 +  USING gist ((GeoJSON_to_ecluster("location")));
    1.17 +CREATE INDEX IF NOT EXISTS "unit_location_idx"
    1.18 +  ON "unit"
    1.19 +  USING gist ((GeoJSON_to_ecluster("location")));
    1.20 +CREATE INDEX IF NOT EXISTS "area_location_idx"
    1.21 +  ON "area"
    1.22 +  USING gist ((GeoJSON_to_ecluster("location")));
    1.23 +CREATE INDEX IF NOT EXISTS "initiative_location_idx"
    1.24 +  ON "initiative"
    1.25 +  USING gist ((GeoJSON_to_ecluster("location")));
    1.26 +CREATE INDEX IF NOT EXISTS "draft_location_idx"
    1.27 +  ON "draft"
    1.28 +  USING gist ((GeoJSON_to_ecluster("location")));
    1.29 +CREATE INDEX IF NOT EXISTS "suggestion_location_idx"
    1.30 +  ON "suggestion"
    1.31 +  USING gist ((GeoJSON_to_ecluster("location")));
    1.32 +
    1.33 +
    1.34 +------------------------
    1.35 +-- Geospatial lookups --
    1.36 +------------------------
    1.37 +
    1.38 +/*
    1.39 +CREATE OR REPLACE FUNCTION "closed_initiatives_in_bounding_box"
    1.40 +  ( "bounding_box_p" EBOX,
    1.41 +    "limit_p"        INT4 )
    1.42 +  RETURNS SETOF "initiative"
    1.43 +  LANGUAGE 'plpgsql' STABLE AS $$
    1.44 +    DECLARE
    1.45 +      "limit_v" INT4;
    1.46 +      "count_v" INT4;
    1.47 +    BEGIN
    1.48 +      "limit_v" := "limit_p" + 1;
    1.49 +      LOOP
    1.50 +        SELECT count(1) INTO "count_v"
    1.51 +          FROM "initiative"
    1.52 +          JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.53 +          WHERE "issue"."closed" NOTNULL
    1.54 +          AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    1.55 +          LIMIT "limit_v";
    1.56 +        IF "count_v" < "limit_v" THEN
    1.57 +          RETURN QUERY SELECT "initiative".*
    1.58 +            FROM (
    1.59 +              SELECT
    1.60 +                "initiative"."id" AS "initiative_id",
    1.61 +                "issue"."closed"
    1.62 +              FROM "initiative"
    1.63 +              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.64 +              WHERE "issue"."closed" NOTNULL
    1.65 +              AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    1.66 +            ) AS "subquery"
    1.67 +            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
    1.68 +            ORDER BY "subquery"."closed" DESC
    1.69 +            LIMIT "limit_p";
    1.70 +          RETURN;
    1.71 +        END IF;
    1.72 +        SELECT count(1) INTO "count_v"
    1.73 +          FROM (
    1.74 +            SELECT "initiative"."id" AS "initiative_id"
    1.75 +            FROM "initiative"
    1.76 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.77 +            WHERE "issue"."closed" NOTNULL
    1.78 +            ORDER BY "closed" DESC
    1.79 +            LIMIT "limit_v"
    1.80 +          ) AS "subquery"
    1.81 +          JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
    1.82 +          WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    1.83 +          LIMIT "limit_p";
    1.84 +        IF "count_v" >= "limit_p" THEN
    1.85 +          RETURN QUERY SELECT "initiative".*
    1.86 +            FROM (
    1.87 +              SELECT
    1.88 +                "initiative"."id" AS "initiative_id",
    1.89 +                "issue"."closed"
    1.90 +              FROM "initiative"
    1.91 +              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.92 +              WHERE "issue"."closed" NOTNULL
    1.93 +              ORDER BY "closed" DESC
    1.94 +              LIMIT "limit_v"
    1.95 +            ) AS "subquery"
    1.96 +            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
    1.97 +            WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    1.98 +            ORDER BY "subquery"."closed" DESC
    1.99 +            LIMIT "limit_p";
   1.100 +          RETURN;
   1.101 +        END IF;
   1.102 +        "limit_v" := "limit_v" * 2;
   1.103 +      END LOOP;
   1.104 +    END;
   1.105 +  $$;
   1.106 +
   1.107 +COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
   1.108 +  ( EBOX, INT4 )
   1.109 +  IS 'TODO';
   1.110 +*/
   1.111 +
   1.112 +
   1.113 +COMMIT;