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
|
CREATE TABLE upgrade_test(g1 geometry, g2 geography);
INSERT INTO upgrade_test(g1,g2) VALUES
('POINT(0 0)', 'LINESTRING(0 0, 1 1)'),
('POINT(1 0)', 'LINESTRING(0 1, 1 1)');
-- Add view using ST_Union aggregate
-- See https://trac.osgeo.org/postgis/ticket/4386
CREATE VIEW upgrade_view_test_union AS
SELECT ST_Union(g1) FROM upgrade_test;
-- Add view using overlay functions
CREATE VIEW upgrade_view_test_overlay AS
SELECT
ST_Intersection(g1, g1) as geometry_intersection,
ST_Intersection(g2, g2) as geography_intersection,
ST_Difference(g1, g1) as geometry_difference,
ST_SymDifference(g1, g1) as geometry_symdifference
FROM upgrade_test;
-- Add view using unaryunion function
-- NOTE: 2.0.0 introduced ST_UnaryUnion
CREATE VIEW upgrade_view_test_unaryunion AS
SELECT
ST_UnaryUnion(g1) as geometry_unaryunion
FROM upgrade_test;
-- Add view using unaryunion function
-- NOTE: 2.2.0 introduced ST_Subdivide
CREATE VIEW upgrade_view_test_subdivide AS
SELECT
ST_Subdivide(g1, 256) as geometry_subdivide
FROM upgrade_test;
-- Add view using ST_ForceX function
-- NOTE: 3.1.0 changed them from taking only geometry
-- to also take optional zvalue/mvalue params
CREATE VIEW upgrade_view_test_force_dims AS
SELECT
ST_Force3D(g1) as geometry_force3d,
ST_Force3DZ(g1) as geometry_force3dz,
ST_Force3DM(g1) as geometry_force3dm,
ST_Force4D(g1) as geometry_force4d
FROM upgrade_test;
-- Add view using ST_AsKML function
-- NOTE: 2.0.0 changed them to add default params
CREATE VIEW upgrade_view_test_askml AS
SELECT
ST_AsKML(g1) as geometry_askml,
ST_AsKML(g2) as geography_askml
FROM upgrade_test;
-- Add view using ST_DWithin functions
-- See https://trac.osgeo.org/postgis/ticket/5494
CREATE VIEW upgrade_view_test_dwithin AS
SELECT
ST_DWithin(NULL::text, NULL::text, NULL::float8) as text_dwithin,
-- Available since 1.5.0, changed in 3.0.0 to add optional 4th use_spheroid param
ST_DWithin(NULL::geography, NULL::geography, NULL::float8) as geography_dwithin,
-- Available since 1.3.0
ST_DWithin(NULL::geometry, NULL::geometry, NULL::float8) as geometry_dwithin
;
-- Add view using ST_ClusterKMeans windowing function
-- NOTE: 3.2.0 changed it to add max_radius parameter
CREATE VIEW upgrade_view_test_clusterkmeans AS
SELECT
ST_ClusterKMeans(g1, 1) OVER ()
FROM upgrade_test;
-- This view uses ST_Distance signatures, available since 1.5.0
-- NOTE: 3.0.0 changed them to use default arguments
-- See https://trac.osgeo.org/postgis/ticket/5380
CREATE VIEW upgrade_view_test_distance AS
SELECT
ST_Distance(g2, g2) geog_dist1,
ST_Distance(g2, g2, true) geog_dist2
FROM upgrade_test;
-- Break probin of all postgis functions, as we expect
-- the upgrade procedure to replace them all
UPDATE pg_proc SET probin = probin || '-uninstalled'
WHERE probin like '%postgis%';
-- Change SECURITY of postgis_version() to DEFINER
-- to verify the bit is reset upon upgrade
--
-- NOTE: we pick postgis_version as one of the oldest
-- function names
--
ALTER FUNCTION postgis_version() SECURITY DEFINER;
|
