jbe@622: BEGIN;
jbe@622: 
jbe@622: 
jbe@622: CREATE EXTENSION IF NOT EXISTS latlon;
jbe@622: 
jbe@622: 
jbe@622: ------------------------
jbe@622: -- Geospatial indices --
jbe@622: ------------------------
jbe@622: 
jbe@622: CREATE INDEX IF NOT EXISTS "member_location_idx"
jbe@622:   ON "member"
jbe@622:   USING gist ((GeoJSON_to_ecluster("location")));
jbe@622: CREATE INDEX IF NOT EXISTS "unit_location_idx"
jbe@622:   ON "unit"
jbe@622:   USING gist ((GeoJSON_to_ecluster("location")));
jbe@622: CREATE INDEX IF NOT EXISTS "area_location_idx"
jbe@622:   ON "area"
jbe@622:   USING gist ((GeoJSON_to_ecluster("location")));
jbe@622: CREATE INDEX IF NOT EXISTS "initiative_location_idx"
jbe@622:   ON "initiative"
jbe@622:   USING gist ((GeoJSON_to_ecluster("location")));
jbe@622: CREATE INDEX IF NOT EXISTS "draft_location_idx"
jbe@622:   ON "draft"
jbe@622:   USING gist ((GeoJSON_to_ecluster("location")));
jbe@622: CREATE INDEX IF NOT EXISTS "suggestion_location_idx"
jbe@622:   ON "suggestion"
jbe@622:   USING gist ((GeoJSON_to_ecluster("location")));
jbe@622: 
jbe@622: 
jbe@622: ------------------------
jbe@622: -- Geospatial lookups --
jbe@622: ------------------------
jbe@622: 
jbe@622: /*
jbe@622: CREATE OR REPLACE FUNCTION "closed_initiatives_in_bounding_box"
jbe@622:   ( "bounding_box_p" EBOX,
jbe@622:     "limit_p"        INT4 )
jbe@622:   RETURNS SETOF "initiative"
jbe@622:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@622:     DECLARE
jbe@622:       "limit_v" INT4;
jbe@622:       "count_v" INT4;
jbe@622:     BEGIN
jbe@622:       "limit_v" := "limit_p" + 1;
jbe@622:       LOOP
jbe@622:         SELECT count(1) INTO "count_v"
jbe@622:           FROM "initiative"
jbe@622:           JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622:           WHERE "issue"."closed" NOTNULL
jbe@622:           AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622:           LIMIT "limit_v";
jbe@622:         IF "count_v" < "limit_v" THEN
jbe@622:           RETURN QUERY SELECT "initiative".*
jbe@622:             FROM (
jbe@622:               SELECT
jbe@622:                 "initiative"."id" AS "initiative_id",
jbe@622:                 "issue"."closed"
jbe@622:               FROM "initiative"
jbe@622:               JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622:               WHERE "issue"."closed" NOTNULL
jbe@622:               AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622:             ) AS "subquery"
jbe@622:             JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
jbe@622:             ORDER BY "subquery"."closed" DESC
jbe@622:             LIMIT "limit_p";
jbe@622:           RETURN;
jbe@622:         END IF;
jbe@622:         SELECT count(1) INTO "count_v"
jbe@622:           FROM (
jbe@622:             SELECT "initiative"."id" AS "initiative_id"
jbe@622:             FROM "initiative"
jbe@622:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622:             WHERE "issue"."closed" NOTNULL
jbe@622:             ORDER BY "closed" DESC
jbe@622:             LIMIT "limit_v"
jbe@622:           ) AS "subquery"
jbe@622:           JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
jbe@622:           WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622:           LIMIT "limit_p";
jbe@622:         IF "count_v" >= "limit_p" THEN
jbe@622:           RETURN QUERY SELECT "initiative".*
jbe@622:             FROM (
jbe@622:               SELECT
jbe@622:                 "initiative"."id" AS "initiative_id",
jbe@622:                 "issue"."closed"
jbe@622:               FROM "initiative"
jbe@622:               JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@622:               WHERE "issue"."closed" NOTNULL
jbe@622:               ORDER BY "closed" DESC
jbe@622:               LIMIT "limit_v"
jbe@622:             ) AS "subquery"
jbe@622:             JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
jbe@622:             WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
jbe@622:             ORDER BY "subquery"."closed" DESC
jbe@622:             LIMIT "limit_p";
jbe@622:           RETURN;
jbe@622:         END IF;
jbe@622:         "limit_v" := "limit_v" * 2;
jbe@622:       END LOOP;
jbe@622:     END;
jbe@622:   $$;
jbe@622: 
jbe@622: COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
jbe@622:   ( EBOX, INT4 )
jbe@622:   IS 'TODO';
jbe@622: */
jbe@622: 
jbe@622: 
jbe@622: COMMIT;