def __init__(self, host, user, dbname, password): #Open-Elevation self.openElevation = GDALTileInterface( 'data/', 'data/summary.json') #por agora os parametros vai ser os mesmos self.openElevation.create_summary_json() #resto do processo de inicialização self.host = host self.user = user self.dbname = dbname self.password = password try: self.conn = psycopg2.connect("dbname=" + self.dbname + " user="******" host=" + self.host + " password="******"") self.cur = self.conn.cursor() except Exception as e: print(e) print("I am unable to connect to the database") sys.exit(0)
from bottle import route, run, request, response, hook from gdal_interfaces import GDALTileInterface class InternalException(ValueError): """ Utility exception class to handle errors internally and return error codes to the client """ pass """ Initialize a global interface. This can grow quite large, because it has a cache. """ interface = GDALTileInterface('data/', 'data/summary.json') interface.create_summary_json() def get_elevation(lat, lng): """ Get the elevation at point (lat,lng) using the currently opened interface :param lat: :param lng: :return: """ try: elevation = interface.lookup(lat, lng) except: return { 'latitude': lat,
HOST = parser.get('server', 'host') PORT = parser.getint('server', 'port') NUM_WORKERS = parser.getint('server', 'workers') DATA_FOLDER = parser.get('server', 'data-folder') OPEN_INTERFACES_SIZE = parser.getint('server', 'open-interfaces-size') URL_ENDPOINT = parser.get('server', 'endpoint') ALWAYS_REBUILD_SUMMARY = parser.getboolean('server', 'always-rebuild-summary') CERTS_FOLDER = parser.get('server', 'certs-folder') CERT_FILE = '%s/cert.crt' % CERTS_FOLDER KEY_FILE = '%s/cert.key' % CERTS_FOLDER """ Initialize a global interface. This can grow quite large, because it has a cache. """ interface = GDALTileInterface(DATA_FOLDER, '%s/summary.json' % DATA_FOLDER, OPEN_INTERFACES_SIZE) if interface.has_summary_json() and not ALWAYS_REBUILD_SUMMARY: print('Re-using existing summary JSON') interface.read_summary_json() else: print('Creating summary JSON ...') interface.create_summary_json() def get_elevation(lat, lng): """ Get the elevation at point (lat,lng) using the currently opened interface :param lat: :param lng: :return: """
class init(): conn = '' cur = '' host = '' user = '' dbname = '' password = '' openElevation = '' def __init__(self, host, user, dbname, password): #Open-Elevation self.openElevation = GDALTileInterface( 'data/', 'data/summary.json') #por agora os parametros vai ser os mesmos self.openElevation.create_summary_json() #resto do processo de inicialização self.host = host self.user = user self.dbname = dbname self.password = password try: self.conn = psycopg2.connect("dbname=" + self.dbname + " user="******" host=" + self.host + " password="******"") self.cur = self.conn.cursor() except Exception as e: print(e) print("I am unable to connect to the database") sys.exit(0) def init(self): """ Function that checks if there is all the conditions to initialize the API. Includes: - Verification of the required database extensions. - The raster check with portugal heights. - The verification of the existence of the tables of pois, vertices and edges. :return: """ # Verificar se a base de dados tem as extensões postgis e pgrounting self.cur.execute("SELECT * FROM pg_extension where extname='postgis';") checkPostgis = self.cur.fetchall() if len(checkPostgis) is 0: print( "The database need the extention Postgis, https://postgis.net/install/" ) sys.exit(0) self.cur.execute( "SELECT * FROM pg_extension where extname='pgrouting';") checkPgRouting = self.cur.fetchall() if len(checkPgRouting) is 0: print( "The database need the extention pgRouting, https://pgrouting.org" ) sys.exit(0) #Open-elevation numberRasterFiles = [ fn for fn in os.listdir('./data/') if fn.endswith('.tif') ] if len(numberRasterFiles) == 0: print("Raster was not found.") sys.exit(0) elif len(numberRasterFiles) == 1: print("Processing raster file.") os.system( "./scripts/create-tiles.sh ./data/mdt_aster_pt_D73_GeoTIFF.tif 10 10" ) #dividir o ficheiro em 5 por 5 else: print("Raster file was found and processed") #Verificar se as tabelas rede_viaria_bv e rede_viaria_bv_vertex existem self.checkTable('rede_viaria_bv_vertex', 1, 4) self.checkTable('rede_viaria_bv', 2, 4) #Criar tabela dos Pois self.checkTable('pois', 2, 0) #Criar a função atob if os.name == 'posix': # para linux os.system("PGPASSWORD="******" psql -f ./sql/atob_km_tsp.sql -h " + str(self.host) + " -U " + str(self.user) + " " + str(self.dbname) + " > /dev/null") os.system("PGPASSWORD="******" psql -f ./sql/solo_km_tsp.sql -h " + str(self.host) + " -U " + str(self.user) + " " + str(self.dbname) + " > /dev/null") #os.system("PGPASSWORD="******" psql -f ./sql/solo_km_tsp_nopenalty.sql -h " + str(self.host) + " -U " + str(self.user) + " " + str(self.dbname) + " > /dev/null") self.checkTable('utilizador', 2, 1) self.checkTable('unwanted_poi', 2, 3) self.checkTable('road_blocked', 2, 2) def checkTable(self, tablename, option, pois): """ To check if the necessary tables exist, if they do not exist this method will create them. :param tablename: Table name. :param option: To be used as a parameter for the executeCMG function that is invoked :param pois: To separate the check between the POIS table and the graph tables. :return: """ #TODO: verificar se o ficheiro existe #self.cur.execute("SELECT 1 FROM pg_class where relname='" + tablename + "';") # rede_viaria_bv_vertex self.cur.execute("SELECT 1 FROM pg_class where relname=%s", (tablename, )) rows = self.cur.fetchall() if len(rows) is 0: print("A criar a tabela " + tablename) if pois is 0: query = """ CREATE TABLE pois ( id SERIAL PRIMARY KEY, userid INTEGER, nome varchar(80), tipo varchar(80), latitude varchar(80) NOT NULL, longitude varchar(80)NOT NULL, cv_lat varchar(80) NOT NULL, cv_long varchar(80) NOT NULL, selecionado boolean ); """ self.cur.execute(query) self.conn.commit() self.cur.execute( "SELECT AddGeometryColumn('pois','cv_geom',4326,'POINT',2);" ) self.conn.commit() elif pois is 1: query1 = """CREATE TABLE utilizador ( id SERIAL PRIMARY KEY, username character varying(80), portuguesWalkway_Concrete INTEGER, tarmac INTEGER, cicleway INTEGER, dirt INTEGER, woodenWay_metalWay INTEGER, sidewalk_width INTEGER, allowcars BOOLEAN, allowbikes BOOLEAN, helpMovement BOOLEAN, stairs BOOLEAN, declivity_up integer, declivity_down integer, coverage_shape BOOLEAN, coverage_rainProtected BOOLEAN, password_hash character varying(128) );""" self.cur.execute(query1) self.conn.commit() #self.cur.execute("INSERT INTO utilizador(nome) VALUES ('nome teste')") #self.conn.commit() elif pois is 2: query2 = """ CREATE TABLE road_blocked ( id SERIAL PRIMARY KEY, ref_userID integer , ref_wayID integer , ref_unwantedPoi integer, FOREIGN KEY (ref_userID) REFERENCES utilizador(id), FOREIGN KEY (ref_wayID) REFERENCES rede_viaria_bv(id), FOREIGN KEY (ref_unwantedPoi) REFERENCES unwanted_poi(id) ); """ self.cur.execute(query2) self.conn.commit() elif pois is 3: query3 = """ CREATE TABLE unwanted_poi ( id SERIAL PRIMARY KEY, ref_userID integer , ref_pointID integer , FOREIGN KEY (ref_userID) REFERENCES utilizador(id), FOREIGN KEY (ref_pointID) REFERENCES rede_viaria_bv_vertex(id) ); """ self.cur.execute(query3) self.conn.commit() else: self.executeCMD('' + tablename + '.sql', option) else: print("A tabela " + tablename + " ja existe.") def executeCMD(self, sqlFile, option): """ Function to execute the sql files that are in the sql folder of the API. :param sqlFile: The name of the sql file to execute. :param option: To separate whether the file is for the edges and vertices. :return: """ fo = open('./sql/' + sqlFile, 'r') sqlFile = fo.read() sqlCommands = sqlFile.split(';') fn = open('./sql/tmp.sql', 'w') for lines in sqlCommands: fn.write(lines + ";") if option == 1: print("Campos extra para a rede_viaria_bv_vertex") fn.write( 'ALTER TABLE rede_viaria_bv_vertex ADD COLUMN altura INTEGER;') fn.write( 'ALTER TABLE rede_viaria_bv_vertex ADD COLUMN geom_distMeters geometry(Point,3857);' ) fn.write( 'UPDATE rede_viaria_bv_vertex SET geom_distmeters = ST_Transform(geom_vertex,3857);' ) else: print("Campos extra para a rede_viaria_bv") fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN largura_passeio INTEGER;' ) fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN inclinacao INTEGER;') fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN protecrain BOOLEAN;') fn.write('ALTER TABLE rede_viaria_bv ADD COLUMN shade BOOLEAN;') fn.write('ALTER TABLE rede_viaria_bv ADD COLUMN stairs BOOLEAN;') fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN helpmovement BOOLEAN;') fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN inundation BOOLEAN;') fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN maintenance BOOLEAN;') fn.write('ALTER TABLE rede_viaria_bv ADD COLUMN uneven BOOLEAN;') fn.write( 'ALTER TABLE rede_viaria_bv ADD COLUMN construction BOOLEAN;') fn.write('ALTER TABLE rede_viaria_bv ADD COLUMN wifi BOOLEAN;') fo.close() fn.close() if os.name == 'posix': # para linux os.system("PGPASSWORD="******" psql -f ./sql/tmp.sql -h " + str(self.host) + " -U " + str(self.user) + " " + str(self.dbname) + " > /dev/null") os.remove('./sql/tmp.sql') def dataPopulation(self): """ This method has the functionality of entering the necessary data in the database. :return: """ print("Filling the data:") # veririficar se existe alguma linha cuja altura nao é nula self.cur.execute( "SELECT id, geom_vertex, altura FROM rede_viaria_bv_vertex where altura is null" ) rowsSemAltura = self.cur.fetchall() if len(rowsSemAltura) is not 0: print("+ Entering height data (may take a few minutes)") self.cur.execute( "SELECT id, geom_vertex, altura FROM rede_viaria_bv_vertex order by id asc" ) rows = self.cur.fetchall() for pontos in rows: if pontos[2] != 'None': #self.cur.execute("SELECT ST_AsText('" + pontos[1] + "');") self.cur.execute("SELECT ST_AsText(%s);", (str(pontos[1]), )) row1 = str(self.cur.fetchone()) result1 = re.search('\(\'POINT\((.*)\)\',\)', row1) aux = str(result1.groups(1)).replace("(", "").replace( ")", "").replace(",", "").replace("'", "") coordenadas = aux.split(" ") r = self.get_elevation(float(coordenadas[1]), float(coordenadas[0])) #r = self.get_elevation(40.321867,-7.612967) #serra da estrela radar - altura a espera 1993 altura = r['elevation'] #self.cur.execute("UPDATE rede_viaria_bv_vertex SET altura=" + str(altura) + " WHERE id=" + str(pontos[0]) + ";") self.cur.execute( "UPDATE rede_viaria_bv_vertex SET altura=%s WHERE id=%s;", (str(altura), str(pontos[0]))) self.conn.commit() if pontos[0] % 1000 == 0: print("Number of points inserted :" + str(pontos[0])) else: print("+ There is height data.") self.cur.execute( "SELECT id,source,target,km FROM rede_viaria_bv where inclinacao is null" ) rowsInclinação = self.cur.fetchall() if len(rowsInclinação) is not 0: print("+ Entering slope (may take more few minutes)") for pontos in rowsInclinação: #self.cur.execute("SELECT altura FROM rede_viaria_bv_vertex WHERE id=" + str(pontos[1])) self.cur.execute( "SELECT altura FROM rede_viaria_bv_vertex WHERE id=%s;", (str(pontos[1]), )) source = self.cur.fetchone() #self.cur.execute("SELECT altura FROM rede_viaria_bv_vertex WHERE id=" + str(pontos[2])) self.cur.execute( "SELECT altura FROM rede_viaria_bv_vertex WHERE id=%s", (str(pontos[2]), )) target = self.cur.fetchone() if target[0] is not source[0]: cO = target[0] - source[0] # cateto oposto cA = float( pontos[3] * 100 ) # cateto adjacente, converter os km para metros pq as alturas encontram-se me metros grau = math.degrees(math.atan(cO / cA)) #self.cur.execute("UPDATE rede_viaria_bv SET inclinacao=" + str(grau) + " WHERE id=" + str(pontos[0]) + ";") self.cur.execute( "UPDATE rede_viaria_bv SET inclinacao=%s WHERE id=%s;", (grau, str(pontos[0]))) self.conn.commit() else: #self.cur.execute("UPDATE rede_viaria_bv SET inclinacao=0 WHERE id=" + str(pontos[0]) + ";") self.cur.execute( "UPDATE rede_viaria_bv SET inclinacao=0 WHERE id=%s;", (str(pontos[0]), )) self.conn.commit() else: print("+ There is slope.") #vou deixar esta parte ate ter algum dataset para preencher a largura do passeio. self.cur.execute( "SELECT id,source,target,km FROM rede_viaria_bv where largura_passeio is null" ) rowsLargura = self.cur.fetchall() if len(rowsLargura) is not 0: print("+ Entering side walk width data (almost there)") #for pontos in rowsLargura: #largura do passeio #self.cur.execute("UPDATE rede_viaria_bv SET largura_passeio=%s WHERE id=%s;", (2, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET largura_passeio=2;") self.conn.commit() #proteger da chuva #self.cur.execute("UPDATE rede_viaria_bv SET protecrain=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET protecrain=%s;", (False, )) self.conn.commit() #proteger do sol #self.cur.execute("UPDATE rede_viaria_bv SET shade=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET shade=%s;", (False, )) self.conn.commit() #escadas #self.cur.execute("UPDATE rede_viaria_bv SET stairs=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET stairs=%s;", (False, )) self.conn.commit() #ajudas #self.cur.execute("UPDATE rede_viaria_bv SET helpmovement=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET helpmovement=%s;", (False, )) self.conn.commit() #inundação #self.cur.execute("UPDATE rede_viaria_bv SET inundation=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET inundation=%s;", (False, )) self.conn.commit() #manutenção #self.cur.execute("UPDATE rede_viaria_bv SET maintenance=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET maintenance=%s;", (False, )) self.conn.commit() #desnivelados #self.cur.execute("UPDATE rede_viaria_bv SET uneven=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET uneven=%s;", (False, )) self.conn.commit() #construção #self.cur.execute("UPDATE rede_viaria_bv SET construction=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET construction=%s;", (False, )) self.conn.commit() #wifi #self.cur.execute("UPDATE rede_viaria_bv SET wifi=%s WHERE id=%s;", (False, str(pontos[0]))) self.cur.execute("UPDATE rede_viaria_bv SET wifi=%s;", (False, )) self.conn.commit() else: print("+ There is walking width data.") #ultimo acesso ao objecto self.cur.close() self.conn.close() def get_elevation(self, lat, lng): """ Method taken from the initial Open-Elevations method Get the elevation at point (lat,lng) using the currently opened interface :param lat: :param lng: :return: the information in json format """ try: elevation = self.openElevation.lookup(lat, lng) except: return { 'latitude': lat, 'longitude': lng, 'error': 'No such coordinate (%s, %s)' % (lat, lng) } return {'latitude': lat, 'longitude': lng, 'elevation': elevation}