def get_counties_geom():
"""Counties in GeoJSON, geometry only"""
log.debug("entering get_counties_geom() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT countyfp AS ct_fips, ST_AsGeoJSON(the_geom) AS geometry " \
"FROM tiger_cb14_500k.county WHERE statefp='25'"
data = p2g.getData(con, sql)
log.debug("leaving get_counties_geom()")
return data
def get_counties_geom():
"""Counties in GeoJSON, geometry only"""
log.debug("entering get_counties_geom() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT countyfp AS ct_fips, ST_AsGeoJSON(the_geom) AS geometry " \
"FROM tiger_cb14_500k.county WHERE statefp='25'"
data = p2g.getData(con, sql)
log.debug("leaving get_counties_geom()")
return data
def get_county_by_id_geom(ct_fips):
"""County in GeoJSON, by id, geometry only"""
log.debug("entering get_county_by_id_geom() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT countyfp AS ct_fips, ST_AsGeoJSON(the_geom) AS geometry " \
"FROM tiger_cb14_500k.county " \
"WHERE statefp='25' AND countyfp=%s"
sql_params = (ct_fips, )
data = p2g.getData(con, sql, sql_params)
log.debug("leaving get_county_by_id_geom()")
return data
def get_county_by_id_geom(ct_fips):
"""County in GeoJSON, by id, geometry only"""
log.debug("entering get_county_by_id_geom() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT countyfp AS ct_fips, ST_AsGeoJSON(the_geom) AS geometry " \
"FROM tiger_cb14_500k.county " \
"WHERE statefp='25' AND countyfp=%s"
sql_params = (ct_fips,)
data = p2g.getData(con, sql, sql_params)
log.debug("leaving get_county_by_id_geom()")
return data
def get_synth_counties_all():
"""Counties in GeoJSON synthetic"""
log.debug("entering get_synth_counties_all() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT s.ct_fips, s.ct_name, s.sq_mi, s.pop, CASE WHEN s.pop > 0 THEN s.pop_male / s.pop ELSE 0 END AS pct_male, CASE WHEN s.pop > 0 THEN s.pop_female / s.pop ELSE 0 END AS pct_female, s.pop_sm, " \
"ST_AsGeoJSON(s.ct_poly) AS geometry, " \
"dd.rate as pct_diabetes, dhd.rate as pct_heart_disease, doa.rate as pct_opioid_addiction " \
"FROM synth_ma.synth_county_pop_stats s " \
"JOIN synth_ma.synth_county_disease_stats dd ON dd.ct_fips = s.ct_fips AND dd.disease_name = 'diabetes' " \
"JOIN synth_ma.synth_county_disease_stats dhd ON dhd.ct_fips = s.ct_fips AND dhd.disease_name = 'heart_disease' " \
"JOIN synth_ma.synth_county_disease_stats doa ON doa.ct_fips = s.ct_fips AND doa.disease_name = 'opioid_addiction' "
data = p2g.getData(con, sql)
log.debug("leaving get_synth_counties_all()")
return data
def get_synth_counties_all():
"""Counties in GeoJSON synthetic"""
log.debug("entering get_synth_counties_all() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT s.ct_fips, s.ct_name, s.sq_mi, s.pop, CASE WHEN s.pop > 0 THEN s.pop_male / s.pop ELSE 0 END AS pct_male, CASE WHEN s.pop > 0 THEN s.pop_female / s.pop ELSE 0 END AS pct_female, s.pop_sm, " \
"ST_AsGeoJSON(s.ct_poly) AS geometry, " \
"dd.rate as pct_diabetes, dhd.rate as pct_heart_disease, doa.rate as pct_opioid_addiction " \
"FROM synth_ma.synth_county_pop_stats s " \
"JOIN synth_ma.synth_county_disease_stats dd ON dd.ct_fips = s.ct_fips AND dd.disease_name = 'diabetes' " \
"JOIN synth_ma.synth_county_disease_stats dhd ON dhd.ct_fips = s.ct_fips AND dhd.disease_name = 'heart_disease' " \
"JOIN synth_ma.synth_county_disease_stats doa ON doa.ct_fips = s.ct_fips AND doa.disease_name = 'opioid_addiction' "
data = p2g.getData(con, sql)
log.debug("leaving get_synth_counties_all()")
return data
def get_block_window():
"""Blocks in GeoJSON, by window
Example: /htc/api/v1/block_window?minx=-71.26&maxx=-71.22&miny=42.49&maxy=42.51
"""
log.debug("entering get_block_window() IP=%s" % get_ip())
minx = request.args.get('minx')
maxx = request.args.get('maxx')
miny = request.args.get('miny')
maxy = request.args.get('maxy')
if not (minx and maxx and miny and maxy):
abort(404)
con = get_db_con()
sql = "SELECT s.block_id, s.sq_mi, s.pop, s.pop_male / s.pop as pct_male, s.pop_female / s.pop as pct_female, s.pop_sm, " \
"ST_AsGeoJSON(s.blk_poly) AS geometry " \
"FROM synth_ma.blk_stats s " \
"WHERE s.blk_poly && ST_SetSRID(ST_MakeBox2D(ST_Point(%s,%s), ST_Point(%s,%s)), 4269) AND s.pop > 0"
sql_params = (minx, miny, maxx, maxy)
data = p2g.getData(con, sql, sql_params)
log.debug("leaving get_block_window()")
return data
def get_block_window():
"""Blocks in GeoJSON, by window
Example: /htc/api/v1/block_window?minx=-71.26&maxx=-71.22&miny=42.49&maxy=42.51
"""
log.debug("entering get_block_window() IP=%s" % get_ip())
minx = request.args.get('minx')
maxx = request.args.get('maxx')
miny = request.args.get('miny')
maxy = request.args.get('maxy')
if not (minx and maxx and miny and maxy):
abort(404)
con = get_db_con()
sql = "SELECT s.block_id, s.sq_mi, s.pop, s.pop_male / s.pop as pct_male, s.pop_female / s.pop as pct_female, s.pop_sm, " \
"ST_AsGeoJSON(s.blk_poly) AS geometry " \
"FROM synth_ma.blk_stats s " \
"WHERE s.blk_poly && ST_SetSRID(ST_MakeBox2D(ST_Point(%s,%s), ST_Point(%s,%s)), 4269) AND s.pop > 0"
sql_params = (minx, miny, maxx, maxy)
data = p2g.getData(con, sql, sql_params)
log.debug("leaving get_block_window()")
return data
def get_counties_all():
"""Counties in GeoJSON"""
log.debug("entering get_counties_all() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT s.ct_fips, s.ct_name, s.sq_mi, s.pop, s.pop_male / s.pop as pct_male, s.pop_female / s.pop as pct_female, s.pop_sm, " \
"chr.hs_graduate as chr_hs_grad, chr.college as chr_college, chr.unemployed as chr_unemployed, chr.diabetes_rate as chr_diabetes, " \
"chr.adult_obesity as chr_adult_obesity, chr.adult_smoking as chr_adult_smoking, opioid.deaths as opioid_deaths, " \
"age.fact_pop_0_4 as pop_0_4,age.fact_pop_5_9 as pop_5_9,age.fact_pop_10_14 as pop_10_14,age.fact_pop_15_19 as pop_15_19, "\
"age.fact_pop_20_24 as pop_20_24,age.fact_pop_25_29 as pop_25_29,age.fact_pop_30_34 as pop_30_34,age.fact_pop_35_39 as pop_35_39, " \
"age.fact_pop_40_44 as pop_40_44,age.fact_pop_45_49 as pop_45_49,age.fact_pop_50_54 as pop_50_54,age.fact_pop_55_59 as pop_55_59, " \
"age.fact_pop_60_64 as pop_60_64,age.fact_pop_65_69 as pop_65_69,age.fact_pop_70_74 as pop_70_74,age.fact_pop_75_79 as pop_75_79, " \
"age.fact_pop_80_84 as pop_80_84,age.fact_pop_85_110 as pop_85_110, " \
"ST_AsGeoJSON(the_geom) AS geometry " \
"FROM synth_ma.county_stats s " \
"JOIN synth_ma.ma_opioid_county opioid ON opioid.countyfp = s.ct_fips AND opioid.year = '2015' " \
"JOIN tiger_cb14_500k.county g ON g.statefp = '25' AND g.countyfp = s.ct_fips " \
"JOIN synth_ma.ma_county_age age ON age.ct_fips = s.ct_fips " \
"JOIN county_health.chr ON chr.statefp = '25' AND chr.release_year = 2016 AND chr.countyfp = s.ct_fips"
data = p2g.getData(con, sql)
log.debug("leaving get_counties_all()")
return data
def get_counties_all():
"""Counties in GeoJSON"""
log.debug("entering get_counties_all() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT s.ct_fips, s.ct_name, s.sq_mi, s.pop, s.pop_male / s.pop as pct_male, s.pop_female / s.pop as pct_female, s.pop_sm, " \
"chr.hs_graduate as chr_hs_grad, chr.college as chr_college, chr.unemployed as chr_unemployed, chr.diabetes_rate as chr_diabetes, " \
"chr.adult_obesity as chr_adult_obesity, chr.adult_smoking as chr_adult_smoking, opioid.deaths as opioid_deaths, " \
"age.fact_pop_0_4 as pop_0_4,age.fact_pop_5_9 as pop_5_9,age.fact_pop_10_14 as pop_10_14,age.fact_pop_15_19 as pop_15_19, "\
"age.fact_pop_20_24 as pop_20_24,age.fact_pop_25_29 as pop_25_29,age.fact_pop_30_34 as pop_30_34,age.fact_pop_35_39 as pop_35_39, " \
"age.fact_pop_40_44 as pop_40_44,age.fact_pop_45_49 as pop_45_49,age.fact_pop_50_54 as pop_50_54,age.fact_pop_55_59 as pop_55_59, " \
"age.fact_pop_60_64 as pop_60_64,age.fact_pop_65_69 as pop_65_69,age.fact_pop_70_74 as pop_70_74,age.fact_pop_75_79 as pop_75_79, " \
"age.fact_pop_80_84 as pop_80_84,age.fact_pop_85_110 as pop_85_110, " \
"ST_AsGeoJSON(the_geom) AS geometry " \
"FROM synth_ma.county_stats s " \
"JOIN synth_ma.ma_opioid_county opioid ON opioid.countyfp = s.ct_fips AND opioid.year = '2015' " \
"JOIN tiger_cb14_500k.county g ON g.statefp = '25' AND g.countyfp = s.ct_fips " \
"JOIN synth_ma.ma_county_age age ON age.ct_fips = s.ct_fips " \
"JOIN county_health.chr ON chr.statefp = '25' AND chr.release_year = 2016 AND chr.countyfp = s.ct_fips"
data = p2g.getData(con, sql)
log.debug("leaving get_counties_all()")
return data
def get_cousub_all():
"""Cousubs in GeoJSON"""
log.debug("entering get_cousub_all() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT s.ct_fips, s.ct_name, s.cs_fips, s.cs_name, s.sq_mi, s.pop, s.pop_sm, " \
"CASE WHEN s.pop > 0 THEN s.pop_male / s.pop ELSE 0 END AS pct_male, " \
"CASE WHEN s.pop > 0 THEN s.pop_female / s.pop ELSE 0 END AS pct_female, " \
"op.deaths AS opioid_deaths, pred.pred_diabetes as pct_diabetes, " \
"age.fact_pop_0_4 as pop_0_4,age.fact_pop_5_9 as pop_5_9,age.fact_pop_10_14 as pop_10_14,age.fact_pop_15_19 as pop_15_19, "\
"age.fact_pop_20_24 as pop_20_24,age.fact_pop_25_29 as pop_25_29,age.fact_pop_30_34 as pop_30_34,age.fact_pop_35_39 as pop_35_39, " \
"age.fact_pop_40_44 as pop_40_44,age.fact_pop_45_49 as pop_45_49,age.fact_pop_50_54 as pop_50_54,age.fact_pop_55_59 as pop_55_59, " \
"age.fact_pop_60_64 as pop_60_64,age.fact_pop_65_69 as pop_65_69,age.fact_pop_70_74 as pop_70_74,age.fact_pop_75_79 as pop_75_79, " \
"age.fact_pop_80_84 as pop_80_84,age.fact_pop_85_110 as pop_85_110, " \
"ST_AsGeoJSON(g.the_geom) AS geometry " \
"FROM synth_ma.cousub_stats s " \
"JOIN synth_ma.ma_cousub_pred pred ON pred.state_fips = '25' AND pred.county = s.ct_fips AND pred.county_sub_fips = s.cs_fips AND s.cs_fips != '00000' " \
"JOIN tiger_cb14_500k.cousub g ON g.statefp = '25' AND g.countyfp = s.ct_fips AND g.cousubfp = s.cs_fips AND s.cs_fips != '00000' " \
"JOIN synth_ma.ma_cousub_age age ON age.cs_fips = s.cs_fips " \
"JOIN synth_ma.ma_opioid2 op ON op.cousubfp = s.cs_fips AND s.cs_fips != '00000' AND year = '2015'"
data = p2g.getData(con, sql)
log.debug("leaving get_cousub_all()")
return data
def get_cousub_all():
"""Cousubs in GeoJSON"""
log.debug("entering get_cousub_all() IP=%s" % get_ip())
con = get_db_con()
sql = "SELECT s.ct_fips, s.ct_name, s.cs_fips, s.cs_name, s.sq_mi, s.pop, s.pop_sm, " \
"CASE WHEN s.pop > 0 THEN s.pop_male / s.pop ELSE 0 END AS pct_male, " \
"CASE WHEN s.pop > 0 THEN s.pop_female / s.pop ELSE 0 END AS pct_female, " \
"op.deaths AS opioid_deaths, pred.pred_diabetes as pct_diabetes, " \
"age.fact_pop_0_4 as pop_0_4,age.fact_pop_5_9 as pop_5_9,age.fact_pop_10_14 as pop_10_14,age.fact_pop_15_19 as pop_15_19, "\
"age.fact_pop_20_24 as pop_20_24,age.fact_pop_25_29 as pop_25_29,age.fact_pop_30_34 as pop_30_34,age.fact_pop_35_39 as pop_35_39, " \
"age.fact_pop_40_44 as pop_40_44,age.fact_pop_45_49 as pop_45_49,age.fact_pop_50_54 as pop_50_54,age.fact_pop_55_59 as pop_55_59, " \
"age.fact_pop_60_64 as pop_60_64,age.fact_pop_65_69 as pop_65_69,age.fact_pop_70_74 as pop_70_74,age.fact_pop_75_79 as pop_75_79, " \
"age.fact_pop_80_84 as pop_80_84,age.fact_pop_85_110 as pop_85_110, " \
"ST_AsGeoJSON(g.the_geom) AS geometry " \
"FROM synth_ma.cousub_stats s " \
"JOIN synth_ma.ma_cousub_pred pred ON pred.state_fips = '25' AND pred.county = s.ct_fips AND pred.county_sub_fips = s.cs_fips AND s.cs_fips != '00000' " \
"JOIN tiger_cb14_500k.cousub g ON g.statefp = '25' AND g.countyfp = s.ct_fips AND g.cousubfp = s.cs_fips AND s.cs_fips != '00000' " \
"JOIN synth_ma.ma_cousub_age age ON age.cs_fips = s.cs_fips " \
"JOIN synth_ma.ma_opioid2 op ON op.cousubfp = s.cs_fips AND s.cs_fips != '00000' AND year = '2015'"
data = p2g.getData(con, sql)
log.debug("leaving get_cousub_all()")
return data
#
# Test stub for module access to postgis2geojson
#
import psycopg2 as pg
import postgis2geojson as p2g
con = pg.connect(host="hsi.mitre.org", port=5432, database="hsi", user="******", password="******")
cur = con.cursor()
#cur.execute("select * from sc.sc_worship")
#for record in cur:
# print record
sql = "select id, source, ST_AsGeoJSON(location) as geometry, name from sc.sc_worship"
p2g.argsd["pretty"] = True
gj = p2g.getData(con, sql)
outfile = open("tmp.geojson", "w")
outfile.write(gj)