def getCityDirectEdgePairs(file):
#db = MySQLdb.connect('127.0.0.1', 'root', 'admin', 'pythondb', charset="gbk")
print mod_config.dbhost, mod_config.dbuser, mod_config.dbpassword, mod_config.dbname, mod_config.dbcharset
db = MySQLdb.connect(mod_config.dbhost,
mod_config.dbuser,
mod_config.dbpassword,
mod_config.dbname,
charset=mod_config.dbcharset)
cursor = db.cursor()
fs = codecs.open(file, 'w+', encoding='utf-8')
try:
mysql = 'select departure,city from citynote'
cursor.execute(mysql)
results = cursor.fetchall()
for row in results:
departure = row[0]
destination = row[1]
pair = departure + "," + destination
#print pair
if pair in directEdgeSet:
directEdgeMap[pair] += 1
else:
directEdgeMap[pair] = 1
directEdgeSet.add(pair)
sum = 0
for key, value in directEdgeMap.items():
pairs = key.strip('\r\n')
items = string.split(pairs, ',')
toponym1 = coords[items[0]]
toponym2 = coords[items[1]]
lat1 = toponym1['lat']
lng1 = toponym1['lng']
lat2 = toponym2['lat']
lng2 = toponym2['lng']
distance = geoutil.getPointDistance(lat1, lng1, lat2, lng2)
weight = 0
if distance > 0:
weight = value / distance
print items[0], toponym1, items[1], toponym2, distance
fs.write(key + "," + str(value) + "," + str(distance) + "," +
str(weight) + "\r\n")
# fs.write(key+","+str(value)+"\r\n")
#print key,value
sum += value
fs.flush()
fs.close()
print len(directEdgeMap), sum
except Exception, msg:
print msg
def getSpotDirectEdgePairs(file):
db = MySQLdb.connect(
mod_config.dbhost,
mod_config.dbuser,
mod_config.dbpassword,
mod_config.dbname,
charset=mod_config.dbcharset
) #'222.29.117.151', 'root', 'admin', 'pythondb', charset='utf8')
cursor = db.cursor()
fs = codecs.open(file, 'w+', encoding='utf8')
try:
mysql = 'select departure,city,spot,url from note limit 0,' + str(
MAXROW)
cursor.execute(mysql)
results = cursor.fetchall()
sqlindex = 1
directCitySpotEdgeMapUrls = set()
for row in results:
departure = row[0]
destination = row[1]
spot = row[1] + row[2]
url = row[3]
print "sqlindex=", sqlindex, url, departure, destination, spot
sqlindex += 1
pair1 = departure + "," + destination
pair11 = departure + "," + destination + "," + url
# departure city -> destination city
# print pair1
if pair1 in directCitySpotEdgeMap.keys():
if pair11 not in directCitySpotEdgeMapUrls:
directCitySpotEdgeMap[pair1] += 1
else:
directCitySpotEdgeMap[pair1] = 1
directCitySpotEdgeMapUrls.add(pair11)
# destination city -> destination spot
pair21 = destination + "," + spot
pair22 = spot + "," + destination
# print pair2
if pair21 in directSpotEdgeMap.keys():
directSpotEdgeMap[pair21] += 1
directSpotEdgeMap[pair22] += 1
else:
directSpotEdgeMap[pair21] = 1
directSpotEdgeMap[pair22] = 1
if url not in directSpot2SpotEdgeMap.keys():
directSpot2SpotEdgeMap[url] = {}
if destination not in directSpot2SpotEdgeMap[url].keys():
directSpot2SpotEdgeMap[url][destination] = set()
directSpot2SpotEdgeMap[url][destination].add(spot)
else:
if destination not in directSpot2SpotEdgeMap[url].keys():
directSpot2SpotEdgeMap[url][destination] = set()
directSpot2SpotEdgeMap[url][destination].add(spot)
else:
directSpot2SpotEdgeMap[url][destination].add(spot)
spotindex = 1
for key, value in directSpot2SpotEdgeMap.items():
spotSet = ()
spotKey = ''
for (k, v) in value.items():
spotKey = k
spotSet = v
lstValue = list(spotSet)
print "spotindex=", spotindex, key, spotKey
spotindex += 1
for i in range(0, len(lstValue)):
for j in range(i + 1, len(lstValue)):
pair31 = lstValue[i] + "," + lstValue[j]
pair32 = lstValue[j] + "," + lstValue[i]
#print pair31, pair32
if pair31 in directSpot2SpotEdgeMapWithWeight.keys():
directSpot2SpotEdgeMapWithWeight[pair31] += 1
directSpot2SpotEdgeMapWithWeight[pair32] += 1
elif pair32 in directSpot2SpotEdgeMapWithWeight.keys():
directSpot2SpotEdgeMapWithWeight[pair31] += 1
directSpot2SpotEdgeMapWithWeight[pair32] += 1
else:
directSpot2SpotEdgeMapWithWeight[pair31] = 1
directSpot2SpotEdgeMapWithWeight[pair32] = 1
dataindex = 1
sum = 0
for key, value in directCitySpotEdgeMap.items():
print 'dataindex=', dataindex, key, value
dataindex += 1
# key2 = key.rfind(',')
# key = key[0:key2]
pairs = key.strip('\r\n')
items = string.split(pairs, ',')
toponym1 = coords[items[0]]
toponym2 = coords[items[1]]
lat1 = toponym1['lat']
lng1 = toponym1['lng']
lat2 = toponym2['lat']
lng2 = toponym2['lng']
distance = geoutil.getPointDistance(lat1, lng1, lat2, lng2)
weight = 0
if distance > 0:
weight = value / distance
print items[0], toponym1, items[1], toponym2, distance
fs.write(key + "," + str(value) + "," + str(distance) + "," +
str(weight) + "\r\n")
#print key
#fs.write(key + "," + str(value) + "\r\n")
# print key, value
sum += value
for key, value in directSpotEdgeMap.items():
print 'dataindex=', dataindex, key, value
dataindex += 1
pairs = key.strip('\r\n')
items = string.split(pairs, ',')
toponym1 = coords.get(items[0])
toponym2 = coords.get(items[1])
if toponym1 is None:
print items[0], 'is None'
elif toponym2 is None:
print items[1], 'is None'
if toponym1 is not None and toponym2 is not None:
lat1 = toponym1.get('lat')
lng1 = toponym1.get('lng')
lat2 = toponym2.get('lat')
lng2 = toponym2.get('lng')
print items[0], items[1]
distance = geoutil.getPointDistance(lat1, lng1, lat2, lng2)
weight = 0
if distance > 0:
weight = value / distance
print items[0], toponym1, items[1], toponym2, distance
fs.write(key + "," + str(value) + "," + str(distance) + "," +
str(weight) + "\r\n")
# fs.write(key + "," + str(value) + "\r\n")
# print key, value
sum += value
for key, value in directSpot2SpotEdgeMapWithWeight.items():
print 'dataindex=', dataindex, key, value
dataindex += 1
pairs = key.strip('\r\n')
items = string.split(pairs, ',')
toponym1 = coords[items[0]]
toponym2 = coords[items[1]]
lat1 = toponym1['lat']
lng1 = toponym1['lng']
lat2 = toponym2['lat']
lng2 = toponym2['lng']
distance = geoutil.getPointDistance(lat1, lng1, lat2, lng2)
weight = 0
if distance > 0:
weight = value / distance
print items[0], toponym1, items[1], toponym2, distance
fs.write(key + "," + str(value) + "," + str(distance) + "," +
str(weight) + "\r\n")
# fs.write(key + "," + str(value) + "\r\n")
# print key, value
sum += value
fs.flush()
fs.close()
print len(directCitySpotEdgeMap), len(directSpotEdgeMap), len(
directSpot2SpotEdgeMapWithWeight
), len(directCitySpotEdgeMap) + len(directSpotEdgeMap) + len(
directSpot2SpotEdgeMapWithWeight), sum
except Exception, msg:
print msg
def computeGyration():
db = MySQLdb.connect(mod_config.dbhost,
mod_config.dbuser,
mod_config.dbpassword,
mod_config.dbname,
charset=mod_config.dbcharset)
cursor = db.cursor()
mysql = 'select distinct(uid) from user where finish = 0 limit 0,50000'
cursor.execute(mysql)
users = cursor.fetchall()
getCoords()
selectUserTravelSQL = 'select fromcity,toccity from citytravel where userid = %s'
updateUserSQL = 'update user set gyration = %s, visitcity = %s, finish = 1 where uid = "%s"'
#updateUserValues = []
index = 0
for user in users:
cursor.execute(selectUserTravelSQL, (user, ))
travels = cursor.fetchall()
travelCount = 0
gyrationSum = 0.0
for travel in travels:
fromcity = str(travel[0])
toccity = str(travel[1])
fromcity = fromcity.strip()
toccity = toccity.strip()
if fromcity == toccity:
continue
#print fromcity,toccity
if fromcity not in coords.keys() or toccity not in coords.keys():
continue
toponym1 = coords[fromcity]
toponym2 = coords[toccity]
lat1 = toponym1['lat']
lng1 = toponym1['lng']
lat2 = toponym2['lat']
lng2 = toponym2['lng']
distance = geoutil.getPointDistance(lat1, lng1, lat2, lng2)
travelCount += 1
gyrationSum += distance
now = int(time.time())
timeArray = time.localtime(now)
otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
if travelCount == 0:
#print index, 'user='******'travelCount = ', travelCount, ', gyrationSum=', gyrationSum
index += 1
updateUserSQL = 'update user set finish = 1 where uid = "%s"' % (
str(user[0]), )
cursor.execute(updateUserSQL)
db.commit()
continue
gyration = gyrationSum / travelCount
if index % 10 == 0:
print index, otherStyleTime, 'user='******'travelCount = ', travelCount, ', gyrationSum=', gyrationSum, 'gyration', gyration
#updateUserValues.append((gyration, travelCount, str(user[0])))
updateUserSQL = 'update user set finish = 1,gyration = %s, visitcity = %s where uid = "%s"' % (
gyration, travelCount, str(user[0]))
#print updateUserSQL
#if index % 10 == 0:
#cursor.executemany(updateUserSQL,updateUserValues)
cursor.execute(updateUserSQL)
db.commit()
#updateUserValues = []
index += 1
def gravityModel2(count, distance, file):
fs = codecs.open(file, 'w+', encoding='utf8')
fs.write("id;pi;pj;dij;fij" + "\r\n")
db = MySQLdb.connect(mod_config.dbhost,
mod_config.dbuser,
mod_config.dbpassword,
mod_config.dbname,
charset=mod_config.dbcharset)
cursor = db.cursor()
queryUserCountSQL = 'select cname,inflow,outflow from city'
cursor.execute(queryUserCountSQL)
rows = cursor.fetchall()
userCountMap = {}
inflowMap = {}
outflowMap = {}
for row in rows:
key = str(row[0])
userCountMap[key] = row[1]
inflowMap[key] = row[1]
outflowMap[key] = row[2]
getCoords()
flowMap = {}
index = 1
index1 = 1
index2 = 1
for city1 in allCities:
key1 = str(city1[0])
index2 = 1
flowMap = {}
flowSQL = "select toccity,count(*) from citytravel where fromcity = '" + key1 + "' group by toccity"
cursor.execute(flowSQL)
rows = cursor.fetchall()
for row in rows:
key = str(row[0])
flowMap[key] = row[1]
for city2 in allCities:
key2 = str(city2[0])
if key1 == key2:
continue
#pi = userCountMap[key1]
#pj = userCountMap[key2]
pi = inflowMap[key1]
pj = outflowMap[key2]
if pi < count or pj < count:
continue
toponym1 = coords[key1]
toponym2 = coords[key2]
lat1 = toponym1['lat']
lng1 = toponym1['lng']
lat2 = toponym2['lat']
lng2 = toponym2['lng']
dij = geoutil.getPointDistance(lat1, lng1, lat2, lng2)
# flowSQL = "select count(*) from citytravel where fromcity = '"+str(key1)+"' and toccity = '"+str(key2)+"'"
# print flowSQL
# cursor.execute(flowSQL)
# rows = cursor.fetchall()
# fij = rows[0][0]
if key2 in flowMap.keys():
fij = flowMap[key2]
if fij > 0:
print index, key1, key2, index1, index2, 'pi=', pi, 'pj=', pj, 'dij=', dij, 'fij=', fij
fs.write(
str(index) + ";" + str(pi) + ";" + str(pj) + ";" +
str(dij) + ";" + str(fij) + "\r\n")
index2 += 1
index += 1
index1 += 1
fs.flush()
fs.close()