Trashbin_SQL

-- VYTVARIM NOVOU TABULKU KDE SPOJUJI ACCESS A POSITION + VYTVARIM KOLONKU NA CLEANINGFREQUENCY_TXT
CREATE OR REPLACE TABLE TRASHBIN_POSITION_ACCESS AS
(SELECT TB."stationID" AS STATIONID,
 TB."treshtypename" AS TRASHTYPENAME,
 TB."latitude" AS LATITUDE,
 TB."longitude" AS LONGITUDE,
 TBA."STATIONNAME" AS STATION_ADDRESS,
 TBA."STATIONNUMBER" AS STATION_NUMBER,
 TBA."CITYDISTRICTRUIANCODE",
  CASE
    WHEN TBA."CITYDISTRICT" = 'Kunratice' THEN 'Praha 4'
    WHEN TBA."CITYDISTRICT" = 'Slivenec' THEN 'Praha 5'
    WHEN TBA."CITYDISTRICT" IN ('Nebušice','Lysolaje','Suchdol','Přední Kopanina') THEN 'Praha 6'
    WHEN TBA."CITYDISTRICT" = 'Troja' THEN 'Praha 7'
    WHEN TBA."CITYDISTRICT" IN ('Ďáblice','Březiněves','Dolní Chabry') THEN 'Praha 8'
    WHEN TBA."CITYDISTRICT" IN ('Šeberov','Újezd','Křeslice') THEN 'Praha 11'
    WHEN TBA."CITYDISTRICT" = 'Libuš' THEN 'Praha 12'
    WHEN TBA."CITYDISTRICT" = 'Řeporyje' THEN 'Praha 13'
    WHEN TBA."CITYDISTRICT" = 'Dolní Počernice' THEN 'Praha 14'
    WHEN TBA."CITYDISTRICT" IN ('Dolní Měcholupy', 'Dubeč', 'Štěrboholy', 'Petrovice') THEN 'Praha 15'
    WHEN TBA."CITYDISTRICT" IN ('Zbraslav','Lipence','Velká Chuchle','Lochkov') THEN 'Praha 16'
    WHEN TBA."CITYDISTRICT" = 'Zličín' THEN 'Praha 17'
    WHEN TBA."CITYDISTRICT" IN ('Vinoř','Čakovice','Satalice') THEN 'Praha 19'
    WHEN TBA."CITYDISTRICT" IN ('Běchovice','Koloděje','Klánovice') THEN 'Praha 21'
    WHEN TBA."CITYDISTRICT" IN ('Benice','Kolovraty','Nedvězí','Královice') THEN 'Praha 22'
    ELSE TBA."CITYDISTRICT"
END AS CITYDISTRICT,
 TBA.PRISTUP AS ACCESS,
 TB."cleaningfrequency" AS CLEANINGFREQUENCY,
   CASE
    WHEN CLEANINGFREQUENCY = 11 THEN '1x za týden'
    WHEN CLEANINGFREQUENCY = 12 THEN '2x za týden'
    WHEN CLEANINGFREQUENCY = 13 THEN '3x za týden'
    WHEN CLEANINGFREQUENCY = 14 THEN '4x za týden'
    WHEN CLEANINGFREQUENCY = 15 THEN '5x za týden'
    WHEN CLEANINGFREQUENCY = 16 THEN '6x za týden'
    WHEN CLEANINGFREQUENCY = 17 THEN '7x za týden'
    WHEN CLEANINGFREQUENCY = 21 THEN '1x za 2 týdny'
    WHEN CLEANINGFREQUENCY = 31 THEN '1x za 3 týdny'
    WHEN CLEANINGFREQUENCY = 41 THEN '1x za 4 týdny'
    WHEN CLEANINGFREQUENCY = 61 THEN '1x za 6 týdnů'
    ELSE 'N/A'
 END AS CLEANINGFREQUENCY_TXT
FROM "TrashBin_position" AS TB
LEFT JOIN "TrashBin_access" AS TBA ON TB."stationID" = TBA.ID);

UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.086369 WHERE STATIONID = 3626;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.419139 WHERE STATIONID = 3626;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.086141 WHERE STATIONID = 4168;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.417608 WHERE STATIONID = 4168;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.092736 WHERE STATIONID = 3657;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.422132 WHERE STATIONID = 3657;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.090053 WHERE STATIONID = 4288;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.422351 WHERE STATIONID = 4288;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.088509 WHERE STATIONID = 4071;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.396105 WHERE STATIONID = 4071;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.088390 WHERE STATIONID = 4076;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.396193 WHERE STATIONID = 4076;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.088332 WHERE STATIONID = 3990;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.395910 WHERE STATIONID = 3990;
UPDATE TRASHBIN_POSITION_ACCESS SET LATITUDE = 50.088072 WHERE STATIONID = 3993;
UPDATE TRASHBIN_POSITION_ACCESS SET LONGITUDE = 14.393525 WHERE STATIONID = 3993;

-- VYTVARIM SPOJENI TABULEK MEASUREMENT A SENSOR. TABULKA SENSOR JE SPOJOVACI PRVEK MEZI MEASUREMENT A TRASHBIN POSITION ACCESS. ROVNOU SI I PREPISU DISTRICT DO HEZCI FORMY A OPRAVIM STATIONNUMBER, KDE JE MEZERA PRED LOMITKEM (JE TAM JEN JEDEN)
CREATE OR REPLACE TABLE TRASHBIN_SENSOR_MEASUREMENT AS
(SELECT
TBS."stationid"::NUMBER AS STATION_ID,
TBS."name" AS ADDRESS,
 CASE
  WHEN TBS."stationnumber" = '0009 /160' THEN '0009/ 160'
  ELSE TBS."stationnumber"
END AS STATIONUMBER,
CASE
    WHEN TBS."district" = 'praha-6' THEN 'Praha 6'
    WHEN TBS."district" = 'praha-9' THEN 'Praha 9'
    WHEN TBS."district" = 'praha-7' THEN 'Praha 7'
    WHEN TBS."district" = 'praha-3' THEN 'Praha 3'
    WHEN TBS."district" = 'praha-22' THEN 'Praha 22'
    WHEN TBS."district" = 'praha-1' THEN 'Praha 1'
    WHEN TBS."district" = 'praha-18' THEN 'Praha 18'
    WHEN TBS."district" = 'praha-8' THEN 'Praha 8'
    WHEN TBS."district" = 'praha-10' THEN 'Praha 10'
    WHEN TBS."district" = 'praha-5' THEN 'Praha 5'
    WHEN TBS."district" = 'praha-16' THEN 'Praha 16'
    WHEN TBS."district" = 'praha-20' THEN 'Praha 20'
    ELSE 'N/A'
END AS DISTRICT,
TBS."latitude"::FLOAT AS LATITUDE,
TBS."longitude"::FLOAT AS LONGITUDE,
TBS."acces" AS ACCESS,
TBS."cleaningfrequency_id" AS CLEANINGFREQUENCY_ID,
TBS."container_type" AS CONTAINER_TYPE,
TBS."trash_type_description" AS TRASHTYPE_DESCRIPTION,
TBS."trash_type_id" AS TRASHTYPE_ID,
TBS."sensor_code" AS SENSOR_CODE,
TBS."sensor_container_id" AS SENSOR_CONTAINER_ID,
TBM."percent_calculated" AS PERCENT_CALCULATED,
TBM."temperature" AS TEMPERATURE,
TBM."firealarm" AS FIREALARM,
TBM."measured_at_utc" AS MEASURED_AT_UTC,
TBM."id" AS MEASUREMENT_ID
FROM "TrashBin_measurement" AS TBM
JOIN "TrashBin_sensor" AS TBS ON TBM."container_id" = TBS."sensor_container_id");

-- SMAZ VSECHNY RADKY, KDE TRASHTYPE_DESCRIPTION JE NEZNAMY
DELETE FROM TRASHBIN_SENSOR_MEASUREMENT WHERE TRASHTYPE_DESCRIPTION = 'neznámý';

----NAPOJUJU TRASBIN_POSITION_ACCESS NA TRASHBIN_SENSOR_MEASUREMENT
--CREATE OR REPLACE TABLE TRASHBIN_POSITION_ACCESS_SENSOR_MEASUREMENT AS
--(SELECT * FROM TRASHBIN_SENSOR_MEASUREMENT AS TSM
--LEFT JOIN TRASHBIN_POSITION_ACCESS AS TPA
--   ON TSM.STATIONUMBER = TPA.STATION_NUMBER NEBO RADEJI PODLE STATIONID
--    AND TSM.TRASHTYPE_DESCRIPTION = TPA.TRASHTYPENAME
--ORDER BY STATION_ID DESC);