Abfragen in SQL mit pgAdmin und ArcGIS Pro

(Optional) Übung 12: Abfragen in SQL

Wir haben in der Vorlesung ein paar SQL-Abfragen über die “waelder” Datenbank definiert. Da wir jetzt über die vorigen Übungen ein paar Beispiel Daten in die Datenbank importiert haben, können wir diese Datenbank nutzen, um noch ein paar Abfragen zu üben. Es ist nicht Ziel alle Abfragen gemacht zu haben. Machen Sie einfach so viele Abfragen wie Sie können. Beachten Sie auch, dass es oft mehr als einen Lösungsweg gibt.

  1. Starten Sie pgAdmin und verbinden mit der Datenbank “waelder” mit Ihrem zugewiesenen Login (studentNr).
  2. Formulieren Sie die folgenden SQL-Abfragen und führen diese über das Query Tool von pgAdmin aus. Schauen Sie sich anschliessend das Ergebnis in der Attributtabelle und im Geometry Viewer an.
  • Nicht räumliche Abfragen:
    1. Welche Sichtungsmeldungen wurden von “id_erfasser_x” erfasst?
    2. Wurden Löwen gesichtet? Wenn ja, wie viele?
    3. Wie viele Tiere von jeder Art wurden jeweils gesichtet. Geben Sie eine Liste mit den Namen der Tierarten und der Anzahl der Sichtungen aus.
    4. Wie viele Hasen wurden gesichtet? Geben Sie eine Zahl aus.
    5. Welcher Erfasser hat am meisten Meldungen erfasst? Geben Sie die id_erfasser und die Anzahl der erfassten Tiere aus.
    6. Welcher Erfasser hat am meisten unterschiedliche Tiere erfasst? Geben Sie die id_erfasser und die Anzahl der unterschiedlichen Tiere aus.
    7. In welchen Wäldern kommen Rehe vor? Geben Sie die Namen und die Beschreibung der betroffenen Wälder und die Tierart aus.
    8. Wie viele unterschiedliche Tierarten wurden gesichtet und welche sind das? Zeigen Sie jede Tierart mit dem Datum der letzten Sichtung. SQL→MAX(datum).
    9. Welche Tierarten wurden NICHT gesichtet?
    10. In welchen Monaten wurden die meisten Tiere gesichtet? Geben Sie die drei “besten” Monate für Tiersichtungen aus.
  • Räumliche Abfragen:
    1. Wie gross sind die erfassten Wälder? Geben Sie eine Liste mit den Waldflächen in qm aus.
      • Funktion: ST_AREA
    2. Wie viel Fläche in qm haben alle Wälder zusammen?
      • Funktion: ST_AREA und SUM
    3. Welche Sichtungsmeldungen liegen innerhalb eines Waldes?
      • Funktion: ST_CONTAINS oder ST_WITHIN
    4. Welche Sichtungsmeldungen liegen ausserhalb des Waldes?
      • Funktion: ST_UNION und ST_CONTAINS
    5. In welchem Wald gibt es die meisten Sichtungen?
      • Funktion: ST_CONTAINS und SUM
    6. Wie weit sind die ausserhalb liegenden Sichtungsmeldungen vom Wald entfernt?
      • Funktion: ST_DISTANCE, ST_UNION und ST_CONTAINS
    7. Welche Tiere wurden ausserhalb vom Wald gesichtet?
      • Funktion: ST_UNION, ST_CONTAINS + JOIN
  • Weitere Abfragen:
    1. Gibt es Tierarten, welche nur im Wald gesichtet wurden? Welche sind das?
    2. Gibt es Tierarten, welche nie im Wald gesichtet wurden? Welche sind das?
    3. In welchen Wäldern wurden Füchse gesichtet?
    4. In welchen Wäldern kommen Rehe und Füchse gemeinsam vor?
    5. Wo gibt es Hermeline aber keine Mauswiesel?