def test_query_to_sql(db_session): input = ExampleTable(name="John Doe", uni=u"Jane and John Doe") db_session.add(input) db_session.commit() query = (db_session.query(ExampleTable).filter_by(id=input.id).order_by( ExampleTable.name)) sql_text = sql.query_to_sql(query) assert is_unicode(sql_text) assert ( sql_text == "SELECT ilike_test.id, ilike_test.name, ilike_test.uni \n" + "FROM ilike_test \n" + "WHERE ilike_test.id = {} ORDER BY ilike_test.name".format(input.id))
def test_query_to_sql(db_session): input = ExampleTable(name="John Doe", uni=u"Jane and John Doe") db_session.add(input) db_session.commit() query = ( db_session.query(ExampleTable) .filter_by(id=input.id) .order_by(ExampleTable.name) ) sql_text = sql.query_to_sql(query) assert is_unicode(sql_text) assert ( sql_text == "SELECT ilike_test.id, ilike_test.name, ilike_test.uni \n" + "FROM ilike_test \n" + "WHERE ilike_test.id = {} ORDER BY ilike_test.name".format(input.id) )
def test_query_to_sql2(db_session): from geoalchemy2.shape import from_shape from sqlalchemy.sql import literal_column from shapely.geometry import LineString linestring = LineString([(0, 0), (1, 2), (3, 4)]) locations = from_shape(linestring, srid=4326) one = literal_column("1 as flight_id") query = db_session.query(locations.label("flight_geometry"), one) sql_text = sql.query_to_sql(query) assert is_unicode(sql_text) assert ( sql_text == u"SELECT ST_GeomFromWKB('\\001\\002\\000\\000\\000\\003\\000\\000\\000\\000\\000\\000\\000" + "\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\360?\\000" + "\\000\\000\\000\\000\\000\\000@\\000\\000\\000\\000\\000\\000\\010@\\000\\000\\000\\000\\000\\000" + "\\020@'::bytea, 4326) AS flight_geometry, 1 as flight_id" )
def test_query_to_sql2(db_session): from geoalchemy2.shape import from_shape from sqlalchemy.sql import literal_column from shapely.geometry import LineString linestring = LineString([(0, 0), (1, 2), (3, 4)]) locations = from_shape(linestring, srid=4326) one = literal_column("1 as flight_id") query = db_session.query(locations.label("flight_geometry"), one) sql_text = sql.query_to_sql(query) assert is_unicode(sql_text) assert ( sql_text == u"SELECT ST_GeomFromWKB('\\001\\002\\000\\000\\000\\003\\000\\000\\000\\000\\000\\000\\000" + "\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\360?\\000" + "\\000\\000\\000\\000\\000\\000@\\000\\000\\000\\000\\000\\000\\010@\\000\\000\\000\\000\\000\\000" + "\\020@'::bytea, 4326) AS flight_geometry, 1 as flight_id")
def airspace_image(cache_key, airspace_id): if not mapscript_available: abort(404) # get information from cache... infringements = cache.get('upload_airspace_infringements_' + cache_key) flight_path = cache.get('upload_airspace_flight_path_' + cache_key) # abort if invalid cache key if not infringements \ or not flight_path: abort(404) # Convert the coordinate into a list of tuples coordinates = [(c.location['longitude'], c.location['latitude']) for c in flight_path] # Create a shapely LineString object from the coordinates linestring = LineString(coordinates) # Save the new path as WKB locations = from_shape(linestring, srid=4326) highlight_locations = [] extent_epsg4326 = [180, 85.05112878, -180, -85.05112878] for period in infringements[airspace_id]: # Convert the coordinate into a list of tuples coordinates = [(c['location']['longitude'], c['location']['latitude']) for c in period] # Create a shapely LineString object from the coordinates if len(coordinates) == 1: # a LineString must contain at least two points... linestring = LineString([coordinates[0], coordinates[0]]) else: linestring = LineString(coordinates) highlight_locations.append(linestring) # gather extent (minx, miny, maxx, maxy) = linestring.bounds extent_epsg4326[0] = min(extent_epsg4326[0], minx) extent_epsg4326[1] = min(extent_epsg4326[1], miny) extent_epsg4326[2] = max(extent_epsg4326[2], maxx) extent_epsg4326[3] = max(extent_epsg4326[3], maxy) # Save the new path as WKB highlight_multilinestring = from_shape( MultiLineString(highlight_locations), srid=4326) # increase extent by factor 1.05 width = abs(extent_epsg4326[0] - extent_epsg4326[2]) height = abs(extent_epsg4326[1] - extent_epsg4326[3]) center_x = (extent_epsg4326[0] + extent_epsg4326[2]) / 2 center_y = (extent_epsg4326[1] + extent_epsg4326[3]) / 2 extent_epsg4326[0] = center_x - width / 2 * 1.05 extent_epsg4326[1] = center_y - height / 2 * 1.05 extent_epsg4326[2] = center_x + width / 2 * 1.05 extent_epsg4326[3] = center_y + height / 2 * 1.05 # minimum extent should be 0.3 deg width = abs(extent_epsg4326[0] - extent_epsg4326[2]) height = abs(extent_epsg4326[1] - extent_epsg4326[3]) if width < 0.3: extent_epsg4326[0] = center_x - 0.15 extent_epsg4326[2] = center_x + 0.15 if height < 0.3: extent_epsg4326[1] = center_y - 0.15 extent_epsg4326[3] = center_y + 0.15 # convert extent from EPSG4326 to EPSG3857 epsg4326 = pyproj.Proj(init='epsg:4326') epsg3857 = pyproj.Proj(init='epsg:3857') x1, y1 = pyproj.transform(epsg4326, epsg3857, extent_epsg4326[0], extent_epsg4326[1]) x2, y2 = pyproj.transform(epsg4326, epsg3857, extent_epsg4326[2], extent_epsg4326[3]) extent_epsg3857 = [x1, y1, x2, y2] # load basemap and set size + extent basemap_path = os.path.join( current_app.config.get('SKYLINES_MAPSERVER_PATH'), 'basemap.map') map_object = mapscript.mapObj(basemap_path) map_object.setSize(400, 400) map_object.setExtent(extent_epsg3857[0], extent_epsg3857[1], extent_epsg3857[2], extent_epsg3857[3]) # enable airspace and airports layers num_layers = map_object.numlayers for i in range(num_layers): layer = map_object.getLayer(i) if layer.group == 'Airports': layer.status = mapscript.MS_ON if layer.group == 'Airspace': layer.status = mapscript.MS_ON # get flights layer flights_layer = map_object.getLayerByName('Flights') highlight_layer = map_object.getLayerByName('Flights_Highlight') # set sql query for blue flight one = literal_column('1 as flight_id') flight_query = db.session.query(locations.label('flight_geometry'), one) flights_layer.data = 'flight_geometry FROM (' + query_to_sql(flight_query) + ')' + \ ' AS foo USING UNIQUE flight_id USING SRID=4326' # set sql query for highlighted linestrings highlighted_query = db.session.query( highlight_multilinestring.label('flight_geometry'), one) highlight_layer.data = 'flight_geometry FROM (' + query_to_sql(highlighted_query) + ')' + \ ' AS foo USING UNIQUE flight_id USING SRID=4326' highlight_layer.status = mapscript.MS_ON # get osm layer and set WMS url osm_layer = map_object.getLayerByName('OSM') osm_layer.connection = current_app.config.get('SKYLINES_MAP_TILE_URL') + \ '/service?' # draw map map_image = map_object.draw() # get image mapscript.msIO_installStdoutToBuffer() map_image.write() content = mapscript.msIO_getStdoutBufferBytes() # return to client resp = make_response(content) resp.headers['Content-type'] = map_image.format.mimetype return resp
def airspace_image(cache_key, as_id): if not mapscript_available: abort(404) # get information from cache... infringements = current_app.cache.get('upload_airspace_infringements_' + cache_key) flight_path = current_app.cache.get('upload_airspace_flight_path_' + cache_key) # abort if invalid cache key if not infringements \ or not flight_path: abort(404) # Convert the coordinate into a list of tuples coordinates = [(c.location['longitude'], c.location['latitude']) for c in flight_path] # Create a shapely LineString object from the coordinates linestring = LineString(coordinates) # Save the new path as WKB locations = from_shape(linestring, srid=4326) highlight_locations = [] extent_epsg4326 = [180, 85.05112878, -180, -85.05112878] for period in infringements[as_id]: # Convert the coordinate into a list of tuples coordinates = [(c['location']['longitude'], c['location']['latitude']) for c in period] # Create a shapely LineString object from the coordinates if len(coordinates) == 1: # a LineString must contain at least two points... linestring = LineString([coordinates[0], coordinates[0]]) else: linestring = LineString(coordinates) highlight_locations.append(linestring) # gather extent (minx, miny, maxx, maxy) = linestring.bounds extent_epsg4326[0] = min(extent_epsg4326[0], minx) extent_epsg4326[1] = min(extent_epsg4326[1], miny) extent_epsg4326[2] = max(extent_epsg4326[2], maxx) extent_epsg4326[3] = max(extent_epsg4326[3], maxy) # Save the new path as WKB highlight_multilinestring = from_shape(MultiLineString(highlight_locations), srid=4326) # increase extent by factor 1.05 width = abs(extent_epsg4326[0] - extent_epsg4326[2]) height = abs(extent_epsg4326[1] - extent_epsg4326[3]) center_x = (extent_epsg4326[0] + extent_epsg4326[2]) / 2 center_y = (extent_epsg4326[1] + extent_epsg4326[3]) / 2 extent_epsg4326[0] = center_x - width / 2 * 1.05 extent_epsg4326[1] = center_y - height / 2 * 1.05 extent_epsg4326[2] = center_x + width / 2 * 1.05 extent_epsg4326[3] = center_y + height / 2 * 1.05 # minimum extent should be 0.3 deg width = abs(extent_epsg4326[0] - extent_epsg4326[2]) height = abs(extent_epsg4326[1] - extent_epsg4326[3]) if width < 0.3: extent_epsg4326[0] = center_x - 0.15 extent_epsg4326[2] = center_x + 0.15 if height < 0.3: extent_epsg4326[1] = center_y - 0.15 extent_epsg4326[3] = center_y + 0.15 # convert extent from EPSG4326 to EPSG3857 epsg4326 = pyproj.Proj(init='epsg:4326') epsg3857 = pyproj.Proj(init='epsg:3857') x1, y1 = pyproj.transform(epsg4326, epsg3857, extent_epsg4326[0], extent_epsg4326[1]) x2, y2 = pyproj.transform(epsg4326, epsg3857, extent_epsg4326[2], extent_epsg4326[3]) extent_epsg3857 = [x1, y1, x2, y2] # load basemap and set size + extent basemap_path = os.path.join(current_app.config.get('SKYLINES_MAPSERVER_PATH'), 'basemap.map') map_object = mapscript.mapObj(basemap_path) map_object.setSize(400, 400) map_object.setExtent(extent_epsg3857[0], extent_epsg3857[1], extent_epsg3857[2], extent_epsg3857[3]) # enable airspace and airports layers num_layers = map_object.numlayers for i in range(num_layers): layer = map_object.getLayer(i) if layer.group == 'Airports': layer.status = mapscript.MS_ON if layer.group == 'Airspace': layer.status = mapscript.MS_ON # get flights layer flights_layer = map_object.getLayerByName('Flights') highlight_layer = map_object.getLayerByName('Flights_Highlight') # set sql query for blue flight one = literal_column('1 as flight_id') flight_query = db.session.query(locations.label('flight_geometry'), one) flights_layer.data = 'flight_geometry FROM (' + query_to_sql(flight_query) + ')' + \ ' AS foo USING UNIQUE flight_id USING SRID=4326' # set sql query for highlighted linestrings highlighted_query = db.session.query(highlight_multilinestring.label('flight_geometry'), one) highlight_layer.data = 'flight_geometry FROM (' + query_to_sql(highlighted_query) + ')' + \ ' AS foo USING UNIQUE flight_id USING SRID=4326' highlight_layer.status = mapscript.MS_ON # get osm layer and set WMS url osm_layer = map_object.getLayerByName('OSM') osm_layer.connection = current_app.config.get('SKYLINES_MAP_TILE_URL') + \ '/service?' # draw map map_image = map_object.draw() # get image mapscript.msIO_installStdoutToBuffer() map_image.write() content = mapscript.msIO_getStdoutBufferBytes() # return to client resp = make_response(content) resp.headers['Content-type'] = map_image.format.mimetype return resp