def test_within_distance(self):
"""
Because SDO_WITHIN_DISTANCE requires a spatial index for the geometry used
as first parameter, we have to insert out test geometries into tables,
unlike to the other databases.
Note that Oracle uses meter as unit for the tolerance value for geodetic coordinate
systems (like 4326)!
"""
# test if SDO_functions._within_distance is called correctly
eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(0 0)', 0)).count(), 1)
eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(0 0)', 0.1)).count(), 1)
eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(9 9)', 100000)).count(), 0)
eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location,
'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)).count(), 3)
eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location,
'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 0)).count(), 4)
eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location,
'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 200000)).count(), 5)
# test if SDO_GEOM.functions._within_distance is called correctly
eq_(session.scalar(select([text('1')], from_obj=['dual']).where(
functions._within_distance('POINT(0 0)', 'POINT(0 0)', 0,
{'tol' : 0.00000005}))), 1)
eq_(session.scalar(select([text('1')], from_obj=['dual']).where(
functions._within_distance('POINT(0 0)', 'POINT(0 0)', 0,
{'dim1' : text(diminfo),
'dim2' : text(diminfo)}))), 1)
def create_geom_filter(request, mapped_class, **kwargs):
"""Create MapFish geometry filter based on the request params. Either
a box or within or geometry filter, depending on the request params.
Additional named arguments are passed to the spatial filter."""
tolerance = 0
if 'tolerance' in request.params:
tolerance = float(request.params['tolerance'])
epsg = None
if 'epsg' in request.params:
epsg = int(request.params['epsg'])
box = None
if 'bbox' in request.params:
box = request.params['bbox']
geometry = None
if box is not None:
box = map(float, box.split(','))
geometry = Polygon(
((box[0], box[1]), (box[0], box[3]), (box[2], box[3]),
(box[2], box[1]), (box[0], box[1])))
elif 'lon' and 'lat' in request.params:
geometry = Point(float(request.params['lon']),
float(request.params['lat']))
elif 'geometry' in request.params:
factory = lambda ob: GeoJSON.to_instance(ob)
geometry = loads(request.params['geometry'], object_hook=factory)
geometry = asShape(geometry)
if geometry is None:
return None
geom_column = mapped_class.geometry_column()
epsg = geom_column.type.srid if epsg is None else epsg
if epsg != geom_column.type.srid:
geom_column = functions.transform(geom_column, epsg)
wkb_geometry = WKBSpatialElement(buffer(geometry.wkb), epsg)
if 'additional_params' in kwargs:
return functions._within_distance(geom_column, wkb_geometry, tolerance,
kwargs['additional_params'])
else:
return functions._within_distance(geom_column, wkb_geometry, tolerance)
def create_geom_filter(request, mapped_class, **kwargs):
"""Create MapFish geometry filter based on the request params. Either
a box or within or geometry filter, depending on the request params.
Additional named arguments are passed to the spatial filter."""
tolerance = 0
if 'tolerance' in request.params:
tolerance = float(request.params['tolerance'])
epsg = None
if 'epsg' in request.params:
epsg = int(request.params['epsg'])
box = None
if 'bbox' in request.params:
box = request.params['bbox']
geometry = None
if box is not None:
box = map(float, box.split(','))
geometry = Polygon(((box[0], box[1]), (box[0], box[3]),
(box[2], box[3]), (box[2], box[1]),
(box[0], box[1])))
elif 'lon' and 'lat' in request.params:
geometry = Point(float(request.params['lon']),
float(request.params['lat']))
elif 'geometry' in request.params:
factory = lambda ob: GeoJSON.to_instance(ob)
geometry = loads(request.params['geometry'], object_hook=factory)
geometry = asShape(geometry)
if geometry is None:
return None
geom_column = mapped_class.geometry_column()
epsg = geom_column.type.srid if epsg is None else epsg
if epsg != geom_column.type.srid:
geom_column = functions.transform(geom_column, epsg)
wkb_geometry = WKBSpatialElement(buffer(geometry.wkb), epsg)
if 'additional_params' in kwargs:
return functions._within_distance(geom_column, wkb_geometry, tolerance,
kwargs['additional_params'])
else:
return functions._within_distance(geom_column, wkb_geometry, tolerance)
def test_within_distance(self):
ok_(
session.scalar(
functions._within_distance(
'POINT(-88.9139332929936 42.5082802993631)',
'POINT(-88.9139332929936 35.5082802993631)', 10)))
ok_(
session.scalar(
functions._within_distance('Point(0 0)', 'Point(0 0)', 0)))
ok_(
session.scalar(
functions._within_distance(
'Point(0 0)', 'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))',
0)))
ok_(
session.scalar(
functions._within_distance(
'Point(5 5)', 'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))',
0)))
ok_(
session.scalar(
functions._within_distance(
'Point(6 5)', 'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))',
1)))
ok_(
session.scalar(
functions._within_distance(
'Polygon((0 0, 1 0, 1 8, 0 8, 0 0))',
'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)))
def create_geom_filter(request,
mapped_class,
geom_attr,
within_distance_additional_params={}):
"""Create MapFish geometry filter based on the request params. Either
a box or within or geometry filter, depending on the request params.
Additional named arguments are passed to the spatial filter.
Arguments:
request
the request.
mapped_class
the SQLAlchemy mapped class.
geom_attr
the key of the geometry property as defined in the SQLAlchemy
mapper. If you use ``declarative_base`` this is the name of
the geometry attribute as defined in the mapped class.
within_distance_additional_params
additional_params to pass to the ``within_distance`` function.
"""
tolerance = float(request.params.get('tolerance', 0.0))
epsg = None
if 'epsg' in request.params:
epsg = int(request.params['epsg'])
box = request.params.get('bbox')
geometry = None
if box is not None:
box = map(float, box.split(','))
geometry = Polygon(
((box[0], box[1]), (box[0], box[3]), (box[2], box[3]),
(box[2], box[1]), (box[0], box[1])))
elif 'lon' and 'lat' in request.params:
geometry = Point(float(request.params['lon']),
float(request.params['lat']))
elif 'geometry' in request.params:
geometry = loads(request.params['geometry'],
object_hook=GeoJSON.to_instance)
geometry = asShape(geometry)
if geometry is None:
return None
column_epsg = _get_col_epsg(mapped_class, geom_attr)
geom_attr = getattr(mapped_class, geom_attr)
epsg = column_epsg if epsg is None else epsg
if epsg != column_epsg:
geom_attr = functions.transform(geom_attr, epsg)
wkb_geometry = WKBSpatialElement(buffer(geometry.wkb), epsg)
return functions._within_distance(geom_attr, wkb_geometry, tolerance,
within_distance_additional_params)
def create_geom_filter(request, mapped_class, geom_attr,
within_distance_additional_params={}):
"""Create MapFish geometry filter based on the request params. Either
a box or within or geometry filter, depending on the request params.
Additional named arguments are passed to the spatial filter.
Arguments:
request
the request.
mapped_class
the SQLAlchemy mapped class.
geom_attr
the key of the geometry property as defined in the SQLAlchemy
mapper. If you use ``declarative_base`` this is the name of
the geometry attribute as defined in the mapped class.
within_distance_additional_params
additional_params to pass to the ``within_distance`` function.
"""
tolerance = float(request.params.get('tolerance', 0.0))
epsg = None
if 'epsg' in request.params:
epsg = int(request.params['epsg'])
box = request.params.get('bbox')
geometry = None
if box is not None:
box = map(float, box.split(','))
geometry = Polygon(((box[0], box[1]), (box[0], box[3]),
(box[2], box[3]), (box[2], box[1]),
(box[0], box[1])))
elif 'lon' and 'lat' in request.params:
geometry = Point(float(request.params['lon']),
float(request.params['lat']))
elif 'geometry' in request.params:
geometry = loads(request.params['geometry'], object_hook=GeoJSON.to_instance)
geometry = asShape(geometry)
if geometry is None:
return None
column_epsg = _get_col_epsg(mapped_class, geom_attr)
geom_attr = getattr(mapped_class, geom_attr)
epsg = column_epsg if epsg is None else epsg
if epsg != column_epsg:
geom_attr = functions.transform(geom_attr, epsg)
wkb_geometry = WKBSpatialElement(buffer(geometry.wkb), epsg)
return functions._within_distance(geom_attr, wkb_geometry, tolerance,
within_distance_additional_params)
def test_within_distance(self):
ok_(session.scalar(functions._within_distance('POINT(-88.9139332929936 42.5082802993631)', 'POINT(-88.9139332929936 35.5082802993631)', 10)))
ok_(session.scalar(functions._within_distance('Point(0 0)', 'Point(0 0)', 0)))
ok_(session.scalar(functions._within_distance('Point(0 0)',
'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)))
ok_(session.scalar(functions._within_distance('Point(5 5)',
'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)))
ok_(session.scalar(functions._within_distance('Point(6 5)',
'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 1)))
ok_(session.scalar(functions._within_distance('Polygon((0 0, 1 0, 1 8, 0 8, 0 0))',
'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)))
def near(lat, lng, distance):
list = []
point = WKTSpatialElement('POINT({0} {1})'.format(lat, lng))
logging.info("Doing search with %s" % 'POINT({0} {1})'.format(lat, lng))
logging.info("And distance %s" % distance)
session = sessionmaker(bind=engine)()
query = session.query(Offices).filter(functions._within_distance(Offices.location, point, distance)).order_by(Offices.location)
# TODO: https://marshmallow.readthedocs.org/en/latest/nesting.html
for office in query:
list.append({
"id": office.id,
"desc": office.desc,
"address": office.address,
"latitude": office.lat,
"longitude": office.lng
})
return list
def video_search():
# this method is temporary since geoalchemy has problem with sqlite video can not be found by distance between to coordinates.
if request.method == 'POST':
#db.session.query(Video).filter()
#test3 = db.session.scalar(functions._within_distance(Video.location_lat_lng, 'POINT(-88.9139332929936 35.5082802993631)', 10))
posted_corr = 'POINT(' + request.data.replace('"','').replace(',',' ') + ')'
geo_data_list = db.session.query(Video.video_url).filter(functions._within_distance(Video.location_lat_lng, posted_corr, 0.6))[:30]
#test2 = session.scalar(functions._within_distance('POINT(-88.9139332929936 42.5082802993631)', 'POINT(-88.9139332929936 35.5082802993631)', 10))
geo_data_dict ={}
for i in range(len(geo_data_list)):
geo_data_dict[i] = geo_data_list[i][0]
#youtube_link = "http://gdata.youtube.com/feeds/api/videos?v=2&alt=json&prettyprint=true&location=" + request.data.replace('"','') + "!&location-radius=6mi&max-results=12";
#youtube_request = requests.get(youtube_link)
## Convert it to a Python dictionary
#youtube_data = json.loads(youtube_request.text)
#test = request.data
#coor = request.data.split(',')
return jsonify(geo_data_dict)
def test_issue17(self):
r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
lakes_within_distance = session.query(Lake).filter(
Lake.road.has(functions._within_distance(Road.road_geom,r1.road_geom, 0.20))).all()
lakes_within_distance = session.query(Lake).filter(
Lake.road.has(functions.within_distance(Road.road_geom,r1.road_geom, 0.20))).all()
def test_issue17(self):
r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
lakes_within_distance = session.query(Lake).filter(
Lake.road.has(functions._within_distance(Road.road_geom,r1.road_geom, 0.20))).all()
lakes_within_distance = session.query(Lake).filter(
Lake.road.has(functions.within_distance(Road.road_geom,r1.road_geom, 0.20))).all()
