def _bbox_2_wkt(bbox, srid):
'''
Given a bbox dictionary, return a WKTSpatialElement, transformed
into the database\'s CRS if necessary.
returns e.g. WKTSpatialElement("POLYGON ((2 0, 2 1, 7 1, 7 0, 2 0))", 4326)
'''
db_srid = int(config.get('ckan.spatial.srid', '4326'))
bbox_template = Template(
'POLYGON (($minx $miny, $minx $maxy, $maxx $maxy, $maxx $miny, $minx $miny))'
)
wkt = bbox_template.substitute(minx=bbox['minx'],
miny=bbox['miny'],
maxx=bbox['maxx'],
maxy=bbox['maxy'])
if srid and srid != db_srid:
# Input geometry needs to be transformed to the one used on the database
input_geometry = functions.transform(WKTSpatialElement(wkt, srid),
db_srid)
else:
input_geometry = WKTSpatialElement(wkt, db_srid)
return input_geometry
def test_update_extent(self):
package = Package.get('annakarenina')
geojson = json.loads(self.geojson_examples['point'])
shape = asShape(geojson)
package_extent = PackageExtent(package_id=package.id,
the_geom=WKTSpatialElement(
shape.wkt, self.db_srid))
package_extent.save()
assert Session.scalar(
package_extent.the_geom.geometry_type) == 'ST_Point'
# Update the geometry (Point -> Polygon)
geojson = json.loads(self.geojson_examples['polygon'])
shape = asShape(geojson)
package_extent.the_geom = WKTSpatialElement(shape.wkt, self.db_srid)
package_extent.save()
assert package_extent.package_id == package.id
assert Session.scalar(
package_extent.the_geom.geometry_type) == 'ST_Polygon'
assert Session.scalar(package_extent.the_geom.srid) == self.db_srid
def test_insert_node():
with test_db_session() as session:
new_node = OSRMNode(1, 3400000, 11800000, False, False)
session.add(new_node)
session.commit()
# check if we can query it
results = session.query(OSRMNode).filter_by(osm_id=1).all()
eq_(len(results), 1)
eq_(results[0].lat, 3400000)
eq_(results[0].lon, 11800000)
eq_(results[0].bollard, False)
eq_(results[0].traffic_light, False)
eq_(len(results[0].in_edges), 0)
eq_(len(results[0].out_edges), 0)
# check if we can spatial query it
covering_box = WKTSpatialElement(
'POLYGON((117 33, 119 33, 119 35, 117 35, 117 33))')
eq_(
session.query(OSRMNode).filter(
OSRMNode.geom.covered_by(covering_box)).count(), 1)
noncovering_box = WKTSpatialElement(
'POLYGON((117 38, 119 38, 119 39, 117 39, 117 38))')
eq_(
session.query(OSRMNode).filter(
OSRMNode.geom.covered_by(noncovering_box)).count(), 0)
def setUp(self):
from c2cgeoportal.models import DBSession, User, Role, Layer, \
RestrictionArea, Theme, LayerGroup
role1 = Role(name=u'__test_role1')
user1 = User(username=u'__test_user1',
password=u'__test_user1',
role=role1)
role2 = Role(name=u'__test_role2',
extent=WKTSpatialElement(
"POLYGON((1 2, 1 4, 3 4, 3 2, 1 2))", srid=21781))
user2 = User(username=u'__test_user2',
password=u'__test_user2',
role=role2)
public_layer = Layer(name=u'__test_public_layer',
order=40,
public=True)
public_layer.isChecked = False
private_layer = Layer(name=u'__test_private_layer',
order=40,
public=False)
private_layer.geoTable = 'a_schema.a_geo_table'
layer_in_group = Layer(name=u'__test_layer_in_group')
layer_group = LayerGroup(name=u'__test_layer_group')
layer_group.children = [layer_in_group]
layer_wmsgroup = Layer(name=u'test_wmsfeaturesgroup')
layer_wmsgroup.isChecked = False
theme = Theme(name=u'__test_theme')
theme.children = [
public_layer, private_layer, layer_group, layer_wmsgroup
]
poly = "POLYGON((-100 0, -100 20, 100 20, 100 0, -100 0))"
area = WKTSpatialElement(poly, srid=21781)
RestrictionArea(name=u'__test_ra1',
description=u'',
layers=[private_layer],
roles=[role1],
area=area)
area = WKTSpatialElement(poly, srid=21781)
RestrictionArea(name=u'__test_ra2',
description=u'',
layers=[private_layer],
roles=[role2],
area=area,
readwrite=True)
DBSession.add_all([user1, user2, public_layer, private_layer])
transaction.commit()
def get_trips_that_passed_through_geo_rect(self, geo_rect):
session = db.Session()
box = WKTSpatialElement(adapters.convert_geo_rect_to_wkt(geo_rect))
query = session.query(Trip).filter(
box.intersects(Trip.path)
)
counts = query.count()
session.commit()
return counts
def setUp(self):
import transaction
from sqlalchemy import func
from geoalchemy import WKTSpatialElement
from c2cgeoportal.models import FullTextSearch, User, Role
from c2cgeoportal.models import DBSession
user1 = User(username=u'__test_user1', password=u'__test_user1')
role1 = Role(name=u'__test_role1', description=u'__test_role1')
user1.role = role1
user2 = User(username=u'__test_user2', password=u'__test_user2')
role2 = Role(name=u'__test_role2', description=u'__test_role2')
user2.role = role2
entry1 = FullTextSearch()
entry1.label = 'label1'
entry1.layer_name = 'layer1'
entry1.ts = func.to_tsvector('french', 'soleil travail')
entry1.the_geom = WKTSpatialElement("POINT(-90 -45)")
entry1.public = True
entry2 = FullTextSearch()
entry2.label = 'label2'
entry2.layer_name = 'layer2'
entry2.ts = func.to_tsvector('french', 'pluie semaine')
entry2.the_geom = WKTSpatialElement("POINT(-90 -45)")
entry2.public = False
entry3 = FullTextSearch()
entry3.label = 'label3'
entry3.layer_name = 'layer3'
entry3.ts = func.to_tsvector('french', 'vent neige')
entry3.the_geom = WKTSpatialElement("POINT(-90 -45)")
entry3.public = False
entry3.role = role2
entry4 = FullTextSearch()
entry4.label = 'label4'
entry4.layer_name = 'layer1'
entry4.ts = func.to_tsvector('french', 'soleil travail')
entry4.the_geom = WKTSpatialElement("POINT(-90 -45)")
entry4.public = True
entry5 = FullTextSearch()
entry5.label = 'label5'
entry5.layer_name = 'layer1'
entry5.ts = func.to_tsvector('french', 'params')
entry5.the_geom = WKTSpatialElement("POINT(-90 -45)")
entry5.public = True
entry5.params = {'floor': 5}
DBSession.add_all(
[user1, user2, entry1, entry2, entry3, entry4, entry5])
transaction.commit()
def setUp(self):
metadata.drop_all()
metadata.create_all()
# Add objects. We can use strings...
session.add_all([
Road(road_name=u'Jeff Rd', road_geom='LINESTRING(-88.9139332929936 42.5082802993631,-88.8203027197452 42.5985669235669,-88.7383759681529 42.7239650127389,-88.6113059044586 42.9680732929936,-88.3655256496815 43.1402866687898)'),
Road(road_name=u'Peter Rd', road_geom='LINESTRING(-88.9139332929936 42.5082802993631,-88.8203027197452 42.5985669235669,-88.7383759681529 42.7239650127389,-88.6113059044586 42.9680732929936,-88.3655256496815 43.1402866687898)'),
Road(road_name=u'Geordie Rd', road_geom='LINESTRING(-89.2232485796178 42.6420382611465,-89.2449842484076 42.9179140573248,-89.2316084522293 43.106847178344,-89.0710987261147 43.243949044586,-89.092834566879 43.2957802993631,-89.092834566879 43.2957802993631,-89.0309715095541 43.3175159681529)'),
Road(road_name=u'Paul St', road_geom='LINESTRING(-88.2652071783439 42.5584395350319,-88.1598727834395 42.6269904904459,-88.1013536751592 42.621974566879,-88.0244428471338 42.6437102356688,-88.0110670509554 42.6771497261147)'),
Road(road_name=u'Graeme Ave', road_geom='LINESTRING(-88.5477708726115 42.6988853949045,-88.6096339299363 42.9697452675159,-88.6029460318471 43.0884554585987,-88.5912422101911 43.187101955414)'),
Road(road_name=u'Phil Tce', road_geom='LINESTRING(-88.9356689617834 42.9363057770701,-88.9824842484076 43.0366242484076,-88.9222931656051 43.1085191528662,-88.8487262866242 43.0449841210191)'),
Lake(lake_name=u'My Lake', lake_geom='POLYGON((-88.7968950764331 43.2305732929936,-88.7935511273885 43.1553344394904,-88.716640299363 43.1570064140127,-88.7250001719745 43.2339172420382,-88.7968950764331 43.2305732929936))'),
Lake(lake_name=u'Lake White', lake_geom='POLYGON((-88.1147292993631 42.7540605095542,-88.1548566878981 42.7824840764331,-88.1799363057325 42.7707802547771,-88.188296178344 42.7323248407643,-88.1832802547771 42.6955414012739,-88.1565286624204 42.6771496815287,-88.1448248407643 42.6336783439491,-88.131449044586 42.5718152866242,-88.1013535031847 42.565127388535,-88.1080414012739 42.5868630573248,-88.1164012738854 42.6119426751592,-88.1080414012739 42.6520700636943,-88.0980095541401 42.6838375796178,-88.0846337579618 42.7139331210191,-88.1013535031847 42.7423566878981,-88.1147292993631 42.7540605095542))'),
Lake(lake_name=u'Lake Blue', lake_geom='POLYGON((-89.0694267515924 43.1335987261147,-89.1078821656051 43.1135350318471,-89.1329617834395 43.0884554140127,-89.1312898089172 43.0466560509554,-89.112898089172 43.0132165605096,-89.0694267515924 42.9898089171975,-89.0343152866242 42.953025477707,-89.0209394904459 42.9179140127389,-89.0042197452229 42.8961783439491,-88.9774681528663 42.8644108280255,-88.9440286624204 42.8292993630573,-88.9072452229299 42.8142515923567,-88.8687898089172 42.815923566879,-88.8687898089172 42.815923566879,-88.8102707006369 42.8343152866242,-88.7734872611465 42.8710987261147,-88.7517515923567 42.9145700636943,-88.7433917197452 42.9730891719745,-88.7517515923567 43.0299363057325,-88.7734872611465 43.0867834394905,-88.7885352038217 43.158678388535,-88.8738057324841 43.1620222929936,-88.947372611465 43.1937898089172,-89.0042197452229 43.2138535031847,-89.0410031847134 43.2389331210191,-89.0710987261147 43.243949044586,-89.0660828025478 43.2238853503185,-89.0543789808917 43.203821656051,-89.0376592356688 43.175398089172,-89.0292993630573 43.1519904458599,-89.0376592356688 43.1369426751592,-89.0393312101911 43.1386146496815,-89.0393312101911 43.1386146496815,-89.0510350318471 43.1335987261147,-89.0694267515924 43.1335987261147))'),
Lake(lake_name=u'Lake Deep', lake_geom='POLYGON((-88.9122611464968 43.038296178344,-88.9222929936306 43.0399681528663,-88.9323248407643 43.0282643312102,-88.9206210191083 43.0182324840764,-88.9105891719745 43.0165605095542,-88.9005573248408 43.0232484076433,-88.9072452229299 43.0282643312102,-88.9122611464968 43.038296178344))'),
Spot(spot_height=420.40, spot_location='POINT(-88.5945861592357 42.9480095987261)'),
Spot(spot_height=102.34, spot_location='POINT(-88.9055734203822 43.0048567324841)'),
Spot(spot_height=388.62, spot_location='POINT(-89.201512910828 43.1051752038217)'),
Spot(spot_height=454.66, spot_location='POINT(-88.3304141847134 42.6269904904459)'),
])
# or use an explicit WKTSpatialElement (similar to saying func.GeomFromText())
self.r = Road(road_name=u'Dave Cres', road_geom=WKTSpatialElement('LINESTRING(-88.6748409363057 43.1035032292994,-88.6464173694267 42.9981688343949,-88.607961955414 42.9680732929936,-88.5160033566879 42.9363057770701,-88.4390925286624 43.0031847579618)', 4326))
session.add(self.r)
session.commit()
def test_start_point(self):
r = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
eq_(session.scalar(functions.wkt(r.road_geom.start_point)),
'POINT(-88.5477708726115 42.6988853949045)')
ok_(session.query(Road).filter(Road.road_geom.start_point == WKTSpatialElement('POINT(-88.9139332929936 42.5082802993631)')).first() is not None)
eq_(session.scalar(functions.wkt(functions.start_point('LINESTRING(0 1, 0 2)'))),
'POINT(0 1)')
def test_intersection(self):
l = session.query(Lake).filter(Lake.lake_name == 'Lake Blue').one()
r = session.query(Road).filter(Road.road_name == 'Geordie Rd').one()
s = session.query(Spot).filter(Spot.spot_height == 454.66).one()
eq_(
session.scalar(
func.STAsText(l.lake_geom.intersection(s.spot_location))),
'GEOMETRYCOLLECTION EMPTY')
eq_(
session.scalar(
func.STAsText(
session.scalar(l.lake_geom.intersection(r.road_geom)))),
'POINT(-89.0710987261147 43.243949044586)')
l = session.query(Lake).filter(Lake.lake_name == 'Lake White').one()
r = session.query(Road).filter(Road.road_name == 'Paul St').one()
eq_(
session.scalar(
func.STAsText(
session.scalar(l.lake_geom.intersection(r.road_geom)))),
'LINESTRING(-88.1430673666454 42.6255500261493,-88.1140839697546 42.6230657349872)'
)
ok_(
session.query(Lake).filter(
Lake.lake_geom.intersection(r.road_geom) == WKTSpatialElement(
'LINESTRING(-88.1430673666454 42.6255500261493,-88.1140839697546 42.6230657349872)'
)).first() is not None)
def test_contains(self):
l = session.query(Lake).filter(Lake.lake_name == 'Lake Blue').one()
l1 = session.query(Lake).filter(Lake.lake_name == 'Lake White').one()
p1 = session.query(Spot).filter(Spot.spot_height == 102.34).one()
p2 = session.query(Spot).filter(Spot.spot_height == 388.62).one()
containing_lakes = session.query(Lake).filter(
Lake.lake_geom.gcontains(p1.spot_location)).all()
ok_(session.scalar(l.lake_geom.gcontains(p1.spot_location)))
ok_(not session.scalar(l.lake_geom.gcontains(p2.spot_location)))
ok_(l in containing_lakes)
ok_(l1 not in containing_lakes)
ok_(
session.scalar(
l.lake_geom.gcontains(
WKTSpatialElement(
'POINT(-88.9055734203822 43.0048567324841)'))))
containing_lakes = session.query(Lake).filter(
Lake.lake_geom.gcontains(
'POINT(-88.9055734203822 43.0048567324841)')).all()
ok_(l in containing_lakes)
ok_(l1 not in containing_lakes)
spots_within = session.query(Spot).filter(
l.lake_geom.gcontains(Spot.spot_location)).all()
ok_(session.scalar(l.lake_geom.gcontains(p1.spot_location)))
ok_(not session.scalar(l.lake_geom.gcontains(p2.spot_location)))
ok_(p1 in spots_within)
ok_(p2 not in spots_within)
def get_package_ids_in_poly(coords,db_srid):
"""
TODO: This needs to be removed as spatial backend is changed to Solr.
"""
poly_template_str = ''
x = coords
i=0
for item in x['poly']:
print item
if i==0:
poly_template_str = poly_template_str + ''+str(item[1]) +' '+ str(item[0])+', '
elif i==len(x['poly'])-1:
poly_template_str = poly_template_str +''+str(item[1]) +' '+ str(item[0])
else:
poly_template_str = poly_template_str +''+str(item[1]) +' '+ str(item[0])+', '
i=i+1
poly_template_str = 'POLYGON (('+poly_template_str + ', '+str(x['poly'][0][1]) +' '+ str(x['poly'][0][0])+'))'
# bbox_template = Template('POLYGON (($minx $miny, $minx $maxy, $maxx $maxy, $maxx $miny, $minx $miny))')
wkt = poly_template_str
input_geometry = WKTSpatialElement(wkt,db_srid)
extents = Session.query(PackageExtent).filter(PackageExtent.package_id==Package.id).filter(PackageExtent.the_geom.intersects(input_geometry)).filter(Package.state==u'active').all()
ids = [extent.package_id for extent in extents]
return ids
def test_wkt(self):
l = session.query(Lake).get(1)
assert session.scalar(
self.r.road_geom.wkt
) == 'LINESTRING (-88.6748409363057 43.1035032292994, -88.6464173694267 42.9981688343949, -88.607961955414 42.9680732929936, -88.5160033566879 42.9363057770701, -88.4390925286624 43.0031847579618)'
eq_(
session.scalar(l.lake_geom.wkt),
'POLYGON ((-88.7968950764331 43.2305732929936, -88.7935511273885 43.1553344394904, -88.716640299363 43.1570064140127, -88.7250001719745 43.2339172420382, -88.7968950764331 43.2305732929936))'
)
ok_(not session.query(Spot).filter(
Spot.spot_location.wkt == 'POINT (0,0)').first())
ok_(
session.query(Spot).get(1) is session.query(Spot).filter(
Spot.spot_location ==
'POINT (-88.5945861592357 42.9480095987261)').first())
r = session.query(Road).get(1)
p = DBSpatialElement(session.scalar(r.road_geom.point_n(5)))
eq_(session.scalar(p.wkt),
u'POINT (-88.3655256496815 43.1402866687898)')
eq_(
session.scalar(
WKTSpatialElement(
'POINT (-88.5769371859941 42.9915634871979)').wkt),
u'POINT (-88.5769371859941 42.9915634871979)')
eq_(
session.query(
Spot.spot_location.wkt).filter(Spot.spot_id == 1).first(),
(u'POINT (-88.5945861592357 42.9480095987261)', ))
def test_end_point(self):
r = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
eq_(session.scalar(functions.wkt(r.road_geom.end_point)),
'POINT(-88.5912422101911 43.187101955414)')
ok_(session.query(Road).filter(Road.road_geom.end_point == WKTSpatialElement('POINT(-88.3655256496815 43.1402866687898)')).first() is not None)
eq_(session.scalar(functions.wkt(functions.end_point('LINESTRING(0 1, 0 2)'))),
'POINT(0 2)')
def write_ride_streams(streams, ride):
"""
Store GPS track for activity as LINESTRING in db.
:param streams: The Strava :class:`stravalib.model.Activity` object.
:type streams: list[stravalib.model.Stream]
:param ride: The db model object for ride.
:type ride: :class:`bafs.model.Ride`
"""
try:
streams_dict = {s.type: s for s in streams}
""" :type: dict[str,stravalib.model.Stream] """
lonlat_points = [(lon,lat) for (lat,lon) in streams_dict['latlng'].data]
if not lonlat_points:
raise ValueError("No data points in latlng streams.")
except (KeyError, ValueError) as x:
log.info("No GPS track for activity {} (skipping): {}".format(ride, x), exc_info=log.isEnabledFor(logging.DEBUG))
ride.track_fetched = None
else:
# Start by removing any existing segments for the ride.
db.engine.execute(RideTrack.__table__.delete().where(RideTrack.ride_id == ride.id))
gps_track = WKTSpatialElement(wktutils.linestring_wkt(lonlat_points))
ride_track = RideTrack()
ride_track.gps_track = gps_track
ride_track.ride_id = ride.id
ride_track.elevation_stream = streams_dict['altitude'].data
ride_track.time_stream = streams_dict['time'].data
db.session.add(ride_track)
ride.track_fetched = True
def test_convex_hull(self):
r = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
eq_(session.scalar(functions.wkt(r.road_geom.convex_hull)),
'POLYGON((-88.5477708726115 42.6988853949045,-88.6096339299363 42.9697452675159,-88.6029460318471 43.0884554585987,-88.5912422101911 43.187101955414,-88.5477708726115 42.6988853949045))')
ok_(session.query(Spot).filter(Spot.spot_location.convex_hull == WKTSpatialElement('POINT(-88.5945861592357 42.9480095987261)')).first() is not None)
eq_(session.scalar(functions.wkt(functions.convex_hull('POINT(-88.5945861592357 42.9480095987261)'))),
'POINT(-88.5945861592357 42.9480095987261)')
def __init__(self, osm_id, lat, lon, bollard, traffic_light):
self.osm_id = osm_id
self.lat = lat
self.lon = lon
self.bollard = bool(bollard)
self.traffic_light = bool(traffic_light)
self.geom = WKTSpatialElement("POINT(%0.6f %0.6f)" %
(lon / 1E5, lat / 1E5))
def test_point_n(self):
l = session.query(Lake).get(1)
r = session.query(Road).get(1)
ok_(not session.scalar(l.lake_geom.point_n(1)))
ok_(session.query(Road).filter(Road.road_geom.point_n(5) == WKTSpatialElement('POINT(-88.3655256496815 43.1402866687898)')).first() is not None)
eq_(session.scalar(r.road_geom.point_n(5).wkt), 'POINT(-88.3655256496815 43.1402866687898)')
eq_(session.scalar(functions.wkt(functions.point_n('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)', 1)))
, 'POINT(77.29 29.07)')
def test_distance(self):
r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
r2 = session.query(Road).filter(Road.road_name=='Geordie Rd').one()
r3 = session.query(Road).filter(Road.road_name=='Peter Rd').one()
assert_almost_equal(session.scalar(r1.road_geom.distance(r2.road_geom)), 0.336997238682841)
eq_(session.scalar(r1.road_geom.distance(r3.road_geom)), 0.0)
ok_(session.query(Spot).filter(Spot.spot_location.distance(WKTSpatialElement('POINT(-88.5945861592357 42.9480095987261)')) < 10).first() is not None)
assert_almost_equal(session.scalar(functions.distance('POINT(-88.5945861592357 42.9480095987261)', 'POINT(-88.5945861592357 42.9480095987261)')), 0)
def test_transform(self):
spot = session.query(Spot).get(1)
# compare the coordinates using a tolerance, because they may vary on different systems
assert_almost_equal(session.scalar(functions.x(spot.spot_location.transform(2249))), -3890517.6109559298)
assert_almost_equal(session.scalar(functions.y(spot.spot_location.transform(2249))), 3627658.6746507999)
ok_(session.query(Spot).filter(Spot.spot_location.transform(2249).wkt == 'POINT(-3890517.61095593 3627658.6746508)').first() is not None)
eq_(session.scalar(functions.wkt(functions.transform(WKTSpatialElement('POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))', 2249), 4326))),
'POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009,-71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.1776848522251 42.3902896512902))')
def test_envelope(self):
r = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
eq_(session.scalar(functions.wkt(r.road_geom.envelope)),
'POLYGON((-88.6096339299363 42.6988853949045,-88.6096339299363 43.187101955414,-88.5477708726115 43.187101955414,-88.5477708726115 42.6988853949045,-88.6096339299363 42.6988853949045))')
eq_(session.scalar(functions.geometry_type(self.r.road_geom.envelope)), 'ST_Polygon')
ok_(session.query(Spot).filter(Spot.spot_location.envelope == WKTSpatialElement('POINT(-88.5945861592357 42.9480095987261)')).first() is not None)
eq_(session.scalar(functions.wkt(functions.envelope('POINT(-88.5945861592357 42.9480095987261)'))),
'POINT(-88.5945861592357 42.9480095987261)')
def test_centroid(self):
r = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
l = session.query(Lake).filter(Lake.lake_name=='Lake Blue').one()
eq_(session.scalar(functions.wkt(r.road_geom.centroid)), 'POINT(-88.5889975373709 42.941769988935)')
eq_(session.scalar(functions.wkt(l.lake_geom.centroid)), 'POINT(-88.9214538261088 43.0191497691548)')
ok_(session.query(Spot).filter(Spot.spot_location.centroid == WKTSpatialElement('POINT(-88.5945861592357 42.9480095987261)')).first() is not None)
eq_(session.scalar(functions.wkt(functions.centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'))),
'POINT(2.30769230769231 3.30769230769231)')
def test_equals(self):
r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
r2 = session.query(Road).filter(Road.road_name=='Peter Rd').one()
r3 = session.query(Road).filter(Road.road_name=='Paul St').one()
equal_roads = session.query(Road).filter(Road.road_geom.equals(r1.road_geom)).all()
ok_(r1 in equal_roads)
ok_(r2 in equal_roads)
ok_(r3 not in equal_roads)
ok_(session.query(Spot).filter(Spot.spot_location.equals(WKTSpatialElement('POINT(-88.5945861592357 42.9480095987261)'))).first() is not None)
eq_(session.scalar(functions.equals('POINT(-88.5945861592357 42.9480095987261)', 'POINT(-88.5945861592357 42.9480095987261)')), True)
def test_persistent(self):
eq_(session.scalar(functions.wkt(func.ST_GeomFromWKB(self.r.road_geom.wkb, 4326))),
'LINESTRING(-88.6748409363057 43.1035032292994,-88.6464173694267 42.9981688343949,-88.607961955414 42.9680732929936,-88.5160033566879 42.9363057770701,-88.4390925286624 43.0031847579618)')
geom = WKTSpatialElement('POINT(30250865.9714116 -610981.481754275)', 2249)
spot = Spot(spot_height=102.34, spot_location=geom)
session.add(spot)
session.commit();
assert_almost_equal(session.scalar(spot.spot_location.x), 0)
assert_almost_equal(session.scalar(spot.spot_location.y), 0)
def test_transform(self):
spot = session.query(Spot).get(1)
eq_(session.scalar(functions.wkt(spot.spot_location.transform(2249))),
'POINT(-3890517.610956 3627658.674651)')
ok_(
session.query(Spot).filter(
sqlite_functions.mbr_contains(
functions.buffer(Spot.spot_location.transform(2249), 10),
WKTSpatialElement('POINT(-3890517.610956 3627658.674651)',
2249))).first() is not None)
eq_(
session.scalar(
functions.wkt(
functions.transform(
WKTSpatialElement(
'POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))',
2249), 4326))),
'POLYGON((-71.177685 42.39029, -71.177684 42.390383, -71.177584 42.390383, -71.177583 42.390289, -71.177685 42.39029))'
)
def setUp(self):
from c2cgeoportal.models import User, Role, Layer, RestrictionArea, \
Functionality, DBSession
TestPoint.__table__.create(bind=DBSession.bind, checkfirst=True)
geom = WKTSpatialElement("MULTIPOINT((-90 -45))", srid=21781)
p1 = TestPoint(the_geom=geom, name=u'foo', city=u'Lausanne', country=u'Swiss')
geom = WKTSpatialElement("MULTIPOINT((-90 45))", srid=21781)
p2 = TestPoint(the_geom=geom, name=u'bar', city=u'Chambéry', country=u'France')
geom = WKTSpatialElement("MULTIPOINT((90 45))", srid=21781)
p3 = TestPoint(the_geom=geom, name=u'éàè', city="Paris", country=u'France')
geom = WKTSpatialElement("MULTIPOINT((90 -45))", srid=21781)
p4 = TestPoint(the_geom=geom, name=u'123', city='Londre', country=u'UK')
pt1 = Functionality(name=u'print_template', value=u'1 Wohlen A4 portrait')
pt2 = Functionality(name=u'print_template', value=u'2 Wohlen A3 landscape')
user1 = User(username=u'__test_user1', password=u'__test_user1')
role1 = Role(name=u'__test_role1', description=u'__test_role1', functionalities=[pt1, pt2])
user1.role = role1
user1.email = u'Tarenpion'
user2 = User(username=u'__test_user2', password=u'__test_user2')
role2 = Role(name=u'__test_role2', description=u'__test_role2', functionalities=[pt1, pt2])
user2.role = role2
user2.email = u'Tarenpion'
user3 = User(username=u'__test_user3', password=u'__test_user3')
role3 = Role(name=u'__test_role3', description=u'__test_role3', functionalities=[pt1, pt2])
user3.role = role3
user3.email = u'Tarenpion'
layer2 = Layer(u'testpoint_protected', 400, public=False)
layer3 = Layer(u'testpoint_protected_query_with_collect', public=False)
area = "POLYGON((-100 30, -100 50, 100 50, 100 30, -100 30))"
area = WKTSpatialElement(area, srid=21781)
restricted_area1 = RestrictionArea(u'__test_ra1', u'', [layer2, layer3], [role1], area)
area = "POLYGON((-100 0, -100 20, 100 20, 100 0, -100 0))"
area = WKTSpatialElement(area, srid=21781)
restricted_area2 = RestrictionArea(u'__test_ra2', u'', [layer2, layer3], [role2, role3], area)
area = "POLYGON((-95 43, -95 47, 95 47, 95 43, -95 43))"
area = WKTSpatialElement(area, srid=21781)
restricted_area3 = RestrictionArea(u'__test_ra3', u'', [layer3], [role3], area, readwrite=True)
DBSession.add_all([
p1, p2, p3, p4, user1, user2, user3,
restricted_area1, restricted_area2, restricted_area3
])
DBSession.flush()
self.id_lausanne = p1.id
self.id_paris = p3.id
transaction.commit()
def test_organization_extent(self):
import json
# Peterborough
geojson = get_boundary(
"http://statistics.data.gov.uk/doc/statistical-geography/E06000031"
)
shape = asShape(geojson)
w = WKTSpatialElement(shape.wkt, 4326)
assert w is not None
def insert_rows(logger, rows, commit, one_by_one, precommit_sql):
if not os.environ.get('SUPPRESS_LOGGING'):
import logging
sl = logging.getLogger('sqlalchemy.engine')
formatter = logging.Formatter("%(message)s")
ch = logging.StreamHandler(sys.stdout)
ch.setLevel(logging.INFO)
ch.setFormatter(formatter)
sl.addHandler(ch)
sl.setLevel(logging.INFO)
from geoalchemy import WKTSpatialElement
to_commit = []
for row in rows:
j = Jurisdiction(
name=unicode(row['name']),
uri=unicode(row['uri']),
type_uri=unicode(row['type_uri']),
geom=WKTSpatialElement(row['geom']),
properties=row.get('properties'),
)
if not commit:
logger.debug('Adding Jurisdiction %r', j)
session.add_all([j])
else:
if one_by_one:
logger.debug('Immediately committing %r', j)
session.add_all([j])
session.commit()
else:
logger.debug('Deferring Jurisdiction %r', j)
to_commit.append(j)
# Need to add everything and force a flush here so the pre-commit
# raw SQL expressions run in the right context.
if to_commit and commit:
session.add_all(to_commit)
session.flush()
from sqlalchemy.sql import text
for sql_expressions in precommit_sql:
if isinstance(sql_expressions, basestring):
sql_expressions = [sql_expressions]
for sql in sql_expressions:
logger.notify("Running pre-commit sql expression: %r" % sql)
session.connection().execute(text(sql))
if to_commit and commit:
logger.notify('COMMIT')
session.commit()
elif not commit:
logger.notify('ROLLBACK')
session.rollback()
def test_area(self):
l = session.query(Lake).filter(Lake.lake_name == 'Lake Blue').one()
assert_almost_equal(session.scalar(l.lake_geom.area), 0.10475991566721)
ok_(
session.query(Lake).filter(
Lake.lake_geom.area > 0).first() is not None)
assert_almost_equal(
session.scalar(
functions.area(
WKTSpatialElement(
'POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))',
2249))), 928.625)
def test_make_valid(self):
session.add(
Shape(shape_name=u'Invalid Shape',
shape_geom=WKTSpatialElement(
u'LINESTRING(0 2, 1 1, 1 0, 1 1, 2 2)')))
invalid_line = session.query(Shape).filter(
Shape.shape_name == u'Invalid Shape').first()
eq_(session.scalar(invalid_line.shape_geom.is_valid), 0)
invalid_line.shape_geom = DBSpatialElement(
session.scalar(invalid_line.shape_geom.make_valid))
valid_line = session.query(Shape).filter(
Shape.shape_name == u'Invalid Shape').first()
eq_(session.scalar(valid_line.shape_geom.is_valid), 1)
def add_item(db):
title = request.GET.get('title')
price = request.GET.get('price')
desc = request.GET.get('desc')
lng = request.GET.get('lng')
lat = request.GET.get('lat')
wkt = 'POINT(%s %s)' % (lat, lng)
location = WKTSpatialElement(wkt)
item = Item(title=title, price=price, desc=desc, location=location)
session.add(item)
session.commit()
return "user %s added!" % title
def test_create_extent(self):
package = Package.get('annakarenina')
assert package
geojson = json.loads(self.geojson_examples['point'])
shape = asShape(geojson)
package_extent = PackageExtent(package_id=package.id,the_geom=WKTSpatialElement(shape.wkt, self.db_srid))
package_extent.save()
assert package_extent.package_id == package.id
assert Session.scalar(package_extent.the_geom.x) == geojson['coordinates'][0]
assert Session.scalar(package_extent.the_geom.y) == geojson['coordinates'][1]
assert Session.scalar(package_extent.the_geom.srid) == self.db_srid
