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