def test_close(self):
_ = database_connection()
self.proj.close()
with self.assertRaises(FileExistsError):
_ = database_connection()
def delete(self):
conn = database_connection()
curr = conn.cursor()
curr.execute(
f'DELETE FROM link_types where link_type_id="{self.link_type_id}"')
conn.commit()
del self
def renumber(self, new_id: int):
"""Renumbers the node in the network
Raises a warning if another node already exists with this node_id
Args:
*new_id* (:obj:`int`): New node_id
"""
new_id = int(new_id)
if new_id == self.node_id:
warn("This is already the node number")
return
conn = database_connection()
curr = conn.cursor()
curr.execute("BEGIN;")
curr.execute("Update Nodes set node_id=? where node_id=?",
[new_id, self.node_id])
curr.execute("Update Links set a_node=? where a_node=?",
[new_id, self.node_id])
curr.execute("Update Links set b_node=? where b_node=?",
[new_id, self.node_id])
curr.execute("COMMIT;")
conn.close()
logger.info(f"Node {self.node_id} was renumbered to {new_id}")
self.__dict__["node_id"] = new_id
self.__original__["node_id"] = new_id
def save(self):
"""Saves/Updates the zone data to the database"""
if self.zone_id != self.__original__["zone_id"]:
raise ValueError("One cannot change the zone_id")
conn = database_connection()
curr = conn.cursor()
curr.execute(
f'select count(*) from zones where zone_id="{self.zone_id}"')
if curr.fetchone()[0] == 0:
data = [self.zone_id, self.geometry.wkb]
curr.execute(
"Insert into zones (zone_id, geometry) values(?, ST_Multi(GeomFromWKB(?, 4326)))",
data)
for key, value in self.__dict__.items():
if key != "zone_id" and key in self.__original__:
v_old = self.__original__.get(key, None)
if value != v_old and value is not None:
self.__original__[key] = value
if key == "geometry":
sql = "update 'zones' set geometry=ST_Multi(GeomFromWKB(?, 4326)) where zone_id=?"
curr.execute(sql, [value.wkb, self.zone_id])
else:
curr.execute(
f"update 'zones' set '{key}'=? where zone_id=?",
[value, self.zone_id])
conn.commit()
conn.close()
def __init__(self, mode_id: str) -> None:
if mode_id is None:
raise ValueError('Mode IDs cannot be None')
if len(mode_id) != 1 or mode_id not in string.ascii_letters:
raise ValueError('Mode IDs must be a single ascii character')
conn = database_connection()
curr = conn.cursor()
curr.execute('pragma table_info(modes)')
table_struct = curr.fetchall()
self.__fields = [x[1] for x in table_struct]
self.__original__ = {}
# data for the mode
curr.execute(f"select * from 'modes' where mode_id='{mode_id}'")
dt = curr.fetchone()
if dt is None:
# if the mode was not found, we return a new one
for k in self.__fields:
self.__dict__[k] = None
self.__original__[k] = None
self.__dict__['mode_id'] = mode_id
self.__original__['mode_id'] = mode_id
else:
for k, v in zip(self.__fields, dt):
self.__dict__[k] = v
self.__original__[k] = v
conn.close()
def save(self):
if self.mode_id not in self.__alowed_characters:
raise ValueError('mode_id needs to be a ascii letter')
for letter in self.mode_name:
if letter not in self.__alowed_characters:
raise ValueError('mode_name can only contain letters and "_"')
conn = database_connection()
curr = conn.cursor()
curr.execute(
f'select count(*) from modes where mode_id="{self.mode_id}"')
if curr.fetchone()[0] == 0:
raise ValueError(
"Mode does not exist in the model. You need to explicitly add it"
)
curr.execute('pragma table_info(modes)')
table_struct = [x[1] for x in curr.fetchall()]
for key, value in self.__dict__.items():
if key in table_struct and key != 'mode_id':
v_old = self.__original__.get(key, None)
if value != v_old and value is not None:
self.__original__[key] = value
curr.execute(
f"update 'modes' set '{key}'=? where mode_id='{self.mode_id}'",
[value])
conn.commit()
conn.close()
def __run_query_fetch_all(self, qry: str):
conn = database_connection()
curr = conn.cursor()
curr.execute(qry)
dt = curr.fetchall()
conn.close()
return dt
def delete(self):
"""Deletes link from database"""
conn = database_connection()
curr = conn.cursor()
curr.execute(f'DELETE FROM links where link_id="{self.link_id}"')
conn.commit()
self.__stil_exists = False
def open(self, project_path: str) -> None:
"""
Loads project from disk
Args:
*project_path* (:obj:`str`): Full path to the project data folder. If the project inside does
not exist, it will fail.
"""
if self.__other_project_still_open():
raise Exception("You already have a project open. Close that project before opening another one")
file_name = os.path.join(project_path, "project_database.sqlite")
if not os.path.isfile(file_name):
raise FileNotFoundError("Model does not exist. Check your path and try again")
self.project_base_path = project_path
self.path_to_file = file_name
self.source = self.path_to_file
os.environ[ENVIRON_VAR] = self.project_base_path
self.conn = database_connection()
self.__load_objects()
self.__set_logging_path()
logger.info(f"Opened project on {self.project_base_path}")
self.logger = logger
clean()
def save_results(self, table_name: str) -> None:
"""Saves the assignment results to results_database.sqlite
Method fails if table exists
Args:
table_name (:obj:`str`): Name of the table to hold this assignment result
"""
df = self.results()
conn = sqlite3.connect(
path.join(environ[ENVIRON_VAR], "results_database.sqlite"))
df.to_sql(table_name, conn)
conn.close()
conn = database_connection()
report = {
"convergence": str(self.assignment.convergence_report),
"setup": str(self.info())
}
data = [
table_name, "traffic assignment", self.procedure_id,
str(report), self.procedure_date, self.description
]
conn.execute(
"""Insert into results(table_name, procedure, procedure_id, procedure_report, timestamp,
description) Values(?,?,?,?,?,?)""",
data,
)
conn.commit()
conn.close()
def delete(self):
"""Removes the zone from the database"""
conn = database_connection()
curr = conn.cursor()
curr.execute(f'DELETE FROM zones where zone_id="{self.zone_id}"')
conn.commit()
self.__zoning._remove_zone(self.zone_id)
del self
def __init__(self, network):
self.network = network
self.__all_types = []
self.conn = database_connection()
self.__curr = self.conn.cursor()
self.__fields = []
if self.__has_zoning():
self.__load()
def __run_query_commit(self, qry: str, values=None) -> None:
conn = database_connection()
if values is None:
conn.execute(qry)
else:
conn.execute(qry, values)
conn.commit()
conn.close()
def _read_assignment_results(self) -> pd.DataFrame:
conn = database_connection()
results_df = pd.read_sql("SELECT * FROM 'results'", conn)
conn.close()
res = results_df.loc[results_df.table_name == self.table_name]
assert len(
res
) == 1, f"Found {len(res)} assignment result with this table name, need exactly one"
return res
def __setattr__(self, instance, value) -> None:
if instance == 'name':
value = str(value).lower()
conn = database_connection()
curr = conn.cursor()
curr.execute('Select count(*) from matrices where LOWER(name)=?', [value])
if sum(curr.fetchone()) > 0:
raise ValueError('Another matrix with this name already exists')
conn.close()
elif instance == 'file_name':
conn = database_connection()
curr = conn.cursor()
curr.execute('Select count(*) from matrices where LOWER(file_name)=?', [str(value).lower()])
if sum(curr.fetchone()) > 0:
raise ValueError('There is another matrix record for this file')
self.__dict__[instance] = value
if instance in ['file_name', 'cores']:
self.__dict__['cores'] = self.__get_cores()
def clean():
# Since we cannot decide the order of trigger execution in SQLITE, we make sure to remove any
# extraneous nodes at a few key moments (i.e. opening and closing the model)
conn = database_connection()
sqls = ['''DELETE from Nodes where is_centroid=0 and
(SELECT count(*) FROM links WHERE a_node = node_id OR b_node = node_id) = 0;''']
for sql in sqls:
conn.execute(sql)
conn.commit()
conn.close()
def delete(self):
"""Deletes this matrix record and the underlying data from disk"""
conn = database_connection()
curr = conn.cursor()
curr.execute('DELETE FROM matrices where name=?', [self.name])
conn.commit()
if isfile(join(self.fldr, self.file_name)):
try:
unlink(join(self.fldr, self.file_name))
except Exception as e:
logger.error(f'Could not remove matrix from disk: {e.args}')
conn.close()
self._exists = False
def test_connection_with_new_project(self):
temp_proj_folder = os.path.join(tempfile.gettempdir(),
uuid.uuid4().hex)
proj = Project()
proj.new(temp_proj_folder)
proj.close()
proj = Project()
proj.open(temp_proj_folder)
conn = database_connection()
cursor = conn.cursor()
cursor.execute('select count(*) from links')
self.assertEqual(cursor.fetchone()[0], 0,
"Returned more links thant it should have")
proj.close()
def save(self):
"""Saves matrix record to the project database"""
conn = database_connection()
curr = conn.cursor()
curr.execute('select count(*) from matrices where name=?', [self.name])
if curr.fetchone()[0] == 0:
data = [str(self.name), str(self.file_name), int(self.cores)]
curr.execute('Insert into matrices (name, file_name, cores) values(?,?,?)', data)
for key, value in self.__dict__.items():
if key != 'name' and key in self.__original__:
v_old = self.__original__.get(key, None)
if value != v_old and value:
self.__original__[key] = value
curr.execute(f"update matrices set '{key}'=? where name=?", [value, self.name])
conn.commit()
conn.close()
def __init__(self):
self.conn = database_connection()
self.curr = self.conn.cursor()
self.fldr = os.path.join(os.environ.get(ENVIRON_VAR), "matrices")
tl = TableLoader()
matrices_list = tl.load_table(self.curr, "matrices")
self.__fields = [x for x in tl.fields]
if matrices_list:
self.__properties = list(matrices_list[0].keys())
for lt in matrices_list:
if lt["name"] in self.__items:
if not self.__items[lt["name"]]._exists:
del self.__items[lt["name"]]
if lt["name"] not in self.__items:
if isfile(join(self.fldr, lt["file_name"])):
lt["fldr"] = self.fldr
self.__items[lt["name"].lower()] = MatrixRecord(lt)
def __create_empty_project(self):
# We create the project folder and create the base file
os.mkdir(self.project_base_path)
shutil.copyfile(spatialite_database, self.path_to_file)
self.conn = database_connection()
# Write parameters to the project folder
p = Parameters()
p.parameters["system"]["logging_directory"] = self.project_base_path
p.write_back()
_ = StartsLogging()
# Create actual tables
cursor = self.conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")
self.conn.commit()
initialize_tables(self.conn)
def save(self):
"""Saves link to database"""
conn = database_connection()
curr = conn.cursor()
if self.__new:
data, sql = self._save_new_with_geometry()
else:
data, sql = self.__save_existing_link()
if data:
curr.execute(sql, data)
conn.commit()
conn.close()
self.__new = False
for key in self.__original__.keys():
self.__original__[key] = self.__dict__[key]
def save(self):
"""Saves node to database"""
conn = database_connection()
curr = conn.cursor()
if self.node_id != self.__original__["node_id"]:
raise ValueError("One cannot change the node_id")
if self.__new:
data, sql = self._save_new_with_geometry()
else:
data, sql = self.__save_existing_node()
if data:
curr.execute(sql, data)
conn.commit()
conn.close()
self.__new = False
def assign_matrix(self, matrix: AequilibraeMatrix, result_name: str):
conn = database_connection()
sql = f"select link_id, direction, a_node, b_node, distance, 1 capacity from {DELAUNAY_TABLE}"
df = pd.read_sql(sql, conn)
centroids = np.array(np.unique(np.hstack((df.a_node.values, df.b_node.values))), int)
g = Graph()
g.mode = 'delaunay'
g.network = df
g.prepare_graph(centroids)
g.set_blocked_centroid_flows(True)
tc = TrafficClass('delaunay', g, matrix)
ta = TrafficAssignment()
ta.set_classes([tc])
ta.set_time_field('distance')
ta.set_capacity_field('capacity')
ta.set_vdf('BPR')
ta.set_vdf_parameters({"alpha": 0, "beta": 1.0})
ta.set_algorithm('all-or-nothing')
ta.execute()
report = {"setup": str(ta.info())}
data = [result_name, "Delaunay assignment", self.procedure_id, str(report), ta.procedure_date, '']
conn.execute("""Insert into results(table_name, procedure, procedure_id, procedure_report, timestamp,
description) Values(?,?,?,?,?,?)""", data)
conn.commit()
conn.close()
cols = []
for x in matrix.view_names:
cols.extend([f'{x}_ab', f'{x}_ba', f'{x}_tot'])
df = ta.results()[cols]
conn = sqlite3.connect(join(environ[ENVIRON_VAR], "results_database.sqlite"))
df.to_sql(result_name, conn)
conn.close()
def save(self):
conn = database_connection()
curr = conn.cursor()
curr.execute(
f'select count(*) from link_types where link_type_id="{self.link_type_id}"'
)
if curr.fetchone()[0] == 0:
data = [self.link_type_id, self.link_type]
curr.execute(
'Insert into link_types (link_type_id, link_type) values(?,?)',
data)
for key, value in self.__dict__.items():
if key != 'link_type_id' and key in self.__original__:
v_old = self.__original__.get(key, None)
if value != v_old and value is not None:
self.__original__[key] = value
curr.execute(
f"update 'link_types' set '{key}'=? where link_type_id='{self.link_type_id}'",
[value])
conn.commit()
conn.close()
def create_network(self, source='zones', overwrite=False):
"""Creates a delaunay network based on the existing model
Args:
*source* (:obj:`str`, `Optional`): Source of the centroids/zones. Defaults to *zones*, but can be *network*
*overwrite path* (:obj:`bool`, `Optional`): Whether we should overwrite am existing Delaunay Network.
Defaults to False
"""
if source not in ['zones', 'network']:
raise ValueError("Source must be 'zones' or 'network'")
conn = database_connection()
tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type ='table'", conn)
if DELAUNAY_TABLE in tables.name.values:
if not overwrite:
raise ValueError("Delaunay network already exist. Use the overwrite flag to re-run it")
conn.execute(f'DROP TABLE {DELAUNAY_TABLE}')
conn.execute('delete from geometry_columns where f_table_name=?', [DELAUNAY_TABLE])
conn.commit()
zone_sql = 'select zone_id node_id, X(st_centroid(geometry)) x, Y(st_centroid(geometry)) y from zones'
network_sql = 'select node_id, X(geometry) x, Y(geometry) y from nodes where is_centroid=1'
points = pd.read_sql(zone_sql, conn) if source == 'zones' else pd.read_sql(network_sql, conn)
dpoints = np.array(points[['x', 'y']])
all_edges = Delaunay(np.array(dpoints)).simplices
edges = []
for triangle in all_edges:
links = list(combinations(triangle, 2))
for i in links:
f, t = sorted(list(i))
edges.append([points.at[f, 'node_id'], points.at[t, 'node_id']])
edges = pd.DataFrame(edges)
edges.drop_duplicates(inplace=True)
edges.columns = ['a_node', 'b_node']
edges = edges.assign(direction=0, distance=0, link_id=np.arange(edges.shape[0]) + 1)
edges = edges[['link_id', 'direction', 'a_node', 'b_node', 'distance']]
edges.to_sql(DELAUNAY_TABLE, conn, index=False)
# Now we create the geometries for the delaunay triangulation
conn.execute(f"select AddGeometryColumn( '{DELAUNAY_TABLE}', 'geometry', 4326, 'LINESTRING', 'XY', 0);")
conn.execute("CREATE UNIQUE INDEX unique_link_id_delaunay on delaunay_network(link_id)")
node_geo_sql = '''INSERT INTO delaunay_network (link_id, geometry)
select lnk.link_id, MakeLine(nd.geometry, nf.geometry) from delaunay_network lnk
inner join nodes nd on lnk.a_node=nd.node_id
inner join nodes nf on lnk.b_node=nf.node_id
ON CONFLICT(link_id) DO UPDATE SET geometry=excluded.geometry'''
zone_geo_sql = '''INSERT INTO delaunay_network (link_id, geometry)
select lnk.link_id, MakeLine(st_centroid(za.geometry), st_centroid(zb.geometry)) from delaunay_network lnk
inner join zones za on lnk.a_node=za.zone_id
inner join zones zb on lnk.b_node=zb.zone_id
ON CONFLICT(link_id) DO UPDATE SET geometry=excluded.geometry;'''
sql = zone_geo_sql if source == 'zones' else node_geo_sql
conn.execute(sql)
# Updates link distance
conn.execute('update delaunay_network set distance=GeodesicLength(geometry);')
conn.commit()
conn.close()
def connector_creation(geo,
zone_id: int,
srid: int,
mode_id: str,
link_types="",
connectors=1):
if len(mode_id) > 1:
raise Exception(
"We can only add centroid connectors for one mode at a time")
conn = database_connection()
curr = conn.cursor()
curr.execute("select count(*) from nodes where node_id=?", [zone_id])
if curr.fetchone() is None:
warn("This centroid does not exist. Please create it first")
return
proj_nodes = network.Nodes()
node = proj_nodes.get(zone_id)
curr.execute(
"select count(*) from links where a_node=? and instr(modes,?) > 0",
[zone_id, mode_id])
if curr.fetchone()[0] > 0:
warn("Mode is already connected")
return
if len(link_types) > 0:
lt = f"*[{link_types}]*"
else:
curr.execute("Select link_type_id from link_types")
lt = "".join([x[0] for x in curr.fetchall()])
lt = f"*[{lt}]*"
sql = """select node_id, ST_asBinary(geometry), modes, link_types from nodes where ST_Within(geometry, GeomFromWKB(?, ?)) and
(nodes.rowid in (select rowid from SpatialIndex where f_table_name = 'nodes' and
search_frame = GeomFromWKB(?, ?)))
and link_types glob ? and instr(modes, ?)>0"""
# We expand the area by its average radius until it is 20 times
# beginning with a strict search within the zone
buffer = 0
increase = sqrt(geo.area / pi)
dt = []
while dt == [] and buffer <= increase * 10:
wkb = geo.buffer(buffer).wkb
curr.execute(sql, [wkb, srid, wkb, srid, lt, mode_id])
dt = curr.fetchall()
buffer += increase
if buffer > increase:
msg = f"Could not find node inside zone {zone_id}. Search area was expanded until we found a suitable node"
logger.warning(msg)
if dt == []:
warn(
f"FAILED! Could not find suitable nodes to connect within 5 times the diameter of zone {zone_id}."
)
return
coords = []
nodes = []
for node_id, wkb, modes, link_types in dt:
geo = shapely.wkb.loads(wkb)
coords.append([geo.x, geo.y])
nodes.append(node_id)
num_connectors = connectors
if len(nodes) == 0:
raise Exception(
"We could not find any candidate nodes that satisfied your criteria"
)
elif len(nodes) < connectors:
warn(
f"We have fewer possible nodes than required connectors for zone {zone_id}. Will connect all of them."
)
num_connectors = len(nodes)
if num_connectors == len(coords):
all_nodes = nodes
else:
features = np.array(coords)
whitened = whiten(features)
centroids, allocation = kmeans2(whitened, num_connectors)
all_nodes = set()
for i in range(num_connectors):
nds = [x for x, y in zip(nodes, list(allocation)) if y == i]
centr = centroids[i]
positions = [x for x, y in zip(whitened, allocation) if y == i]
if positions:
dist = cdist(np.array([centr]), np.array(positions)).flatten()
node_to_connect = nds[dist.argmin()]
all_nodes.add(node_to_connect)
nds = list(all_nodes)
data = [zone_id] + nds
curr.execute(
f'select b_node from links where a_node=? and b_node in ({",".join(["?"] * len(nds))})',
data)
data = [x[0] for x in curr.fetchall()]
if data:
qry = ",".join(["?"] * len(data))
dt = [mode_id, zone_id] + data
curr.execute(
f"Update links set modes=modes || ? where a_node=? and b_node in ({qry})",
dt)
nds = [x for x in nds if x not in data]
logger.warning(
f"Mode {mode_id} added to {len(data)} existing centroid connectors for zone {zone_id}"
)
conn.commit()
curr.close()
links = network.Links()
for node_to_connect in nds:
link = links.new()
node_to = proj_nodes.get(node_to_connect)
link.geometry = LineString([node.geometry, node_to.geometry])
link.modes = mode_id
link.direction = 0
link.link_type = "centroid_connector"
link.name = f"centroid connector zone {zone_id}"
link.capacity_ab = INFINITE_CAPACITY
link.capacity_ba = INFINITE_CAPACITY
link.save()
if nds:
logger.warning(
f"{len(nds)} new centroid connectors for mode {mode_id} added for centroid {zone_id}"
)
conn.commit()
def __init__(self):
self.conn = database_connection()
self.curr = self.conn.cursor()
if self.sql == "":
self.refresh_fields()
def test_database_connection(self):
# Errors when project does not exist
with self.assertRaises(FileExistsError):
_ = database_connection()
