Opened by Froschpopo at 2008-03-15 21:57
User since
2004-07-19
1776 Artikel
HausmeisterIn
![[Homepage]](/battie/theme/default/homepage.gif)
2004-07-19
1776 Artikel
HausmeisterIn
Froschpopo+2008-03-17 11:48:25--Mit der Doku kann man rein garnichts anfangen. [...]
Ich gebe zwar zu, dass die Datenbankstruktur von opengeodb vielleicht nicht trivial ist, aber die Dokumentation ist zusammen mit einem gesunden Verständnis von SQL mehr als ausreichend um jede erdenkliche Abfrage zu konstruieren. Genau genommen braucht man nichtmal die Dokumentation sondern lediglich die Tabellenstrukturdefinitionen, über die einem auch die Datenbankengine Auskunft geben kann!
Aber zurück zum Thema: Wenn Du die Abfrage auf bestimmte Regionen eingrenzen willst, hast Du das Problem, dass die Daten in den Tabellen von opengeodb hier eine Baumstruktur definieren, die man rekursiv durchlaufen müsste, was die meisten Datenbanken nicht so einfach unterstützen. Praktischer wäre es da schon, die Tabelle geodb_hierarchies zu benutzen, welche zwar definiert, blöderweise aber leer ist ;-)
Mein Vorschlag wäre daher, zunächst einmal diese Tabelle zu befüllen:
Code: (dl
)
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
INSERT INTO geodb_hierarchies(loc_id, level,
id_lvl1, id_lvl2, id_lvl3, id_lvl4, id_lvl5,
id_lvl6, id_lvl7, id_lvl8, id_lvl9,
valid_since, date_type_since,
valid_until, date_type_until)
SELECT loc_id, level,
CASE WHEN level > 0 THEN 0 ELSE NULL END,
CASE WHEN level > 1 THEN 0 ELSE NULL END,
CASE WHEN level > 2 THEN 0 ELSE NULL END,
CASE WHEN level > 3 THEN 0 ELSE NULL END,
CASE WHEN level > 4 THEN 0 ELSE NULL END,
CASE WHEN level > 5 THEN 0 ELSE NULL END,
CASE WHEN LEVEL > 6 THEN 0 ELSE NULL END,
CASE WHEN level > 7 THEN 0 ELSE NULL END,
CASE WHEN LEVEL > 8 THEN 0 ELSE NULL END,
valid_since, date_type_since, valid_until, date_type_until
FROM (SELECT loc.loc_id AS loc_id, dat.text_val::integer AS level,
dat.valid_since AS valid_since,
dat.date_type_since AS date_type_since,
dat.valid_until AS valid_until,
dat.date_type_until AS date_type_until
FROM geodb_locations AS loc
JOIN geodb_textdata AS dat USING (loc_id)
WHERE dat.text_type = 400200000) AS loc_dat;
UPDATE geodb_hierarchies
SET id_lvl1 = loc_id
WHERE level = 1;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl2 = loc_id
WHERE level = 2;
UPDATE geodb_hierarchies AS hier
SET id_lvl2 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl3 = loc_id
WHERE level = 3;
UPDATE geodb_hierarchies AS hier
SET id_lvl3 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl4 = loc_id
WHERE level = 4;
UPDATE geodb_hierarchies AS hier
SET id_lvl4 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl5 = loc_id
WHERE level = 5;
UPDATE geodb_hierarchies AS hier
SET id_lvl5 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl6 = loc_id
WHERE level = 6;
UPDATE geodb_hierarchies AS hier
SET id_lvl6 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl7 = loc_id
WHERE level = 7;
UPDATE geodb_hierarchies AS hier
SET id_lvl7 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl8 = loc_id
WHERE level = 8;
UPDATE geodb_hierarchies AS hier
SET id_lvl8 = coalesce(
(SELECT CAST(text_val AS integer)
FROM geodb_textdata
WHERE loc_id = hier.loc_id AND text_type = 400100000),
0
),
id_lvl9 = loc_id
WHERE level = 9;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl2), 0)
WHERE level = 3;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl3), 0),
id_lvl2 = coalesce((SELECT id_lvl2
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl3), 0)
WHERE level = 4;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl4), 0),
id_lvl2 = coalesce((SELECT id_lvl2
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl4), 0),
id_lvl3 = coalesce((SELECT id_lvl3
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl4), 0)
WHERE level = 5;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl5), 0),
id_lvl2 = coalesce((SELECT id_lvl2
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl5), 0),
id_lvl3 = coalesce((SELECT id_lvl3
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl5), 0),
id_lvl4 = coalesce((SELECT id_lvl4
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl5), 0)
WHERE level = 6;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl6), 0),
id_lvl2 = coalesce((SELECT id_lvl2
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl6), 0),
id_lvl3 = coalesce((SELECT id_lvl3
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl6), 0),
id_lvl4 = coalesce((SELECT id_lvl4
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl6), 0),
id_lvl5 = coalesce((SELECT id_lvl5
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl6), 0)
WHERE level = 7;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl7), 0),
id_lvl2 = coalesce((SELECT id_lvl2
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl7), 0),
id_lvl3 = coalesce((SELECT id_lvl3
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl7), 0),
id_lvl4 = coalesce((SELECT id_lvl4
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl7), 0),
id_lvl5 = coalesce((SELECT id_lvl5
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl7), 0),
id_lvl6 = coalesce((SELECT id_lvl6
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl7), 0)
WHERE level = 8;
UPDATE geodb_hierarchies AS hier
SET id_lvl1 = coalesce((SELECT id_lvl1
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0),
id_lvl2 = coalesce((SELECT id_lvl2
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0),
id_lvl3 = coalesce((SELECT id_lvl3
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0),
id_lvl4 = coalesce((SELECT id_lvl4
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0),
id_lvl5 = coalesce((SELECT id_lvl5
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0),
id_lvl6 = coalesce((SELECT id_lvl6
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0),
id_lvl7 = coalesce((SELECT id_lvl7
FROM geodb_hierarchies
WHERE loc_id = hier.id_lvl8), 0)
WHERE level = 9;
[edit: Standard SQL CAST-Syntax statt PostgreSQL spezifischer Konstrukte.]
Anschließend kann man die Abfrage für Orte, die zu einer Postleitzahl gehören, etwas modifizieren:
Code: (dl
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT code.text_val AS area_code, name.text_val AS name,
loc.lon AS lon, loc.lat AS lat
FROM geodb_textdata AS code
JOIN geodb_hierarchies AS hier ON (
code.text_type = 500300000 AND hier.loc_id = code.loc_id
)
JOIN geodb_textdata AS country ON (
country.text_type = 500500000 AND country.loc_id = hier.id_lvl2
)
JOIN geodb_textdata AS name ON (
name.text_type = 500100000 AND name.loc_id = code.loc_id
)
JOIN geodb_coordinates AS loc ON (
loc.loc_id = code.loc_id
)
WHERE country.text_val = ? AND code.text_val = ?;
(Die beiden Platzhalter stehen für das Autokennzeichen des Landes und die Postleitzahl des Ortes)
Wenn Dir das alles auf Dauer zu kompliziert ist, kannst Du ja auch einfach eine einzelne Tabelle mit genau den Informationen, die Du brauchst, anlegen und den Rest der opengeodb wieder wegwerfen die obigen Abfrage in einen passenden CREATE TABLE .. AS SELECT ...-Befehl zu verwandeln und passende Indizes zu erstellen, dürfte auch ohne Handbuch leicht zu machen sein :-P
When C++ is your hammer, every problem looks like your thumb.