Geo Datenbank Aufbau

[pallers]

Hallo zusammen
ich bin dabei eine Auto-Geo-Datenbank mittels MySQL aufzubauen und habe bis jetzt folgenden Tabellen, die ich hierfür benutzen möchten. So habe ich die folgenden Tabellen:

Autos:
ID | Name | Ort_ID | Gruppen_ID

Art:
ID | Artname

Standort:
ID | Long | Lat | Benutzer_ID | Gruppen_ID | Timestamp

Ich möchte gerne, dass die Standorte der Autos eingetragen werden können. So kann sich ein Auto in nur einer Artklasse befinden und der Standort kann in einer Tabelle festgehalten werden. Ich möchte jetzt z.B. alle Autos der Art Privatfahrtzeuge ausgeben. Ziel soll es sein auf einer Karte z.B. alle Autos dieser Gruppe auf einmal anzuzeigen. Wobei ich noch Probleme sehe ist, dass ich nicht genau spezifizieren kann, wenn sich ein Auto an einen anderen Ort begibt. Hier würde das Auto ja einen neuen Standort und einen neuen Timestamp bekommen. Der Rest würde ja bestehen bleiben. Wie würde denn dann eine solche Abfrage aussehen. Aktueller Zeitwert?


Ist die Datenbank so in etwa richtig oder was wäre hier zu ändern / ergänzen?
Besten dank!

 

[akretschmer]

Wozu eine Ort_ID bei den Autos? Wozu dient die Tabelle Art? Warum die Gruppen_ID in der Standort-Tabelle?

Alles etwas konfus. Für die Speicherung von Ortsangaben gibt es mit PostGIS bessere Möglichkeiten. Wie sind die Tabellen genau definiert? Hast Du Fremdschlüssel? Und was soll überhaupt genau erreicht werden? Fahrzeug-Tracking in Echtzeit via GPS?

 

[akretschmer]

Um mal eine ganz simple Möglichkeit zu zeigen, für den Fall, daß es nur darum geht, den 'Standort' eines Fahrzeuges im Sinne "gehört zum Firmenstandort A" oder "ist in der Stadt B zugelassen" oder so.

test=# create table auto_gruppen (id int primary key, name text);
CREATE TABLE
test=*# create table autos (id int primary key, name text, gruppe int references auto_gruppen);
CREATE TABLE
test=*# create table auto_standort(id int primary key, auto int references autos, standort point, von_bis tsrange, exclude using gist(auto with =, von_bis with &&));
CREATE TABLE
test=*#

Das stellt so auch noch sicher, daß ein Auto nicht gleichzeitig an 2 Standorten sein kann.

Sollte es tatsächlich in Richtung GPS-Tracking gehen solltest Du viel Hirn in das Table-Design stecken, denn dann kommen recht schnell erhebliche Datenmengen zusammen. Ich weiß, wovon ich rede ...

 

[pallers]

Hallo und danke für die schnelle Antwort. Ja es soll sich um eine Art Tracking handeln. So soll ein Auto geortet werden und in diesem Zuge mit dem Zeitpunkt,Standort und der Art in der DB abgespeichert werden. Es sollen dann z.b. Alle Fahrzeuge einer Art mit ihrem Standort ausgegeben werden.

 

[akretschmer]

So soll ein Auto geortet werden und in diesem Zuge mit dem Zeitpunkt,Standort und der Art in der DB abgespeichert werden. Es sollen dann z.b. Alle Fahrzeuge einer Art mit ihrem Standort ausgegeben werden.

Ja.

Vermutlich wllst aber auch den 'Verlauf' wissen wollen, also wo war Fahrzeug X zu Zeitpunkt Y. Ich hab ja schon gezeigt, wie es gehen könnte. Wenn es um GPS-Traking geht dann hast so viele Bewegungsdaten, daß Du da eher nicht von-bis speichern willst, sondern rein 'zu Zeitpunkt X war Fahrzeug Y an Ort Z'.

Das mit der Art: ein Fahrzeug ist ja sicher einer (oder mehreren) Arten zugeordnet, und zwar fest. Also ist es Murks, bei jeder Speicherung 'Fahrzeug war zum Zeitpunkt X an Ort Y' auch noch die Art des Fahrzeuges zu speichern - das geht aus den Stammdaten hervor. Du willst diese Tabelle (die bei der Anwendung, die ich kenne, einige Milliarden Rows enthält) möglichst schmal halten.Also wirklich nur: Fahrzeug X war zum Zeitpunkt Y an Ort Z. Punkt.

Den aktuellen Standort aller Fahrzeuge bekommst dann sinngemäß so:

select * from position where (id, ts) = (select id, max(ts) from position group by id)

Das kannst bei Bedarf noch mit Where nach Deiner Fahrzeugart abschmecken. Wenn die Daten (so wie ich das kenne) längere Zeit (Jahre) zu speichern sind würden hier Partitionierung nach der Zeit massiv helfen - und diese Condition würde dann auch in das Where mit gehören müssen.

 

[pallers]

Ok. In der letzten Tabelle verstehe ich den letzten Teil nicht ganz:
exclude using gist(auto with =, von_bis with &&));
Ich möchte es so realisieren, dass nicht von einem Standort ausgegangen wird sondern von den gruppen. Also zeige alle Autos an, die sich in der Gruppe befinden, mit ihrem letzten Standort.

 

[akretschmer]

Das exclude ... ist ein Exclusion Constraint, der verhindern soll, daß ein und dasselbe Auto zu einer Zeit bzw. zu einer überlappenden Zeit an 2 unterschiedlichen Standorten eingetragen werden kann - oder hantierst Du auch mit halben Autos?

So wirkt das:

test=# insert into autos values (1, 'auto1');
INSERT 0 1
test=*# insert into auto_standort values (1, 1, '(2,2)', '[2016-01-01,2016-01-31)');
INSERT 0 1
test=*# insert into auto_standort values (2, 1, '(1,1)', '[2016-01-01,2016-01-31)');
ERROR:  conflicting key value violates exclusion constraint "auto_standort_auto_von_bis_excl"
DETAIL:  Key (auto, von_bis)=(1, ["2016-01-01 00:00:00","2016-01-31 00:00:00")) conflicts with existing key (auto, von_bis)=(1, ["2016-01-01 00:00:00","2016-01-31 00:00:00")).

 

[pallers]

Top! Danke

Wie verbinde ich denn jetzt die Tabellen, so dass die auto_gruppen mit den jeweiligen Standorten ausgegeben werden können. Also zeige alle Autos der Auto_gruppe A mit ihrem letzten Standort.

 

[akretschmer]

sinngemäß:

select id, max(ts) from standort where auto_id in (select id from autos where gruppe in (1,4,7));

(untested yet)


Damit bekommst die ID's, mit denen Du dann noch mal gegen die Standorte joinen kannst.