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