Schrift
Start · Board · Beiträge im Abseits · Sonstige Beiträge (auch Spaß und Sinnloses)
[thread]11471[/thread]

opengeodb

Leser: 3


<< >> 9 Einträge, 1 Seite
Froschpopo
 2008-03-15 21:57
#107093 #107093
User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]
also irgendwie blick ich durch die neue Version der openGeoDB nicht mehr durch.
Die geodb_textdata-Tabelle ist mir einfach zu hoch.
Dabei wollte ich einfach nur anhand der Postleitzahl eine Liste mit den zu dieser Postleitzahl gehörenden Orte und Koordinaten ermitteln.

Also man gibt eine Postleitzahl ein und bekommt dann alle Orte und Koordinaten.
Angeblich ist die openGeoDB ja die größte Datenbank, die offensichtlich ja auch auf den größten Communities hierzulande eingesetzt wird.
Aber bei der SQL-Architektur bleibt mir echt die Spucke weg.

Hier mal die offizielle Seite:
http://opengeodb.hoppe-media.com/index.php

Kennt Ihr vielleicht noch eine gute Alternative? Oder wisst evtl. wie man o.g. Statement umsetzen könnte?
murphy
 2008-03-16 16:11
#107098 #107098
User since
2004-07-19
1776 Artikel
HausmeisterIn
[Homepage]
user image
Wie wäre es denn mit dieser einfachen kleinen Abfrage ;-)
Code: (dl )
1
2
3
4
5
6
7
SELECT code.text_val AS area_code, name.text_val AS name,
       coords.lon AS lon, coords.lat AS lat
  FROM geodb_textdata AS code JOIN geodb_textdata AS name USING (loc_id)
  JOIN geodb_coordinates AS coords USING (loc_id)
 WHERE code.text_type = 500300000 AND name.text_type = 500100000 AND
       code.text_val = ?
 ORDER BY name ASC;

(wobei der Platzhalter für die gewünschte Postleitzahl steht)
When C++ is your hammer, every problem looks like your thumb.
Froschpopo
 2008-03-17 12:06
#107122 #107122
User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]
das habe ich sogar noch etwas einfacher hinbekommen:
Code: (dl )
1
2
3
4
SELECT
   a.text_type, a.text_val, a.loc_id, c.lon, c.lat
FROM geodb_textdata a, geodb_textdata b, geodb_coordinates c
WHERE ( a.text_locale = 'de' AND a.text_type = 500100000 AND b.loc_id = a.loc_id AND b.text_type = 500300000 AND b.text_val = 8000 AND c.loc_id = b.loc_id )

Mein Problem fängt aber an, wenn ich z.B. nur deutsche Postleitzahlen ausgeben will.

Das obige Beispiel verwendet PLZ 8000 die für Zürich (Schweiz) steht.
Aber wenns darum geht, ein Land vorzugeben, dann hört bei mir der Verstand auf.
Gast Gast
 2008-03-17 12:47
#107124 #107124
Unter http://sourceforge.net/docman/display_doc.php?doci... und http://sourceforge.net/docman/display_doc.php?doci... ist doch alles zu finden.
Gast Gast
 2008-03-17 12:47
#107125 #107125
Oder deutsch unter http://sourceforge.net/docman/display_doc.php?doci...
Froschpopo
 2008-03-17 12:48
#107126 #107126
User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]
Mit der Doku kann man rein garnichts anfangen. Hab ich alles schon gelesen. Mit der Praxis hat das allerdings nichts zu tun! Schonmal jemand die Datenbank mit eigenen Augen gesehen? Bin mal auf eure Reaktionen gespannt.
moritz
 2008-03-17 16:14
#107131 #107131
User since
2007-05-11
923 Artikel
HausmeisterIn
[Homepage]
user image
Froschpopo+2008-03-17 11:48:25--
Mit der Doku kann man rein garnichts anfangen.


Ich finde, dass du es dir zu leicht machst.

Selbst wenn du nicht jeden einzelnen Satz verstehst, gibt es in der Dokumentation einige Beispiele, die dich weiterbringen sollten.

Natürlich musst du dich ein bisschen mit SQL auskennen, da kommst du sicher nicht drum rum.
Perl 6 - Perls Zukunft
Froschpopo
 2008-03-17 16:58
#107137 #107137
User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]
ich bin bereits seit Monaten in der Mailingliste aktiv. Aber leider weiss dort auch niemand bescheid. Selbst die Macher sind mit der Architektur unzufrieden.
Die Datenbank kannst du dir ja auch nicht angesehen haben, sonst hättest du ja gesehen, dass geodb_hierarchies komplett leer ist. Abgesehen davon habe ich die hierarchies immer noch nicht verstanden.
Davon abgesehen komme ich mit SQL soweit zurecht. Danke.
Aber man muss auch wissen was man tun muss, bevor man sich an ein Statement ransetzt.
murphy
 2008-03-17 17:17
#107138 #107138
User since
2004-07-19
1776 Artikel
HausmeisterIn
[Homepage]
user image
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.
<< >> 9 Einträge, 1 Seite



View all threads created 2008-03-15 21:57.