· 6 years ago · Aug 22, 2019, 03:12 AM
1create or replace function hannover.world_grid(tbl text, square_size integer)
2returns void AS $grid$
3
4begin
5
6 drop table if exists coordinates_dist;
7 create table if not exists coordinates_dist (
8 long float8,
9 lat float8
10 );
11
12 drop table if exists uniq_squares;
13 create table if not exists uniq_squares (
14 uuid uuid,
15 geom geometry,
16 water bool,
17 terrain bool
18 );
19
20 drop table if exists region_matchs;
21 create table if not exists region_matchs (
22 square uuid,
23 region uuid,
24 area float8
25 );
26
27 drop table if exists square_matchs;
28 create table if not exists square_matchs (
29 square uuid,
30 region uuid,
31 area float8,
32 geoid character varying(255)
33 );
34
35 drop table if exists square_costs;
36 create table if not exists square_costs (
37 square uuid,
38 land smallint,
39 naval smallint,
40 air smallint
41 );
42
43
44 execute format($$
45 insert into coordinates_dist (long,lat)
46 SELECT -180 + j*1 AS long, -90 + i*1 AS lat
47 FROM generate_series(0, ceil(180/1 - 1)::int ) AS i,
48 generate_series(0, ceil(360/1 - 1)::int ) AS j;
49 $$,square_size);
50
51
52 execute format($$
53 insert into uniq_squares (uuid,geom,water,terrain)
54 SELECT public.uuid_generate_v5(public.uuid_nil(), CAST( coord.lat AS text ) || CAST( coord.long AS text )) AS uuid,
55 ST_Translate(('LINESTRING(0 0, 0 1 , 1 1 , 1 0, 0 0)')::geometry,coord.long,coord.lat) as geom,
56 true as water,
57 false as terrain
58 FROM coordinates_dist as coord;
59 $$, square_size);
60
61 execute format($$
62 insert into region_matchs (square,region,area)
63 select
64 sq.uuid, reg.uuid, st_area(st_intersection(Box2D(sq.geom), reg.geom))
65 from
66 (SELECT uuid, geom
67 FROM hannover.hvrgeo_0) as reg,
68 (SELECT uuid, geom
69 FROM uniq_squares) as sq
70 where ST_Intersects(sq.geom,reg.geom);
71 $$);
72
73 execute format($$
74 update uniq_squares as uq
75 set
76 terrain = true,
77 water = false
78 from
79 (select
80 rm.square as sq
81 from
82 region_matchs AS rm
83 join hannover.hvrgeo_0 AS hh on hh.uuid = rm.region
84 join uniq_squares AS us on us.uuid = rm.square
85 group by rm.square
86 having SUM(rm.area/1) > 0.5) as ts
87 where ts.sq = uq.uuid;
88 $$);
89
90 execute format($$
91
92 with max_stuff as (
93 select square, max(rm.area) as max_area
94 from region_matchs AS rm
95 group by rm.square)
96
97 insert into square_matchs (square,region,area,geoid)
98 select rm2.square,
99 rm2.region,
100 rm2.area,
101 hh.geoid
102 from region_matchs AS rm2
103 join max_stuff AS ms
104 on rm2.square = ms.square
105 and rm2.area = ms.max_area
106 join hannover.regions AS hh
107 on hh.uuid = rm2.region
108 $$);
109
110 execute format($$
111 delete from square_matchs as sq
112 where sq.square = (SELECT uuid
113 FROM uniq_squares as uq
114 WHERE water and uq.uuid = sq.square)
115 $$);
116
117 execute format($$
118 insert into square_costs (square,land,naval,air)
119 SELECT uq.uuid as square,255 as land, 1 as naval, 1 as air
120 FROM uniq_squares as uq
121 WHERE uq.water
122 $$);
123
124 execute format($$
125 insert into square_costs (square,land,naval,air)
126 SELECT uq.uuid as square, 1 as land, 255 as naval, 1 as air
127 FROM uniq_squares as uq
128 WHERE uq.terrain
129 $$);
130
131
132 execute format($$
133 truncate %1$s
134 $$, tbl);
135
136 execute format($$
137 insert into %1$s (uuid,geom,water,terrain,cost)
138 select uuid, geom, water,terrain,cost
139 from uniq_squares;
140 $$, tbl);
141end
142
143$grid$ language plpgsql strict;