Example #1
0
 def distance(cls, latitude, longitude):
     earth_radius = 6371
     pi_converter = func.pi() / 180
     dlat = (latitude - cls.latitude) * pi_converter
     dlon = (longitude - cls.longitude) * pi_converter
     lat1 = (cls.latitude) * pi_converter
     lat2 = (latitude) * pi_converter
     haversine = func.sin(dlat/2) * func.sin(dlat/2) + func.sin(dlon/2) * func.sin(dlon/2) * func.cos(lat1) * func.cos(lat2)
     return earth_radius * 2 * func.atan2(func.sqrt(haversine), func.sqrt(1-haversine))
Example #2
0
def search_location():
    latitude = request.args['latitude']
    longitude = request.args['longitude']
    radius = int(request.args['radius'])
    primary = True if (request.args['primary'] == 'primary') else False
    page = int(request.args.get('page', '1'))
    format = request.args.get('format', 'html')

    radius_m = radius * 1000

    wkt = "POINT(%s %s)" % (longitude, latitude)
    location = WKTSpatialElement(wkt)

    loc = sql.cast(LocationBase.location, Geography)
    q_loc = sql.cast(location, Geography)

    query = CadorsReport.query.join(LocationRef).join(LocationBase).filter(
        functions.within_distance(loc, q_loc, radius_m))

    if primary:
        query = query.filter(LocationRef.primary == True)

    if format == 'html':
        query = query.add_column(functions.distance(loc, q_loc).label('distance'))
        query = query.add_column(
            func.ST_Azimuth(location,
                            LocationBase.location.RAW) * (180/func.pi()))
        query = query.add_column(LocationBase.name)
        query = query.order_by('distance ASC',
                               CadorsReport.timestamp.desc())
        pagination = query.paginate(page)

        response = make_response(
            render_template('sr_loc.html',
                            reports=pagination.items, pagination=pagination,
                            get_direction=get_direction, radius=radius,
                            latitude=latitude, longitude=longitude))
        return prepare_response(response, 300)
    else:
        pagination = query.paginate(page)
        title = "Events within %s km of %s, %s" % (radius, latitude,
                                                   longitude)
        return render_list(pagination, title, format)
Example #3
0
##Step 4. Dealing with Duplicates
from sqlalchemy import distinct

print(session.query(Customer.town).filter(Customer.id < 10).all())
print(session.query(Customer.town).filter(Customer.id < 10).distinct().all())

print(
    session.query(func.count(distinct(Customer.town)),
                  func.count(Customer.town)).all())

##Step 5. Casting
from sqlalchemy import cast, Date, distinct, union

print(
    session.query(
        cast(func.pi(), Integer),
        cast(
            func.pi(),
            String(200),
            cast("2010-12-01", DateTime),
            cast("2010-12-01", Date),
        ).all()))

##Step 6. Unions
s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
print(s1.union(s2).all())

##Step  7. Updating Data
i = session.query(Item).get(8)
i.selling_price = 25.91
Example #4
0
    print("Name: ", i.first_name, " ", i.last_name, " Address:", i.address, " Email:", i.email)

#Dealing with Duplicates
from sqlalchemy import distinct
print(session.query(Customer.town).filter(Customer.id  < 10).all())
print(session.query(Customer.town).filter(Customer.id  < 10).distinct().all())

print(session.query(
    func.count(distinct(Customer.town)),
    func.count(Customer.town)
).all())

#Casting
from sqlalchemy import cast, Date, distinct, union
session.query(
    cast(func.pi(), Integer),
    cast(func.pi(), Numeric(10, 2)),
    cast("2010-12-01", DateTime),
    cast("2010-12-01", Date),
all()
)


# Unions
s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
print(s1.union(s2).all())


# Union All
print(s1.union_all(s2).all())