liquid_feedback_core
diff core.sql @ 529:96ee2db56bec
New PostgreSQL extension "pgLatLon" for geospatial operations and indexing in LiquidFeedback
| author | jbe |
|---|---|
| date | Thu Aug 18 20:19:58 2016 +0200 (2016-08-18) |
| parents | 3e28fd842354 |
| children | 5855ff9e5c8f |
line diff
1.1 --- a/core.sql Sun May 29 20:42:40 2016 +0200 1.2 +++ b/core.sql Thu Aug 18 20:19:58 2016 +0200 1.3 @@ -3,6 +3,8 @@ 1.4 1.5 BEGIN; 1.6 1.7 +CREATE EXTENSION latlon; -- load pgLatLon extenstion 1.8 + 1.9 CREATE VIEW "liquid_feedback_version" AS 1.10 SELECT * FROM (VALUES ('4.0.0', 4, 0, 0)) 1.11 AS "subquery"("string", "major", "minor", "revision"); 1.12 @@ -46,586 +48,6 @@ 1.13 1.14 1.15 1.16 ------------------------------------------------ 1.17 --- spatial indexing and distance calculation -- 1.18 ------------------------------------------------ 1.19 - 1.20 - 1.21 -CREATE DOMAIN "geoindex" AS point CHECK (VALUE <@ '((-1,-1),(1,1))'::box); 1.22 - 1.23 -COMMENT ON DOMAIN "geoindex" IS 'Data type used for indexing geographical coordinates'; 1.24 - 1.25 - 1.26 -CREATE FUNCTION "geoindex" 1.27 - ( "latitude_p" FLOAT, 1.28 - "longitude_p" FLOAT ) 1.29 - RETURNS "geoindex" 1.30 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.31 - SELECT point("latitude_p" / 90, "longitude_p" / 180)::"geoindex" 1.32 - $$; 1.33 - 1.34 -COMMENT ON FUNCTION "geoindex" 1.35 - (FLOAT, FLOAT) 1.36 - IS 'Transforms coordinates given as latitude (from -90 to 90) and logitude (from -180 to 180) into a data type suitable for a quadtree index'; 1.37 - 1.38 - 1.39 -CREATE TYPE "geosearch" AS ( 1.40 - "lat" FLOAT, 1.41 - "lon" FLOAT, 1.42 - "c3" FLOAT, 1.43 - "c2" FLOAT, 1.44 - "c1" FLOAT, 1.45 - "c0" FLOAT, 1.46 - "dist" FLOAT, 1.47 - "bbox1" BOX, 1.48 - "bbox2" BOX ); 1.49 - 1.50 -COMMENT ON TYPE "geosearch" IS 'Structure returned by function "geosearch" holding one or two bounding boxes ("bbox1" and optionally "bbox2") for index lookup as well as the starting point (i.e. center point given by "lat" and "lon") and coefficients ("c3" through "c0") for distance approximation'; 1.51 - 1.52 -COMMENT ON COLUMN "geosearch"."lat" IS 'Latitude of center of search area'; 1.53 -COMMENT ON COLUMN "geosearch"."lon" IS 'Longitude of center of search area'; 1.54 -COMMENT ON COLUMN "geosearch"."c3" IS 'Coefficient for distance calculation'; 1.55 -COMMENT ON COLUMN "geosearch"."c2" IS 'Coefficient for distance calculation'; 1.56 -COMMENT ON COLUMN "geosearch"."c1" IS 'Coefficient for distance calculation'; 1.57 -COMMENT ON COLUMN "geosearch"."c0" IS 'Coefficient for distance calculation'; 1.58 -COMMENT ON COLUMN "geosearch"."bbox1" IS 'Bounding box suitable for use with output of "geoindex" function'; 1.59 -COMMENT ON COLUMN "geosearch"."bbox2" IS 'Second bounding box (see column "bbox1") set if search crosses the 180th meridian'; 1.60 - 1.61 - 1.62 -CREATE FUNCTION "geosearch" 1.63 - ( "latitude_p" FLOAT, 1.64 - "longitude_p" FLOAT ) 1.65 - RETURNS "geosearch" 1.66 - LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.67 - DECLARE 1.68 - -- constants: 1.69 - -- (with a = radius at equator, and b = radius at pole) 1.70 - "degrad" FLOAT := 0.017453292519943295; -- pi / 180 1.71 - "degrad2" FLOAT := 0.00030461741978670857; -- (pi / 180)^2 1.72 - "a2" FLOAT := 40680631590769; -- a^2 1.73 - "eps2" FLOAT := 0.006694379990141316; -- (a^2 - b^2) / a^2 1.74 - "subeps2" FLOAT := 0.9933056200098587; -- 1 - "eps2" 1.75 - "subeps22" FLOAT := 0.9866560547431698; -- (1 - "eps2")^2 1.76 - -- latitude in radians: 1.77 - "phi0rad" FLOAT := "degrad" * "latitude_p"; 1.78 - -- helper variables: 1.79 - "sinp0" FLOAT; -- sin("phi0rad") 1.80 - "cosp0" FLOAT; -- cos("phi0rad") 1.81 - "sinp02" FLOAT; -- sin("phi0rad")^2 1.82 - "e2sinp02" FLOAT; -- "eps2" * sin("phi0rad")^2 1.83 - "subsin" FLOAT; -- 1 - sin("phi0rad")^2 1.84 - "subse" FLOAT; -- 1 - "eps2" * sin("phi0rad")^2 1.85 - "subse2" FLOAT; -- ("subse")^2 1.86 - "t20" FLOAT; -- taylor coefficient for (delta latitude)^2 1.87 - "t02" FLOAT; -- taylor coefficient for (delta longitude)^2 / "t20" 1.88 - "t12" FLOAT; -- taylor coefficient for (d lat)^1*(d lon)^2 / "t20" 1.89 - "t22" FLOAT; -- taylor coefficient for (d lat)^2*(d lon)^2 / "t20" 1.90 - -- result: 1.91 - "res" "geosearch"; 1.92 - BEGIN 1.93 - PERFORM "geoindex"("latitude_p", "longitude_p"); -- check bounds 1.94 - "sinp0" := sin("phi0rad"); 1.95 - "cosp0" := cos("phi0rad"); 1.96 - "sinp02" := "sinp0" * "sinp0"; 1.97 - "e2sinp02" := "eps2" * "sinp02"; 1.98 - "subsin" := 1 - "sinp02"; 1.99 - "subse" := 1 - "e2sinp02"; 1.100 - "subse2" := "subse" * "subse"; 1.101 - "t20" := "a2" * "subeps22" / ("subse" * "subse2"); 1.102 - "t02" := "subsin" * "subse2" / "subeps22"; 1.103 - "t12" := -"sinp0" * "cosp0" * "subse" / "subeps2"; 1.104 - "t22" := -"subsin" * (0.5+"e2sinp02") / "subeps2"; 1.105 - "res"."lat" := "latitude_p"; 1.106 - "res"."lon" := "longitude_p"; 1.107 - "res"."c3" := "degrad2" * "t20"; 1.108 - "res"."c2" := "degrad2" * "t22"; 1.109 - "res"."c1" := "degrad" * ("t12" - 2 * "t22" * "phi0rad"); 1.110 - "res"."c0" := ("t22" * "phi0rad" - "t12") * "phi0rad" + "t02"; 1.111 - "res"."dist" := 'Infinity'::FLOAT; 1.112 - "res"."bbox1" := box(point(1, 1), point(-1, -1)); 1.113 - "res"."bbox2" := NULL; 1.114 - RETURN "res"; 1.115 - END; 1.116 - $$; 1.117 - 1.118 -COMMENT ON FUNCTION "geosearch" 1.119 - ( FLOAT, FLOAT ) 1.120 - IS 'Takes latitude and longitude (in degrees), and returns a "geosearch" structure suitable for distance calculation; NOTE: use function with same name but three arguments, i.e. "geosearch"(FLOAT, FLOAT, FLOAT), for searches with limited radius'; 1.121 - 1.122 - 1.123 -CREATE FUNCTION "geosearch" 1.124 - ( "latitude_p" FLOAT, 1.125 - "longitude_p" FLOAT, 1.126 - "radius_p" FLOAT ) 1.127 - RETURNS "geosearch" 1.128 - LANGUAGE plpgsql IMMUTABLE AS $$ 1.129 - DECLARE 1.130 - -- constants: 1.131 - "philimit" FLOAT := 84; -- maximum latitude (needs to be less than 90) 1.132 - "margin" FLOAT := 1.0001; -- slightly increase search area 1.133 - -- NOTE: search area is increased by "margin", search radius gets 1.134 - -- increased by sqrt("margin"); choose a value > 1.0 to avoid 1.135 - -- floating point errors 1.136 - "fixzero" FLOAT := 0.0001; -- small value > 0 to fix singularity 1.137 - -- result: 1.138 - "res" "geosearch"; 1.139 - -- helper variables: 1.140 - "rc3mar" FLOAT; -- ("radius_p")^2 / "c3", multiplied with "margin" 1.141 - "subc02" FLOAT; -- 2 * ("c0" - "c2" * "rc3mar") 1.142 - "b4" FLOAT; -- ("c2")^2 1.143 - "b3" FLOAT; -- 2 * "c1" * "c2" 1.144 - "b2" FLOAT; -- ("c1")^2 + "subc02" * "c2" 1.145 - "b1" FLOAT; -- "subc02" * "c1" 1.146 - "b0" FLOAT; -- ("c2")^2 * ("rc3mar")^2 + 1.147 - -- 2 * "c0" * "c2" - ("c1")^2 * ("rc3mar")^1 + 1.148 - -- ("c0")^2 * ("rc3mar")^0 1.149 - "sqrtval" FLOAT; -- "b4" * ("latitude_p")^4 + 1.150 - -- "b3" * ("latitude_p")^3 + 1.151 - -- "b2" * ("latitude_p")^2 + 1.152 - -- "b1" * ("latitude_p")^1 + 1.153 - -- "b0" * ("latitude_p")^0 1.154 - "phic" FLOAT; -- ( "c2" * (("latitude_p")^2 - "rc3mar") - "c0" 1.155 - -- + sqrt("sqrtval") 1.156 - -- ) / ( 2 * "c2" * "latitude_p" + "c1" ) 1.157 - "dlat" FLOAT; -- delta latitude in degrees = sqrt("rc3mar") 1.158 - "dlon2" FLOAT; -- square of delta longitude 1.159 - "dlon" FLOAT; -- delta longitude in degrees = 1.160 - -- sqrt( 1.161 - -- ( "rc3mar" - ("phic" - "latitude_p")^2 ) / 1.162 - -- ( "c2" * ("phic")^2 + 1.163 - -- "c1" * ("phic")^1 + 1.164 - -- "c0" ) 1.165 - -- ) 1.166 - "lat_min" FLOAT; 1.167 - "lat_max" FLOAT; 1.168 - "lon_min" FLOAT; 1.169 - "lon_max" FLOAT; 1.170 - BEGIN 1.171 - "res" := "geosearch"("latitude_p", "longitude_p"); 1.172 - IF "res" ISNULL THEN RETURN NULL; END IF; 1.173 - IF "radius_p" ISNULL OR "radius_p" = 'Infinity'::FLOAT THEN 1.174 - RETURN "res"; 1.175 - END IF; 1.176 - "res"."dist" := "radius_p"; 1.177 - "rc3mar" := "margin" * "radius_p" * "radius_p" / "res"."c3"; 1.178 - "subc02" := 2 * ("res"."c0" - "res"."c2" * "rc3mar"); 1.179 - "b4" := "res"."c2" * "res"."c2"; 1.180 - "b3" := 2 * "res"."c1" * "res"."c2"; 1.181 - "b2" := "res"."c1" * "res"."c1" + "subc02" * "res"."c2"; 1.182 - "b1" := "subc02" * "res"."c1"; 1.183 - "b0" := ( "b4" * "rc3mar" + 1.184 - 2 * "res"."c0" * "res"."c2" - "res"."c1" * "res"."c1" 1.185 - ) * "rc3mar" + "res"."c0" * "res"."c0"; 1.186 - "sqrtval" := ( ( ( ( "b4" 1.187 - ) * "latitude_p" + "b3" 1.188 - ) * "latitude_p" + "b2" 1.189 - ) * "latitude_p" + "b1" 1.190 - ) * "latitude_p" + "b0"; 1.191 - IF "sqrtval" < 0 THEN 1.192 - IF "latitude_p" >= 0 THEN "phic" := "philimit"; 1.193 - ELSE "phic" := -"philimit"; END IF; 1.194 - ELSE 1.195 - IF abs("latitude_p") <= "fixzero" THEN 1.196 - "phic" := "latitude_p"; 1.197 - ELSE 1.198 - "phic" := ( 1.199 - "res"."c2" * ("latitude_p" * "latitude_p" - "rc3mar") 1.200 - - "res"."c0" + sqrt("sqrtval") 1.201 - ) / ( 1.202 - 2 * "res"."c2" * "latitude_p" + "res"."c1" 1.203 - ); 1.204 - IF "phic" > "philimit" THEN "phic" := "philimit"; 1.205 - ELSIF "phic" < -"philimit" THEN "phic" := -"philimit"; END IF; 1.206 - END IF; 1.207 - END IF; 1.208 - "dlat" := sqrt("rc3mar"); 1.209 - "dlon2" := ( 1.210 - ( "rc3mar" - ("phic" - "latitude_p")^2 ) / 1.211 - ( ( "res"."c2" * "phic" + "res"."c1" ) * "phic" + "res"."c0" ) 1.212 - ); 1.213 - IF "dlon2" > 0 THEN "dlon" := sqrt("dlon2"); 1.214 - ELSE "dlon" := 0; END IF; 1.215 - "lat_min" := "latitude_p" - "dlat"; 1.216 - "lat_max" := "latitude_p" + "dlat"; 1.217 - IF "lat_min" < -90 THEN "lat_min" := -90; END IF; 1.218 - IF "lat_max" > 90 THEN "lat_max" := 90; END IF; 1.219 - "lon_min" := "longitude_p" - "dlon"; 1.220 - "lon_max" := "longitude_p" + "dlon"; 1.221 - IF "lon_min" < -180 THEN 1.222 - IF "lon_max" > 180 THEN 1.223 - "res"."bbox1" := box( 1.224 - "geoindex"("lat_min", -180), 1.225 - "geoindex"("lat_max", 180) ); 1.226 - "res"."bbox2" := NULL; 1.227 - ELSE 1.228 - "res"."bbox1" := box( 1.229 - "geoindex"("lat_min", -180), 1.230 - "geoindex"("lat_max", "lon_max") ); 1.231 - "res"."bbox2" := box( 1.232 - "geoindex"("lat_min", "lon_min" + 360), 1.233 - "geoindex"("lat_max", 180) ); 1.234 - END IF; 1.235 - ELSIF "lon_max" > 180 THEN 1.236 - "res"."bbox1" := box( 1.237 - "geoindex"("lat_min", "lon_min"), 1.238 - "geoindex"("lat_max", 180) ); 1.239 - "res"."bbox2" := box( 1.240 - "geoindex"("lat_min", -180), 1.241 - "geoindex"("lat_max", "lon_max" - 360) ); 1.242 - ELSE 1.243 - "res"."bbox1" := box( 1.244 - "geoindex"("lat_min", "lon_min"), 1.245 - "geoindex"("lat_max", "lon_max") ); 1.246 - "res"."bbox2" := NULL; 1.247 - END IF; 1.248 - RETURN "res"; 1.249 - END; 1.250 - $$; 1.251 - 1.252 -COMMENT ON FUNCTION "geosearch" 1.253 - ( FLOAT, FLOAT, FLOAT ) 1.254 - IS 'Takes latitude, longitude (both in degrees), and a radius (in meters), and returns a "geoindex" structure suitable for searching and distance calculation'; 1.255 - 1.256 - 1.257 -CREATE FUNCTION "geodist" 1.258 - ( "geosearch_p" "geosearch", 1.259 - "latitude_p" FLOAT, 1.260 - "longitude_p" FLOAT ) 1.261 - RETURNS FLOAT 1.262 - LANGUAGE sql IMMUTABLE AS $$ 1.263 - SELECT sqrt( 1.264 - "geosearch_p"."c3" * ( 1.265 - ("latitude_p" - "geosearch_p"."lat")^2 + 1.266 - ( ( "geosearch_p"."c2" * "latitude_p" + "geosearch_p"."c1" ) 1.267 - * "latitude_p" + "geosearch_p"."c0" ) * 1.268 - ( "longitude_p" + 1.269 - CASE WHEN "longitude_p" - "geosearch_p"."lon" > 180 1.270 - THEN -360 ELSE 1.271 - CASE WHEN "longitude_p" - "geosearch_p"."lon" < -180 1.272 - THEN 360 1.273 - ELSE 0 END 1.274 - END - "geosearch_p"."lon" 1.275 - )^2 1.276 - ) 1.277 - ) 1.278 - $$; 1.279 - 1.280 -COMMENT ON FUNCTION "geodist" 1.281 - ( "geosearch", FLOAT, FLOAT ) 1.282 - IS 'Takes a "geosearch" structure as well as geographical coordinates in degrees and returns the distance on earth in meters'; 1.283 - 1.284 - 1.285 -/* Usage of spatial indexing and distance calculation functions for points: 1.286 - 1.287 --- create table containing geographical coordinates in degrees: 1.288 -CREATE TABLE "t1" ( 1.289 - "id" SERIAL4 PRIMARY KEY, 1.290 - "latitude" NUMERIC NOT NULL, 1.291 - "longitude" NUMERIC NOT NULL ); 1.292 - 1.293 --- create an SP-GiST index (with default quad_point_ops operator class): 1.294 -CREATE INDEX "t1_geoindex" ON "t1" USING spgist (("geoindex"("latitude", "longitude"))); 1.295 - 1.296 --- return results within 10,000 meters of Brandenburg Gate: 1.297 -SELECT "t1".*, "distance" 1.298 - FROM "t1" 1.299 - CROSS JOIN "geosearch"(52.5162746, 13.377704, 10000) 1.300 - CROSS JOIN LATERAL "geodist"("geosearch", "latitude", "longitude") AS "distance" 1.301 - WHERE ( 1.302 - "geoindex"("latitude", "longitude") <@ "geosearch"."bbox1" OR 1.303 - "geoindex"("latitude", "longitude") <@ "geosearch"."bbox2" ) 1.304 - AND "distance" <= "geosearch"."dist"; 1.305 - 1.306 -*/ 1.307 - 1.308 - 1.309 -CREATE FUNCTION "polyindex" 1.310 - ( "point_p" POINT ) 1.311 - RETURNS POLYGON 1.312 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.313 - SELECT "point_p"::TEXT::POLYGON; 1.314 - $$; 1.315 - 1.316 -COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a point (with latitude in degrees as first value and longitude in degrees as second value) into a polygon suitable for GiST poly_ops indexing'; 1.317 - 1.318 - 1.319 -CREATE FUNCTION "polyindex" 1.320 - ( "latitude_p" FLOAT, 1.321 - "longitude_p" FLOAT ) 1.322 - RETURNS POLYGON 1.323 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.324 - SELECT "polyindex"(point("latitude_p", "longitude_p")) 1.325 - $$; 1.326 - 1.327 -COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a point given as latitude and longitude in degrees into a polygon suitable for GiST poly_ops indexing'; 1.328 - 1.329 - 1.330 -CREATE FUNCTION "polyindex" 1.331 - ( "polygon_p" POLYGON ) 1.332 - RETURNS POLYGON 1.333 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.334 - SELECT "polygon_p" 1.335 - $$; 1.336 - 1.337 -COMMENT ON FUNCTION "polyindex"(POLYGON) IS 'Does nothing but returning the polygon unchanged (for GiST poly_ops indexing)'; 1.338 - 1.339 - 1.340 -CREATE FUNCTION "polyindex" 1.341 - ( "box_p" BOX ) 1.342 - RETURNS POLYGON 1.343 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.344 - SELECT "box_p"::POLYGON 1.345 - $$; 1.346 - 1.347 -COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a box (with latitude in degrees as first value and longitude in degrees as second value in each point) into a polygon suitable for GiST poly_ops indexing'; 1.348 - 1.349 - 1.350 -CREATE FUNCTION "polyindex" 1.351 - ( "path_p" PATH ) 1.352 - RETURNS POLYGON 1.353 - LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.354 - DECLARE 1.355 - "match_v" TEXT[]; 1.356 - "points_v" TEXT[]; 1.357 - "idx1" INTEGER; 1.358 - "idx2" INTEGER; 1.359 - BEGIN 1.360 - IF isclosed("path_p") THEN 1.361 - RETURN "path_p"::POLYGON; 1.362 - ELSE 1.363 - "points_v" := '{}'; 1.364 - "idx1" := 0; 1.365 - FOR "match_v" IN 1.366 - SELECT regexp_matches("path_p"::TEXT, E'\\(([^()]*)\\)', 'g') 1.367 - LOOP 1.368 - "idx1" := "idx1" + 1; 1.369 - "points_v"["idx1"] := "match_v"[1]; 1.370 - END LOOP; 1.371 - "idx2" := "idx1"; 1.372 - LOOP 1.373 - EXIT WHEN "idx1" < 3; 1.374 - "idx1" := "idx1" - 1; 1.375 - "idx2" := "idx2" + 1; 1.376 - "points_v"["idx2"] := "points_v"["idx1"]; 1.377 - END LOOP; 1.378 - RETURN array_to_string("points_v", ',')::POLYGON; 1.379 - END IF; 1.380 - END; 1.381 - $$; 1.382 - 1.383 -COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a path (with latitude in degrees as first value and longitude in degrees as second value in each point) into a polygon suitable for GiST poly_ops indexing; NOTE: closed PATHs where isclosed(path)=TRUE represent filled polygons, use an open PATH with the last point equal to the first point (e.g. "[1,1,3,4,6,6,1,1]") for a non-filled structure'; 1.384 - 1.385 - 1.386 -CREATE FUNCTION "polyindex" 1.387 - ( "lseg_p" LSEG ) 1.388 - RETURNS POLYGON 1.389 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.390 - SELECT "polyindex"("lseg_p"::TEXT::PATH) 1.391 - $$; 1.392 - 1.393 -COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a line segment (with latitude in degrees as first value and longitude in degrees as second value in each point) into a polygon suitable for GiST poly_ops indexing'; 1.394 - 1.395 - 1.396 -CREATE FUNCTION "geoshift180" 1.397 - ( "point_p" POINT ) 1.398 - RETURNS POINT 1.399 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.400 - SELECT CASE WHEN "point_p" <^ '0,0'::POINT THEN 1.401 - "point_p" + '0,360'::POINT 1.402 - ELSE 1.403 - "point_p" - '0,360'::POINT 1.404 - END 1.405 - $$; 1.406 - 1.407 -COMMENT ON FUNCTION "geoshift180"(POINT) IS 'Returns a point (latitude and longitude in degrees) with its longitude translated by 360 degress to allow for searches crossing the 180th meridian'; 1.408 - 1.409 - 1.410 -CREATE FUNCTION "geoshift180" 1.411 - ( "polygon_p" POLYGON ) 1.412 - RETURNS POLYGON 1.413 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.414 - SELECT CASE WHEN center("polygon_p"::CIRCLE) <^ '0,0'::POINT THEN 1.415 - ("polygon_p"::PATH + '0,360'::POINT)::POLYGON 1.416 - ELSE 1.417 - ("polygon_p"::PATH - '0,360'::POINT)::POLYGON 1.418 - END 1.419 - $$; 1.420 - 1.421 -COMMENT ON FUNCTION "geoshift180"(POLYGON) IS 'Returns a polygon (latitude and longitude in degrees) with its longitude translated by 360 degress to allow for searches crossing the 180th meridian'; 1.422 - 1.423 - 1.424 -CREATE FUNCTION "polysearch" 1.425 - ( "geosearch_p" "geosearch", 1.426 - "steps_p" INTEGER = 24 ) 1.427 - RETURNS POLYGON 1.428 - LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.429 - DECLARE 1.430 - "philimit" FLOAT := 84; -- maximum latitude (needs to be less than 90) 1.431 - "halfsteps" INTEGER; 1.432 - "step" INTEGER := 0; 1.433 - "angle" FLOAT; 1.434 - "dlat" FLOAT := "geosearch_p"."dist" / sqrt("geosearch_p"."c3"); 1.435 - "lat" FLOAT; 1.436 - "dlon" FLOAT; 1.437 - "coords" TEXT[]; 1.438 - BEGIN 1.439 - IF "steps_p" < 4 OR "steps_p" % 2 != 0 THEN 1.440 - RAISE EXCEPTION 'Invalid step count'; 1.441 - END IF; 1.442 - "halfsteps" := "steps_p" / 2; 1.443 - LOOP 1.444 - "angle" := 2.0 * pi() * "step" / "steps_p"; 1.445 - "lat" := "geosearch_p"."lat" + "dlat" * cos("angle"); 1.446 - IF "lat" > "philimit" THEN "lat" := "philimit"; 1.447 - ELSIF "lat" < -"philimit" THEN "lat" := -"philimit"; 1.448 - END IF; 1.449 - "dlon" := "dlat" * sin("angle") / sqrt( 1.450 - ("geosearch_p"."c2" * "lat" + "geosearch_p"."c1") * "lat" + 1.451 - "geosearch_p"."c0" ); 1.452 - "coords"[2*"step"+1] := "lat"; 1.453 - "coords"[2*"step"+2] := "geosearch_p"."lon" + "dlon"; 1.454 - EXIT WHEN "step" >= "halfsteps"; 1.455 - IF "step" > 0 THEN 1.456 - "coords"[2*("steps_p"-"step")+1] := "lat"; 1.457 - "coords"[2*("steps_p"-"step")+2] := "geosearch_p"."lon" - "dlon"; 1.458 - END IF; 1.459 - "step" := "step" + 1; 1.460 - END LOOP; 1.461 - RETURN array_to_string("coords", ',')::POLYGON; 1.462 - END; 1.463 - $$; 1.464 - 1.465 -COMMENT ON FUNCTION "polysearch" 1.466 - ("geosearch", INTEGER) 1.467 - IS 'Returns a polygon approximating the search circle represented by a given "geosearch" structure; The optional second parameter denotes the number of vertices (defaults to 24)'; 1.468 - 1.469 - 1.470 -CREATE FUNCTION "polysearch" 1.471 - ( "latitude_p" FLOAT, 1.472 - "longitude_p" FLOAT, 1.473 - "radius_p" FLOAT, 1.474 - "steps_p" INTEGER = 24 ) 1.475 - RETURNS POLYGON 1.476 - LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.477 - DECLARE 1.478 - "geosearch_v" "geosearch"; 1.479 - BEGIN 1.480 - "geosearch_v" := "geosearch"("latitude_p", "longitude_p"); 1.481 - "geosearch_v"."dist" := "radius_p"; 1.482 - RETURN "polysearch"("geosearch_v", "steps_p"); 1.483 - END; 1.484 - $$; 1.485 - 1.486 -COMMENT ON FUNCTION "polysearch" 1.487 - ("geosearch", INTEGER) 1.488 - IS 'Returns a polygon approximating a search circle around a point given as latitude and longitude in degrees as well as the radius in meters; The optional second parameter denotes the number of vertices (defaults to 24)'; 1.489 - 1.490 - 1.491 -/* Usage of spatial indexing for other basic geometric objects: 1.492 - 1.493 --- create table containing geographical coordinates in degrees: 1.494 -CREATE TABLE "t2" ( 1.495 - "id" SERIAL4 PRIMARY KEY, 1.496 - "geometry" PATH ); 1.497 - 1.498 --- create a GiST (not SP-GiST!) index (with default poly_ops operator class): 1.499 -CREATE INDEX "t2_geoindex" ON "t2" USING gist (("polyindex"("geometry"))); 1.500 - 1.501 --- return results within 10,000 meters of Brandenburg Gate: 1.502 --- NOTE: use && operator for overlapping, use <@ operator for containment 1.503 -SELECT "t2".* 1.504 - FROM "t2" 1.505 - CROSS JOIN "polysearch"(52.5162746, 13.377704, 10000) AS "poly1" 1.506 - CROSS JOIN LATERAL "geoshift180"("poly1") AS "poly2" 1.507 - WHERE ( 1.508 - "polyindex"("geometry") && "poly1" OR 1.509 - "polyindex"("geometry") && "poly2" ); 1.510 - 1.511 -*/ 1.512 - 1.513 - 1.514 -CREATE FUNCTION "scattered_polygon_contains_point" 1.515 - ( "scattered_polygon_p" POLYGON[], 1.516 - "point_p" POINT ) 1.517 - RETURNS BOOLEAN 1.518 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.519 - SELECT count(1) % 2 = 1 1.520 - FROM unnest("scattered_polygon_p") AS "entry" 1.521 - WHERE "entry" @> "point_p" 1.522 - $$; 1.523 - 1.524 -COMMENT ON FUNCTION "scattered_polygon_contains_point" 1.525 - ( POLYGON[], POINT ) 1.526 - IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and, as a second argument, a point; returns TRUE if the number of polygons in which the point is contained is odd'; 1.527 - 1.528 - 1.529 -CREATE FUNCTION "scattered_polygon_contains_point" 1.530 - ( "scattered_polygon_p" POLYGON[], 1.531 - "point_x_p" FLOAT, 1.532 - "point_y_p" FLOAT ) 1.533 - RETURNS BOOLEAN 1.534 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.535 - SELECT "scattered_polygon_contains_point"( 1.536 - "scattered_polygon_p", 1.537 - point("point_x_p", "point_y_p") 1.538 - ) 1.539 - $$; 1.540 - 1.541 -COMMENT ON FUNCTION "scattered_polygon_contains_point" 1.542 - ( POLYGON[], FLOAT, FLOAT ) 1.543 - IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and, as second and third arguments, two coordinates representing a point; returns TRUE if the number of polygons in which the point is contained is odd'; 1.544 - 1.545 - 1.546 -CREATE FUNCTION "scattered_polygon_bound_box" 1.547 - ( "scattered_polygon_p" POLYGON[] ) 1.548 - RETURNS BOX 1.549 - LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.550 - DECLARE 1.551 - "first_v" BOOLEAN; 1.552 - "bound_box_v" BOX; 1.553 - "polygon_v" POLYGON; 1.554 - BEGIN 1.555 - "first_v" := TRUE; 1.556 - FOR "polygon_v" IN SELECT * FROM unnest("scattered_polygon_p") LOOP 1.557 - IF "first_v" THEN 1.558 - "bound_box_v" := box("polygon_v"); 1.559 - "first_v" := FALSE; 1.560 - ELSE 1.561 - "bound_box_v" := bound_box("bound_box_v", box("polygon_v")); 1.562 - END IF; 1.563 - END LOOP; 1.564 - RETURN "bound_box_v"; 1.565 - END; 1.566 - $$; 1.567 - 1.568 -COMMENT ON FUNCTION "scattered_polygon_bound_box" 1.569 - ( POLYGON[] ) 1.570 - IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and returns a bounding box'; 1.571 - 1.572 - 1.573 -/* Usage of spatial indexing for scattered (or hollow) polygons: 1.574 - 1.575 --- create table containing geographical coordinates in degrees: 1.576 -CREATE TABLE "t3" ( 1.577 - "id" SERIAL4 PRIMARY KEY, 1.578 - "region" POLYGON[] ); 1.579 - 1.580 --- create a GiST (not SP-GiST!) index (with default box_ops operator class): 1.581 -CREATE INDEX "t3_geoindex" ON "t3" USING gist (("scattered_polygon_bound_box"("region"))); 1.582 - 1.583 --- return rows containing Brandenburg Gate's location: 1.584 -SELECT "t3".* 1.585 - FROM "t3" 1.586 - CROSS JOIN point(52.5162746, 13.377704) AS "point1" 1.587 - CROSS JOIN LATERAL "geoshift180"("point1") AS "point2" 1.588 - WHERE ( 1.589 - "scattered_polygon_contains_point"("region", "point1") OR 1.590 - "scattered_polygon_contains_point"("region", "point2") ); 1.591 - 1.592 -*/ 1.593 - 1.594 - 1.595 - 1.596 ------------------------- 1.597 -- Tables and indicies -- 1.598 ------------------------- 1.599 @@ -717,8 +139,7 @@ 1.600 "external_posts" TEXT, 1.601 "formatting_engine" TEXT, 1.602 "statement" TEXT, 1.603 - "latitude" NUMERIC, 1.604 - "longitude" NUMERIC, 1.605 + "location" EPOINT, 1.606 "text_search_data" TSVECTOR, 1.607 CONSTRAINT "active_requires_activated_and_last_activity" 1.608 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)), 1.609 @@ -731,12 +152,10 @@ 1.610 CONSTRAINT "notification_dow_requires_notification_hour" 1.611 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL), 1.612 CONSTRAINT "name_not_null_if_activated" 1.613 - CHECK ("activated" ISNULL OR "name" NOTNULL), 1.614 - CONSTRAINT "latitude_and_lontitude_both_null_or_not_null" 1.615 - CHECK (("latitude" NOTNULL) = ("longitude" NOTNULL)) ); 1.616 + CHECK ("activated" ISNULL OR "name" NOTNULL) ); 1.617 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.618 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.619 -CREATE INDEX "member_geolocation_idx" ON "member" USING spgist (("geoindex"("latitude", "longitude"))); 1.620 +CREATE INDEX "member_location_idx" ON "member" USING gist ("location"); 1.621 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); 1.622 CREATE TRIGGER "update_text_search_data" 1.623 BEFORE INSERT OR UPDATE ON "member" 1.624 @@ -789,8 +208,7 @@ 1.625 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization'; 1.626 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; 1.627 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile'; 1.628 -COMMENT ON COLUMN "member"."latitude" IS 'Latitude (in degrees) of member''s location'; 1.629 -COMMENT ON COLUMN "member"."longitude" IS 'Longitude (in degrees) of member''s location'; 1.630 +COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth'; 1.631 1.632 1.633 CREATE TABLE "member_history" ( 1.634 @@ -1034,12 +452,12 @@ 1.635 "description" TEXT NOT NULL DEFAULT '', 1.636 "external_reference" TEXT, 1.637 "member_count" INT4, 1.638 - "region" POLYGON[], 1.639 + "region" ECLUSTER, 1.640 "text_search_data" TSVECTOR ); 1.641 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 1.642 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 1.643 CREATE INDEX "unit_active_idx" ON "unit" ("active"); 1.644 -CREATE INDEX "unit_geolocation_idx" ON "unit" USING gist (("scattered_polygon_bound_box"("region"))); 1.645 +CREATE INDEX "unit_region_idx" ON "unit" USING gist ("region"); 1.646 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); 1.647 CREATE TRIGGER "update_text_search_data" 1.648 BEFORE INSERT OR UPDATE ON "unit" 1.649 @@ -1083,11 +501,11 @@ 1.650 "name" TEXT NOT NULL, 1.651 "description" TEXT NOT NULL DEFAULT '', 1.652 "external_reference" TEXT, 1.653 - "region" POLYGON[], 1.654 + "region" ECLUSTER, 1.655 "text_search_data" TSVECTOR ); 1.656 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); 1.657 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.658 -CREATE INDEX "area_geolocation_idx" ON "area" USING gist (("scattered_polygon_bound_box"("region"))); 1.659 +CREATE INDEX "area_region_idx" ON "area" USING gist ("region"); 1.660 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data"); 1.661 CREATE TRIGGER "update_text_search_data" 1.662 BEFORE INSERT OR UPDATE ON "area" 1.663 @@ -1318,10 +736,8 @@ 1.664 "revoked" TIMESTAMPTZ, 1.665 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.666 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.667 - "latitude1" NUMERIC, 1.668 - "longitude1" NUMERIC, 1.669 - "latitude2" NUMERIC, 1.670 - "longitude2" NUMERIC, 1.671 + "location1" EPOINT, 1.672 + "location2" EPOINT, 1.673 "external_reference" TEXT, 1.674 "admitted" BOOLEAN, 1.675 "supporter_count" INT4, 1.676 @@ -1349,12 +765,8 @@ 1.677 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)), 1.678 CONSTRAINT "non_revoked_initiatives_cant_suggest_other" 1.679 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL), 1.680 - CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null" 1.681 - CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)), 1.682 - CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null" 1.683 - CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)), 1.684 - CONSTRAINT "latutude2_requires_latitude1" 1.685 - CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL), 1.686 + CONSTRAINT "location2_requires_location1" 1.687 + CHECK ("location2" ISNULL OR "location1" NOTNULL), 1.688 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.689 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.690 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.691 @@ -1376,8 +788,8 @@ 1.692 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") ); 1.693 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.694 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.695 -CREATE INDEX "initiative_geolocation1_idx" ON "initiative" USING spgist (("geoindex"("latitude1", "longitude1"))); 1.696 -CREATE INDEX "initiative_geolocation2_idx" ON "initiative" USING spgist (("geoindex"("latitude2", "longitude2"))); 1.697 +CREATE INDEX "initiative_location1_idx" ON "initiative" USING gist ("location1"); 1.698 +CREATE INDEX "initiative_location2_idx" ON "initiative" USING gist ("location2"); 1.699 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.700 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data"); 1.701 CREATE TRIGGER "update_text_search_data" 1.702 @@ -1390,10 +802,8 @@ 1.703 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")'; 1.704 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 1.705 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative'; 1.706 -COMMENT ON COLUMN "initiative"."latitude1" IS 'Latitude (in degrees) of initiative (automatically copied from most recent draft)'; 1.707 -COMMENT ON COLUMN "initiative"."longitude1" IS 'Longitude (in degrees) of initiative (automatically copied from most recent draft)'; 1.708 -COMMENT ON COLUMN "initiative"."latitude2" IS 'Latitude (in degrees) of initiative''s second marker (automatically copied from most recent draft)'; 1.709 -COMMENT ON COLUMN "initiative"."longitude2" IS 'Longitude (in degrees) of initiative''s second marker (automatically copied from most recent draft)'; 1.710 +COMMENT ON COLUMN "initiative"."location1" IS 'Geographic location of initiative (automatically copied from most recent draft)'; 1.711 +COMMENT ON COLUMN "initiative"."location2" IS 'Geographic location of initiative''s second marker (automatically copied from most recent draft)'; 1.712 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference'; 1.713 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.714 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.715 @@ -1462,22 +872,16 @@ 1.716 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.717 "formatting_engine" TEXT, 1.718 "content" TEXT NOT NULL, 1.719 - "latitude1" NUMERIC, 1.720 - "longitude1" NUMERIC, 1.721 - "latitude2" NUMERIC, 1.722 - "longitude2" NUMERIC, 1.723 + "location1" EPOINT, 1.724 + "location2" EPOINT, 1.725 "external_reference" TEXT, 1.726 "text_search_data" TSVECTOR, 1.727 - CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null" 1.728 - CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)), 1.729 - CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null" 1.730 - CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)), 1.731 - CONSTRAINT "latutude2_requires_latitude1" 1.732 - CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL) ); 1.733 + CONSTRAINT "location2_requires_location1" 1.734 + CHECK ("location2" ISNULL OR "location1" NOTNULL) ); 1.735 CREATE INDEX "draft_created_idx" ON "draft" ("created"); 1.736 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); 1.737 -CREATE INDEX "draft_geolocation1_idx" ON "draft" USING spgist (("geoindex"("latitude1", "longitude1"))); 1.738 -CREATE INDEX "draft_geolocation2_idx" ON "draft" USING spgist (("geoindex"("latitude2", "longitude2"))); 1.739 +CREATE INDEX "draft_location1_idx" ON "draft" USING gist ("location1"); 1.740 +CREATE INDEX "draft_location2_idx" ON "draft" USING gist ("location2"); 1.741 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data"); 1.742 CREATE TRIGGER "update_text_search_data" 1.743 BEFORE INSERT OR UPDATE ON "draft" 1.744 @@ -1488,10 +892,8 @@ 1.745 1.746 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used'; 1.747 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"'; 1.748 -COMMENT ON COLUMN "draft"."latitude1" IS 'Latitude (in degrees) of initiative (automatically copied to "initiative" table if draft is most recent)'; 1.749 -COMMENT ON COLUMN "draft"."longitude1" IS 'Longitude (in degrees) of initiative (automatically copied to "initiative" table if draft is most recent)'; 1.750 -COMMENT ON COLUMN "draft"."latitude2" IS 'Latitude (in degrees) of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)'; 1.751 -COMMENT ON COLUMN "draft"."longitude2" IS 'Longitude (in degrees) of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)'; 1.752 +COMMENT ON COLUMN "draft"."location1" IS 'Geographic location of initiative (automatically copied to "initiative" table if draft is most recent)'; 1.753 +COMMENT ON COLUMN "draft"."location2" IS 'Geographic location of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)'; 1.754 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference'; 1.755 1.756 1.757 @@ -1515,10 +917,8 @@ 1.758 "name" TEXT NOT NULL, 1.759 "formatting_engine" TEXT, 1.760 "content" TEXT NOT NULL DEFAULT '', 1.761 - "latitude1" NUMERIC, 1.762 - "longitude1" NUMERIC, 1.763 - "latitude2" NUMERIC, 1.764 - "longitude2" NUMERIC, 1.765 + "location1" EPOINT, 1.766 + "location2" EPOINT, 1.767 "external_reference" TEXT, 1.768 "text_search_data" TSVECTOR, 1.769 "minus2_unfulfilled_count" INT4, 1.770 @@ -1530,16 +930,12 @@ 1.771 "plus2_unfulfilled_count" INT4, 1.772 "plus2_fulfilled_count" INT4, 1.773 "proportional_order" INT4, 1.774 - CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null" 1.775 - CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)), 1.776 - CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null" 1.777 - CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)), 1.778 - CONSTRAINT "latutude2_requires_latitude1" 1.779 - CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL) ); 1.780 + CONSTRAINT "location2_requires_location1" 1.781 + CHECK ("location2" ISNULL OR "location1" NOTNULL) ); 1.782 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.783 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.784 -CREATE INDEX "suggestion_geolocation1_idx" ON "suggestion" USING spgist (("geoindex"("latitude1", "longitude1"))); 1.785 -CREATE INDEX "suggestion_geolocation2_idx" ON "suggestion" USING spgist (("geoindex"("latitude2", "longitude2"))); 1.786 +CREATE INDEX "suggestion_location1_idx" ON "suggestion" USING gist ("location1"); 1.787 +CREATE INDEX "suggestion_location2_idx" ON "suggestion" USING gist ("location2"); 1.788 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.789 CREATE TRIGGER "update_text_search_data" 1.790 BEFORE INSERT OR UPDATE ON "suggestion" 1.791 @@ -1550,10 +946,8 @@ 1.792 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.793 1.794 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.795 -COMMENT ON COLUMN "suggestion"."latitude1" IS 'Latitude (in degrees) of suggestion'; 1.796 -COMMENT ON COLUMN "suggestion"."longitude1" IS 'Longitude (in degrees) of suggestion'; 1.797 -COMMENT ON COLUMN "suggestion"."latitude2" IS 'Latitude (in degrees) of suggestion''s second marker'; 1.798 -COMMENT ON COLUMN "suggestion"."longitude2" IS 'Longitude (in degrees) of suggestion''s second marker'; 1.799 +COMMENT ON COLUMN "suggestion"."location1" IS 'Geographic location of suggestion'; 1.800 +COMMENT ON COLUMN "suggestion"."location2" IS 'Geographic location of suggestion''s second marker'; 1.801 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference'; 1.802 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.803 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.804 @@ -2552,10 +1946,8 @@ 1.805 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p" 1.806 FOR UPDATE; 1.807 UPDATE "initiative" SET 1.808 - "latitude1" = "draft"."latitude1", 1.809 - "longitude1" = "draft"."longitude1", 1.810 - "latitude2" = "draft"."latitude2", 1.811 - "longitude2" = "draft"."longitude2", 1.812 + "location1" = "draft"."location1", 1.813 + "location2" = "draft"."location2", 1.814 "draft_text_search_data" = "draft"."text_search_data" 1.815 FROM "current_draft" AS "draft" 1.816 WHERE "initiative"."id" = "initiative_id_p"