In [16]:
import sys
sys.path.append('C:\Users\Chrisi\Desktop\Studium\iv\Project\DirtyDrive')
sys.path.append('C:\Users\Chrisi\Desktop\Studium\iv\Project\DirtyDrive\DirtyDrive')
sys.path
import pandas as pd
import numpy as np
import django, os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'DirtyDrive.settings')
#settings.configure()
django.setup()
from django.contrib.gis import gdal
gdal.HAS_GDAL
from DirtyDrive import db_saver
import datetime
from DirtyDrive.models import CityDistrict, DriveNowCarType, DriveNowCar, DriveNowChargingStation, DriveNowPetrolStation
from django.db import connection
from django.db.models import Count, Avg
from django.contrib.gis.geos import Point, Polygon
In [17]:
import db_controller
c = db_controller.db_controller()
c.get_available_dates()
Out[17]:
In [18]:
# prozentual Dirt Anteil in Zeit
clean_levels = ['VERY_CLEAN', 'CLEAN', 'REGULAR', 'POOR']
#todo nochmal drüber denken
for s in clean_levels:
print(s, DriveNowCar.objects.filter(innerCleanliness=s).count()*5 /60 /350)
In [19]:
#point in polygon
car = DriveNowCar.objects.all()[0]
#p_cd.contains(car)
car
res = CityDistrict.objects.get(polygon__contains = car.point)
res.name
Out[19]:
In [11]:
# distance between points
from django.contrib.gis.db.models.functions import Distance
from django.db.models.query import QuerySet
car1 = DriveNowCar.objects.filter(id=1)
car2 = DriveNowCar.objects.filter(id=2).first()
res = car1.annotate(dist = Distance('point', car2.point))
print(res[0].dist)
#SouthTexasCity.objects.filter(point__distance_lte=(pnt, D(km=7)))
In [12]:
# generate Drives
from django.contrib.gis.db.models.functions import Distance
# alle verfügbaren autos sind vorhanden
# pro auto
# sortiert nach datum
# wenn es sich mehr als 1km bewegt hat
# endpunkt hinzufügen
# CityDistrict hinzufügen
# wenn ein auto nicht verfügbar ist ist es noch am fahren? nicht in objects dabei?
car = DriveNowCar.objects.order_by('id').first()
print car.name
bytime = DriveNowCar.objects.filter(carId = car.carId).order_by('datetime')
last_car = None
for c in bytime:
if(last_car != None):
timeDiff = c.datetime - last_car.datetime
dist = bytime.filter(datetime =c.datetime).annotate(dist= Distance('point', last_car.point))
dist_km = dist.first().dist.km
#timeDiff > datetime.timedelta(minutes = 19, seconds = 0) and
if(dist_km > 0):
print(c.datetime)
print(c.datetime - last_car.datetime)
print('%f km' % dist.first().dist.km)
#print(last_car.fuelLevel - c.fuelLevel)
print('')
last_car = c
In [2]:
CityDistrict.objects.all()[1]
Out[2]:
In [ ]:
car = DriveNowCar.objects.all()
#p_cd.contains(car)
clean_levels = ['VERY_CLEAN', 'CLEAN', 'REGULAR', 'POOR']
#todo nochmal drüber denken
DriveNowCar.objects.filter(innerCleanliness=clean_levels[3])
#res = CityDistrict.objects.get(polygon__contains = car.point)
#res.name
res = []
#
for district in CityDistrict.objects.all():
for car in DriveNowCar.objects.all():
if(district.polygon.contains(car.point)):
res.append((district,car))
In [ ]:
In [15]:
district = CityDistrict.objects.all()[1]
res = {}
for car in DriveNowCar.objects.all():
if(district.polygon.contains(car.point)):
res[district] = car
res
#DriveNowCar.objects.annotate(district.polygon.contains(point) )
Out[15]:
In [20]:
CityDistrict.objects.all()
Out[20]:
In [ ]:
In [72]:
# 2 geojson
from geojson import FeatureCollection, Feature
features = []
for cd in CityDistrict.objects.order_by("id").raw('SELECT *, ST_AsGeoJSON(polygon) FROM public."DirtyDrive_citydistrict"'):
p = geojson.loads(cd.st_asgeojson)
f = Feature(geometry=p, properties={"id": cd.id, "name": cd.name})
features.append(f)
fc = FeatureCollection(features)
dump = geojson.dumps(fc, sort_keys=False)
print("falid: ", geojson.is_valid(geojson.loads(dump))['valid'])
with open("muenchen.json", "w") as text_file:
text_file.write(dump)
In [ ]:
In [ ]:
In [185]:
# % poor
def dictfetchall(cursor):
"Return all rows from a cursor as a dict"
columns = [col[0] for col in cursor.description]
return [
dict(zip(columns, row))
for row in cursor.fetchall()
]
from django.db import connection
#count cars by citydistrict
with connection.cursor() as cursor:
cursor.execute(
'SELECT dis.id, dis.name, count(car.id) As counter \
FROM public."DirtyDrive_citydistrict" as dis, public."DirtyDrive_drivenowcar" as car \
WHERE ST_Contains(dis.polygon, car.point) \
GROUP BY dis.id;'
)
#res = cursor.fetchall()
df_citydistrict = pd.DataFrame(dictfetchall(cursor))
# count all dirty by district
with connection.cursor() as cursor:
cursor.execute(
'SELECT dis.id, dis.name, count(car.id) As count_poor \
FROM public."DirtyDrive_citydistrict" as dis, "DirtyDrive_drivenowcar" as car \
WHERE ST_Contains(dis.polygon, car.point) AND car."innerCleanliness"=\'POOR\'\
GROUP BY dis.id;'
)
#res = cursor.fetchall()
df_poor = pd.DataFrame(dictfetchall(cursor))
df_citydistrict
Out[185]:
In [186]:
with connection.cursor() as cursor:
cursor.execute(
'SELECT dis.id, dis.name, car."innerCleanliness" as clean, count(car.id) As count \
FROM public."DirtyDrive_citydistrict" as dis, "DirtyDrive_drivenowcar" as car \
WHERE ST_Contains(dis.polygon, car.point) \
GROUP BY dis.id , car."innerCleanliness";'
)
#res = cursor.fetchall()
df_clean_group = pd.DataFrame(dictfetchall(cursor))
df_clean_group
Out[186]:
In [187]:
df = pd.merge(df_clean_group, df_citydistrict[['id', 'counter']], on='id')
df['relative'] = df['count']/df['counter']
df[df['clean']=='POOR']
df_to_save = df.sort_values(['id'], ascending=False)
df_to_save
Out[187]:
In [205]:
a = df_to_save[df_to_save['id']== 5]
a.iloc[0]['counter']
Out[205]:
In [208]:
# clean 2 geojson
from geojson import FeatureCollection, Feature
features = []
for cd in CityDistrict.objects.order_by("id").raw('SELECT *, ST_AsGeoJSON(polygon) FROM public."DirtyDrive_citydistrict"'):
p = geojson.loads(cd.st_asgeojson)
the_id = cd.id
df = df_to_save[df_to_save['id'] == the_id]
counter = int(df.iloc[0]['counter'])
properties={"id": cd.id, "name": cd.name, 'counter':counter}
for clean_level in ['VERY_CLEAN', 'CLEAN', 'REGULAR', 'POOR']:
a = df[df['clean'] == clean_level]
if len(a) > 0:
properties[clean_level] = int(a['count'])
else:
properties[clean_level] = 0
print(properties)
f = Feature(geometry=p, properties=properties)
features.append(f)
fc = FeatureCollection(features)
dump = geojson.dumps(fc, sort_keys=False)
print("falid: ", geojson.is_valid(geojson.loads(dump))['valid'])
with open("muenchen.geo.json", "w") as text_file:
text_file.write(dump)