1.1 --- a/core.sql	Sat Dec 05 18:56:20 2020 +0100
     1.2 +++ b/core.sql	Fri Feb 05 12:53:00 2021 +0100
     1.3 @@ -3,10 +3,8 @@
     1.4  
     1.5  BEGIN;
     1.6  
     1.7 -CREATE EXTENSION IF NOT EXISTS latlon;
     1.8 -
     1.9  CREATE VIEW "liquid_feedback_version" AS
    1.10 -  SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
    1.11 +  SELECT * FROM (VALUES ('4.2.2', 4, 2, 2))
    1.12    AS "subquery"("string", "major", "minor", "revision");
    1.13  
    1.14  
    1.15 @@ -125,7 +123,6 @@
    1.16            CHECK ("activated" ISNULL OR "name" NOTNULL) );
    1.17  CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
    1.18  CREATE INDEX "member_active_idx" ON "member" ("active");
    1.19 -CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
    1.20  
    1.21  COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
    1.22  
    1.23 @@ -640,7 +637,6 @@
    1.24  CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    1.25  CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
    1.26  CREATE INDEX "unit_active_idx" ON "unit" ("active");
    1.27 -CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
    1.28  
    1.29  COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
    1.30  
    1.31 @@ -679,7 +675,6 @@
    1.32          "external_reference"    TEXT,
    1.33          "location"              JSONB );
    1.34  CREATE INDEX "area_active_idx" ON "area" ("active");
    1.35 -CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location")));
    1.36  
    1.37  COMMENT ON TABLE "area" IS 'Subject areas';
    1.38  
    1.39 @@ -928,7 +923,6 @@
    1.40          CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
    1.41          CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
    1.42  CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    1.43 -CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
    1.44  
    1.45  COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
    1.46  
    1.47 @@ -998,7 +992,6 @@
    1.48          "external_reference"    TEXT );
    1.49  CREATE INDEX "draft_created_idx" ON "draft" ("created");
    1.50  CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
    1.51 -CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
    1.52  
    1.53  COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
    1.54  
    1.55 @@ -1051,7 +1044,6 @@
    1.56          "proportional_order"    INT4 );
    1.57  CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    1.58  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    1.59 -CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
    1.60  
    1.61  COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
    1.62  
    1.63 @@ -4868,86 +4860,6 @@
    1.64  
    1.65  
    1.66  
    1.67 -------------------------
    1.68 --- Geospatial lookups --
    1.69 -------------------------
    1.70 -
    1.71 -/*
    1.72 -CREATE FUNCTION "closed_initiatives_in_bounding_box"
    1.73 -  ( "bounding_box_p" EBOX,
    1.74 -    "limit_p"        INT4 )
    1.75 -  RETURNS SETOF "initiative"
    1.76 -  LANGUAGE 'plpgsql' STABLE AS $$
    1.77 -    DECLARE
    1.78 -      "limit_v" INT4;
    1.79 -      "count_v" INT4;
    1.80 -    BEGIN
    1.81 -      "limit_v" := "limit_p" + 1;
    1.82 -      LOOP
    1.83 -        SELECT count(1) INTO "count_v"
    1.84 -          FROM "initiative"
    1.85 -          JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.86 -          WHERE "issue"."closed" NOTNULL
    1.87 -          AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    1.88 -          LIMIT "limit_v";
    1.89 -        IF "count_v" < "limit_v" THEN
    1.90 -          RETURN QUERY SELECT "initiative".*
    1.91 -            FROM (
    1.92 -              SELECT
    1.93 -                "initiative"."id" AS "initiative_id",
    1.94 -                "issue"."closed"
    1.95 -              FROM "initiative"
    1.96 -              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.97 -              WHERE "issue"."closed" NOTNULL
    1.98 -              AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    1.99 -            ) AS "subquery"
   1.100 -            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
   1.101 -            ORDER BY "subquery"."closed" DESC
   1.102 -            LIMIT "limit_p";
   1.103 -          RETURN;
   1.104 -        END IF;
   1.105 -        SELECT count(1) INTO "count_v"
   1.106 -          FROM (
   1.107 -            SELECT "initiative"."id" AS "initiative_id"
   1.108 -            FROM "initiative"
   1.109 -            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.110 -            WHERE "issue"."closed" NOTNULL
   1.111 -            ORDER BY "closed" DESC
   1.112 -            LIMIT "limit_v"
   1.113 -          ) AS "subquery"
   1.114 -          JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
   1.115 -          WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
   1.116 -          LIMIT "limit_p";
   1.117 -        IF "count_v" >= "limit_p" THEN
   1.118 -          RETURN QUERY SELECT "initiative".*
   1.119 -            FROM (
   1.120 -              SELECT
   1.121 -                "initiative"."id" AS "initiative_id",
   1.122 -                "issue"."closed"
   1.123 -              FROM "initiative"
   1.124 -              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.125 -              WHERE "issue"."closed" NOTNULL
   1.126 -              ORDER BY "closed" DESC
   1.127 -              LIMIT "limit_v"
   1.128 -            ) AS "subquery"
   1.129 -            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
   1.130 -            WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
   1.131 -            ORDER BY "subquery"."closed" DESC
   1.132 -            LIMIT "limit_p";
   1.133 -          RETURN;
   1.134 -        END IF;
   1.135 -        "limit_v" := "limit_v" * 2;
   1.136 -      END LOOP;
   1.137 -    END;
   1.138 -  $$;
   1.139 -
   1.140 -COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
   1.141 -  ( EBOX, INT4 )
   1.142 -  IS 'TODO';
   1.143 -*/
   1.144 -
   1.145 -
   1.146 -
   1.147  ---------------------------
   1.148  -- Transaction isolation --
   1.149  ---------------------------