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 roads_local (
ogc_fid INTEGER,
geom GEOMETRY,
module VARCHAR(8),
tlid INTEGER,
side1 INTEGER,
source CHAR(1),
fedirp VARCHAR(2),
fename VARCHAR(30),
fetype VARCHAR(4),
fedirs VARCHAR(2),
cfcc VARCHAR(3),
fraddl INTEGER,
toaddl INTEGER,
fraddr INTEGER,
toaddr INTEGER,
friaddl CHAR(1),
toiaddl CHAR(1),
friaddr CHAR(1),
toiaddr CHAR(1),
zipl INTEGER,
zipr INTEGER,
aianhhfpl INTEGER,
aianhhfpr INTEGER,
aihhtlil CHAR(1),
aihhtlir CHAR(1),
census1 CHAR(1),
census2 CHAR(1),
statel INTEGER,
stater INTEGER,
countyl INTEGER,
countyr INTEGER,
cousubl INTEGER,
cousubr INTEGER,
submcdl INTEGER,
submcdr INTEGER,
placel INTEGER,
placer INTEGER,
tractl INTEGER,
tractr INTEGER,
blockl INTEGER,
blockr INTEGER
);
insert into roads_local
select
ogc_fid,
wkb_geometry,
trim(module),
tlid,
side1,
trim(source),
trim(fedirp),
trim(fename),
trim(fetype),
trim(fedirs),
trim(cfcc),
to_number(fraddl,'99999999999'),
to_number(toaddl,'99999999999'),
to_number(fraddr,'99999999999'),
to_number(toaddr,'99999999999'),
trim(friaddl),
trim(toiaddl),
trim(friaddr),
trim(toiaddr),
zipl,
zipr,
aianhhfpl,
aianhhfpr,
trim(aihhtlil),
trim(aihhtlir),
trim(census1),
trim(census2),
statel,
stater,
countyl,
countyr,
cousubl,
cousubr,
submcdl,
submcdr,
placel,
placer,
tractl,
tractr,
blockl,
blockr
from completechain;
CREATE INDEX roads_local_tlid_idx ON roads_local (tlid);
CREATE INDEX roads_local_geom_idx ON roads_local USING GIST (geom public.gist_geometry_ops);
CREATE INDEX roads_local_cfcc_idx ON roads_local (cfcc);
|
