Databasteknik: Lösningar till övning 5, deluppgift 6

Det här är några förslag på lösningar. Det finns förstås andra lösningar som också är korrekta,

Om vi råkat blanda ihop koordinaterna för tätorterna, kanske flera tätorter fått samma koordinater. Finns det två olika tätorter med samma koordinater?

SELECT *
FROM "Tätorter" AS t1, "Tätorter" AS t2
WHERE t1.latitud = t2.latitud
AND t1.longitud = t2.longitud
AND t1.kod <> t2.kod;

Övning: Den frågan tar lång tid att köra (på en viss databashanterare som vi provade med). Vad kan det bero på?

Övning: På en annan databashanterare som vi provade med, gick frågan snabbt att köra. Vad kan det bero på?

Tätorternas namn är inte unika, så ett troligt scenario där det kan ha blivit fel är om vi tagit fel på vilken tätort som är vilken när vi la in koordinaterna. Finns det två olika tätorter med samma namn och samma koordinater?

SELECT *
FROM "Tätorter" AS t1, "Tätorter" AS t2
WHERE t1.latitud = t2.latitud
AND t1.longitud = t2.longitud
AND t1.namn = t2.namn
AND t1.kod <> t2.kod;

Övning: Den frågan går mycket fortare att köra än den föregående, trots att vi lagt på ett villkor till i WHERE-delen. Vad kan det bero på?

Om en tätort fått fel koordinater kanske den enligt databasen har hamnat långt från de andra orterna i kommunen. Vilka två tätorter som ligger i samma kommun har (enligt databasen) längst avstånd mellan sig?

SELECT *
FROM "Tätorter" AS t1, "Tätorter" AS t2
WHERE t1.kommun = t2.kommun
AND t1.kod <> t2.kod
AND t1.latitud IS NOT NULL
AND t2.latitud IS NOT NULL
AND t1.longitud IS NOT NULL
AND t2.longitud IS NOT NULL
ORDER BY (t1.latitud-t2.latitud)*(t1.latitud-t2.latitud) + (t1.longitud-t2.longitud)*(t1.longitud-t2.longitud) DESC;

Formeln för avstånd ovan är fel, men ger en uppskattning av de relativa avstånden som stämmer åtminstone inom en faktor 10 eller så. Dessutom ger SQL-frågan inte riktigt det sökta svaret. Så här kan vi få fram rätt svar:

CREATE VIEW "Avstånd Inom Kommunen" AS
SELECT t1.kod AS "tätortskod1", t1.namn AS "tätortsnamn1", t2.kod AS "tätortskod2", t2.namn AS "tätortsnamn2", Kommuner.kod, Kommuner.namn AS kommunnamn,
       (t1.latitud-t2.latitud)*(t1.latitud-t2.latitud) + (t1.longitud-t2.longitud)*(t1.longitud-t2.longitud) AS "avstånd"
FROM "Tätorter" AS t1, "Tätorter" AS t2, Kommuner
WHERE t1.kommun = t2.kommun
AND t2.kommun = Kommuner.kod
AND t1.kod < t2.kod
AND t1.latitud IS NOT NULL
AND t2.latitud IS NOT NULL
AND t1.longitud IS NOT NULL
AND t2.longitud IS NOT NULL;

SELECT * FROM "Avstånd Inom Kommunen"
WHERE "avstånd" = (SELECT MAX("avstånd") FROM "Avstånd Inom Kommunen");

Finns det någon kommun utan tätorter?

SELECT *
FROM Kommuner
WHERE NOT EXISTS (SELECT * FROM "Tätorter" WHERE kommun = Kommuner.kod);

Ja, det finns det, men det beror inte på fel i databasen, utan på hur tätorter definieras! Exempelvis innehåller Solna kommun centralorten Solna, men den har växt ihop med Stockholm och räknas inte som en egen tätort, utan som en del av tätorten Stockholm.

Finns det något län utan kommuner?

SELECT *
FROM "Län"
WHERE NOT EXISTS (SELECT * FROM Kommuner WHERE "län" = "Län".kod);

• Finns det någon residensstad som inte ligger i det län som den är residensstad för? (Dvs: Finns det någon residensstad som inte tillhör en kommun som tillhör det länet?)
• Vilket är det största avståndet från en tätort till residensstaden för det län som tätorten ligger i?