def dbBounds(bound):
return ppygis.Polygon([
ppygis.LineString([
ppygis.Point(bound[0], bound[1], 0, srid=4336),
ppygis.Point(bound[0], bound[3], 0, srid=4336),
ppygis.Point(bound[2], bound[1], 0, srid=4336),
ppygis.Point(bound[2], bound[3], 0, srid=4336)
])
]).write_ewkb()
def savePlan(self, plan):
cursor = self.cursor()
if not 'journey_id' in plan or plan['journey_id'] is None or plan[
'journey_id'] == '':
raise BadRequestException('plan journey_id is missing')
if not 'timestamp' in plan or plan['timestamp'] is None or plan[
'timestamp'] == '':
raise BadRequestException('plan timestamp is missing')
if not 'coordinates' in plan or type(plan['coordinates']) is not list:
raise BadRequestException('plan coordinates are missing')
linestring = []
for point in plan['coordinates']:
# as per http://geojson.org/geojson-spec.html
longitude = float(point[0])
latitude = float(point[1])
if len(point) > 2:
altitude = float(point[2])
else:
altitude = 0
linestring.append(
ppygis.Point(longitude, latitude, altitude, srid=4326))
cursor.execute(
"INSERT INTO plan (geometry, journey_id, timestamp) VALUES (%s, %s, %s) RETURNING plan_id",
(ppygis.LineString(
linestring, srid=4326), plan['journey_id'], plan['timestamp']))
plan_id = cursor.fetchone()[0]
self.connection.commit()
return plan_id
def saveTraces(self, traces):
if type(traces) is not list:
traces = [traces]
cursor = self.cursor()
for trace in traces:
if not 'journey_id' in trace or trace[
'journey_id'] is None or trace['journey_id'] == '':
raise BadRequestException('trace journey_id is missing')
if not 'timestamp' in trace or trace['timestamp'] is None or trace[
'timestamp'] == '':
raise BadRequestException('trace timestamp is missing')
if not 'latitude' in trace or trace['latitude'] is None or trace[
'latitude'] == '':
raise BadRequestException('trace latitude is missing')
if not 'longitude' in trace or trace['longitude'] is None or trace[
'longitude'] == '':
raise BadRequestException('trace longitude is missing')
if not 'altitude' in trace or trace['altitude'] is None or trace[
'altitude'] == '':
trace['altitude'] = 0
cursor.execute(
"INSERT INTO trace (geometry, journey_id, timestamp) VALUES (%s, %s, %s)",
(ppygis.Point(
float(trace['longitude']),
float(trace['latitude']),
float(trace['altitude']),
srid=4326), trace['journey_id'], trace['timestamp']))
self.connection.commit()
return
def insert(self, cursor, oid, userid, osver, mkMod, iWth, iHht, ipA, fLen):
nSat = 0
if "GPS" in self.ls:
nSat = self.ls[self.ls.index('(') + 1:self.ls.index(')')]
ipoint = ppygis.Point(self.lon, self.lat)
ipoint.srid = SRID
print self.rid
cursor.execute(OBS_INSERT,\
(self.rid,nSat,self.acc,self.ls,osver,mkMod,iWth,iHht,fLen,self.vah,self.vav,self.ts,self.azi,self.pth,self.rll,self.temp,self.press,ipoint))
mk = str(self.mkX) + ' ' + str(self.mkY)
imURL = ipA + self.fn
print imURL
#PL+","+FL+","+MRK+","+RID
cursor.execute(IMG_INSERT,\
(self.pl,imURL,mk,self.rid))
#OID+","+RID
cursor.execute(REC_INSERT,\
(oid,self.rid))
#RID, USER_ID
cursor.execute(RU_INSERT,\
(self.rid,userid))
def tweet_to_insert_string(tweet, table, psql_cursor):
if tweet['coordinates'] is not None:
lat = tweet['coordinates']['coordinates'][1]
lon = tweet['coordinates']['coordinates'][0]
else:
lat = lon = 0
coordinates = ppygis.Point(lon, lat, srid=4326)
created_at = parse_date(tweet['created_at'])
hstore_user = make_hstore(tweet['user'])
hstore_place = make_hstore(tweet['place'])
hstore_entities = make_hstore(tweet['entities'])
# Sometimes there's no lang, or filter_level. Not sure why. Fix it I guess?
if 'filter_level' not in tweet:
tweet['filter_level'] = ''
if 'lang' not in tweet:
tweet['lang'] = ''
insert_str = psql_cursor.mogrify(
"INSERT INTO " + table + "(contributors, " +
"coordinates, created_at, entities, favorite_count, filter_level, " +
"lang, id, id_str, in_reply_to_screen_name, in_reply_to_status_id, " +
"in_reply_to_status_id_str, in_reply_to_user_id, in_reply_to_user_id_str, "
+
"place, retweet_count, source, twitter_user, text, user_screen_name) "
+ "VALUES (" + ','.join(['%s' for key in twitter_data_types]) + ")",
(tweet['contributors'], coordinates, created_at, hstore_entities,
tweet['favorite_count'], tweet['filter_level'], tweet['lang'],
tweet['id'], tweet['id_str'], tweet['in_reply_to_screen_name'],
tweet['in_reply_to_status_id'], tweet['in_reply_to_status_id_str'],
tweet['in_reply_to_user_id'], tweet['in_reply_to_user_id_str'],
hstore_place, tweet['retweet_count'], tweet['source'], hstore_user,
tweet['text'], tweet['user']['screen_name']))
return insert_str
def on_success(self, data):
self.log("Incoming tweet!")
#Check the tweet contains needed info
if 'text' in data:
self.log(data['text'].encode('utf-8'))
if 'id' in data:
id = data['id']
else:
self.log("No ID found in tweet data, ignoring!")
return
if 'created_at' in data:
date = self.stringToDate(data['created_at'])
else:
self.log("Created_at missing")
return
#Dump the tweet to a json string for storage in Postgres' Json type
json = simplejson.dumps(data)
#If coords are populated in the tweet, use them, otherwise ignore.
if 'coordinates' in data:
if data['coordinates'] is not None:
self.log("It's Geotagged!")
c = data['coordinates']
#Create the proper ppygis object and set srid so that it gets formated for postgres properly
point = ppygis.Point(c['coordinates'][0], c['coordinates'][1])
point.srid = self.srid
try:
self.cur.execute(self.insert_coords_sql,
(id, json, point, date))
except Exception as e:
self.log("Exception encountered wrtiting tweet")
self.log(e)
return
#Write non geotagged tweet
try:
self.cur.execute(self.insert_sql, (id, json, date))
except Exception as e:
self.log("Exception encountered wrtiting tweet")
self.log(e)
def verwerkTweets(self, data, naam):
ntweets = len(data)
sql = ''
if ntweets > 50:
ngeotagged = 0
ngeotaggedA = 0
locatedTweets = []
print ""
i = 0
for tweet in data:
i = i + 1
if tweet['coordinates'] != None:
ngeotagged = ngeotagged + 1
coordinates = tweet['coordinates']['coordinates']
lat = float(coordinates[1])
lon = float(coordinates[0])
if (lat >= ONDER and lat <= BOVEN) and (lon >= LINKS
and lon <= RECHTS):
ngeotaggedA = ngeotaggedA + 1
datumtijd = datetime.datetime.strptime(
tweet['created_at'], '%a %b %d %H:%M:%S +0000 %Y')
locatedTweets.append(
(tweet['id_str'], naam, datumtijd,
ppygis.Point(lat, lon).getquoted(), lat, lon,
datumtijd.date()))
print "Tweet %d / %d, geotagged: %d, in Amsterdam: %d" % (
i, ntweets, ngeotagged, ngeotaggedA)
if (ngeotaggedA > 5 and float(ngeotaggedA / ngeotagged) < 0.2):
for tweet in locatedTweets:
sql = "INSERT INTO scraped_tweets (tweet_id, tweet_name, tijddatum, the_geom, lat, lon, datum) VALUES ('%s', '%s', '%s', %s, %f, %f, '%s')" % tweet
self.kursor.execute(sql)
sql = "UPDATE users SET processed=true WHERE name='%s'" % naam
self.kursor.execute(sql)
else:
sql = "UPDATE users SET useless=true WHERE name='%s'" % naam
self.kursor.execute(sql)
else:
sql = "UPDATE users SET useless=true WHERE name='%s'" % naam
self.kursor.execute(sql)
self.kursor.connection.commit()
def instagram_to_insert_string(instagram, collection, psql_cursor):
if instagram['caption'] != None:
caption_from_username = instagram['caption']['from']['username']
caption_id = int(instagram['caption']['id'])
caption_text = instagram['caption']['text']
else:
caption_from_username = caption_id = caption_text = None
comments_count = instagram['comments']['count']
created_time = datetime.datetime.fromtimestamp(
int(instagram['created_time']))
filter = instagram['filter']
id = instagram['_id']
image_standard_res_url = instagram['images']['standard_resolution']['url']
likes_count = instagram['likes']['count']
link = instagram['link']
lat = float(instagram['location']['latitude'])
lon = float(instagram['location']['longitude'])
location = ppygis.Point(lon, lat, srid=4326)
tags = instagram['tags']
type = instagram['type']
instagram_user = make_hstore(instagram['user'])
user_username = instagram['user']['username']
user_id = int(instagram['user']['id'])
insert_str = psql_cursor.mogrify(
"INSERT INTO " + collection + "(caption_from_username," +
"caption_id, caption_text, comments_count, created_time, filter, id," +
"image_standard_res_url, likes_count, link, location, tags, type," +
"instagram_user, user_username, user_id) " + "VALUES (" +
','.join(['%s' for key in instagram_data_types]) + ")",
(caption_from_username, caption_id, caption_text, comments_count,
created_time, filter, id, image_standard_res_url, likes_count, link,
location, tags, type, instagram_user, user_username, user_id))
return insert_str
def insert_nodes(nn):
global nnc
for n in nn:
c.execute('insert into nodes values(%s,%s,%s,%s,%s,%s,%s,%s)',(n.id,n.version,n.user_id,n.timestamp,n.changeset_id,n.visible,n.tags,ppygis.Point(n.lon,n.lat,None,None,4326)))
cursor_nodememstore.execute("insert into nodes values (?,?,?)",(n.id,n.timestamp.strftime("%Y%m%d%H%M%S"),n.version))
conn.commit()
conn_nodememstore.commit()
nnc += len(nn)
del nn[:]
def pointsFromDb(gis_points):
result = []
for point in gis_points.points:
result.append(ppygis.Point(point.x, point.y, point.z, srid=4326))
return result
if (input_year == None and y == '2011') or (input_year != None and input_year == '2011' and y == '2011'):
# Read locations file and insert into DB. Clear DB first
pp("\n[%s] Importing to stations." % y)
db.execute("TRUNCATE TABLE stations_" + y)
committed = conn.commit()
locations_file = os.path.join(path, '../data/2011_Station_Locations.csv')
reader = csv.reader(open(locations_file, 'rU'), delimiter=',', dialect=csv.excel_tab)
row_count = 0
for row in reader:
if row_count > 0:
if row[0]:
# terminal_id character varying(16), common_name character varying(128),
# station character varying(128), lat numeric, lon numeric, install_date date,
db.execute("INSERT INTO stations_" + y + " (terminal_id, common_name, station, lat, lon, install_date, location_geom) VALUES (%s, %s, %s, %s, %s, %s, %s)" ,
(ct(row[0]), ct(row[1]), ct(row[2]), float(row[3]), float(row[4]), dt(row[5], 'date'), ppygis.Point(float(row[4]), float(row[3]), srid=4326)))
committed = conn.commit()
row_count += 1
pp("\n[%s] Commited %s rows to stations.\n" % (y, row_count))
# Read casual_subscriptions file and insert into DB. Clear DB first
"""
pp("\n[%s] Importing to casual_subscriptions." % y)
db.execute("TRUNCATE TABLE casual_subscriptions_" + y)
committed = conn.commit()
locations_file = os.path.join(path, '../data/NRMN_2011_reports/Casual_subscriptions_2011_Season.csv')
reader = csv.reader(open(locations_file, 'rU'), delimiter=',', dialect=csv.excel_tab)
def export(path):
try:
import config, json
file = open(config.get("path", "data_dir") + "/" + path, 'r')
flooddata = file.read()
geojflood = json.loads(flooddata)
fts = geojflood[FTS]
plat = -1000
plon = -1000
img = []
polyCoord = []
for feat in fts:
geo = feat[GEOM]
coord = geo[COORD]
if geo[TYPE] == POINT:
if plat == -1000:
plat = coord[0]
plon = coord[1]
timeSt = feat[PROP][TIMES]
else:
prop = feat[PROP]
marker = prop.get(MK)
if marker == None:
mkX = ''
mkY = ''
else:
mkX = marker[XVAL]
mkY = marker[YVAL]
polyline = prop.get(PL)
if polyline == None:
polyline = ''
temperature = prop.get(TPR)
if temperature == None:
temperature = 'nan'
pressure = prop.get(PRSS)
if pressure == None:
pressure = 'nan'
viewAngle = prop[VA]
compass = prop[COMPASS]
img.append(Img(prop[RIDENT],prop[FNAME],prop[TIMES],prop[ACC],viewAngle[VER],viewAngle[HOR],\
compass[AZIMUTH],compass[PITCH],compass[ROLL],prop[LS],coord[0],coord[1],mkX,mkY,polyline,temperature,pressure))
else:
for crd in coord[0]:
lat = crd[0]
lon = crd[1]
polyCoord.append(ppygis.Point(lon, lat))
fcProp = geojflood[PROP]
note = fcProp[NOTE]
oid = path[path.rindex('/') + 1:path.rindex('.')]
userid = path[0:path.index('/')]
osver = fcProp[OSV]
mkMod = fcProp[MKM]
iWth = fcProp[IWIDTH]
iHht = fcProp[IHEIGHT]
fLen = fcProp[FLENGTH]
dec = fcProp[DEC]
#Define connection string
conn_string = "host='" + HOST + "' dbname='" + DBNAME + "' user='******' password='******'"
log.debug("Connecting to database\n ->%s" % (conn_string))
# get a connection, if a connect cannot be made an exception will be raised here
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
log.debug("Connected!\n")
point = ppygis.Point(plon, plat)
point.srid = SRID
#OID+","+NT+\
#","+TSTMP+","+GEO
cursor.execute(OBSGP_INSERT,\
(oid,note,timeSt,point))
ipA = ipaddr('eth0') + config.get(
"imgurl", "iurl") + path[0:path.rindex('/') + 1]
for image in img:
image.insert(cursor, oid, userid, osver, mkMod, iWth, iHht, ipA,
fLen)
#OID+","+OPT
st = 1
for opt in dec:
cursor.execute(DEC_INSERT, (oid, opt, st))
st = st + 1
if len(polyCoord) > 0:
ls = ppygis.LineString((polyCoord))
pgon = ppygis.Polygon((ls, ))
pgon.srid = SRID
#OID+","+GEO
cursor.execute(POL_INSERT, (oid, pgon))
else:
cursor.execute(POL_INSERT, (oid, None))
conn.commit()
cursor.close()
conn.close()
except Exception as e:
import traceback
exMsg = traceback.format_exc()
log.exception("Exception: " + exMsg)
#return {"error":1 , "msg": exMsg}
return {"error": 1, "msg": str(e)}
return {"error": 0, "msg": "Operation successful"}
def buildAverageSpeedsReport(self, type='realtime'):
if type != 'baseline':
if type != 'realtime':
type = 'combined'
min_length = 250 # shorter non-slow segments will be grouped together
cursor = self.cursor()
speeds = {}
readings = {}
# Clean up
sql = "DELETE FROM report"
if type == 'realtime':
sql += " WHERE realtime=true"
elif type == 'baseline':
sql += " WHERE realtime=false"
cursor.execute(sql)
# Build average speed cache for routes
sql = "SELECT ST_GeoHash(geometry, 30), AVG(speed), COUNT(*) FROM route"
if type == 'realtime':
sql += " WHERE realtime=true"
elif type == 'baseline':
sql += " WHERE realtime=false"
sql += " GROUP BY geometry HAVING COUNT(*) > 0"
cursor.execute(sql)
for record in cursor.fetchall():
speeds[record[0]] = float(record[1])
readings[record[0]] = float(record[2])
# Group raw routes into linestring geometries
sql = "SELECT ST_AsGeoJSON((ST_Dump(ST_LineMerge(ST_Collect(route.geometry)))).geom)::json->'coordinates' FROM (SELECT geometry FROM route GROUP BY geometry HAVING COUNT(*) > 0) AS route"
cursor.execute(sql)
collection = []
for record in cursor.fetchall():
linestring = json.loads(record[0])
cursor.execute(
"select ST_Length(ST_Transform(ST_GeomFromGeoJSON(%s)::geometry, 2839))",
(json.dumps({
'type': 'LineString',
'coordinates': linestring,
'crs': {
'type': 'name',
'properties': {
'name': 'EPSG:4326'
}
}
}), ))
length = cursor.fetchone()[0]
feature = {
"coordinates": [],
"speed": 0,
"reading": 0,
"remaining-length": length,
"speeds": [],
"readings": [],
"lengths": []
}
# run through each group to detect speed category change and exclude short routes
for i, point in enumerate(linestring):
# start bulding route segments upon reaching second linestring point
if len(feature['coordinates']) > 0:
route = []
route.append(
ppygis.Point(linestring[i - 1][0],
linestring[i - 1][1],
linestring[i - 1][2],
srid=4326))
route.append(
ppygis.Point(linestring[i][0],
linestring[i][1],
linestring[i][2],
srid=4326))
route = ppygis.LineString(route, srid=4326)
cursor.execute(
"select ST_Length(ST_Transform(%s::geometry, 2839)), ST_GeoHash(%s::geometry, 30)",
(route, route))
record = cursor.fetchone()
length = record[0]
if record[1] in speeds:
speed = speeds[record[1]]
reading = readings[record[1]]
else:
print "Route cache miss", record[1]
speed = -1
reading = 0
if len(feature['speeds']):
if self.categorizeSpeed(speed) != self.categorizeSpeed(
feature['speeds'][-1]):
if (sum(feature['lengths']) >= min_length
and feature['remaining-length'] -
sum(feature['lengths']) >= min_length) or (
self.categorizeSpeed(speed) == 1):
feature['speed'] = float(sum(
feature['speeds'])) / len(
feature['speeds'])
feature['reading'] = float(
sum(feature['readings'])) / len(
feature['readings'])
collection.append([
copy.deepcopy(feature['coordinates']),
feature['speed'], feature['reading']
])
# reset
feature['remaining-length'] -= sum(
feature['lengths'])
feature['coordinates'] = [
feature['coordinates'][-1]
]
feature['speed'] = 0
feature['reading'] = 0
feature['lengths'] = []
feature['speeds'] = []
feature['readings'] = []
feature['coordinates'].append(
[point[0], point[1], point[2]])
feature['speeds'].append(speed)
feature['readings'].append(reading)
feature['lengths'].append(length)
else:
feature['coordinates'].append(
[point[0], point[1], point[2]])
feature['speed'] = float(sum(feature['speeds'])) / len(
feature['speeds'])
feature['reading'] = float(sum(feature['readings'])) / len(
feature['readings'])
collection.append([
copy.deepcopy(feature['coordinates']), feature['speed'],
feature['reading']
])
now = datetime.datetime.fromtimestamp(time.time())
for i, feature in enumerate(collection):
cursor.execute(
"INSERT INTO report (geometry, speed, reading, type, timestamp) VALUES (ST_GeomFromGeoJSON(%s), %s, %s, %s, %s)",
(json.dumps({
'type': 'LineString',
'coordinates': feature[0],
'crs': {
'type': 'name',
'properties': {
'name': 'EPSG:4326'
}
}
}), feature[1], feature[2], type, now))
collection[i][0] = json.dumps({
'type': 'LineString',
'coordinates': feature[0]
}) # just as the database would have returned it
self.connection.commit()
return collection
def send_off_sail_data_abstract(self, dataset):
if dataset.transekt_id == u'B':
current_highest_index = self.__get_latest_fahrt_id()
last_transect_end = self.__get_last_transect_end(
current_highest_index)
if last_transect_end is None:
self.dialogs['missing_transect_end_dialog'].exec_()
if self.dialogs['missing_transect_end_dialog'].delete:
self.__delete_old_transect(current_highest_index)
else:
return
fahrt_id = current_highest_index + 1
ins = self.transects.insert().values(fahrt_id=fahrt_id,
datum_b=dataset.datum,
zeit_b=dataset.zeit,
kurswoche=dataset.kurswoche,
guide=dataset.guide,
schiff=dataset.schiff,
akustik=dataset.akustik)
self.engine.execute(ins)
elif dataset.transekt_id == u'E':
current_highest_index = self.__get_latest_fahrt_id()
last_transect_end = self.__get_last_transect_end(
current_highest_index)
if last_transect_end is not None:
self.dialogs['missing_transect_beginn_dialog'].exec_()
return
else:
fahrt_id = current_highest_index
begin_transect = self.engine.execute(
select([
self.points
]).where(self.points.c.fahrt_id == fahrt_id)).fetchone()
result = self.engine.execute(
select([self.points
]).where(self.points.c.fahrt_id == fahrt_id).where(
self.points.c.transekt_id == 'KW '))
polyline = []
point = ppygis.Point(begin_transect.laenge,
begin_transect.breite,
srid=4326)
polyline.append(point)
for row in result:
point = ppygis.Point(row.laenge, row.breite, srid=4326)
polyline.append(point)
point = ppygis.Point(dataset.laenge, dataset.breite, srid=4326)
linestring = ppygis.LineString(polyline, srid=4326)
fahrt_id = current_highest_index
self.connection.execute(self.transects.update().where(
self.transects.c.fahrt_id == fahrt_id).values(
datum_e=dataset.datum,
zeit_e=dataset.zeit,
geom=linestring))
elif dataset.transekt_id == 'KW':
fahrt_id = self.__get_latest_fahrt_id()
last_transect_end = self.__get_last_transect_end(fahrt_id)
if last_transect_end is not None:
self.dialogs['invalid_course_dialog'].exec_()
return
result = self.engine.execute(select([func.max(self.points.c.punkt_id)
]))
for row in result:
punkt_id = row[0] + 1
ins = self.points.insert().values(
punkt_id=punkt_id,
fahrt_id=fahrt_id,
transekt_id=dataset.transekt_id,
sicht_id=dataset.sicht_id,
bewoelkung_id=dataset.bewoelkung_id,
windrichtung_id=dataset.windrichtung_id,
wind_id=dataset.wind_id,
meer_id=dataset.meer_id,
kurs=dataset.kurs,
kurswoche=dataset.kurswoche,
breite=dataset.breite,
laenge=dataset.laenge,
schiff=dataset.schiff,
guide=dataset.guide,
segel=dataset.segel,
akustik=dataset.akustik,
militaer=dataset.militaer,
zeit=dataset.zeit,
datum=dataset.datum,
fotos=dataset.fotos,
aufnahme=dataset.aufnahme)
self.engine.execute(ins)
self.set_main_frame_post_entry()
def importGTFSData(self):
city="Oulu"
country="Finland"
cursor = self.cursor()
try:
cursor.execute("INSERT INTO mt_city (name, country) VALUES (%s, %s)",
(city, country))
file=unicodecsv.reader(open('gtfs/agency.txt'), encoding='utf-8')
file.next()
for line in file:
if len(line) >= 4:
cursor.execute("INSERT INTO mt_agency (agency_id, city_id, name, url, timezone, language, phone) VALUES (%s, (SELECT city_id FROM mt_city WHERE name=%s AND country=%s), %s, %s, %s, %s, %s)",
(line[0],
city,
country,
line[1] if len(line) >= 1 else None,
line[2] if len(line) >= 2 else None,
line[3] if len(line) >= 3 else None,
line[4] if len(line) >= 4 else None,
line[5] if len(line) >= 5 else None,
))
file=unicodecsv.reader(open('gtfs/stops.txt'), encoding='utf-8')
file.next()
for line in file:
if len(line) >= 5:
cursor.execute("INSERT INTO mt_stop (stop_id, city_id, code, name, description, timezone, geometry) VALUES (%s, (SELECT city_id FROM mt_city WHERE name=%s AND country=%s), %s, %s, %s, %s, ST_GeomFromText('POINT(%s %s 0)', 4326))",
(line[0],
city,
country,
line[1],
line[2],
line[3],
line[10] if len(line) >= 10 else None,
float(line[5]),
float(line[4]),
))
file=unicodecsv.reader(open('gtfs/shapes.txt'), encoding='utf-8')
file.next()
shapes={}
for line in file:
if len(line) >= 3:
if line[0] not in shapes:
shapes[line[0]]={}
shapes[line[0]][int(line[3])+1]=[float(line[2]), float(line[1]), 0]
# sort according to sequence number
for shape_id in shapes:
shape=[]
for i in sorted(shapes[shape_id].iterkeys()):
shape.append(shapes[shape_id][i])
shapes[shape_id]=shape
file=unicodecsv.reader(open('gtfs/trips.txt'), encoding='utf-8')
file.next()
routes={}
for line in file:
if len(line) >= 6:
if line[0] not in routes:
routes[line[0]]={'shapes': {}}
if line[6] not in routes[line[0]]['shapes']:
if line[6] in shapes:
routes[line[0]]['shapes'][line[6]]=shapes[line[6]]
file=unicodecsv.reader(open('gtfs/routes.txt'), encoding='utf-8')
file.next()
for line in file:
if len(line) >= 5:
if line[0] not in routes:
routes[line[0]]={'shapes': {}}
routes[line[0]]['agency_id']=line[1]
routes[line[0]]['short_name']=line[2]
routes[line[0]]['full_name']=line[3]
routes[line[0]]['description']=line[4]
routes[line[0]]['type']=line[5]
routes[line[0]]['url']=line[6]
for route_id in routes:
route=routes[route_id]
linestring=[]
for shape_id in route['shapes']:
for point in route['shapes'][shape_id]:
linestring.append(ppygis.Point(point[0], point[1], point[2], srid=4326))
if len(linestring)>1:
cursor.execute("INSERT INTO mt_route (route_id, agency_id, short_name, full_name, type, description, url, geometry) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
(route_id,
route['agency_id'],
route['short_name'],
route['full_name'],
int(route['type']),
route['description'],
route['url'],
ppygis.LineString(linestring, srid=4326),
))
file=unicodecsv.reader(open('gtfs/shapes.txt'), encoding='utf-8')
file.next()
shapes={}
for line in file:
if len(line) >= 2:
if line[0] not in shapes:
shapes[line[0]]=[]
shapes[line[0]].append([line[2], line[1]])
except Exception as e:
print "Error: ", e
raise
self.connection.commit()
return
def importXpt(xptfile,verbose=False):
#verbose = True
#xptfile = '/Users/igswahwsmcevan/Altimetry/xpd/Gulkana.1995.137.2000.153.selected.xpt'
conn,cur = ConnectDb()
#FINDING DATES OF FILE AND GLACIER NAME
if verbose:print "FINDING DATES OF FILE AND GLACIER NAME"
try:
name,yr1,doy1,yr2,doy2 = re.findall('(\w+)\.(\d{4})\.(\d{3})\.(\d{4})\.(\d{3})\.selected',os.path.basename(xptfile))[0]
except:
print "Something is wrong with the filename: "+xptfile
return
yr1 = int(yr1)
yr2 = int(yr2)
doy1 = int(doy1)
doy2 = int(doy2)
date1 = doy_to_datetime(doy1,yr1)
date2 = doy_to_datetime(doy2,yr2)
#FINDING UTM ZONE
if verbose:print "FINDING UTM ZONE"
try:
zone = re.findall('zone(\d{1,2})',glob.glob(cfg.get('Section One', 'analysispath')+name+'/shp/*zone*')[0],re.IGNORECASE)[0]
except:
print "Could not identify zone for "+xptfile
return
#GLID AND LAMBID
if verbose:print "GLID AND LAMBID"
#print "select gid from glnames where name = '"+name+"';"
#print 'here1'
#sys.stdout.flush()
try:
glid = GetSqlData("select gid from glnames where name = '"+name+"';")[0]['gid']
except:
print xptfile+" does not have a glacier entry in glnames!"
return
#print "select gid from lamb where glid = "+str(glid)+" and date1 = '"+date1.isoformat()+"' and date2 = '"+date2.isoformat()+"';"
try:
lambid = GetSqlData("select gid from lamb where glid = "+str(glid)+" and date1 = '"+date1.isoformat()+"' and date2 = '"+date2.isoformat()+"';")[0]['gid']
except:
lambid = 'NULL'
print " WARNING: "+xptfile+" does not have an associated lambfile!"
return
if verbose:
t = GetSqlData('select glnames.name as name,lamb.date1 as date1,lamb.date2 as date2 from lamb inner join glnames on lamb.glid=glnames.gid where lamb.gid ='+str(lambid)+';')[0]
print t['name'],t['date1'],t['date2'],xptfile
#READING FILE INTO ARRAYS
#print 'here2'
#sys.stdout.flush()
if verbose:print "load xptfile"
time1 = time.time()
x,y,z1,z2,dz = N.loadtxt(xptfile,usecols=[0,1,2,3,4],unpack=True)
time2 = time.time()
#print temp.size
#f = open(xptfile)
#x=N.array([],dtype=N.float64)
#y=N.array([],dtype=N.float64)
#z1=N.array([],dtype=N.float64)
#z2=N.array([],dtype=N.float64)
#dz=N.array([],dtype=N.float64)
#
#for line in f:
# (x_add,y_add,z1_add,z2_add,dz_add,trash,trash,trash,trash,trash) = [float(field) for field in line.split()]
# x = N.append(x,x_add)
# y = N.append(y,y_add)
# z1 = N.append(z1,z1_add)
# z2 = N.append(z2,z2_add)
# dz = N.append(dz,dz_add)
if verbose:print 'Reprojecting'
#sys.stdout.flush()
#REFERENCE SYSTEM OF GRID
old_cs = osr.SpatialReference()
old_cs.ImportFromProj4('+proj=utm +zone=%s +ellps=WGS84 +datum=WGS84 +units=m +no_defs' % zone)
#WGS84 REFERNCE SYSTEM
wgs84_wkt = """GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]"""
new_cs = osr.SpatialReference()
new_cs.ImportFromWkt(wgs84_wkt)
#ALBERS REFERNCE SYSTEM
#albers_cs = osr.SpatialReference()
#albers_cs.ImportFromProj4("""+proj=aea +lat_1=55 +lat_2=65 +lat_0=50 +lon_0=-154 +x_0=0 +y_0=0 +ellps=GRS80 +datum=WGS84 +units=m +no_defs """)
# create a transform object to convert between coordinate systems
transformtowgs = osr.CoordinateTransformation(old_cs, new_cs)
#transformtoalbers = osr.CoordinateTransformation(old_cs, albers_cs)
coordswgs=[]
#coordsalb=[]
sys.stdout.flush()
for i in xrange(len(x)):
coordswgs.append(transformtowgs.TransformPoint(x[i],y[i]))
#coordsalb.append(transformtoalbers.TransformPoint(x[i],y[i]))
#lon,lat,trash = zip(*coords)
time3 = time.time()
#values = str(glid)+','+str(lambid)+",'"+date1.isoformat()+"','"+date2.isoformat()+"'"
#values2 = #',"+z1+'::real,'+str(z2[0])+'::real,'+str(dz[0])+'::real,POINT'+re.sub(', 0.0','',str(coords[0]))
#print "INSERT INTO xpt (glid, lambid,date1,date2) VALUES ("+values+");"
#cur.execute("INSERT INTO xpt (glid, lambid,date1,date2) VALUES ("+values+");")
if verbose:print 'Updating database'
#sys.stdout.flush()
maxgid = GetSqlData2("SELECT max(gid) FROM xpts;")['max'][0]
if type(maxgid) == NoneType:maxgid=-1
print type(maxgid)
#print "%s\t%s\t%s\t%s\t%s\t%s\n" % (i,lambid,z1[i],z2[i],dz[i],ppygis.Point(coordswgs[i][0],coordswgs[i][1]).write_ewkb())
buffer = StringIO.StringIO()
for i in xrange(len(coordswgs)):
#print "%s\t%s\t%s\t%s\t%s\t%s\n" % (i,lambid,z1[i],z2[i],dz[i],ppygis.Point(coordswgs[i][0],coordswgs[i][1]).write_ewkb())
buffer.write("%s\t%s\t%s\t%s\t%s\t%s\n" % ((i+maxgid+1),lambid,z1[i],z2[i],dz[i],ppygis.Point(coordswgs[i][0],coordswgs[i][1]).write_ewkb()))
buffer.seek(0)
cur.copy_from(buffer, 'xpts')
#for i in xrange(len(coordswgs)):
# cur.execute("INSERT INTO xpts (lambid,z1,z2,dz,geog) VALUES (%s,%s,%s,%s,ST_GeogFromText('POINT(%10.7f %10.7f)'));" % (lambid,z1[i],z2[i],dz[i],coordswgs[i][0],coordswgs[i][1]))
#cur.execute(insertxpts2)
#if i % 50000 == 0: print i
#print 'here6'
#sys.stdout.flush()
####AddGeometryColumn********
time4 = time.time()
if verbose:print "Readfile", time2-time1
if verbose:print "Reproject", time3-time2
if verbose:print "Insert to table", time4-time3
conn.commit()
cur.close()
conn.close()
def computeCentroid(points):
xs = map(lambda p: p.x, points)
ys = map(lambda p: p.y, points)
centroid = ppygis.Point(np.mean(xs), np.mean(ys), 0, srid=4326)
return centroid
def migrate(self):
from_conn = spatialite.connect('navirec.sqlite')
from_cursor = from_conn.cursor()
to_cursor = self.cursor()
# traces
print "Migrating traces"
sql = "select session_id, X(geom) as longitude, Y(geom) as latitude, altitude, timestamp from Traces"
from_cursor.execute(sql)
traces = from_cursor.fetchall()
if traces is not None and len(traces) > 0:
for trace in traces:
journey_id = trace[0]
longitude = trace[1]
latitude = trace[2]
altitude = trace[3]
timestamp = trace[4]
if altitude is None:
altitude = 0
to_cursor.execute(
"SELECT * FROM trace WHERE journey_id=%s AND timestamp=%s AND ST_Equals(geometry, %s)",
(journey_id, timestamp,
ppygis.Point(longitude, latitude, altitude, srid=4326)))
matching_traces = to_cursor.fetchall()
if len(matching_traces) == 0:
sys.stdout.write('.')
to_cursor.execute(
"INSERT INTO trace (journey_id, timestamp, geometry) VALUES (%s, %s, %s)",
(journey_id, timestamp,
ppygis.Point(longitude, latitude, altitude,
srid=4326)))
else:
sys.stdout.write('!')
# routes
print "Migrating routes"
sql = "select session_id, X(PointN(geom, 1)) as longitude1, Y(PointN(geom, 1)) as latitude1, X(PointN(geom, 2)) as longitude2, Y(PointN(geom, 2)) as latitude2, timestamp, speed, mode from Routes"
from_cursor.execute(sql)
routes = from_cursor.fetchall()
if routes is not None and len(routes) > 0:
for route in routes:
journey_id = route[0]
longitude1 = route[1]
latitude1 = route[2]
longitude2 = route[3]
latitude2 = route[4]
timestamp = route[5]
speed = route[6] / 3.6
mode = route[7]
altitude = 0
point1 = ppygis.Point(longitude1,
latitude1,
altitude,
srid=4326)
point2 = ppygis.Point(longitude2,
latitude2,
altitude,
srid=4326)
line = ppygis.LineString((point1, point2), srid=4326)
to_cursor.execute(
"SELECT * FROM route WHERE journey_id=%s AND timestamp=%s AND ST_Equals(geometry, %s)",
(journey_id, timestamp, line))
matching_routes = to_cursor.fetchall()
if len(matching_routes) == 0:
sys.stdout.write('.')
to_cursor.execute(
"INSERT INTO route (journey_id, timestamp, geometry, speed, mode) VALUES (%s, %s, %s, %s, %s)",
(journey_id, timestamp, line, speed, mode))
else:
sys.stdout.write('!')
self.connection.commit()
from_conn.close()
return
def pullPublicTransportVehicleSpeeds(self, offset=0):
limit=1
cursor = self.cursor()
url='http://92.62.36.215:8080/siri-vm-ws/NotificationProducer'
# url='http://127.0.0.1:8080/siri-vm-ws/NotificationProducer'
headers = {
"Content-type": "application/xml; charset=utf-8",
}
sql = """SELECT distinct route_id, short_name, ST_AsGeoJSON(geometry) FROM mt_route r ORDER BY route_id ASC LIMIT %s OFFSET %s"""
cursor.execute(sql, (limit, offset,))
records = cursor.fetchall()
opener = urllib2.build_opener()
for record in records:
#print "Updating line " + str(record[1])
body ="""<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:siri="http://wsdl.siri.org.uk/siri" xmlns:siri1="http://www.siri.org.uk/siri">
<soapenv:Header/>
<soapenv:Body>
<siri:GetVehicleMonitoring>
<ServiceRequestInfo>
<siri1:RequestTimestamp>2015-06-12T11:50:11.406+02:00</siri1:RequestTimestamp>
<siri1:RequestorRef>okffi</siri1:RequestorRef>
</ServiceRequestInfo>
<Request version="2.0">
<siri1:RequestTimestamp>2015-06-12T11:50:11.406+02:00</siri1:RequestTimestamp>
<siri1:LineRef>""" + record[1] + """</siri1:LineRef>
</Request>
<RequestExtension>
<!--You may enter ANY elements at this point-->
</RequestExtension>
</siri:GetVehicleMonitoring>
</soapenv:Body>
</soapenv:Envelope>"""
req = urllib2.Request(url, body, headers)
res = opener.open(req)
xml = res.read();
root = ET.fromstring(xml)
for activity in root.findall('.//{http://www.siri.org.uk/siri}MonitoredVehicleJourney[{http://www.siri.org.uk/siri}PreviousCalls][{http://www.siri.org.uk/siri}DirectionRef][{http://www.siri.org.uk/siri}LineRef]'):
vehicleRef=activity.find('.//{http://www.siri.org.uk/siri}VehicleRef')
lineRef=activity.find('.//{http://www.siri.org.uk/siri}LineRef')
directionRef=activity.find('.//{http://www.siri.org.uk/siri}DirectionRef')
journeyStartTime=activity.find('.//{http://www.siri.org.uk/siri}PreviousCalls/{http://www.siri.org.uk/siri}PreviousCall[last()]/{http://www.siri.org.uk/siri}AimedArrivalTime')
journeyID = "oulubuses.line:" + lineRef.text + ".direction:" + directionRef.text + ".vehicle:" + vehicleRef.text + ".start:" + journeyStartTime.text
measurements=[]
stops=activity.findall('.//{http://www.siri.org.uk/siri}PreviousCalls/{http://www.siri.org.uk/siri}PreviousCall[{http://www.siri.org.uk/siri}StopPointRef][{http://www.siri.org.uk/siri}ActualArrivalTime][{http://www.siri.org.uk/siri}ActualDepartureTime]')
for i, stop in enumerate(stops):
measurements.append({
'stopRef': stop.find('{http://www.siri.org.uk/siri}StopPointRef').text,
'arrivalTime': stop.find('{http://www.siri.org.uk/siri}ActualArrivalTime').text,
'departureTime': stop.find('{http://www.siri.org.uk/siri}ActualDepartureTime').text
});
if i>0:
sql = """SELECT ST_Length(ST_Transform(geometry, 2839)) as distance,
EXTRACT(EPOCH FROM (%s::timestamptz - %s::timestamptz)) AS duration,
ST_AsGeoJSON(geometry) AS geometry
FROM (SELECT
ST_Line_Substring(
r.geometry,
LEAST(ST_Line_Locate_Point(r.geometry, (SELECT geometry FROM mt_stop WHERE stop_id=%s)),
ST_Line_Locate_Point(r.geometry, (SELECT geometry FROM mt_stop WHERE stop_id=%s))),
GREATEST(ST_Line_Locate_Point(r.geometry, (SELECT geometry FROM mt_stop WHERE stop_id=%s)),
ST_Line_Locate_Point(r.geometry, (SELECT geometry FROM mt_stop WHERE stop_id=%s)))
) AS geometry
FROM mt_route r
WHERE short_name=%s) s"""
cursor.execute(sql, (measurements[i-1]['arrivalTime'],
measurements[i]['departureTime'],
measurements[i-1]['stopRef'],
measurements[i]['stopRef'],
measurements[i-1]['stopRef'],
measurements[i]['stopRef'],
record[1]
,))
for route in cursor.fetchall():
distance=route[0]
duration=route[1]
linestring=json.loads(route[2])['coordinates']
for j, point in enumerate(linestring):
if j>0:
route=[]
route.append(ppygis.Point(linestring[j-1][0], linestring[j-1][1], linestring[j-1][2], srid=4326))
route.append(ppygis.Point(linestring[j][0], linestring[j][1], linestring[j][2], srid=4326))
route=ppygis.LineString(route, srid=4326)
try:
cursor.execute("INSERT INTO route (geometry, journey_id, timestamp, speed, mode, realtime) VALUES (%s, %s, %s, %s, %s, %s)",
(route, journeyID, measurements[i]['departureTime'], 0 if duration == 0 else distance/duration, "BUS", False, ))
except db.IntegrityError as e:
self.connection.rollback()
except:
raise
else:
self.connection.commit()
#print "done updating line"
if(not len(records)):
offset=0
else:
offset+=limit
threading.Timer(5.0, lambda: MaaS().pullPublicTransportVehicleSpeeds(offset)).start()
return
def dbPoint(point):
return ppygis.Point(point.latitude,
point.longitude,
point.elevation,
srid=4326).write_ewkb()
out = commands.getstatusoutput(route_this)
# Check if route was alright, if not mark and try new one.
if ('Routed OK' in out[1] and check_route_output
== True) or check_route_output == False:
pp('[Route found] ')
# Read data from pgx file
gpx_dom = parse(out_gpx)
points = gpx_dom.getElementsByTagName('trkpt')
line_points = []
for p in points:
if p.hasAttribute('lat') and p.hasAttribute('lon'):
line_points.append(
ppygis.Point(float(p.getAttribute('lon')),
float(p.getAttribute('lat')),
srid=srid))
else:
pp('[missing lat/lon] ')
if len(line_points) == 0:
pp('[no points] ')
routes_not_committed += 1
else:
route = ppygis.LineString(line_points, srid=srid)
# Put into DB
db.execute(
"INSERT INTO routes_" + y +
" (terminal_id_start, terminal_id_end, start_geom, end_geom, route_geom) VALUES (%s, %s, %s, %s, %s)",
(v['start'], v['end'],
ppygis.Point(float(v['start_lon']),
def dbPoints(points):
return ppygis.LineString(
map(
lambda p: ppygis.Point(
p.latitude, p.longitude, p.elevation, srid=4326), points),
4326).write_ewkb()
def sweg_vs_retrieve_db_data(cur_rli_db, i_dict_boundary, i_dict_register_info,
i_str_weather_dataset, i_dict_weather_grid_info,
i_dict_performance_curves_info,
i_dict_powercurve_mix, i_dict_windspeed_corr,
i_dict_z0_info, i_dict_random_input,
dict_console_info):
'''
Retrieves simulation input data from a local database.
Copyright (c)2013 Marcus Biank, Reiner Lemoine Institut, Berlin
Keyword arguments:
cur_rli_db -- cursor on database connection
i_dict_boundary -- dictionary containing information on
a existing boundary conditions
i_dict_register_info -- dictionary containing information on the wind
installation register to be used
i_str_weather_dataset -- string specifying which dataset is to be used
i_dict_weather_grid_info -- dictionary containing information
on the weather datasets
i_dict_performance_curves_info -- dictionary containing information on
the performance curves to be used
i_dict_powercurve_mix -- dictionary containing information on source
for power curve mixing
i_dict_windspeed_corr -- dict specifying which method for windspeed
correction is to be used
i_dict_windfarm_efficiencies -- dict specifying how wind farm
efficiencies are to be calculated
i_dict_z0_info -- dictionary containing information on z0
i_dict_random_input -- dictionary containing information on the usage
of randomly generated data
dict_console_info -- dictionary containing information on the console
logging functionality
'''
#import additional modules
import shapefile
import sys
import ppygis
from Modules.sweg_vs_additional_modules import console_info as Info
from Modules.sweg_vs_additional_modules import loadmat
#declare local variables
arr_error = []
arr_register = []
arr_administration = []
arr_gridcell_id = []
arr_shp_points = []
arr_cp = []
arr_P = []
arr_P_shares = []
arr_z0 = []
dict_random_input = {}
#detect source for boundary polygon
if i_dict_boundary['source'] == "GeoDB":
#compose sql statement for additional boundary query
str_sql_statement_bound_src = ", " + \
i_dict_boundary['dict_boundary_geodb']['str_schema'] + "." + \
i_dict_boundary['dict_boundary_geodb']['str_data'] + " AS boundary "
if i_dict_boundary['dict_boundary_geodb'][
'str_geom_adminlvl'] == "Land":
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_geodb']['str_geom_column'] + \
", register.geom) AND boundary.bez_nat = '" + \
i_dict_boundary['dict_boundary_geodb']['str_geom_name'] + \
'''' AND boundary.bez_lan is NULL AND
boundary.bez_rbz is NULL AND
boundary.bez_krs is NULL AND
boundary.bez_vwg is NULL AND
boundary.bez_gem is NULL '''
elif i_dict_boundary['dict_boundary_geodb'][
'str_geom_adminlvl'] == "Bundesland":
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_geodb']['str_geom_column'] + \
", register.geom) AND boundary.bez_lan = '" + \
i_dict_boundary['dict_boundary_geodb']['str_geom_name'] + \
'''' AND boundary.bez_rbz is NULL AND
boundary.bez_krs is NULL AND
boundary.bez_vwg is NULL AND
boundary.bez_gem is NULL '''
elif i_dict_boundary['dict_boundary_geodb'][
'str_geom_adminlvl'] == "Regierungsbezirk":
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_geodb']['str_geom_column'] + \
", register.geom) AND boundary.bez_rbz = '" + \
i_dict_boundary['dict_boundary_geodb']['str_geom_name'] + \
'''' AND boundary.bez_krs is NULL AND
boundary.bez_vwg is NULL AND
boundary.bez_gem is NULL '''
elif i_dict_boundary['dict_boundary_geodb'][
'str_geom_adminlvl'] == "Kreis":
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_geodb']['str_geom_column'] + \
", register.geom) AND boundary.bez_krs = '" + \
i_dict_boundary['dict_boundary_geodb']['str_geom_name'] + \
'''' AND boundary.bez_vwg is NULL AND
boundary.bez_gem is NULL '''
elif i_dict_boundary['dict_boundary_geodb'][
'str_geom_adminlvl'] == "Verwaltungsgemeinschaft":
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_geodb']['str_geom_column'] + \
", register.geom) AND boundary.bez_vwg = '" + \
i_dict_boundary['dict_boundary_geodb']['str_geom_name'] + \
"' AND boundary.bez_gem is NULL "
elif i_dict_boundary['dict_boundary_geodb'][
'str_geom_adminlvl'] == "Gemeinde":
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_geodb']['str_geom_column'] + \
", register.geom) AND boundary.bez_gem = '" + \
i_dict_boundary['dict_boundary_geodb']['str_geom_name'] + "' "
elif i_dict_boundary['source'] == "Shapefile":
#compose sql statement for additional boundary query
#set database relevant variables to ""
str_sql_statement_bound_src = ""
try:
shp_file = shapefile.Reader(
str(i_dict_boundary['str_shp_filepath']))
Info("Shapefile successfully read.", 2, dict_console_info)
shp_shapes = shp_file.shapes()
Info("Number of shapes in shp_file: " + \
str(len(shp_shapes)), 2,dict_console_info)
if len(shp_shapes) == 1:
#define points
for point in shp_shapes[0].points:
arr_shp_points.append(
ppygis.Point(point[0], point[1], 4326))
arr_shp_points.append(
ppygis.Point(shp_shapes[0].points[0][0],
shp_shapes[0].points[0][1], 4326))
#define linestring ring
postgis_shp_linestring = [
ppygis.LineString(arr_shp_points, 4326)
]
#define polygon
postgis_shp_polygon = ppygis.Polygon(postgis_shp_linestring,
4326)
#define ewkb (extended well-known binary) of polygon
str_shpgeom = ppygis.Geometry.getquoted(postgis_shp_polygon)
str_sql_statement_bound_cond = "AND ST_CONTAINS(" + \
str_shpgeom + ", register.geom) "
#print str_sql_statement_bound_cond
else:
arr_error.append("Error: " + str(len(shp_shapes)) + \
" shapes in the provided shapefile! Only one allowed.")
except:
arr_error.append("Error: " + str(sys.exc_info()))
elif i_dict_boundary['source'] == "DBtable":
#compose sql statement for additional boundary query
str_sql_statement_bound_src = ", " + \
i_dict_boundary['dict_boundary_dbtable']['str_schema'] + "." + \
i_dict_boundary['dict_boundary_dbtable']['str_data'] + " AS boundary "
str_sql_statement_bound_cond = "AND ST_CONTAINS(boundary." + \
i_dict_boundary['dict_boundary_dbtable']['str_geom_column'] + \
", register.geom) AND boundary." + i_dict_boundary[
'dict_boundary_dbtable']['str_condition_col'] + " = '" + \
i_dict_boundary['dict_boundary_dbtable']['str_condition_val'] + "'"
else:
str_sql_statement_bound_cond = ""
if str_sql_statement_bound_cond != "":
#compose sql statement for register retrieval
str_sql_statement = '''
SELECT wp.*, adm.bez_nat, adm.bez_lan, adm.bez_krs
FROM (
SELECT register.*, grid.gridcell_id, grid.x, grid.y
FROM ''' + \
i_dict_register_info['str_schema'] + "." + \
i_dict_register_info['str_data'] + " AS register, " + \
i_dict_weather_grid_info[
i_str_weather_dataset]['str_schema'] + '.' + \
i_dict_weather_grid_info[
i_str_weather_dataset]['str_data'] + " AS grid " + \
str_sql_statement_bound_src + \
"WHERE inbetriebn IS NOT NULL AND ST_CONTAINS(grid." + \
i_dict_weather_grid_info[i_str_weather_dataset][
'str_geom_column'] + ", register.geom) " + \
str_sql_statement_bound_cond + ") AS wp, " + \
i_dict_boundary['dict_boundary_geodb']['str_schema'] + "." + \
i_dict_boundary['dict_boundary_geodb']['str_data'] + " AS adm " +\
'''WHERE ST_CONTAINS(adm.geom,wp.geom) AND
adm.bez_gem IS NOT NULL
ORDER BY wp.gridcell_id;'''
#print str_sql_statement
#retrieve register data
try:
cur_rli_db.execute(str_sql_statement)
arr_register = [row for row in cur_rli_db.fetchall()]
Info("Register successfully retrieved.", 1, dict_console_info)
Info("Number of rows in register: " + \
str(len(arr_register)), 3, dict_console_info)
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_register == []:
arr_error.append(
"Error: Selection of register data yielded no results!")
#print [x[15] for x in arr_register]
#compose sql statement for administration retrieval
str_sql_statement = '''
SELECT adm.bez_nat, adm.bez_lan, adm.bez_krs, adm.bez_gem
FROM (
SELECT register.*
FROM ''' + i_dict_register_info['str_schema'] + "." + \
i_dict_register_info['str_data'] + " AS register" + \
str_sql_statement_bound_src + \
"WHERE inbetriebn IS NOT NULL " + str_sql_statement_bound_cond + \
") AS wp, " + \
i_dict_boundary['dict_boundary_geodb']['str_schema'] + \
"." + i_dict_boundary['dict_boundary_geodb']['str_data'] + ''' AS adm
WHERE ST_CONTAINS(adm.geom,wp.geom) AND
adm.bez_gem IS NOT NULL
GROUP BY adm.bez_nat, adm.bez_lan, adm.bez_krs, adm.bez_gem;'''
#print str_sql_statement
#retrieve administration data
try:
cur_rli_db.execute(str_sql_statement)
arr_administration = [row for row in cur_rli_db.fetchall()]
Info("Administration areas successfully retrieved.", 1,
dict_console_info)
Info("Number of rows in administration areas: " + \
str(len(arr_administration)), 3, dict_console_info)
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_administration == []:
arr_error.append(
"Error: Selection of administration data yielded no results!"
)
else:
#compose sql statement for register retrieval
str_sql_statement = '''
SELECT register.*, grid.gridcell_id, grid.x, grid.y
FROM ''' + \
i_dict_register_info['str_schema'] + "." + \
i_dict_register_info['str_data'] + " AS register, " + \
i_dict_weather_grid_info[
i_str_weather_dataset]['str_schema'] + '.' + \
i_dict_weather_grid_info[
i_str_weather_dataset]['str_data'] + " AS grid " + \
str_sql_statement_bound_src + \
"WHERE inbetriebn IS NOT NULL AND ST_CONTAINS(grid." + \
i_dict_weather_grid_info[i_str_weather_dataset][
'str_geom_column'] + ", register.geom) " + \
str_sql_statement_bound_cond + \
"ORDER BY wp.gridcell_id;"
#print str_sql_statement
#retrieve register data
try:
cur_rli_db.execute(str_sql_statement)
arr_register = [row for row in cur_rli_db.fetchall()]
Info("Register successfully retrieved.", 1, dict_console_info)
Info("Number of rows in register: " + \
str(len(arr_register)), 3, dict_console_info)
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_register == []:
arr_error.append(
"Error: Selection of register data yielded no results!")
#print [x[15] for x in arr_register]
#compose sql statement for gridcell_id retrieval
str_sql_statement = '''
SELECT grid.gridcell_id, grid.x, grid.y
FROM ''' + \
i_dict_register_info['str_schema'] + "." + \
i_dict_register_info['str_data'] + " AS register, " + \
i_dict_weather_grid_info[
i_str_weather_dataset]['str_schema'] + '.' + \
i_dict_weather_grid_info[
i_str_weather_dataset]['str_data'] + " AS grid " + \
str_sql_statement_bound_src + \
"WHERE inbetriebn IS NOT NULL AND ST_CONTAINS(grid." + \
i_dict_weather_grid_info[i_str_weather_dataset][
'str_geom_column'] +", register.geom) " + \
str_sql_statement_bound_cond + \
'''GROUP BY grid.gridcell_id, grid.x, grid.y
ORDER BY grid.gridcell_id;'''
#print str_sql_statement
#retrieve array of unique gridcell_ids
try:
cur_rli_db.execute(str_sql_statement)
arr_gridcell_id = [row for row in cur_rli_db.fetchall()]
Info("Gridcell Ids successfully retrieved.", 1, dict_console_info)
Info("Number of unique gridcell_id: " + \
str(len(arr_gridcell_id)), 3, dict_console_info)
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_gridcell_id == []:
arr_error.append(
"Error: Selection of gridcell_id data yielded no results!")
#print [x[0] for x in arr_gridcell_id]
#compose sql statement for power curves retrieval
str_sql_statement = '''
SELECT rli_anlagen_id, p_nenn, "1", "2", "3", "4", "5", "6", "7",
"8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18",
"19", "20", "21", "22", "23", "24", "25"
FROM ''' + \
i_dict_performance_curves_info['power_curves']['str_schema'] + "." +\
i_dict_performance_curves_info['power_curves']['str_data'] + \
' WHERE "0" IS NOT NULL ORDER BY rli_anlagen_id'
#retrieve power curve data
try:
cur_rli_db.execute(str_sql_statement)
arr_P = [row for row in cur_rli_db.fetchall()]
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_P == []:
arr_error.append(
"Error: Selection of power curve data yielded no results!")
#retrieve turbine type specific yearly shares
if i_dict_powercurve_mix['source']['method'] == 'weighted_average':
#compose sql statement for powercurves shares retrieval
str_sql_statement = '''
SELECT *
FROM ''' + \
i_dict_powercurve_mix['source']['str_schema'] + "." +\
i_dict_powercurve_mix['source']['str_data'] + \
' ORDER BY rli_anlagen_id'
#retrieve powercurve shares data
try:
cur_rli_db.execute(str_sql_statement)
arr_P_shares = [row for row in cur_rli_db.fetchall()]
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
arr_P_shares = []
#compose sql statement for cp curves retrieval
str_sql_statement = '''
SELECT
rli_anlagen_id, p_nenn, "1", "2", "3", "4", "5", "6", "7", "8",
"9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19",
"20", "21", "22", "23", "24", "25"
FROM ''' + \
i_dict_performance_curves_info['cp_curves']['str_schema'] + \
"." + i_dict_performance_curves_info['cp_curves']['str_data'] + \
' WHERE "0" IS NOT NULL ORDER BY rli_anlagen_id'
#retrieve cp curve data
try:
cur_rli_db.execute(str_sql_statement)
arr_cp = [row for row in cur_rli_db.fetchall()]
Info("Performance curves successfully retrieved.", 1,
dict_console_info)
Info("Number of cp-curves: " + str(len(arr_cp)), 3,
dict_console_info)
Info("Number of Power-curves: " + str(len(arr_P)), 3,
dict_console_info)
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_cp == []:
arr_error.append(
"Error: Selection of cp curve data yielded no results!")
#compose sql statement for roughness length retrieval
if i_dict_windspeed_corr['height_corr'] != 'linear':
str_sql_statement = '''
SELECT
grid.gridcell_id, z0.z0
FROM (
SELECT grid.gridcell_id
FROM ''' + \
i_dict_register_info['str_schema'] + "." + \
i_dict_register_info['str_data'] + " AS register, " +\
i_dict_weather_grid_info[i_str_weather_dataset][
'str_schema'] + '.' + \
i_dict_weather_grid_info[i_str_weather_dataset][
'str_data'] + " AS grid " + str_sql_statement_bound_src + \
"WHERE ST_CONTAINS(grid." + \
i_dict_weather_grid_info[i_str_weather_dataset][
'str_geom_column'] +", register.geom) " + \
str_sql_statement_bound_cond + \
'''GROUP BY grid.gridcell_id) as grid
INNER JOIN ''' + \
i_dict_z0_info['str_schema'] + "." + i_dict_z0_info['str_data'] +\
''' as z0 ON z0.gridcell_id = grid.gridcell_id
ORDER BY grid.gridcell_id;'''
try:
cur_rli_db.execute(str_sql_statement)
arr_of_rows = [row for row in cur_rli_db.fetchall()]
grid_cell_id = [x[0] for x in arr_of_rows]
z_0 = [x[1] for x in arr_of_rows]
arr_z0 = [grid_cell_id, z_0] #array of cols
Info("Roughness lengths successfully retrieved.", 1,
dict_console_info)
except Exception as e:
arr_error.append("Error: " + e.pgerror)
else:
if arr_z0 == []:
arr_error.append('''Error:
Selection of roughness length data yielded no results!''')
#retrieve random input (technical availability)
if i_dict_random_input['bol_load'] == True:
try:
dict_random_input = loadmat(i_dict_random_input['str_load_path'])
Info("Timeseries of random input was retrieved.", 1,
dict_console_info)
except:
dict_random_input = {}
if arr_error != []:
print arr_error[0]
return (arr_error, list(arr_register), list(arr_administration),
list(arr_gridcell_id), list(arr_cp), list(arr_P),
list(arr_P_shares), list(arr_z0), dict_random_input)
