import pandas as pd
import folium
from folium.plugins import HeatMap
import psycopg2
import geopandas as gpd
from unidecode import unidecode

db = psycopg2.connect(user = "postgres", password = "psql", host = "localhost", database = "postgres")
cursor = db.cursor()
cursor.execute("select st_y(st_centroid(st_collect(geom))),st_x(st_centroid(st_collect(geom)))"\
  ",concelho from cont_aad_caop2017 group by 3 order by 3;")
results = cursor.fetchall()
lat = []
log = []
conc = []
amount = []
for row in results:
    lat.append(row[0])
    log.append(row[1])
    conc.append(row[2])

for i in conc:
    cursor.execute("select count(final_point) from cont_aad_caop2017,"\
      "taxi_services where st_contains(geom,final_point) and concelho ilike '"+i+"';")
    results_1 = cursor.fetchone()
    amount.append(results_1[0])

for_map = pd.DataFrame()

for_map['concelho'] = conc
for_map['latitude'] = lat
for_map['longitude'] = log
for_map['amount'] = amount

max_amount = float(for_map['amount'].max())

hmap = folium.Map(location=[38.6817,-7.996], zoom_start=7)

hm_wide = HeatMap( list(zip(for_map.latitude.values, for_map.longitude.values, for_map.amount.values)),
                   min_opacity=0.2,
                   max_val=max_amount,
                   radius=17, blur=15,
                   max_zoom=1,
                 )

hmap.add_child(hm_wide)
hmap.save('index.html')