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
