def countCitiesInViewPor self lat1 lng1 lat2 lng2 country None conditi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
def countCitiesInViewPort(self, lat1, lng1, lat2, lng2, country=None):
condition = self.getSpatialCondition(lat1, lng1, lat2, lng2)
if not country:
condition = "SELECT count(*) FROM cities_coordinates \
WHERE %s" % condition
else:
condition = """SELECT count(*) FROM cities_coordinates
JOIN cities on cities_coordinates.id=cities.id
WHERE (%s) AND cities.country="%s" """ % (condition, country)
res = self.site.dbConn.execute(Query(condition))
return res.fetchall()[0][0]
def getCitiesInViewPort(self, lat1, lng1, lat2, lng2,
cond=None, limit=30, page=1, country=None):
condition = self.getSpatialCondition(lat1, lng1, lat2, lng2)
if cond:
condition+=(" AND "+cond)
# use index(cities_trips_index)
offset = (page-1) * limit
if not country:
coord_select = "SELECT id, coord, trips FROM cities_coordinates \
WHERE %s ORDER BY trips DESC LIMIT %d" % (condition, limit)
else:
coord_select = """SELECT cities_coordinates.id, coord,
cities_coordinates.trips
FROM cities_coordinates INNER JOIN cities on cities_coordinates.id=cities.id
WHERE (%s) AND cities.country="%s"
ORDER BY trips DESC LIMIT %d, %d""" % (
condition, country, offset, limit)
res = self.site.dbConn.execute(Query(
"""SELECT cities.id as id, cities.title as title,
cities.title_ru as title_ru, cities.title_en as title_en,
x(_coord.coord) as longitude, y(_coord.coord) as latitude,
_coord.trips as trips, countries.title_ru as country_title,
cities.population,countries.title_en,cities.namesakes,
cities.beenHereCounter, cities.liveHereCounter
FROM (%s) as _coord JOIN cities ON _coord.id=cities.id
JOIN countries on cities.country=countries.id""" % coord_select))
return [self.getCityObject(id=row[0], title=row[1],
title_ru=self.convert_ru(row[2]),
title_en=self.convert_ru(row[3]),
longitude=row[4], latitude=row[5], trips=row[6],
country_title=self.convert_ru(row[7]), population=row[8],
country_title_en=row[9], namesakes=row[10], beenHereCounter=row[11],
liveHereCounter=row[12]) for row in res.fetchall()]