Bioshops SQL

-- UPRAVA TABULKY S UDAJI Z APIFY, UPRAVA ADRESY NA ULICI A DISTRICT, ODSTRANENI PREBYTECNEHO TEXTU V OPENING HOURS
CREATE OR REPLACE TABLE BIOSHOPS_SCRAPPED_CLEAN AS
SELECT "location_lat"::FLOAT AS LATITUDE,
"location_lng"::FLOAT AS LONGITUDE,
"title"::VARCHAR AS NAME,
array_to_string(array_slice(split("address", ', '), 0, 1),' ') AS STREET,
array_to_string(array_slice(split(array_to_string(array_slice(split("address", ', '), 1, 4),' '), ' '), 2, -1),' ') AS DISTRICT,
"categoryName" AS CATEGORY_NAME,
"url" AS URL
FROM "BioShops_scrapping";

-- ODSTRANENY PRODEJEN MIMO PRAHU + JEDNE DUPLIKATNI RADKY
DELETE FROM BIOSHOPS_SCRAPPED_CLEAN WHERE DISTRICT IN ('Dobřichovice', 'Bavoryně', 'Poděbrady', 'Rudná u Prahy') OR NAME = 'Cesmína.bio, s.r.o.';

-- UPRAVA TABULKY STAZENE Z OPENDATA pro BioShops
CREATE OR REPLACE TABLE BIOSHOPS_CLEAN AS
SELECT DISTINCT "latitude" LATITUDE,
"longitude" LONGITUDE,
"name"::VARCHAR NAME
FROM "BioShops";

-- ZISKAT DATA Z OBOU TABULEK A ZAROVN SPOJIT POKUD MOZNE
CREATE OR REPLACE TABLE BIOSHOPS_ALL_CLEAN AS
SELECT
CASE WHEN BSC.LATITUDE IS NULL THEN BC.LATITUDE
ELSE BSC.LATITUDE
END::STRING AS LATITUDE,
CASE WHEN BSC.LONGITUDE IS NULL THEN BC.LONGITUDE
ELSE BSC.LONGITUDE
END::STRING AS LONGITUDE,
CASE WHEN BSC.NAME IS NULL THEN BC.NAME
ELSE BSC.NAME
END::STRING AS NAME,
STREET,
CASE WHEN DISTRICT ILIKE '%-%' THEN
LEFT(DISTRICT, CHARINDEX('-', DISTRICT) - 1)
ELSE DISTRICT
END AS DISTRICT1,
CASE
    WHEN DISTRICT1 = 'Letňany' THEN 'Praha 18'
    WHEN DISTRICT1 = 'Malá Strana' THEN 'Praha 1'
    WHEN DISTRICT1 = 'Staré Město' THEN 'Praha 1'
    WHEN DISTRICT1 = 'Nové Město' THEN 'Praha 1'
    WHEN DISTRICT1 = 'Vinohrady' THEN 'Praha 2'
    WHEN DISTRICT1 = 'Zbraslav' THEN 'Praha 16'
    WHEN DISTRICT1 = 'Strašnice' THEN 'Praha 10'
    WHEN DISTRICT = 'Praha-Holešovice' THEN 'Praha 7'
    WHEN DISTRICT = 'Praha-Kunratice' THEN 'Praha 4'
    WHEN DISTRICT1 = 'Praha 6 Bubeneč' THEN 'Praha 6'
    ELSE DISTRICT1
END AS DISTRICT,
CATEGORY_NAME,
URL
FROM BIOSHOPS_SCRAPPED_CLEAN AS BSC
FULL OUTER JOIN BIOSHOPS_CLEAN  AS BC ON BC.LONGITUDE = BSC.LONGITUDE AND BC.LATITUDE = BSC.LATITUDE;

ALTER TABLE BIOSHOPS_ALL_CLEAN DROP DISTRICT1;