1duckdb -c "Select count(*) from read_parquet('s3://us-west-2.opendata.source.coop/addresscloud/epc/geoparquet-local-authority/*.parquet');" 

1duckdb -c "Select count(*) from read_parquet('s3://us-west-2.opendata.source.coop/addresscloud/epc/geoparquet-local-authority/*.parquet');" 

1aws s3 --no-sign-request ls s3://us-west-2.opendata.source.coop/addresscloud/epc/

1aws s3 --no-sign-request ls s3://us-west-2.opendata.source.coop/addresscloud/epc/

1#!/bin/bash
2# Zip file name in the current directory
3zip_file="all-domestic-certificates.zip"
4# Loop through the zip file contents and extract certificates.csv
5unzip -l "$zip_file" | grep certificates.csv | while read -r line; do
6file_name=$(echo "$line" | awk '{print $NF}')
7folder_name=$(dirname "$file_name")
8# Extract the specific file and save it with the folder name as filename
9unzip -p "$zip_file" "$file_name" > "epc_csv/${folder_name//\//-}.csv"
10done

1#!/bin/bash
2# Zip file name in the current directory
3zip_file="all-domestic-certificates.zip"
4# Loop through the zip file contents and extract certificates.csv
5unzip -l "$zip_file" | grep certificates.csv | while read -r line; do
6file_name=$(echo "$line" | awk '{print $NF}')
7folder_name=$(dirname "$file_name")
8# Extract the specific file and save it with the folder name as filename
9unzip -p "$zip_file" "$file_name" > "epc_csv/${folder_name//\//-}.csv"
10done

1$ duckdb

1$ duckdb

1#!/bin/bash
2shopt -s globstar
3for d in ./**/*.parquet
4do
5dir="${d%/*}" # Strip the *.parquet pathname back to the containing directory
6#echo $dir
7parquet="${d##*/}" # Strip the *.parquet pathname back to just the filename
8new_name=$(echo "$dir" | tr ' ' _ |cut -d'=' -f2)
9#echo "Converted $parquet to geoparquet" >&2
10(
11cd "$dir" || exit
12find . -type f -name "*.parquet" -size -100b -delete
13if [ -f $parquet ]
14then
15gpq convert $parquet $new_name.parquet
16fi
17)
18done
19for d in ./**/*.parquet
20do find . -type f -name "data*" -delete;
21done

1#!/bin/bash
2shopt -s globstar
3for d in ./**/*.parquet
4do
5dir="${d%/*}" # Strip the *.parquet pathname back to the containing directory
6#echo $dir
7parquet="${d##*/}" # Strip the *.parquet pathname back to just the filename
8new_name=$(echo "$dir" | tr ' ' _ |cut -d'=' -f2)
9#echo "Converted $parquet to geoparquet" >&2
10(
11cd "$dir" || exit
12find . -type f -name "*.parquet" -size -100b -delete
13if [ -f $parquet ]
14then
15gpq convert $parquet $new_name.parquet
16fi
17)
18done
19for d in ./**/*.parquet
20do find . -type f -name "data*" -delete;
21done

1create or replace view epc_certs as select * from read_parquet('epc/*.parquet');

1create or replace view epc_certs as select * from read_parquet('epc/*.parquet');

1create or replace view opuprn as select * from read_parquet('opuprn.parquet');

1create or replace view opuprn as select * from read_parquet('opuprn.parquet');

1create or replace view epc as
2select epc_certs.*,
3opuprn.LATITUDE as lat,
4opuprn.LONGITUDE as lon
5from epc_certs
6join opuprn ON epc_certs.uprn = opuprn.uprn::text;

1create or replace view epc as
2select epc_certs.*,
3opuprn.LATITUDE as lat,
4opuprn.LONGITUDE as lon
5from epc_certs
6join opuprn ON epc_certs.uprn = opuprn.uprn::text;

1install spatial;
2load spatial;
3COPY (Select UPRN as uprn,
4LOCAL_AUTHORITY as local_authority,
5LOCAL_AUTHORITY_LABEL as local_authority_label,
6CURRENT_ENERGY_RATING as current_energy_rating,
7POTENTIAL_ENERGY_RATING as potential_rating,
8INSPECTION_DATE as inspection_date,
9ST_AsText(ST_Point(lon::double,lat::double)) as geometry
10from epc)
11TO 'epc_partitioned' (FORMAT PARQUET, PARTITION_BY (LOCAL_AUTHORITY_LABEL));

1install spatial;
2load spatial;
3COPY (Select UPRN as uprn,
4LOCAL_AUTHORITY as local_authority,
5LOCAL_AUTHORITY_LABEL as local_authority_label,
6CURRENT_ENERGY_RATING as current_energy_rating,
7POTENTIAL_ENERGY_RATING as potential_rating,
8INSPECTION_DATE as inspection_date,
9ST_AsText(ST_Point(lon::double,lat::double)) as geometry
10from epc)
11TO 'epc_partitioned' (FORMAT PARQUET, PARTITION_BY (LOCAL_AUTHORITY_LABEL));