-
Notifications
You must be signed in to change notification settings - Fork 0
/
bluebox_schema.sql
9686 lines (6246 loc) · 299 KB
/
bluebox_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
--
-- PostgreSQL database dump
--
-- Dumped from database version 16.0 (Debian 16.0-1.pgdg110+1)
-- Dumped by pg_dump version 16.4 (Ubuntu 16.4-1.pgdg20.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: convert; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA convert;
--
-- Name: SCHEMA convert; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA convert IS 'Contains functions created by convert extension. Helpful conversion functions for speed, distance, area, power, and travel time.';
--
-- Name: pg_cron; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION pg_cron; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';
--
-- Name: dd; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA dd;
--
-- Name: SCHEMA dd; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA dd IS 'Schema for Data Dictionary objects. See https://github.com/rustprooflabs/pgdd';
--
-- Name: osm_seattle; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA osm_seattle;
--
-- Name: SCHEMA osm_seattle; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA osm_seattle IS 'Schema populated by PgOSM Flex. SELECT * FROM osm_seattle.pgosm_flex; for details.';
--
-- Name: osm_spokane; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA osm_spokane;
--
-- Name: SCHEMA osm_spokane; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA osm_spokane IS 'Schema populated by PgOSM Flex. SELECT * FROM osm_spokane.pgosm_flex; for details.';
--
-- Name: osm_wa; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA osm_wa;
--
-- Name: SCHEMA osm_wa; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA osm_wa IS 'Schema populated by PgOSM Flex. SELECT * FROM osm_wa.pgosm_flex; for details.';
--
-- Name: pgosm; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA pgosm;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: -
--
-- *not* creating schema, since initdb creates it
--
-- Name: routing; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA routing;
--
-- Name: tiger; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA tiger;
--
-- Name: tiger_data; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA tiger_data;
--
-- Name: topology; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA topology;
--
-- Name: SCHEMA topology; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA topology IS 'PostGIS Topology schema';
--
-- Name: fuzzystrmatch; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
--
-- Name: EXTENSION fuzzystrmatch; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION fuzzystrmatch IS 'determine similarities and distance between strings';
--
-- Name: pg_stat_statements; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
--
-- Name: EXTENSION pg_stat_statements; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION pg_stat_statements IS 'track planning and execution statistics of all SQL statements executed';
--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';
--
-- Name: postgis_raster; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS postgis_raster WITH SCHEMA public;
--
-- Name: EXTENSION postgis_raster; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION postgis_raster IS 'PostGIS raster types and functions';
--
-- Name: postgis_tiger_geocoder; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
--
-- Name: EXTENSION postgis_tiger_geocoder; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION postgis_tiger_geocoder IS 'PostGIS tiger geocoder and reverse geocoder';
--
-- Name: postgis_topology; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
--
-- Name: EXTENSION postgis_topology; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION postgis_topology IS 'PostGIS topology spatial types and functions';
--
-- Name: append_data_finish(boolean); Type: PROCEDURE; Schema: osm_seattle; Owner: -
--
CREATE PROCEDURE osm_seattle.append_data_finish(IN skip_nested boolean DEFAULT false)
LANGUAGE plpgsql
AS $_$
BEGIN
REFRESH MATERIALIZED VIEW osm_seattle.vplace_polygon_subdivide;
IF $1 = False THEN
RAISE NOTICE 'Populating nested place table';
CALL osm_seattle.populate_place_polygon_nested();
RAISE NOTICE 'Calculating nesting of place polygons';
CALL osm_seattle.build_nested_admin_polygons();
END IF;
END $_$;
--
-- Name: PROCEDURE append_data_finish(IN skip_nested boolean); Type: COMMENT; Schema: osm_seattle; Owner: -
--
COMMENT ON PROCEDURE osm_seattle.append_data_finish(IN skip_nested boolean) IS 'Finalizes PgOSM Flex after osm2pgsql-replication. Refreshes materialized view and (optionally) processes the place_polygon_nested data.';
--
-- Name: append_data_start(); Type: PROCEDURE; Schema: osm_seattle; Owner: -
--
CREATE PROCEDURE osm_seattle.append_data_start()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Truncating table osm_seattle.place_polygon_nested;';
TRUNCATE TABLE osm_seattle.place_polygon_nested;
END $$;
--
-- Name: PROCEDURE append_data_start(); Type: COMMENT; Schema: osm_seattle; Owner: -
--
COMMENT ON PROCEDURE osm_seattle.append_data_start() IS 'Prepares PgOSM Flex database for running osm2pgsql in append mode. Removes records from place_polygon_nested if they existed.';
--
-- Name: build_nested_admin_polygons(bigint); Type: PROCEDURE; Schema: osm_seattle; Owner: -
--
CREATE PROCEDURE osm_seattle.build_nested_admin_polygons(IN batch_row_limit bigint DEFAULT 100)
LANGUAGE plpgsql
AS $_$
DECLARE
rows_to_update BIGINT;
BEGIN
SELECT COUNT(*) INTO rows_to_update
FROM osm_seattle.place_polygon_nested r
WHERE nest_level IS NULL
;
RAISE NOTICE 'Rows to update: %', rows_to_update;
RAISE NOTICE 'Updating in batches of % rows', $1;
FOR counter IN 1..rows_to_update by $1 LOOP
DROP TABLE IF EXISTS places_for_nesting;
CREATE TEMP TABLE places_for_nesting AS
SELECT p.osm_id
FROM osm_seattle.place_polygon_nested p
WHERE p.name IS NOT NULL
AND (admin_level IS NOT NULL
OR osm_type IN ('boundary', 'admin_level', 'suburb',
'neighbourhood')
)
;
CREATE UNIQUE INDEX tmp_ix_places_for_nesting
ON places_for_nesting (osm_id);
DROP TABLE IF EXISTS place_batch;
CREATE TEMP TABLE place_batch AS
SELECT p.osm_id, t.nest_level, t.name_path, t.osm_id_path, t.admin_level_path
FROM osm_seattle.place_polygon p
INNER JOIN LATERAL (
SELECT COUNT(i.osm_id) AS nest_level,
ARRAY_AGG(i.name ORDER BY COALESCE(i.admin_level::INT, 99::INT) ASC) AS name_path,
ARRAY_AGG(i.osm_id ORDER BY COALESCE(i.admin_level::INT, 99::INT) ASC) AS osm_id_path,
ARRAY_AGG(COALESCE(i.admin_level::INT, 99::INT) ORDER BY i.admin_level ASC) AS admin_level_path
FROM osm_seattle.place_polygon i
WHERE ST_Within(p.geom, i.geom)
AND EXISTS (
SELECT 1 FROM places_for_nesting include
WHERE i.osm_id = include.osm_id
)
AND i.name IS NOT NULL
) t ON True
WHERE EXISTS (
SELECT 1 FROM osm_seattle.place_polygon_nested miss
WHERE miss.nest_level IS NULL
AND p.osm_id = miss.osm_id
)
AND EXISTS (
SELECT 1 FROM places_for_nesting include
WHERE p.osm_id = include.osm_id
)
LIMIT $1
;
UPDATE osm_seattle.place_polygon_nested n
SET nest_level = t.nest_level,
name_path = t.name_path,
osm_id_path = t.osm_id_path,
admin_level_path = t.admin_level_path
FROM place_batch t
WHERE n.osm_id = t.osm_id
;
COMMIT;
END LOOP;
DROP TABLE IF EXISTS place_batch;
DROP TABLE IF EXISTS places_for_nesting;
-- With all nested paths calculated the innermost value can be determined.
WITH calc_inner AS (
SELECT a.osm_id
FROM osm_seattle.place_polygon_nested a
WHERE a.row_innermost -- Start with per row check...
-- If an osm_id is found in any other path, cannot be innermost
AND NOT EXISTS (
SELECT 1
FROM osm_seattle.place_polygon_nested i
WHERE a.osm_id <> i.osm_id
AND a.osm_id = ANY(osm_id_path)
)
)
UPDATE osm_seattle.place_polygon_nested n
SET innermost = True
FROM calc_inner i
WHERE n.osm_id = i.osm_id
;
END $_$;
--
-- Name: PROCEDURE build_nested_admin_polygons(IN batch_row_limit bigint); Type: COMMENT; Schema: osm_seattle; Owner: -
--
COMMENT ON PROCEDURE osm_seattle.build_nested_admin_polygons(IN batch_row_limit bigint) IS 'Warning: Expensive procedure! Use to populate the osm_seattle.place_polygon_nested table. This procedure is not ran as part of SQL script automatically due to excessive run time on large regions.';
--
-- Name: populate_place_polygon_nested(); Type: PROCEDURE; Schema: osm_seattle; Owner: -
--
CREATE PROCEDURE osm_seattle.populate_place_polygon_nested()
LANGUAGE sql
AS $$
INSERT INTO osm_seattle.place_polygon_nested (osm_id, name, osm_type, admin_level, geom)
SELECT p.osm_id, p.name, p.osm_type,
COALESCE(p.admin_level::INT, 99) AS admin_level,
geom
FROM osm_seattle.place_polygon p
WHERE (p.boundary = 'administrative'
OR p.osm_type IN ('neighborhood', 'city', 'suburb', 'town', 'admin_level', 'locality')
)
AND p.name IS NOT NULL
AND NOT EXISTS (
SELECT osm_id
FROM osm_seattle.place_polygon_nested n
WHERE n.osm_id = p.osm_id
)
;
$$;
--
-- Name: append_data_finish(boolean); Type: PROCEDURE; Schema: osm_spokane; Owner: -
--
CREATE PROCEDURE osm_spokane.append_data_finish(IN skip_nested boolean DEFAULT false)
LANGUAGE plpgsql
AS $_$
BEGIN
REFRESH MATERIALIZED VIEW osm_spokane.vplace_polygon_subdivide;
IF $1 = False THEN
RAISE NOTICE 'Populating nested place table';
CALL osm_spokane.populate_place_polygon_nested();
RAISE NOTICE 'Calculating nesting of place polygons';
CALL osm_spokane.build_nested_admin_polygons();
END IF;
END $_$;
--
-- Name: PROCEDURE append_data_finish(IN skip_nested boolean); Type: COMMENT; Schema: osm_spokane; Owner: -
--
COMMENT ON PROCEDURE osm_spokane.append_data_finish(IN skip_nested boolean) IS 'Finalizes PgOSM Flex after osm2pgsql-replication. Refreshes materialized view and (optionally) processes the place_polygon_nested data.';
--
-- Name: append_data_start(); Type: PROCEDURE; Schema: osm_spokane; Owner: -
--
CREATE PROCEDURE osm_spokane.append_data_start()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Truncating table osm_spokane.place_polygon_nested;';
TRUNCATE TABLE osm_spokane.place_polygon_nested;
END $$;
--
-- Name: PROCEDURE append_data_start(); Type: COMMENT; Schema: osm_spokane; Owner: -
--
COMMENT ON PROCEDURE osm_spokane.append_data_start() IS 'Prepares PgOSM Flex database for running osm2pgsql in append mode. Removes records from place_polygon_nested if they existed.';
--
-- Name: build_nested_admin_polygons(bigint); Type: PROCEDURE; Schema: osm_spokane; Owner: -
--
CREATE PROCEDURE osm_spokane.build_nested_admin_polygons(IN batch_row_limit bigint DEFAULT 100)
LANGUAGE plpgsql
AS $_$
DECLARE
rows_to_update BIGINT;
BEGIN
SELECT COUNT(*) INTO rows_to_update
FROM osm_spokane.place_polygon_nested r
WHERE nest_level IS NULL
;
RAISE NOTICE 'Rows to update: %', rows_to_update;
RAISE NOTICE 'Updating in batches of % rows', $1;
FOR counter IN 1..rows_to_update by $1 LOOP
DROP TABLE IF EXISTS places_for_nesting;
CREATE TEMP TABLE places_for_nesting AS
SELECT p.osm_id
FROM osm_spokane.place_polygon_nested p
WHERE p.name IS NOT NULL
AND (admin_level IS NOT NULL
OR osm_type IN ('boundary', 'admin_level', 'suburb',
'neighbourhood')
)
;
CREATE UNIQUE INDEX tmp_ix_places_for_nesting
ON places_for_nesting (osm_id);
DROP TABLE IF EXISTS place_batch;
CREATE TEMP TABLE place_batch AS
SELECT p.osm_id, t.nest_level, t.name_path, t.osm_id_path, t.admin_level_path
FROM osm_spokane.place_polygon p
INNER JOIN LATERAL (
SELECT COUNT(i.osm_id) AS nest_level,
ARRAY_AGG(i.name ORDER BY COALESCE(i.admin_level::INT, 99::INT) ASC) AS name_path,
ARRAY_AGG(i.osm_id ORDER BY COALESCE(i.admin_level::INT, 99::INT) ASC) AS osm_id_path,
ARRAY_AGG(COALESCE(i.admin_level::INT, 99::INT) ORDER BY i.admin_level ASC) AS admin_level_path
FROM osm_spokane.place_polygon i
WHERE ST_Within(p.geom, i.geom)
AND EXISTS (
SELECT 1 FROM places_for_nesting include
WHERE i.osm_id = include.osm_id
)
AND i.name IS NOT NULL
) t ON True
WHERE EXISTS (
SELECT 1 FROM osm_spokane.place_polygon_nested miss
WHERE miss.nest_level IS NULL
AND p.osm_id = miss.osm_id
)
AND EXISTS (
SELECT 1 FROM places_for_nesting include
WHERE p.osm_id = include.osm_id
)
LIMIT $1
;
UPDATE osm_spokane.place_polygon_nested n
SET nest_level = t.nest_level,
name_path = t.name_path,
osm_id_path = t.osm_id_path,
admin_level_path = t.admin_level_path
FROM place_batch t
WHERE n.osm_id = t.osm_id
;
COMMIT;
END LOOP;
DROP TABLE IF EXISTS place_batch;
DROP TABLE IF EXISTS places_for_nesting;
-- With all nested paths calculated the innermost value can be determined.
WITH calc_inner AS (
SELECT a.osm_id
FROM osm_spokane.place_polygon_nested a
WHERE a.row_innermost -- Start with per row check...
-- If an osm_id is found in any other path, cannot be innermost
AND NOT EXISTS (
SELECT 1
FROM osm_spokane.place_polygon_nested i
WHERE a.osm_id <> i.osm_id
AND a.osm_id = ANY(osm_id_path)
)
)
UPDATE osm_spokane.place_polygon_nested n
SET innermost = True
FROM calc_inner i
WHERE n.osm_id = i.osm_id
;
END $_$;
--
-- Name: PROCEDURE build_nested_admin_polygons(IN batch_row_limit bigint); Type: COMMENT; Schema: osm_spokane; Owner: -
--
COMMENT ON PROCEDURE osm_spokane.build_nested_admin_polygons(IN batch_row_limit bigint) IS 'Warning: Expensive procedure! Use to populate the osm_spokane.place_polygon_nested table. This procedure is not ran as part of SQL script automatically due to excessive run time on large regions.';
--
-- Name: populate_place_polygon_nested(); Type: PROCEDURE; Schema: osm_spokane; Owner: -
--
CREATE PROCEDURE osm_spokane.populate_place_polygon_nested()
LANGUAGE sql
AS $$
INSERT INTO osm_spokane.place_polygon_nested (osm_id, name, osm_type, admin_level, geom)
SELECT p.osm_id, p.name, p.osm_type,
COALESCE(p.admin_level::INT, 99) AS admin_level,
geom
FROM osm_spokane.place_polygon p
WHERE (p.boundary = 'administrative'
OR p.osm_type IN ('neighborhood', 'city', 'suburb', 'town', 'admin_level', 'locality')
)
AND p.name IS NOT NULL
AND NOT EXISTS (
SELECT osm_id
FROM osm_spokane.place_polygon_nested n
WHERE n.osm_id = p.osm_id
)
;
$$;
--
-- Name: append_data_finish(boolean); Type: PROCEDURE; Schema: osm_wa; Owner: -
--
CREATE PROCEDURE osm_wa.append_data_finish(IN skip_nested boolean DEFAULT false)
LANGUAGE plpgsql
AS $_$
BEGIN
REFRESH MATERIALIZED VIEW osm_wa.vplace_polygon_subdivide;
IF $1 = False THEN
RAISE NOTICE 'Populating nested place table';
CALL osm_wa.populate_place_polygon_nested();
RAISE NOTICE 'Calculating nesting of place polygons';
CALL osm_wa.build_nested_admin_polygons();
END IF;
END $_$;
--
-- Name: PROCEDURE append_data_finish(IN skip_nested boolean); Type: COMMENT; Schema: osm_wa; Owner: -
--
COMMENT ON PROCEDURE osm_wa.append_data_finish(IN skip_nested boolean) IS 'Finalizes PgOSM Flex after osm2pgsql-replication. Refreshes materialized view and (optionally) processes the place_polygon_nested data.';
--
-- Name: append_data_start(); Type: PROCEDURE; Schema: osm_wa; Owner: -
--
CREATE PROCEDURE osm_wa.append_data_start()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Truncating table osm_wa.place_polygon_nested;';
TRUNCATE TABLE osm_wa.place_polygon_nested;
END $$;
--
-- Name: PROCEDURE append_data_start(); Type: COMMENT; Schema: osm_wa; Owner: -
--
COMMENT ON PROCEDURE osm_wa.append_data_start() IS 'Prepares PgOSM Flex database for running osm2pgsql in append mode. Removes records from place_polygon_nested if they existed.';
--
-- Name: build_nested_admin_polygons(bigint); Type: PROCEDURE; Schema: osm_wa; Owner: -
--
CREATE PROCEDURE osm_wa.build_nested_admin_polygons(IN batch_row_limit bigint DEFAULT 100)
LANGUAGE plpgsql
AS $_$
DECLARE
rows_to_update BIGINT;
BEGIN
SELECT COUNT(*) INTO rows_to_update
FROM osm_wa.place_polygon_nested r
WHERE nest_level IS NULL
;
RAISE NOTICE 'Rows to update: %', rows_to_update;
RAISE NOTICE 'Updating in batches of % rows', $1;
FOR counter IN 1..rows_to_update by $1 LOOP
DROP TABLE IF EXISTS places_for_nesting;
CREATE TEMP TABLE places_for_nesting AS
SELECT p.osm_id
FROM osm_wa.place_polygon_nested p
WHERE p.name IS NOT NULL
AND (admin_level IS NOT NULL
OR osm_type IN ('boundary', 'admin_level', 'suburb',
'neighbourhood')
)
;
CREATE UNIQUE INDEX tmp_ix_places_for_nesting
ON places_for_nesting (osm_id);
DROP TABLE IF EXISTS place_batch;
CREATE TEMP TABLE place_batch AS
SELECT p.osm_id, t.nest_level, t.name_path, t.osm_id_path, t.admin_level_path
FROM osm_wa.place_polygon p
INNER JOIN LATERAL (
SELECT COUNT(i.osm_id) AS nest_level,
ARRAY_AGG(i.name ORDER BY COALESCE(i.admin_level::INT, 99::INT) ASC) AS name_path,
ARRAY_AGG(i.osm_id ORDER BY COALESCE(i.admin_level::INT, 99::INT) ASC) AS osm_id_path,
ARRAY_AGG(COALESCE(i.admin_level::INT, 99::INT) ORDER BY i.admin_level ASC) AS admin_level_path
FROM osm_wa.place_polygon i
WHERE ST_Within(p.geom, i.geom)
AND EXISTS (
SELECT 1 FROM places_for_nesting include
WHERE i.osm_id = include.osm_id
)
AND i.name IS NOT NULL
) t ON True
WHERE EXISTS (
SELECT 1 FROM osm_wa.place_polygon_nested miss
WHERE miss.nest_level IS NULL
AND p.osm_id = miss.osm_id
)
AND EXISTS (
SELECT 1 FROM places_for_nesting include
WHERE p.osm_id = include.osm_id
)
LIMIT $1
;
UPDATE osm_wa.place_polygon_nested n
SET nest_level = t.nest_level,
name_path = t.name_path,
osm_id_path = t.osm_id_path,
admin_level_path = t.admin_level_path
FROM place_batch t
WHERE n.osm_id = t.osm_id
;
COMMIT;
END LOOP;
DROP TABLE IF EXISTS place_batch;
DROP TABLE IF EXISTS places_for_nesting;
-- With all nested paths calculated the innermost value can be determined.
WITH calc_inner AS (
SELECT a.osm_id
FROM osm_wa.place_polygon_nested a
WHERE a.row_innermost -- Start with per row check...
-- If an osm_id is found in any other path, cannot be innermost
AND NOT EXISTS (
SELECT 1
FROM osm_wa.place_polygon_nested i
WHERE a.osm_id <> i.osm_id
AND a.osm_id = ANY(osm_id_path)
)
)
UPDATE osm_wa.place_polygon_nested n
SET innermost = True
FROM calc_inner i
WHERE n.osm_id = i.osm_id
;
END $_$;
--
-- Name: PROCEDURE build_nested_admin_polygons(IN batch_row_limit bigint); Type: COMMENT; Schema: osm_wa; Owner: -
--
COMMENT ON PROCEDURE osm_wa.build_nested_admin_polygons(IN batch_row_limit bigint) IS 'Warning: Expensive procedure! Use to populate the osm_wa.place_polygon_nested table. This procedure is not ran as part of SQL script automatically due to excessive run time on large regions.';
--
-- Name: populate_place_polygon_nested(); Type: PROCEDURE; Schema: osm_wa; Owner: -
--
CREATE PROCEDURE osm_wa.populate_place_polygon_nested()
LANGUAGE sql
AS $$
INSERT INTO osm_wa.place_polygon_nested (osm_id, name, osm_type, admin_level, geom)
SELECT p.osm_id, p.name, p.osm_type,
COALESCE(p.admin_level::INT, 99) AS admin_level,
geom
FROM osm_wa.place_polygon p
WHERE (p.boundary = 'administrative'
OR p.osm_type IN ('neighborhood', 'city', 'suburb', 'town', 'admin_level', 'locality')
)
AND p.name IS NOT NULL
AND NOT EXISTS (
SELECT osm_id
FROM osm_wa.place_polygon_nested n
WHERE n.osm_id = p.osm_id
)
;
$$;
--
-- Name: complete_recent_rentals(interval); Type: PROCEDURE; Schema: public; Owner: -
--
CREATE PROCEDURE public.complete_recent_rentals(IN min_interval interval DEFAULT '96:00:00'::interval)
LANGUAGE plpgsql
AS $$
DECLARE
rid int;
close_pct_lower int = 10;
close_pct_upper int = 20;
-- In seconds to get some additional randomness in the rental end time
close_interval_lower int = 57600; -- 16 hours
close_interval_upper int = 345600; -- 96 hours
BEGIN
CREATE TEMPORARY TABLE IF NOT exists tmp_open_rentals (
rental_id int
);
INSERT INTO tmp_open_rentals
SELECT rental_id FROM rental WHERE upper(rental_period) IS NULL
AND lower(rental_period) > now()-min_interval;
ANALYZE tmp_open_rentals;
-- sample 0.01% of the rows from available customers for the given day
-- loop over them and
IF EXISTS (SELECT 1 FROM tmp_open_rentals) THEN
-- we close out somewhere between 10 and 20 percent of the rentals each time.
FOR rid IN (SELECT rental_id FROM tmp_open_rentals TABLESAMPLE bernoulli(random_between(close_pct_lower,close_pct_upper,1)))
LOOP
-- update the rental end period to be between 16 hours and 4 days from initial rental.
UPDATE rental SET rental_period = tstzrange(lower(rental_period), (lower(rental_period) + (random_between(close_interval_lower,close_interval_upper)::TEXT || ' seconds')::INTERVAL))
WHERE rental_id = rid;
COMMIT;
-- create the payment for the completed rental
CALL insert_single_payment(rid);
end LOOP;
END IF;
END;
$$;
--
-- Name: generate_new_rental(); Type: PROCEDURE; Schema: public; Owner: -
--
CREATE PROCEDURE public.generate_new_rental()
LANGUAGE plpgsql
AS $$
DECLARE cus record;
c_inv int;
a_inv int[];
rental_start timestamptz;
rental_range tstzrange;
cust_percent float4 = .01;
BEGIN
CREATE TEMPORARY TABLE tmp_av_customer(LIKE customer INCLUDING ALL);
CREATE TEMPORARY TABLE tmp_av_inventory (LIKE inventory INCLUDING ALL);
CREATE INDEX tmp_av_inventory_id ON tmp_av_inventory (inventory_id);
rental_start = now();
rental_range = tstzrange(now(),now()+'5 minutes'::INTERVAL);
IF EXISTS (SELECT 1 FROM public.holiday WHERE holiday_date::timestamptz <@ rental_range) THEN
SELECT pct INTO cust_percent FROM random_between(.015,.025,3) x(pct);
END IF;
-- Get a list of inventory that is currently on loan as of the rental_start day
-- For new rentals this is a bit faulty as the "rental_start" is a single timestamp
-- but the start time
WITH outstanding_rentals AS (
SELECT rental_id, inventory_id FROM rental
WHERE rental_period @> rental_range
),
-- get a list of inventory that is available
available_inventory AS (
INSERT INTO tmp_av_inventory
SELECT i.* FROM inventory i
LEFT JOIN outstanding_rentals o using(inventory_id)
WHERE o.inventory_id IS NULL
)
-- get a list of customers that do not currently have a video on loan
-- as of this time
INSERT INTO tmp_av_customer
SELECT c.* FROM customer c
LEFT JOIN outstanding_rentals o using(customer_id)
WHERE o.customer_id IS NULL;
ANALYZE tmp_av_inventory;
-- sample 0.01% of the rows from available customers for the given day
-- loop over them and
FOR cus IN (SELECT customer_id, store_id FROM tmp_av_customer TABLESAMPLE bernoulli(cust_percent))
LOOP
-- This is not efficient on larger tables. For a small table like this
-- using 'random()' to order is faster and easier than a multi-step search
SELECT inventory_id INTO c_inv FROM tmp_av_inventory
WHERE inventory_id NOT IN (SELECT UNNEST(a_inv))
ORDER BY random()
LIMIT 1;
--RAISE NOTICE 'customer_id: %, inventory_id: %, a_inv: %',cus.customer_id, c_inv, a_inv;
INSERT INTO rental (rental_period, inventory_id, customer_id, staff_id)
SELECT tstzrange((rental_start + trunc(random() * 300) * '1 second'::INTERVAL),NULL) rental_period,
ai.inventory_id,
cus.customer_id,
1 staff_id
FROM tmp_av_inventory ai
LEFT JOIN staff s ON ai.store_id = s.store_id
WHERE ai.inventory_id = c_inv;
COMMIT;
-- keep a list of inventory that is has been used from the
-- available inventory temp table so that we don't reuse it on this run.
a_inv := array_append(a_inv, c_inv);
end LOOP;
END;
$$;
--
-- Name: generate_rental_history(timestamp with time zone, timestamp with time zone); Type: PROCEDURE; Schema: public; Owner: -
--
CREATE PROCEDURE public.generate_rental_history(IN data_start timestamp with time zone, IN data_end timestamp with time zone)
LANGUAGE plpgsql
AS $$
DECLARE cus record;
c_inv int;
a_inv int[];
rental_start timestamptz;
rental_nextday timestamptz;
sqlstr TEXT;
cust_percent int = 0.1;
BEGIN
IF data_start > data_end THEN
RAISE EXCEPTION 'End date must be after start date!';
END IF;
IF (data_end-data_start) > '365 days'::INTERVAL THEN
RAISE EXCEPTION 'You can only do one year at time! %', (data_end-data_start);
END IF;
CREATE TEMPORARY TABLE tmp_av_customer(LIKE customer INCLUDING ALL);
CREATE TEMPORARY TABLE tmp_av_inventory (LIKE inventory INCLUDING ALL);
CREATE INDEX tmp_av_inventory_id ON tmp_av_inventory (inventory_id);
rental_start = data_start;
WHILE rental_start <= data_end THEN
LOOP -- daily LOOP FOR rentals
-- this is used during the INSERT and pulled out here for visibility
rental_nextday = rental_start + '1 day'::INTERVAL;
-- RAISE NOTICE 'rental_start: %', rental_start;
-- RAISE NOTICE 'data_end: %', data_end;
-- If this date falls on a holiday, increase rentals by some percentage
IF EXISTS (SELECT 1 FROM public.holiday WHERE holiday_date::timestamptz <@ tstzrange(rental_start,rental_nextday)) THEN
SELECT pct INTO cust_percent FROM random_between(.015,.025,3) x(pct);
END IF;
-- Get a list of inventory that is currently on loan as of the rental_start day
WITH outstanding_rentals AS (
SELECT * FROM rental
WHERE rental_period @> rental_start
),
-- get a list of inventory that is available
available_inventory AS (
INSERT INTO tmp_av_inventory
SELECT i.* FROM inventory i
LEFT JOIN outstanding_rentals o using(inventory_id)
WHERE o.inventory_id IS NULL
)
-- get a list of customers that do not currently have a video on loan
-- as of today
INSERT INTO tmp_av_customer
SELECT c.* FROM customer c
LEFT JOIN outstanding_rentals o using(customer_id)
WHERE o.customer_id IS NULL;
ANALYZE tmp_av_inventory;
-- sample rows from available customers for the given day
-- loop over them and
FOR cus IN (SELECT customer_id, store_id FROM tmp_av_customer TABLESAMPLE bernoulli(cust_percent))
LOOP
-- This is not efficient on larger tables. For a small table like this
-- using 'random()' to order is faster and easier than a multi-step search
SELECT inventory_id INTO c_inv FROM tmp_av_inventory
WHERE inventory_id NOT IN (SELECT UNNEST(a_inv))
ORDER BY random()
LIMIT 1;
--RAISE NOTICE 'customer_id: %, inventory_id: %, a_inv: %',cus.customer_id, c_inv, a_inv;
INSERT INTO rental (rental_period, inventory_id, customer_id, staff_id)