Parks and Gardens SQL

-- SPOJIT DVE TABULKY, SCRAPPING "ZAHRADY V PRAZE" A "PARKY V PRAZE"
CREATE OR REPLACE TABLE GREEN_SCRAPPED_DIRTY AS
SELECT
"location_lat" LATITUDE,
"location_lng" LONGITUDE,
"title" TITLE,
CASE WHEN "address" ILIKE '%Czechia%' THEN
LEFT("address", CHARINDEX(', Czechia', "address") - 1)
ELSE "address"
END AS ADDRESS1,
REPLACE(ADDRESS1, 'Pražský hrad,', 'Pražský hrad -') AS ADDRESS,
CASE
    WHEN array_size(split(ADDRESS, ', ')) = 1 THEN ADDRESS
    WHEN array_size(split(ADDRESS, ', ')) = 2 THEN array_to_string(array_slice(split(ADDRESS, ', '), -1, 2),' ')
    WHEN array_size(split(ADDRESS, ', ')) = 3 THEN array_to_string(array_slice(split(ADDRESS, ', '), -1, 3),' ')
    WHEN array_size(split(ADDRESS, ', ')) = 4 THEN array_to_string(array_slice(split(ADDRESS, ', '), -1, 4),' ')
    ELSE 'no address'
END AS DISTRICT1,
regexp_replace (DISTRICT1,'.*[0-9][0-9][0-9] [0-9][0-9]','') AS DISTRICT2,
REPLACE(DISTRICT2, 'Prague', 'Praha') AS DISTRICT,
REPLACE ("categoryName",'Park and Garden','Park') AS CATEGORYNAME,
"phone" PHONE,
"website" URL
FROM "GardenScrapping"
UNION ALL
SELECT "location_lat" LATITUDE,
"location_lng" LONGITUDE,
"title" TITLE,
CASE WHEN "address" ILIKE '%Czechia%' THEN
LEFT("address", CHARINDEX(', Czechia', "address") - 1)
ELSE "address"
END AS ADDRESS1,
REPLACE(ADDRESS1, 'Pražský hrad,', 'Pražský hrad -') AS ADDRESS,
CASE
    WHEN array_size(split(ADDRESS, ', ')) = 1 THEN ADDRESS
    WHEN array_size(split(ADDRESS, ', ')) = 2 THEN array_to_string(array_slice(split(ADDRESS, ', '), -1, 2),' ')
    WHEN array_size(split(ADDRESS, ', ')) = 3 THEN array_to_string(array_slice(split(ADDRESS, ', '), -1, 3),' ')
    WHEN array_size(split(ADDRESS, ', ')) = 4 THEN array_to_string(array_slice(split(ADDRESS, ', '), -1, 4),' ')
    ELSE 'no address'
END AS DISTRICT1,
regexp_replace (DISTRICT1,'.*[0-9][0-9][0-9] [0-9][0-9]','') AS DISTRICT2,
REPLACE(DISTRICT2, 'Prague', 'Praha') AS DISTRICT,
REPLACE ("categoryName",'Park and Garden','Park') AS CATEGORYNAME,
"phone" PHONE,
"website" URL
FROM "ParksScrapping";

--SMAZAT KOLONKY, CO NEJSOU NUTNE
ALTER TABLE GREEN_SCRAPPED_DIRTY DROP ADDRESS1, ADDRESS, DISTRICT1, DISTRICT2;

-- SMAZAT NERELEVANTNI HODNOTY
DELETE FROM GREEN_SCRAPPED_DIRTY
WHERE CATEGORYNAME IS NULL
OR CATEGORYNAME = 'Water park'
OR CATEGORYNAME ilike 'Amusement%'
OR CATEGORYNAME = 'Bonsai plant supplier'
OR CATEGORYNAME = 'Window installation service'
OR CATEGORYNAME = 'Store'
OR CATEGORYNAME ilike '%estaurant'
OR CATEGORYNAME = 'Pharmacy'
OR CATEGORYNAME = 'Ravine'
OR CATEGORYNAME = 'Florist'
OR CATEGORYNAME = 'Spa'
OR CATEGORYNAME = 'Video arcade'
OR CATEGORYNAME = 'Water park'
OR CATEGORYNAME ilike '%center%'
OR CATEGORYNAME = 'Event venue'
OR CATEGORYNAME ilike '%centre%'
OR CATEGORYNAME = 'Pharmacy'
OR CATEGORYNAME = 'Social services organisation'
OR CATEGORYNAME = 'Playground'
OR CATEGORYNAME = 'Landscape Gardener'
OR CATEGORYNAME = 'Architect'
OR CATEGORYNAME = 'Lawn care service'
OR CATEGORYNAME ilike 'Escape room%'
OR CATEGORYNAME = 'Landscape architect'
OR CATEGORYNAME = 'Theme park'
OR CATEGORYNAME ilike '%Shop%'
OR CATEGORYNAME ilike '%Jewellery%'
OR CATEGORYNAME ilike '%estate%'
OR TITLE ilike '%služby%'
OR TITLE ilike '%údržba%'
OR TITLE ilike '%zahradnictví%'
OR TITLE ilike '%údržba%'
OR TITLE ilike '%technika%'
OR TITLE ilike '%práce%'
OR TITLE ilike '%realizace%'
OR TITLE ilike '%Å¡kola%'
OR TITLE ilike '%s.r.o%'
OR TITLE ilike '%Ltd%'
OR TITLE ilike '%zavla%'
OR TITLE ilike '%magazine%'
OR TITLE ilike '%Mountfield%'
OR TITLE = 'Cibule.cz'
OR TITLE ilike 'Dendrological%'
OR TITLE ='E-ZAHRADA.cz'
OR TITLE ='TOBOGA Fantasy Praha';

-- SMAZAT DUPLICITNI VYSCRAPOVANE HODNOTY
CREATE OR REPLACE TABLE GREEN_SCRAPPED_CLEAN AS
SELECT DISTINCT * FROM GREEN_SCRAPPED_DIRTY;

-- UPRAVA TABULKY PARKS
CREATE OR REPLACE TABLE PARKS_CLEAN AS
SELECT
"Lat" AS LATITUDE,
"Long" AS LONGITUDE,
"NAZEV" AS TITLE,
OBJECTID,
"Shape_Area" AS SHAPE_AREA,
"Shape_Length" AS SHAPE_LENGTH,
"type" AS TYPE,
"coordinates_id" AS COORDINATES_ID
FROM "Parks";

--UPRAVA TABULKY GARDENS
CREATE OR REPLACE TABLE GARDENS_CLEAN AS
SELECT
"latitude" AS LATITUDE,
"longitude" AS LONGITUDE,
"name" AS TITLE,
"address" AS ADDRESS,
CASE
    WHEN "district" = 'praha-1' THEN 'Praha 1'
    WHEN "district" = 'praha-2' THEN 'Praha 2'
    WHEN "district" = 'praha-5' THEN 'Praha 5'
    WHEN "district" = 'praha-6' THEN 'Praha 6'
    WHEN "district" = 'praha-7' THEN 'Praha 7'
    ELSE 'N/A'
END AS DISTRICT,
"url" AS URL,
"restaurace" AS RESTAURANTS,
"wc" AS TOILETS,
"misto" AS DESCRIPTION,
"mhd" AS PUBLIC_TRANSPORT,
"sport" AS SPORT,
"brusle" AS BLADING,
"cesty" AS ROUTES,
"provoz" AS OWNER,
"kolo" AS BICYCLE,
"hriste" AS PLAYGROUND,
"parking" AS PARKING,
"doba" AS OPENING
FROM "Gardens";