Complete the Circle

Vigo county in Indiana. Underserved?
Ouch!
Under served county finder in action
CREATE TABLE population_county (
id SERIAL,
statefp VARCHAR(2),
state_name VARCHAR(255),
name VARCHAR(255),
type VARCHAR(100),
pop_2019 integer,
PRIMARY KEY (id)
);
Create the population_county table
county_pop_2019.csv
$ psql carto_boundaries -U awspostgres -p 5432 -h aws-postgres-postgis.xxx.us-east-1.rds.amazonaws.com -c "\copy population_county FROM '/tmp/county_pop_2019.csv' with (format csv,header true, delimiter ',');"
SELECT id, statefp, state_name, name, type, pop_2019
FROM public.population_county;
Importing counties into the cloud
5 tables in the database
SELECT
county.geom as geom,
county.name,
max(county.countyfp) as countyfp,
max(pop.type) as type,
COUNT(geo_points.geom) as geo_points_count,
MAX(pop.pop_2019) as pop_2019,
CASE
WHEN count(geo_points.geom) = 0 THEN max(pop.pop_2019)
ELSE max(pop.pop_2019) / count(geo_points.geom)
END AS persons_per_point
FROM cb_2018_us_county_20m county
LEFT JOIN
(
SELECT ST_SetSRID(ST_MAKEPOINT(longitude::double precision,
latitude::double precision),4326) as geom
FROM csv_import_xxx
)AS geo_points on ST_WITHIN(geo_points.geom, county.geom)
LEFT JOIN population_county pop on pop.name = county.name
JOIN cb_2018_us_state_20m state on state.statefp = county.statefp
WHERE 1 = 1
AND state.stusps = 'IN'
AND pop.statefp = state.statefp
GROUP BY county.geom, county.name
ORDER BY persons_per_point desc;
getGeoJsonForCsv function
Underserved county finder

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store