def upload():
if 'user' not in session:
return redirect(url_for('login'))
if request.method == 'POST':
file = request.files['file']
filetype = request.form.get('filetype')
db = dbaccess.dbConn()
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
filename = str(datetime.datetime.now()).replace(' ', '').replace('-','').replace(':','').replace('.','') + '_' + filename
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
flash('File successfully uploaded')
fields = 'file_name,file_type,create_user'
values = "'{0}','{1}','{2}'".format(filename, filetype,session["user"]["userid"])
db.insert_record('upload_data.uploaded_file', fields,values)
return redirect('/upload')
else:
db=dbaccess.dbConn()
q = """select '/' ||file_id::varchar as file_id,
file_name, file_type, create_date::varchar from upload_data.uploaded_file
where
create_user = '******' and processed_date is null;
""".format(session["user"]["userid"])
d = db.get_data(q)
cp = check_process()
return render_template('upload.html', title='Upload Files', pagename='File upload', data=d, can_process=cp, username=session["user"]["userid"])
def processAddresses(username):
db = dbaccess.dbConn()
sq = """select s.street_address_id, ga.lat, ga.long, d.district_name, d.district_type_name,
d.district_id, d.district_type_id,
d.geo_table
from upload_data.street_address s
inner join
(select dp.precinct_portion_id, dp.district_id, dd.district_name, dt.district_type_id, dt.district_type_name, dt.geo_table from
upload_data.district_precinct dp
inner join upload_data.district dd on dd.district_id = dp.district_id
inner join upload_data.district_type dt on dt.district_type_id = dd.district_type_id
where dp.processed_date is null and dd.processed_date is null
) d on
d.precinct_portion_id = s.precinct_portion_id
inner join public.census_geocoded_address ga on ga.street_address_id = s.street_address_id
where s.processed_date is null and s.create_user = '******'""".format(username)
rec = db.get_data(sq, headers=False)
for aa in rec:
ad = {'lat':aa[1], 'long':aa[2],'geotable':aa[7], 'dist_name':aa[3], 'dist_type':aa[4]}
ageo = GeoValidate(**ad)
ageo.getMapped()
sqlins = """INSERT INTO public.audited_addresses(
street_address_id, district_id, district_type_id,
mapped_district_name, geocoding_source, dist_from_edge, matches, create_user)
VALUES ({0},'{1}', {2},
'{3}', '{4}', {5}, {6}, '{7}');""".format(aa[0],aa[5],aa[6], ageo.mapped_dist,'ArcGis',ageo.km_from_edge, ageo.matched, username)
db.run_query(sqlins)
#update data_upload tables
usql = "update upload_data.{0} set processed_date = now() where processed_date is null and create_user = '******'"
db.run_query(usql.format('district', username))
db.run_query(usql.format('district_precinct', username))
db.run_query(usql.format('street_address', username))
def check_process():
username = username=session["user"]["userid"]
db = dbaccess.dbConn()
q = """select distinct file_type from
upload_data.uploaded_file
where processed_date is null and create_user = '******'""".format(username)
r = db.get_data(q, headers=False)
valid = ["address","district","precinctdistrict"]
for rr in r:
if rr[0] in valid:
valid.remove(rr[0])
if len(valid) > 0:
return 0
else:
return 1
async def GeoFromDb(file_id):
db = dbaccess.dbConn()
sq = """SELECT sa.street_address_id, full_address, city, state, zipcode, latitude, longitude,
precinct_portion_id,
create_user
FROM upload_data.street_address sa
left outer join public.census_geocoded_address aa
on aa.street_address_id = sa.street_address_id
where
aa.census_geocode_id is null and
processed_date is null and file_id='{0}';""".format(file_id)
rec = db.get_data(sq, headers=False)
if len(rec) > 0:
res = await AssyncGeo(rec)
db.bulkinsert(res, "INSERT INTO public.census_geocoded_address(street_address_id, lat, long)")
def get_districts(limit):
d = db.dbConn()
s = "select * from districts.district"
if len(limit) > 0:
s += " where districtid in ("
for l in limit:
s += str(l) + ','
s = s[:-1]
s += ")"
r = d.get_data(s)
dc = u.list_to_dict(r)
w = []
for i in dc:
ww = districts(**i)
w.append(ww)
return w
def getMapped(self):
db = dbaccess.dbConn()
psql = """select t.namelsad,
st_distance(ST_Transform(ST_GeometryFromText('POINT({1} {2})', 4326)::geometry, 3857)
,ST_Transform(ST_Boundary(ST_SetSRID(geom, 4326))::geometry, 3857)) as dist
from public.{0} t
where ST_CONTAINS(ST_SetSRID(geom, 4326),
ST_GeometryFromText('POINT({1} {2})', 4326))""".format(self.table, self.long, self.lat)
rec = db.get_data(psql, headers=False)
for r in rec:
self.mapped_dist = r[0]
self.km_from_edge = float(r[1])/1000
try:
if ' ' + str(int(self.dist_name)) in self.mapped_dist:
self.matched = True
except:
if ' ' + str(self.dist_name) in self.mapped_dist:
self.matched = True
def login():
if current_user.is_authenticated:
return redirect(url_for('index'))
form = LoginForm()
if form.validate_on_submit():
flash('Login requested for user {}, remember_me={}'.format(
form.username.data, form.remember_me.data))
db = dbaccess.dbConn()
ld = db.login(form.username.data, form.password.data, form.remember_me.data)
if ld.is_authenticated == True:
login_user(ld, remember=form.remember_me.data)
session["user"] = json.loads(ld.toJSON())
next_page = url_for('index')
return redirect(next_page)
else:
flash('Login failed, please try again')
return render_template('login.html', title='Sign In', pagename='Login to EIS GIS', form=form)
def process():
if 'user' not in session:
return redirect(url_for('login'))
username = username=session["user"]["userid"]
db = dbaccess.dbConn()
q = """select distinct file_id, file_type, file_name from
upload_data.uploaded_file
where processed_date is null and create_user = '******'""".format(username)
r = db.get_data(q, headers=False)
for fid in r:
db.import_file(fid[0], app.config['UPLOAD_FOLDER'], username)
if fid[1] == 'address':
asyncio.run(pgeo.GeoFromDb(fid[0]))
pgeo.processAddresses(username)
data = db.get_data(pg_queries.QUERIES['get_process'].format(username))
return render_template('process.html', title='Process Data', pagename='Process District Data', data=data)
def delete(tablename, idcolumn, rowid):
db = dbaccess.dbConn()
q = "delete from {0} where {1} = {2}::int".format(tablename,idcolumn,rowid);
db.run_query(q)
return redirect('/upload')