import.sql

\copy (select y.tax_key, (house_number_low || ' ' || street_direction || ' ' || street || ' ' || street_type) as address, zip_code as zip, 'Milwaukee' as "city",ST_Y(ST_Centroid(s.geom)) as lat, ST_X(ST_Centroid(s.geom)) as lon, year_built, zoning, geo_alder, number_stories, last_assessment_amount, count, neighborhd as neighborhood, 0 as "tweeted" from (  select max(z.tax_key) as tax_key, count(*) as count from (    select max(tax_key) as tax_key, count(*) as count from assessments a join shapefiles s on s.taxkey = a.tax_key where year = 2022 and zip_code is NOT NULL and s.geom IS NOT NULL and geo_alder IS NOT NULL group by (house_number_low || ' ' || street_direction || ' ' || street || ' ' || street_type) ) z join assessments a on a.tax_key = z.tax_key and a.year = 2022 join shapefiles s on s.taxkey = z.tax_key group by s.geom) y join assessments a on a.tax_key = y.tax_key and a.year = 2022 join shapefiles s on s.taxkey = y.tax_key left join neighborhood_shapefiles n on ST_Contains(n.geom, ST_Centroid(s.geom)) order by y.tax_key) to './data2.csv' csv header;

select y.tax_key, (house_number_low || ' ' || street_direction || ' ' || street || ' ' || street_type) as address, zip_code as zip, 'Milwaukee' as "city",ST_Y(ST_Centroid(s.geom)) as lat, ST_X(ST_Centroid(s.geom)) as lon, year_built, zoning, alder as geo_alder, number_stories, assessed_total as last_assessment_amount, name as neighborhood, count, 0 as "tweeted" from (  select max(z.tax_key) as tax_key, count(*) as count from (    select max(a.tax_key) as tax_key, count(*) as count from assessments a join assessment_shapefiles s on s.tax_key = a.tax_key where year = 2025 and zip_code is NOT NULL and s.geom IS NOT NULL and alder IS NOT NULL group by (house_number_low || ' ' || street_direction || ' ' || street || ' ' || street_type) ) z join assessments a on a.tax_key = z.tax_key and a.year = 2025 join assessment_shapefiles s on s.tax_key = z.tax_key group by s.geom) y join assessments a on a.tax_key = y.tax_key and a.year = 2025 join assessment_shapefiles s on s.tax_key = y.tax_key left join neighborhoods n on ST_Contains(n.geom, ST_Centroid(s.geom)) order by y.tax_key;

sqlite3 civics_dev.db
.headers on
.mode csv
.output new_data.csv
.quit