def get_ratings_db_fetch(self, city):
result = connection.execute("SELECT travel.geoinfos.place_id, ta_reviews FROM travel.geoinfos " + \
"INNER JOIN travel.ratings " + \
"ON travel.geoinfos.place_id = travel.ratings.place_id " + \
"WHERE travel.geoinfos.city = '{}'".format(city))
df = pd.DataFrame(result.fetchall())
df.columns = result.keys()
return df
def get_types_db_fetch(self, city):
result = connection.execute("SELECT travel.geoinfos.place_id, name, types FROM travel.geoinfos " + \
"INNER JOIN travel.types " + \
"ON travel.geoinfos.place_id = travel.types.place_id " + \
"WHERE travel.geoinfos.city = '{}'".format(city))
df = pd.DataFrame(result.fetchall())
df.columns = result.keys()
df = df.groupby("place_id")['types'].apply(list)
return pd.Series.to_frame(df)
def get_user_type(self, user_id):
result = connection.execute("SELECT user_id, travel.types.place_id, types FROM travel.types " + \
"INNER JOIN travel.users " + \
"ON travel.types.place_id = travel.users.place_id " + \
"WHERE travel.users.user_id = '{}'".format(user_id))
df = pd.DataFrame(result.fetchall())
df.columns = result.keys()
result_list = df.types.tolist()
return result_list
def get_user_db_fetch(self, user_id):
result = connection.execute("SELECT * FROM travel.users WHERE travel.users.user_id = '{}'".format(user_id))
df = pd.DataFrame(result.fetchall())
df.columns = result.keys()
return df
def get_geoinfo_db_fetch(self, city):
result = connection.execute("SELECT * FROM travel.geoinfos WHERE travel.geoinfos.city = '{}'".format(city))
df = pd.DataFrame(result.fetchall())
df.columns = result.keys()
return df.ix[:, ['PLACE_ID', 'NAME', 'LNG', 'LAT']]
def wings(cls, name, face_id, universe_id=None, srid=None):
warnings.warn("Wings have not been fully tested")
# logging.debug("starting wings")
# TODO: get universe / srid from metadata if not given
connection = ConnectionFactory.connection(True)
assert universe_id is not None
assert srid is not None
topo_map = TopoMap(universe_id=universe_id, srid=srid)
fixed = topo_map.add_face(-99, attrs={
'fixed': True,
})
if face_id != universe_id:
sql = """SELECT
face_id::int,
mbr_geometry::geometry
FROM
{0}_face
WHERE face_id = {1}
""".format(name, face_id)
face_id, mbr_geometry, = connection.record(sql)
# mbr_geometry = geom_from_binary(mbr_wkb)
topo_map.add_face(face_id, attrs={
'fixed': False,
})
#edges
sql = """SELECT
edge_id::int,
start_node_id::int,
end_node_id::int,
left_face_id::int,
right_face_id::int,
geometry::geometry
FROM
{0}_edge
WHERE
(left_face_id = %s or right_face_id = %s)
AND
geometry && %s""".format(name)
params = (face_id, face_id, mbr_geometry)
else:
sql = """SELECT
edge_id::int,
start_node_id::int,
end_node_id::int,
left_face_id::int,
right_face_id::int,
geometry::geometry
FROM
{0}_edge
WHERE
(left_face_id = %s or right_face_id = %s)
""".format(name)
params = (
face_id,
face_id,
)
for edge_id, \
start_node_id, end_node_id, \
left_face_id, right_face_id, \
geometry, in connection.recordset(sql, params):
# we set the face ptr to universe
if left_face_id != face_id:
left_face_id = fixed.id
if right_face_id != face_id:
right_face_id = fixed.id
topo_map.add_edge(edge_id, start_node_id, end_node_id,
left_face_id, right_face_id, geometry)
# logging.debug("edge: {0} {1}".format(edge_id, geometry))
LoopFactory.find_loops(topo_map)
current_face = topo_map.faces[face_id]
# for edge in topo_map.half_edges.itervalues():
# if edge.face is current_face:
# print "...",
# print edge, edge.anchor is None, edge.next.id, edge.prev.id
logging.info(current_face)
for loop in current_face.loops:
# print loop
if loop.start.left_face is current_face:
loop_sign = 1
else:
loop_sign = -1
# print ""
# print "loop",
loop_id = loop_sign * loop.start.id
#sql = 'UPDATE temp__{0}_edge SET {1} = %s WHERE edge_id = %s'
sqll = 'INSERT INTO {0}_edge_left (edge_id, next, prev) VALUES (%s, %s, %s)'
sqlr = 'INSERT INTO {0}_edge_right (edge_id, next, prev) VALUES (%s, %s, %s)'
# TODO: next/prev and ccw/cw are mixed up here
for edge in loop.half_edges:
if edge.left_face is edge.face:
# lccw / next left
lccw = edge.__next__
if lccw.left_face is edge.face:
lccw_sign = +1
else:
lccw_sign = -1
# lcw / prev left
lcw = edge.prev
if lcw.left_face is edge.face:
lcw_sign = +1
else:
lcw_sign = -1
connection.execute(sqll.format(name),
parameters=(
edge.id,
lcw_sign * lcw.id,
lccw_sign * lccw.id,
))
if edge.right_face is edge.face:
# rccw / next right
rccw = edge.__next__
if rccw.right_face is edge.face:
rccw_sign = -1
else:
rccw_sign = +1
# rcw / prev right
rcw = edge.prev
if rcw.right_face is edge.face:
rcw_sign = -1
else:
rcw_sign = +1
connection.execute(sqlr.format(name),
parameters=(
edge.id,
rcw_sign * rcw.id,
rccw_sign * rccw.id,
))
def clipped_topo_map_qgis(cls,
name,
rectangular,
imp,
universe_id=None,
srid=None):
"""
Create topomap for clipped viewport of QGIS
1) Create function for retrieving correct left and right pointer of edges
2) get proper faces and edges from db
3) forms loops with clipped polygons
"""
from simplegeom.geometry import Envelope
from . import loopfactory
connection = ConnectionFactory.connection(True)
universe_id = universe_id
srid = srid
name = name
# TODO: get universe / srid from metadata if not given
assert universe_id is not None
assert srid is not None
# also add border face as item
border_face_id = -1 #FIXME: what is this border face? Is universe not sufficient?
topo_map = TopoMap(universe_id=universe_id, srid=srid)
topo_map.add_face(border_face_id, attrs={
'clipped': True
}).unbounded = True
# FIXME: this should not be here, but constructed by the caller!
# then we do not have a dependency here on importing simplegeom
bbox = Envelope(rectangular[0], rectangular[1], rectangular[2],
rectangular[3])
function = """
CREATE OR REPLACE FUNCTION translate_face(integer, numeric)
RETURNS integer
LANGUAGE sql
AS '
with recursive walk_hierarchy(id, parentid, il, ih) as
(
select face_id, parent_face_id, imp_low, imp_high from {0}_tgap_face_hierarchy where face_id = $1
UNION ALL
select fh.face_id, fh.parent_face_id, fh.imp_low, fh.imp_high from {0}_tgap_face_hierarchy fh,
walk_hierarchy w
where w.parentid = fh.face_id and w.ih <= $2
)
select id from walk_hierarchy where il <= $2 and ih > $2;
' IMMUTABLE;
""".format(name)
connection.execute(function)
# faces
sql = """SELECT
face_id::int,
mbr_geometry,
feature_class::int
FROM
{0}_tgap_face
WHERE
mbr_geometry && '{1}'::geometry AND imp_low <= {2} AND imp_high > {2}
""".format(name, dumps(bbox.polygon),
imp) # as_hexewkb(bbox, srid))
# print sql
for face_id, mbr, feature_class, in connection.irecordset(sql):
topo_map.add_face(
face_id,
attrs={
'feature_class':
feature_class,
'clipped':
False if bbox.contains_properly(mbr.envelope) else True
})
#edges
sql = """SELECT
edge_id::int,
start_node_id::int, end_node_id::int,
left_face_id_low::int,
translate_face(left_face_id_low, {2})::int,
right_face_id_low::int,
translate_face(right_face_id_low, {2})::int,
geometry::geometry
FROM
{0}_tgap_edge
WHERE
geometry && '{1}'::geometry AND imp_low <= {2} AND imp_high > {2}
""".format(name, dumps(bbox.polygon), imp)
ec = EdgeClipper(bbox=bbox, border_face_id=-1)
for edge_id, \
start_node_id, end_node_id, \
old_left_face, left_face_id, \
old_right_face, right_face_id, \
geometry, in connection.irecordset(sql):
if old_left_face == universe_id:
left_face_id = universe_id
if old_right_face == universe_id:
right_face_id = universe_id
# print "edge", edge_id, old_left_face, left_face_id,old_right_face, right_face_id
if bbox.contains_properly(geometry.envelope):
topo_map.add_edge(edge_id, start_node_id, end_node_id,
left_face_id, right_face_id, geometry)
else:
ec.clip_edge(edge_id, start_node_id, end_node_id, left_face_id,
right_face_id, geometry)
for item in ec.border_segments:
topo_map.add_edge(*item)
for item in ec.clipped:
topo_map.add_edge(*item)
loopfactory.find_clipped_loops(topo_map)
return topo_map