def almanacs_kml(self): json = request.params.get('extent') # We need to make sure we only select almanacs with pages here, query = meta.Session.query(Almanac).join(Almanac.pages).distinct() # ... and eager-load the pages since the ktml template uses them. query = query.options(eagerload(Almanac.pages)) # Tried also with contains_eager, not sure what the difference is # but I only get a fraction of the expected almanacs: #query = meta.Session.query(Almanac).join(Almanac.pages).distinct() #query = query.options(contains_eager(Almanac.pages)) # Also tried using a single, second query for the pages. # ... NOPE, requires sqlalchemy > 0.6.0 which blows up on us, # maybe not compatible w/ pylons 0.9.7? #query = meta.Session.query(Almanac).join(Almanac.pages).distinct() #query = query.options(subqueryload(Almanac.pages)) # Tried also without the explicit join().distinct(), this gives # back all almanac whether they have any pages or not: #query = meta.Session.query(Almanac).options(eagerload(Almanac.pages)) if json is not None: shape = simplejson.loads(json) # Stupid asShape returns an Adapter instead of a Geometry. We round # trip it through wkb to get the correct type. bbox = wkb.loads(asShape(shape).to_wkb()) query = query.filter(func.st_intersects(Almanac.location, func.st_transform('SRID=%s;%s' % ('4326', b2a_hex(bbox.to_wkb())), storage_SRID))) c.almanacs = query.order_by(Almanac.modified.desc()).limit(200).all() response.content_type = 'application/vnd.google-earth.kml+xml kml' return render('/almanac/kml.mako')
def get_profile(cd_ref): """ .. :quickref: Profiles; Return the profile for a cd_ref """ data = (DB.session.query( func.st_asgeojson( func.st_transform(VmValidProfiles.valid_distribution, 4326)), VmValidProfiles, ).filter(VmValidProfiles.cd_ref == cd_ref)) data = data.one_or_none() if data: return jsonify( Feature(geometry=json.loads(data[0]), properties=data[1].as_dict())) abort(404)
def _nearby_almanacs(self, bounds): return meta.Session.query(Almanac).join(Almanac.pages).distinct().filter(func.st_intersects(Almanac.location, func.st_transform('SRID=%s;%s' % ('4326', b2a_hex(bounds.to_wkb())), storage_SRID))).limit(10).all()
def geocode(self): # The geocoder works from either a name or a point. location = request.GET.get('location') bbox = simplejson.loads(request.GET.get('bbox','false')) if location is None and not bbox: abort(400) geoc = geocoders.Google(g.map_key, output_format='json') name_based = False if not bbox: # We don't have a point, so we work with the name... name_based = True try: result = GeocoderController._result_with_locality(geoc.geocode(location, exactly_one=False)) if not result: return {} if not result.locality or not result.administrative: # We want the geocoder to give us an canonical name for # this location. If it hasn't (because the user searched # using a name that doesn't match up to the canonical name, # we use the location to find the canonical name. result = GeocoderController._result_with_locality(geoc.geocode('%f, %f' % (result.latitude, result.longitude), exactly_one=False)) place, (lat, lng) = result except ValueError: return {} else: bbox = asShape(bbox) try: result = GeocoderController._result_with_locality(geoc.geocode('%f, %f' % (bbox.centroid.y, bbox.centroid.x), exactly_one=False)) place, (lat, lng) = result except ValueError: return {} if result.locality and result.administrative: authoritative_name = '%s, %s' % (result.locality, result.administrative) else: authoritative_name = None try: meta.Session.query(Almanac).filter(Almanac.name==authoritative_name).one() almanac = True except exc.NoResultFound: almanac = False geopoint = Point(lng, lat) if bbox: c.almanacs = self._nearby_almanacs(bbox) else: c.almanacs = meta.Session.query(Almanac).join(Almanac.pages).distinct().filter(func.st_dwithin(Almanac.location, func.st_transform(func.st_geomfromtext('SRID=%s;POINT(%f %f)' % ('4326', result.longitude, result.latitude)), storage_SRID), 6233)).limit(10).all() nearby_kml = render('/almanac/kml.mako') return dict(lat=lat, lng=lng, layer=nearby_kml, geojson=simplejson.dumps(geopoint.__geo_interface__), authoritative_name=authoritative_name, name_based=name_based, almanac=almanac)
@property def location_4326(self): if self._location_4326 is None: # God this is ugly Fix for bug #xxx in SQLAlchemy meta.Session.commit() if self._location_4326 is None: return None if ';' in self._location_4326: geom = wkb.loads(a2b_hex(self._location_4326.split(';')[-1])) geom.srid = 4326 return geom else: geom = wkb.loads(a2b_hex(self._location_4326)) geom.srid = 4326 return geom Almanac._location_4326 = column_property(func.st_transform(Almanac.location, 4326).label('_location_4326')) class Page(Base): __tablename__ = 'pages' id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey('users.id', onupdate='CASCADE', ondelete='CASCADE')) almanac_id = Column(Integer, ForeignKey('almanacs.id', onupdate='CASCADE', ondelete='CASCADE')) name = Column(Unicode) slug = Column(String) on_behalf_of = Column(Unicode) published = Column(Boolean, nullable=False) creation = Column(DateTime, server_default=func.current_timestamp()) # The auto-update field below doesn't do too much, because we almost always # update attached items, not the object itself.