Manual quarterly rental listings export for Q1 2018

export.sh

docker-compose exec -T db sh -c 'psql rent_aggregator_db -t -P pager=off -c "COPY (select geoid, count(*) as count_all, min(ask) as min_all, max(ask) as max_all, quantile(ask, 0.5) as med_all, round(avg(ask/bedrooms) FILTER (WHERE bedrooms > 0),2) as mean_per_bedroom, count(*) FILTER (WHERE bedrooms=1) as count_1br, min(ask) FILTER (WHERE bedrooms=1) as min_1br, max(ask) FILTER (WHERE bedrooms=1) as max_1br, quantile(ask,0.5) FILTER (WHERE bedrooms=1) as med_1br, count(*) FILTER (WHERE bedrooms=2) as count_2br, min(ask) FILTER (WHERE bedrooms=2) as min_2br, max(ask) FILTER (WHERE bedrooms=2) as max_2br, quantile(ask,0.5) FILTER (WHERE bedrooms=2) as med_2br, count(*) FILTER (WHERE bedrooms=3) as count_3br, min(ask) FILTER (WHERE bedrooms=3) as min_3br, max(ask) FILTER (WHERE bedrooms=3) as max_3br, quantile(ask,0.5) FILTER (WHERE bedrooms=3) as med_3br, count(*) FILTER (WHERE bedrooms>=4) as count_4upbr, min(ask) FILTER (WHERE bedrooms>=4) as min_4upbr, max(ask) FILTER (WHERE bedrooms>=4) as max_4upbr, quantile(ask,0.5) FILTER (WHERE bedrooms>=4) as med_4upbr FROM listings__valid WHERE (last_seen >= date '\''2018-01-01'\'') AND (last_seen < date '\''2018-04-01'\'') group by geoid) TO STDOUT WITH CSV HEADER;"' > ./blockgroup_stats/quarterly_blockgroup_stats_20180401.csv

docker-compose exec -T db sh -c 'psql rent_aggregator_db -t -P pager=off -c "SELECT row_to_json(featcoll) FROM (SELECT '\''FeatureCollection'\'' As type, array_to_json(array_agg(feat)) As features FROM (SELECT '\''Feature'\'' As type, ST_AsGeoJSON(tbl.geom)::json As geometry, row_to_json((SELECT l FROM (SELECT geoid,count_all,min_all,max_all,med_all,mean_per_bedroom,count_1br,min_1br,max_1br,med_1br,count_2br,min_2br,max_2br,med_2br,count_3br,min_3br,max_3br,med_3br,count_4upbr,min_4upbr,max_4upbr,med_4upbr) As l)) As properties FROM (SELECT l.*, bg.geom AS geom FROM (select geoid, count(*) as count_all, min(ask) as min_all, max(ask) as max_all, quantile(ask, 0.5) as med_all, round(avg(ask/bedrooms) FILTER (WHERE bedrooms > 0),2) as mean_per_bedroom, count(*) FILTER (WHERE bedrooms=1) as count_1br, min(ask) FILTER (WHERE bedrooms=1) as min_1br, max(ask) FILTER (WHERE bedrooms=1) as max_1br, quantile(ask,0.5) FILTER (WHERE bedrooms=1) as med_1br, count(*) FILTER (WHERE bedrooms=2) as count_2br, min(ask) FILTER (WHERE bedrooms=2) as min_2br, max(ask) FILTER (WHERE bedrooms=2) as max_2br, quantile(ask,0.5) FILTER (WHERE bedrooms=2) as med_2br, count(*) FILTER (WHERE bedrooms=3) as count_3br, min(ask) FILTER (WHERE bedrooms=3) as min_3br, max(ask) FILTER (WHERE bedrooms=3) as max_3br, quantile(ask,0.5) FILTER (WHERE bedrooms=3) as med_3br, count(*) FILTER (WHERE bedrooms>=4) as count_4upbr, min(ask) FILTER (WHERE bedrooms>=4) as min_4upbr, max(ask) FILTER (WHERE bedrooms>=4) as max_4upbr, quantile(ask,0.5) FILTER (WHERE bedrooms>=4) as med_4upbr FROM listings__valid WHERE (last_seen >=  date '\''2018-01-01'\'') AND (last_seen < date '\''2018-04-01'\'') group by geoid) l JOIN blockgroup bg ON bg.geoid10 = l.GEOID) As tbl) As feat) As featcoll;"' > ./blockgroup_stats/quarterly_blockgroup_stats_20180401.geojson

docker-compose exec -T db sh -c 'psql rent_aggregator_db -c "COPY (SELECT ST_X(location) As x,ST_Y(location) as y,id,uid,ask,bedrooms,title,address,posting_date,last_seen,created_at,updated_at,payload,source_id,survey_id FROM listings WHERE (last_seen >= date '\''2018-01-01'\'') AND (last_seen < date '\''2018-04-01'\'')) TO STDOUT WITH CSV HEADER;"' > ./listings/quarterly_rent_listings_20180401.csv