In [2]:

    

import json
import geojson
import psycopg2 as pg

cnx = pg.connect('dbname=cityview user=cityview password=testthis host=localhost')
crs = cnx.cursor()


    

In [3]:

    

query = """select count(*), v."Meter", st_asgeojson(m.wkb_geometry) meter_point
from parking_violations v join meter m on v."Meter"=m.meter_id
group by v."Meter",meter_point
order by count desc;
"""
crs.execute(query)
results = crs.fetchall()


    

In [8]:

    

meters = [ geojson.Feature(properties={'count':r[0],'meter_id': r[1]},geometry=json.loads(r[2])) for r in results ]
output = geojson.FeatureCollection(meters)


    

In [9]:

    

fh = open('/home/larry/code/cityview/web/html/data/parking_meters.json','w')
fh.write(geojson.dumps(output))
fh.close()