forked from MapServer/MapServer
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreate_postgis_test_data.sh
executable file
·145 lines (141 loc) · 8 KB
/
create_postgis_test_data.sh
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
#!/bin/bash
psql -c "drop database if exists msautotest" -U postgres
psql -c "create database msautotest" -U postgres
psql -c "create extension postgis" -d msautotest -U postgres
shp2pgsql -g the_geom query/data/bdry_counpy2.shp bdry_counpy2 | psql -U postgres -d msautotest
shp2pgsql -g the_geom -s 3978 wxs/data/popplace.shp popplace | psql -U postgres -d msautotest
shp2pgsql -g the_geom -s 3978 wxs/data/province.shp province | psql -U postgres -d msautotest
shp2pgsql -g the_geom -s 3978 wxs/data/road.shp road| psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern1.shp pattern1 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern2.shp pattern2 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern3.shp pattern3 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern4.shp pattern4 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern5.shp pattern5 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern6.shp pattern6 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern7.shp pattern7 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern8.shp pattern8 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern9.shp pattern9 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern10.shp pattern10 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern11.shp pattern11 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern12.shp pattern12 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern13.shp pattern13 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern14.shp pattern14 | psql -d msautotest -U postgres
shp2pgsql -s 4326 -g the_geom wxs/data/pattern15.shp pattern15 | psql -d msautotest -U postgres
psql -U postgres -d msautotest -c "
alter TABLE pattern1 RENAME COLUMN time to time_str;
alter TABLE pattern1 ADD COLUMN time timestamp without time zone;
update pattern1 set time = to_timestamp(time_str,'YYYYMMDD');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern2 RENAME COLUMN time to time_str;
alter TABLE pattern2 ADD COLUMN time timestamp without time zone;
update pattern2 set time = to_timestamp(time_str,'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern3 RENAME COLUMN time to time_str;
alter TABLE pattern3 ADD COLUMN time timestamp without time zone;
update pattern3 set time = to_timestamp(time_str,'YYYY-MM-DD\"T\"HH24:MI:SS');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern4 RENAME COLUMN time to time_str;
alter TABLE pattern4 ADD COLUMN time timestamp without time zone;
update pattern4 set time = to_timestamp(time_str,'YYYY-MM-DD HH24:MI:SS');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern5 RENAME COLUMN time to time_str;
alter TABLE pattern5 ADD COLUMN time timestamp without time zone;
update pattern5 set time = to_timestamp(time_str,'YYYY-MM-DD\"T\"HH24:MI');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern6 RENAME COLUMN time to time_str;
alter TABLE pattern6 ADD COLUMN time timestamp without time zone;
update pattern6 set time = to_timestamp(time_str,'YYYY-MM-DD HH24:MI');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern7 RENAME COLUMN time to time_str;
alter TABLE pattern7 ADD COLUMN time timestamp without time zone;
update pattern7 set time = to_timestamp(time_str,'YYYY-MM-DD\"T\"HH24');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern8 RENAME COLUMN time to time_str;
alter TABLE pattern8 ADD COLUMN time timestamp without time zone;
update pattern8 set time = to_timestamp(time_str,'YYYY-MM-DD HH24');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern9 RENAME COLUMN time to time_str;
alter TABLE pattern9 ADD COLUMN time timestamp without time zone;
update pattern9 set time = to_timestamp(time_str,'YYYY-MM-DD');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern10 RENAME COLUMN time to time_str;
alter TABLE pattern10 ADD COLUMN time timestamp without time zone;
update pattern10 set time = to_timestamp(time_str,'YYYY-MM');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern11 RENAME COLUMN time to time_str;
alter TABLE pattern11 ADD COLUMN time timestamp without time zone;
update pattern11 set time = to_timestamp(time_str,'YYYY');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern12 RENAME COLUMN time to time_str;
alter TABLE pattern12 ADD COLUMN time time without time zone;
update pattern12 set time = to_timestamp(time_str,'\"T\"HH24:MI:SSZ')::time;
"
psql -U postgres -d msautotest -c "
alter TABLE pattern13 RENAME COLUMN time to time_str;
alter TABLE pattern13 ADD COLUMN time time without time zone;
update pattern13 set time = to_timestamp(time_str,'THH24:MI:SS')::time;
"
psql -U postgres -d msautotest -c "
alter TABLE pattern14 RENAME COLUMN time to time_str;
alter TABLE pattern14 ADD COLUMN time timestamp without time zone;
update pattern14 set time = to_timestamp(time_str,'YYYY-MM-DD HH24:MI:SSZ');
"
psql -U postgres -d msautotest -c "
alter TABLE pattern15 RENAME COLUMN time to time_str;
alter TABLE pattern15 ADD COLUMN time timestamp without time zone;
update pattern15 set time = to_timestamp(time_str,'YYYY-MM-DDZ');
"
psql -U postgres -d msautotest -c "
CREATE TABLE point3d (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'point3d', 'the_geom', 27700, 'POINT', 3);
INSERT INTO point3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;POINT(1 2 3)'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE multipoint3d (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'multipoint3d', 'the_geom', 27700, 'MULTIPOINT', 3);
INSERT INTO multipoint3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;MULTIPOINT(1 2 3,4 5 6)'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE linestring3d (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'linestring3d', 'the_geom', 27700, 'LINESTRING', 3);
INSERT INTO linestring3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;LINESTRING(1 2 3,4 5 6)'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE multilinestring3d (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'multilinestring3d', 'the_geom', 27700, 'MULTILINESTRING', 3);
INSERT INTO multilinestring3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;MULTILINESTRING((1 2 3,4 5 6),(7 8 9,10 11 12))'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE polygon3d (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'polygon3d', 'the_geom', 27700, 'POLYGON', 3);
INSERT INTO polygon3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;POLYGON((0 0 1,10 0 2,10 10 3,0 10 4,0 0 1))'));
INSERT INTO polygon3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;POLYGON((0 0 1,10 0 2,10 10 3,0 10 4,0 0 1),(1 1 2,1 9 3,9 9 4,9 1 5,1 1 2))'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE multipolygon3d (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'multipolygon3d', 'the_geom', 27700, 'MULTIPOLYGON', 3);
INSERT INTO multipolygon3d (the_geom) VALUES (GeomFromEWKT('SRID=27700;MULTIPOLYGON(((0 0 1,10 0 2,10 10 3,0 10 4,0 0 1),(1 1 2,1 9 3,9 9 4,9 1 5,1 1 2)),((10 10 0,10 20 1,20 20 2,20 10 3,10 10 0)))'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE test_wfs_paging (ID SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'test_wfs_paging', 'the_geom', 27700, 'LINESTRING', 2);
INSERT INTO test_wfs_paging (the_geom) VALUES (GeomFromEWKT('SRID=27700;LINESTRING (1 0,0 1)'));
INSERT INTO test_wfs_paging (the_geom) VALUES (GeomFromEWKT('SRID=27700;LINESTRING(0 0,10 10)'));
INSERT INTO test_wfs_paging (the_geom) VALUES (GeomFromEWKT('SRID=27700;LINESTRING(5 2,5 8)'));
"
psql -U postgres -d msautotest -c "
CREATE TABLE text_datatypes (id SERIAL PRIMARY KEY, mychar5 CHAR(5), myvarchar5 VARCHAR(5), mytext TEXT);
SELECT AddGeometryColumn('public', 'text_datatypes', 'the_geom', 27700, 'POINT', 2);
INSERT INTO text_datatypes (the_geom, mychar5, myvarchar5, mytext) VALUES (GeomFromEWKT('SRID=27700;POINT(1 2)'), 'abc ', 'def ', 'ghi ');
"