pgLatLon

annotate create_test_db.schema.sql @ 79:16787a19a325

find changesets by author, revision, files, or words in the commit message
Do not return CSTRING to static memory

Avoids wrong pfree under certain circumstances
(e.g. array_agg on empty EBOX or ECLUSTER).
author jbe
date Thu Oct 23 10:16:01 2025 +0200 (3 days ago)
parents 3cbce515387f
children
rev   line source
jbe@0 1
jbe@0 2 CREATE EXTENSION latlon;
jbe@0 3
jbe@0 4 CREATE TABLE "test" (
jbe@0 5 "id" SERIAL4 PRIMARY KEY,
jbe@0 6 "location" EPOINT NOT NULL,
jbe@10 7 "surrounding" ECIRCLE NOT NULL,
jbe@10 8 "multipoint" ECLUSTER NOT NULL,
jbe@46 9 "triangle" ECLUSTER NOT NULL,
jbe@46 10 "votes" INT4 NOT NULL );
jbe@0 11
jbe@10 12 CREATE INDEX "test_location_key" ON "test" USING gist ("location");
jbe@10 13 CREATE INDEX "test_surrounding_key" ON "test" USING gist ("surrounding");
jbe@10 14 CREATE INDEX "test_multipoint_key" ON "test" USING gist ("multipoint");
jbe@10 15 CREATE INDEX "test_triangle_key" ON "test" USING gist ("triangle");
jbe@46 16 CREATE INDEX "test_vote_key" ON "test" ("votes");
jbe@0 17
jbe@46 18
jbe@46 19 -- Below follows an example of how to perform a nearest-neighbor search with
jbe@46 20 -- weighted geometric objects (distance scaled anti-proportionally through
jbe@46 21 -- "votes" column).
jbe@46 22 --
jbe@46 23 -- NOTE: The approach may be speeded up by providing new data types like
jbe@46 24 -- "weighted_ecluster" with corresponding GiST index support in future
jbe@46 25 -- versions of pgLatLon.
jbe@46 26
jbe@46 27 CREATE TYPE "test_with_relevance" AS (
jbe@46 28 "id" INT4,
jbe@46 29 "location" EPOINT,
jbe@46 30 "surrounding" ECIRCLE,
jbe@46 31 "multipoint" ECLUSTER,
jbe@46 32 "triangle" ECLUSTER,
jbe@46 33 "votes" INT4,
jbe@46 34 "relevance" FLOAT8 );
jbe@46 35
jbe@46 36 CREATE FUNCTION "get_by_relevance" (epoint, int4 = 1, int4 = 10000)
jbe@46 37 RETURNS SETOF "test_with_relevance"
jbe@46 38 LANGUAGE plpgsql STABLE AS $$
jbe@46 39 DECLARE
jbe@46 40 "max_votes" INT4;
jbe@46 41 "tries" INT4 = 2;
jbe@46 42 "all" INT4;
jbe@46 43 "matches" INT4;
jbe@46 44 BEGIN
jbe@46 45 SELECT "votes" INTO "max_votes" FROM "test" ORDER BY "votes" DESC LIMIT 1;
jbe@46 46 IF "max_votes" > 0 THEN
jbe@46 47 LOOP
jbe@46 48 RAISE DEBUG 'Considering % entries', "tries";
jbe@46 49 SELECT
jbe@46 50 count(1),
jbe@46 51 count(CASE WHEN "relevance" < "worst_case" THEN 1 ELSE NULL END)
jbe@46 52 INTO "all", "matches"
jbe@46 53 FROM (
jbe@46 54 SELECT
jbe@46 55 CASE
jbe@46 56 WHEN "votes" = 0
jbe@46 57 THEN 'inf'::FLOAT8
jbe@46 58 ELSE "fair_distance" / "votes"
jbe@46 59 END AS "relevance",
jbe@46 60 max("fair_distance") OVER () / "max_votes" AS "worst_case"
jbe@46 61 FROM (
jbe@46 62 SELECT fair_distance("triangle", $1, $3), "votes" FROM "test"
jbe@46 63 ORDER BY fair_distance
jbe@46 64 LIMIT "tries"
jbe@46 65 ) AS "subquery1"
jbe@46 66 ) AS "subquery2";
jbe@46 67 EXIT WHEN "matches" >= $2 OR "all" < "tries";
jbe@46 68 "tries" := "tries" * 2;
jbe@46 69 END LOOP;
jbe@46 70 RETURN QUERY SELECT * FROM (
jbe@46 71 SELECT
jbe@46 72 *,
jbe@46 73 CASE
jbe@46 74 WHEN "votes" = 0
jbe@46 75 THEN 'inf'::FLOAT8
jbe@46 76 ELSE fair_distance("triangle", $1, $3) / "votes"
jbe@46 77 END AS "relevance"
jbe@46 78 FROM "test" ORDER BY fair_distance("triangle", $1, $3) LIMIT "tries"
jbe@46 79 ) AS "subquery" ORDER BY "relevance", "id" LIMIT $2;
jbe@46 80 ELSE
jbe@46 81 RETURN QUERY SELECT * FROM "test" ORDER BY "id" LIMIT $2;
jbe@46 82 END IF;
jbe@46 83 RETURN;
jbe@46 84 END;
jbe@46 85 $$;
jbe@46 86

Impressum / About Us