def main():
LOAD_CACHES = True
start = time()
for k, v in OPENSITES.items():
print 'OPENSITE', k
geosite = Geosite.objects.get(code=k)
for rec in v.get('RECTANGLES'):
process(rec, geosite, v)
log(v.get('log_key'), 'OK')
sql = """
select COUNT(*)
FROM
(
select g.code as code, count(id) as cnt
from geothing g
group by g.code
having cnt > 1
) as tbl
"""
dc = sql2val(sql)
message = 'doubles %s' % dc
log('map_opencaching', message)
elapsed = time() - start
print "Elapsed time -->", elapsed
def set_points(self):
sql = """
select sum(IFNULL(cs.points, 0)) as points_sum
from log_seek_cach lsc
left join cach_stat cs on lsc.cach_pid = cs.cach_pid
where lsc.cacher_uid = %s
""" % self.geocacher_uid
self.points = sql2val(sql)
self.save()
def avg_caches_per_month(self):
sql = """
SELECT AVG(cnt)
FROM (
SELECT YEAR(found_date), MONTH(found_date), COUNT(id) as cnt
FROM log_seek_cach
WHERE cacher_uid=%s
GROUP BY YEAR(found_date), MONTH(found_date)
) as tbl
""" % self.uid
return sql2val(sql)
def most_created_one_month(self):
sql = """
SELECT MAX(cnt)
FROM (
SELECT YEAR(created_date), MONTH(created_date), COUNT(id) as cnt
FROM log_create_cach
WHERE author_uid=%s
GROUP BY YEAR(created_date), MONTH(created_date)
) as tbl
""" % self.uid
return sql2val(sql)
def main():
start = time()
yplib.setUp()
yplib.set_debugging(False)
geosite = Geosite.objects.get(code='OCDE')
countries = GeoCountry.objects.all()
countries = countries.values_list('iso', flat=True)
sql = """
SELECT `value`
FROM variables
WHERE `name`='last_ocde_updated'
"""
lastdate = sql2val(sql);
if not lastdate:
lastdate = '20000101000000'
statuses = []
types = []
oc_count = 0
gc_count = 0
nc_count = 0
k = 0
uc = 0
nc = 0
for country in countries:
url = 'http://opencaching.de/xml/ocxml11.php?modifiedsince=%s&cache=1&country=%s' % \
(lastdate, country)
response = urllib2.urlopen(url)
xml = response.read()
try:
root = ET.XML(xml)
except Exception as e:
print 'PARSING ERROR', country, e
continue
# session id
current_session = root[0]
session_id = current_session.text
# count
records = root[1]
caches_count = int(records.get("cache") or 0)
if caches_count:
page_count = int(round(caches_count * 1.0 / CACHES_PER_PAGE, 0)) + 1
for p in range(page_count):
page_url = 'http://www.opencaching.de/xml/ocxml11.php?sessionid=%s&file=%s' % \
(session_id, p + 1)
page_response = urllib2.urlopen(page_url).read()
from StringIO import StringIO
zipdata = StringIO()
zipdata.write(page_response)
try:
zf = zipfile.ZipFile(zipdata)
except:
continue
for name in zf.namelist():
uncompressed = zf.read(name)
cache_root = ET.XML(uncompressed)
latitude = None
longitude = None
status = None
created_date_str = ''
for cache in cache_root.getchildren():
k += 1
if cache.tag == 'cache':
the_geothing = TheGeothing()
the_location = TheLocation()
for param in cache:
if param.tag == 'id':
the_geothing.pid = param.get('id')
if param.tag == 'userid':
the_geothing.author = param.text
if param.tag == 'name':
the_geothing.name = param.text
if param.tag == 'longitude':
longitude = param.text
if param.tag == 'latitude':
latitude = param.text
if param.tag == 'type':
cache_type = param.get('short')
the_geothing.type_code = OCDE_TYPES.get(cache_type)
type_ = (param.get('id'), param.get('short'))
if not type_ in types:
types.append(type_)
if param.tag == 'status':
status = int(param.get('id') or 0)
status_ = (status, param.text)
if not status_ in statuses:
statuses.append(status_)
if param.tag == 'waypoints':
the_geothing.code = param.get('oc')
if the_geothing.code:
oc_count += 1
gccode = param.get('gccom')
if gccode:
gc_count += 1
nccode = param.get('nccom')
if nccode:
nc_count += 1
if param.tag == 'datecreated':
created_date_str = param.text
parts = strptime(created_date_str, '%Y-%m-%d %H:%M:%S')
dt = datetime(parts[0], parts[1], parts[2],
parts[3], parts[4], parts[5])
the_geothing.created_date = dt
if latitude and longitude and status == 1:
the_location.NS_degree = float(latitude)
the_location.EW_degree = float(longitude)
if the_geothing.code and the_geothing.type_code in GEOCACHING_ONMAP_TYPES:
geothing = get_object_or_none(Geothing, pid=the_geothing.pid, geosite=geosite)
if geothing is not None:
uc += update_geothing(geothing, the_geothing, the_location) or 0
else:
create_new_geothing(the_geothing, the_location, geosite)
nc += 1
message = 'OK. updated %s, new %s' % (uc, nc)
log('map_ocde_caches', message)
print message
sql = """
UPDATE `variables`
SET `value`='%s'
WHERE `name`='last_ocde_updated'
""" % ocde_timestamp()
execute_query(sql)
elapsed = time() - start
print "Elapsed time -->", elapsed
def main(processed_pid):
LOAD_GEO_LOCATION = True
LOAD_GOOGLE_LOCATION = True
start = time()
sxml = None
if LOAD_GEO_LOCATION:
#.filter(pid=5408)
#for cach in Cach.objects.all().filter(pid__gt=processed_pid).order_by('pid')[:1990]:
for geokret in GeoKret.objects.all().filter(
country_code__isnull=True,
location__isnull=False,
state__in=[0, 3]).order_by('gkid')[:200]:
lat = geokret.latitude_degree
lng = geokret.longitude_degree
if lat is not None and lng is not None:
cnt = 1
r = 10
admin_code = None
country_code = None
while cnt < 2:
url = 'http://api.geonames.org/countrySubdivision?username=galdor&lat=%s&lng=%s&lang=en&radius=%d' % (
lat, lng, r * cnt)
#print
#print geokret.gkid, url
f = urllib2.urlopen(url)
xml = f.read()
try:
sxml = ET.XML(xml)
except Exception as e:
print type(e)
print e
sub = sxml.getchildren()[0]
if sub.tag == 'countrySubdivision':
#print sub.tag
#print xml
if sub:
for node in sub.getchildren():
if node.tag == 'adminCode1':
txt = node.text
if txt:
admin_code = txt.encode('utf8')
if node.tag == 'countryCode':
txt = node.text
if txt:
country_code = txt.encode('utf8')
if admin_code and country_code:
break
cnt += 1
if admin_code and country_code:
geokret.admin_code = admin_code
geokret.country_code = country_code
geokret.save()
else:
print geokret.gkid, lat, lng, geokret.location.NS, geokret.location.NS_degree, geokret.location.NS_minute, geokret.location.EW, geokret.location.EW_degree, geokret.loc_EW_minute
if LOAD_GOOGLE_LOCATION:
for geokret in GeoKret.objects.all().filter(
country_code__isnull=True,
location__isnull=False,
state__in=[0, 3]).order_by('gkid')[:200]:
lat = geokret.latitude_degree
lng = geokret.longitude_degree
if lat is not None and lng is not None:
admin_code = None
country_code = None
country_name = None
url = 'http://maps.google.com/maps/geo?q=%s,%s&output=xml&sensor=false' % (
lat, lng)
f = urllib2.urlopen(url)
xml = f.read()
try:
sxml = ET.XML(xml)
except Exception as e:
print type(e)
print e
sub = sxml.getchildren()[0]
if sub.tag == '{http://earth.google.com/kml/2.0}Response':
if sub:
for node in sub.getchildren():
if node.tag == '{http://earth.google.com/kml/2.0}Placemark':
for anode in node.getchildren():
if anode.tag == '{urn:oasis:names:tc:ciq:xsdschema:xAL:2.0}AddressDetails':
for a_node in anode.getchildren():
if a_node.tag == '{urn:oasis:names:tc:ciq:xsdschema:xAL:2.0}Country':
for thenode in a_node.getchildren(
):
if thenode.tag == '{urn:oasis:names:tc:ciq:xsdschema:xAL:2.0}CountryNameCode':
txt = thenode.text
if txt:
country_code = txt.encode(
'utf8')
if thenode.tag == '{urn:oasis:names:tc:ciq:xsdschema:xAL:2.0}CountryName':
txt = thenode.text
if txt:
country_name = txt.encode(
'utf8')
if thenode.tag == '{urn:oasis:names:tc:ciq:xsdschema:xAL:2.0}AdministrativeArea':
txt = thenode.text
if txt:
admin_code = txt.encode(
'utf8')
if country_code:
#geokret.admin_code = admin_code
geokret.country_code = country_code
geokret.save()
#print lat, lng, country_code, country_name, admin_code
else:
print lat, lng, country_code, country_name, admin_code
else:
print geokret.gkid, lat, lng, geokret.location.NS, geokret.location.NS_degree, geokret.location.NS_minute, geokret.location.EW, geokret.location.EW_degree, geokret.loc_EW_minute
sql = """
SELECT COUNT(*)
FROM geokret
WHERE country_code IS NULL AND
location_id IS NOT NULL AND
state IN (0, 3)
"""
undef_country_count = sql2val(sql)
print "Count of geokrets with undefined country is %s" % undef_country_count
elapsed = time() - start
print "Elapsed time -->", elapsed
def main(processed_pid):
#if not switch_off_status_updated():
#return False
LOAD_GEO_LOCATION = True
start = time()
if LOAD_GEO_LOCATION:
#.filter(pid=5408)
#for cach in Cach.objects.all().filter(pid__gt=processed_pid).order_by('pid')[:1990]:
for cach in Cach.objects.all().extra(where=["country_code IS NULL OR admin_code IS NULL OR admin_code='777'"]).order_by('pid')[:1000]:
lat = cach.latitude_degree
lng = cach.longitude_degree
if lat is not None and lng is not None:
d = ((0,0), (0.01,0), (-0.01,0), (0,0.01), (0,-0.01))
cnt = 0
while cnt < 5:
url = 'http://api.geonames.org/countrySubdivision?username=galdor&lat=%s&lng=%s&lang=en' % (lat+d[cnt][0], lng+d[cnt][1])
print
print cach.pid, url
yplib.get(url)
try:
soup = yplib.soup()
except:
url = 'http://api.geonames.org/countrySubdivision?username=galdor&lat=%s&lng=%s&lang=en' % (lat+d[cnt][0], lng+d[cnt][1])
yplib.get(url)
try:
soup = yplib.soup()
except:
soup = None
if soup:
item = soup.find('countrysubdivision')
if item:
break
cnt += 1
if soup is None:
print cach.pid, lat, lng, cach.loc_NS, cach.loc_NS_degree, cach.loc_NS_minute, cach.loc_EW, cach.loc_EW_degree, cach.loc_EW_minute
continue
item = soup.find('countrycode')
if item and item.text:
cach.country_code = item.text.encode('utf8')
if soup.admincode1 and soup.admincode1.text:
cach.admin_code = soup.admincode1.text
item = soup.find('code', {'type':'FIPS10-4'})
if item:
cach.code_fips10_4 = item.text
item = soup.find('code', {'type':'ISO3166-2'})
if item:
cach.code_iso3166_2 = item.text
item = soup.find('countryname')
if item:
cach.country_name = item.text.encode('cp1251')
if soup.adminname1:
cach.oblast_name = soup.adminname1.text.encode('cp1251')
#print cach.pid, cach.country_name, cach.country_code, cach.oblast_name
#print soup
#print
#print cach.pid
if cach.country_code and len(cach.country_code) == 2:
cach.save()
else:
print cach.pid, lat, lng, cach.loc_NS, cach.loc_NS_degree, cach.loc_NS_minute, cach.loc_EW, cach.loc_EW_degree, cach.loc_EW_minute
count_without_country = Cach.objects.filter(country_code__isnull=True).count()
count_without_subject = Cach.objects.filter(admin_code__isnull=True).count()
print '%s have no country' % count_without_country
print '%s have no country subject' % count_without_subject
sql = "UPDATE cach SET admin_code='777', oblast_name='undefined subject' WHERE country_code IS NOT NULL AND admin_code IS NULL"
r = execute_query(sql)
sql = """SELECT COUNT(*) FROM cach WHERE country_code IS NULL"""
undefined_country_count = sql2val(sql)
sql = """SELECT COUNT(*) FROM cach WHERE admin_code IS NULL OR admin_code = '777'"""
undefined_subject_count = sql2val(sql)
undefined_count = '%s/%s' % (undefined_country_count, undefined_subject_count)
elapsed = time() - start
print "Elapsed time -->", elapsed
#switch_on_status_updated()
log('gcsu_location', 'OK %s'%undefined_count)
def handle(self, *args, **options):
url = 'https://www.shukach.com/ru/karta?destination=karta'
with requests.Session() as session:
r = session.post(url,
data={
'name': 'gps-fun',
'pass': '******',
'form_id': 'user_login_block',
}).text
if not 'gps-fun' in r:
print('Autorization failed')
return
sql = """
DELETE FROM _temp_geothing
"""
execute_query(sql)
all_points_count = 0
updated_things = 0
updated_points = 0
new_count = 0
removed = []
geosite = Geosite.objects.get(code='SHUKACH')
for k in range(100):
ids = range(k * 1000, (k + 1) * 1000)
ids_str = ','.join([str(id) for id in ids])
url = 'https://www.shukach.com/export_wpt'
r = session.post(url, data={'wptnids': ids_str}).text
wpt = r.split('\n')
print(k, len(wpt))
if len(wpt) < 6:
continue
for point in wpt:
# print(point)
# print
pid = code = None
name = ''
created_date = None
author = type_code = ''
NS_degree = EW_degree = None
fields = point.split(',')
if len(fields) > WPT_TITLE and fields[0].isdigit():
all_points_count += 1
p = re.compile('(\D+)(\d+)')
code = fields[WPT_CODE]
dgs = p.findall(code)
if dgs:
type_code = dgs[0][0]
pid = int(dgs[0][1])
if type_code in GEOCACHING_ONMAP_TYPES:
NS_degree = float(fields[WPT_LAT])
EW_degree = float(fields[WPT_LON])
p = re.compile(r'(.+)от(.+)')
dgs = p.findall(fields[WPT_TITLE])
if dgs:
title = dgs[0]
name = title[0].strip()
author = title[1].strip()
else:
name = fields[WPT_TITLE]
d = float(fields[WPT_DATE])
created_date = Dephi_date_to_python_date(d)
date_str = created_date.strftime(
'%Y-%m-%d %H:%M')
ns_str = '{0:.9}'.format(NS_degree)
ew_str = '{0:.9}'.format(EW_degree)
sql = """
INSERT INTO _temp_geothing
(pid, code, name, created_date, author,
type_code, NS_degree, EW_degree)
VALUES
({},'{}','{}','{}', '{}', '{}', {}, {})
""".format(pid, code, name.replace("'", "\\'"),
date_str, author, type_code, ns_str,
ew_str)
execute_query(sql)
sql = "SELECT id FROM geosite WHERE code='SHUKACH'"
shukach_id = sql2val(sql)
# update existent geothings
sql = """
UPDATE geothing gt
LEFT JOIN _temp_geothing as t
ON gt.pid=t.pid
SET gt.created_date=t.created_date,
gt.name=t.name,
gt.author=t.author,
gt.type_code=t.type_code
WHERE gt.geosite_id={} AND
t.code IS NOT NULL AND
(gt.name != t.name OR
gt.author != t.author OR
gt.type_code != t.type_code)
""".format(shukach_id)
#print sql
updated_things = exec_sql(sql)
sql = """
UPDATE location as l
LEFT JOIN geothing as gt ON l.id=gt.location_id
LEFT JOIN _temp_geothing as t
ON gt.pid=t.pid
SET l.NS_degree=t.NS_degree,
l.EW_degree=t.EW_degree
WHERE gt.geosite_id={} AND
t.code IS NOT NULL AND
((ABS(l.NS_degree - t.NS_degree) > 0.00001) OR
(ABS(l.EW_degree - t.EW_degree) > 0.00001))
""".format(shukach_id)
updated_points = exec_sql(sql)
# list of id of removed geothings
sql = """
SELECT gt.id
FROM geothing gt
LEFT JOIN _temp_geothing as t
ON gt.pid=t.pid
WHERE gt.geosite_id={} AND t.code IS NULL
""".format(shukach_id)
removed = sql2table(sql)
new_count = 0
# insert new geothings
sql = """
SELECT t.pid, t.code, t.name, t.created_date, t.author,
t.country_code, t.type_code, t.NS_degree, t.EW_degree
FROM _temp_geothing as t
LEFT JOIN geothing gt ON gt.pid=t.pid AND gt.geosite_id={}
WHERE gt.pid IS NULL
""".format(shukach_id)
cursor = get_cursor(sql)
while True:
row = cursor.fetchone()
if row is None:
break
else:
sql = """
INSERT INTO location
(NS_degree, EW_degree)
VALUES
({}, {})
""".format(row[7], row[8])
execute_query(sql)
sql = "SELECT LAST_INSERT_ID()"
location_id = sql2val(sql)
sql = """
INSERT INTO geothing
(geosite_id, pid, code, name, created_date, author,
type_code, location_id, admin_code)
SELECT {}, t.pid, t.code, t.name, t.created_date, t.author,
t.type_code, {}, '777'
FROM _temp_geothing as t
WHERE t.pid={}
""".format(shukach_id, location_id, row[0])
execute_query(sql)
new_count += 1
message = 'OK. %s waypoints, updated %s waypoints, updated %s locations, new %s, removed %s' % (
all_points_count, updated_things or 0, updated_points
or 0, new_count, len(removed))
print(message)
log('map_shukach', message)
return 'List of caches from geocaching.su has updated'
def main(processed_pid):
LOAD_GEO_LOCATION = True
LOAD_GOOGLE_LOCATION = True
start = time()
if LOAD_GEO_LOCATION:
for thing in Geothing.objects.all().extra(where=["country_code IS NULL OR admin_code IS NULL OR admin_code='777'"]).order_by('pid')[:100]:
lat = thing.latitude_degree
lng = thing.longitude_degree
if lat is not None and lng is not None:
cnt = 1
r = 10
admin_code = None
while cnt < 2:
url = 'http://api.geonames.org/countrySubdivision?username=galdor&lat=%s&lng=%s&lang=en&radius=%d' % (lat, lng, r*cnt)
yplib.get(url)
try:
soup=yplib.soup()
except:
pass
if soup:
item = soup.find('countrysubdivision')
if item:
if soup.admincode1:
admin_code = soup.admincode1.text
if admin_code:
break
cnt += 1
item = soup.find('countrycode')
if item and item.text:
thing.country_code = item.text.encode('utf8')
if soup.admincode1:
thing.admin_code = soup.admincode1.text
item = soup.find('countryname')
if item:
thing.country_name = item.text
if soup.adminname1:
thing.oblast_name = soup.adminname1.text
if thing.country_code and len(thing.country_code)==2:
thing.save()
else:
print 'no location', thing.pid, lat, lng, thing.location.NS, thing.location.NS_degree, thing.location.NS_minute, thing.location.EW, thing.location.EW_degree, thing.loc_EW_minute
if LOAD_GOOGLE_LOCATION:
for thing in Geothing.objects.all().extra(where=["country_code IS NULL OR country_name IS NULL OR admin_code IS NULL OR admin_code='777'"]).order_by('pid')[:100]:
lat = thing.latitude_degree
lng = thing.longitude_degree
if lat is not None and lng is not None:
admin_name = None
country_code = None
country_name = None
admin_code = None
url = 'http://maps.googleapis.com/maps/api/geocode/json?latlng=%s,%s&sensor=false' % (lat, lng)
f = urllib2.urlopen(url)
data = f.read()
try:
r = json.loads(data)
except Exception as e:
print type(e)
print e
if r.get('status') == 'OK' and len(r.get('results')):
for result in r.get('results'):
if len(result.get('address_components')):
for address in result.get('address_components'):
types = address.get("types")
if "country" in types and "political" in types:
country_code = address.get("short_name")
if "administrative_area_level_1" in types and "political" in types:
admin_name = address.get("short_name")
if len(admin_name) < 6:
admin_name = address.get("long_name")
if country_code:
thing.country_code = country_code
thing.oblast = admin_name
thing.admin_code = get_admin_code_by_name(country_code, admin_name)
thing.save()
else:
print lat, lng, country_code, country_name, admin_name
else:
print thing.pid, lat, lng, thing.location.NS, thing.location.NS_degree, thing.location.NS_minute, thing.location.EW, thing.location.EW_degree, thing.loc_EW_minute
sql = """
UPDATE geothing gt
LEFT JOIN oblast_subject os ON (
gt.country_code=os.country_iso and gt.oblast=os.oblast
)
SET gt.admin_code=os.code
WHERE os.id IS NOT NULL
"""
r = execute_query(sql)
sql = """
UPDATE geothing
SET admin_code='777',
oblast_name='undefined subject'
WHERE country_code IS NOT NULL AND admin_code IS NULL
"""
r = execute_query(sql)
sql = """
update geothing gt
left join geo_country c on gt.country_code=c.iso
set gt.country_name=c.name
"""
r = execute_query(sql)
sql = """
update geothing gt
left join geo_country_subject c on gt.admin_code=c.code and gt.country_code=c.country_iso
set gt.oblast_name=c.name
where gt.admin_code='777'
"""
r = execute_query(sql)
sql = """
update geothing
set country_code='RU',
admin_code='82',
country = 'Россия',
oblast = 'Республика Крым',
country_name = 'Russia',
oblast_name = 'Respublika Krym'
where country_code='UA' and admin_code='11'
"""
r = execute_query(sql)
sql = """SELECT COUNT(*) FROM geothing WHERE country_code IS NULL"""
undefined_country_count = sql2val(sql)
sql = """SELECT COUNT(*) FROM geothing WHERE admin_code IS NULL OR admin_code = '777'"""
undefined_subject_count = sql2val(sql)
undefined_count = '%s/%s' % (undefined_country_count, undefined_subject_count)
log('map_set_location', 'OK %s'%undefined_count)
elapsed = time() - start
print "Elapsed time -->", elapsed
def main(processed_pid):
LOAD_GEO_LOCATION = True
LOAD_GOOGLE_LOCATION = True
start = time()
sxml = None
if LOAD_GEO_LOCATION:
#.filter(pid=5408)
#for cach in Cach.objects.all().filter(pid__gt=processed_pid).order_by('pid')[:1990]:
for geokret in GeoKret.objects.all().filter(country_code__isnull=True, location__isnull=False, state__in=[0, 3]).order_by('gkid')[:200]:
lat = geokret.latitude_degree
lng = geokret.longitude_degree
if lat is not None and lng is not None:
cnt = 1
r = 10
country_code = None
while cnt < 2:
url = 'http://api.geonames.org/countrySubdivision?username=galdor&lat=%s&lng=%s&lang=en&radius=%d' % (lat, lng, r*cnt)
f = urllib2.urlopen(url)
xml = f.read()
try:
sxml = ET.XML(xml)
except Exception as e:
print type(e)
print e
sub = sxml.getchildren()[0]
if sub.tag == 'countrySubdivision':
if sub is not None:
for node in sub.getchildren():
if node.tag == 'countryCode':
txt = node.text
if txt:
country_code = txt.encode('utf8')
if country_code:
break
cnt += 1
if country_code:
geokret.country_code = country_code
geokret.save()
else:
print geokret.gkid, lat, lng, geokret.location.NS, geokret.location.NS_degree, geokret.location.NS_minute, geokret.location.EW, geokret.location.EW_degree, geokret.loc_EW_minute
if LOAD_GOOGLE_LOCATION:
for geokret in GeoKret.objects.all().filter(country_code__isnull=True, location__isnull=False, state__in=[0, 3]).order_by('gkid')[:200]:
lat = geokret.latitude_degree
lng = geokret.longitude_degree
if lat is not None and lng is not None:
admin_code = None
country_code = None
country_name = None
url = 'http://maps.googleapis.com/maps/api/geocode/json?latlng=%s,%s&sensor=false' % (lat, lng)
f = urllib2.urlopen(url)
data = None
json_str = f.read()
try:
data = json.loads(json_str)
if 'status' in data:
if data['status'] == 'OK':
if 'results' in data:
for r in data['results']:
for address in r.get('address_components'):
if 'country' in address.get('types'):
_country_code = address.get('short_name')
if len(_country_code) == 2:
country_code = _country_code
break
else:
print 'no results', url
else:
print geokret.id, lat, lng, 'status', data['status']
else:
print json_str
except Exception as e:
print type(e)
print e
if country_code:
geokret.admin_code = admin_code
geokret.country_code = country_code
geokret.save()
print lat, lng, country_code, country_name, admin_code
else:
print lat, lng, country_code, country_name, admin_code
else:
print geokret.gkid, lat, lng, geokret.location.NS, geokret.location.NS_degree, geokret.location.NS_minute, geokret.location.EW, geokret.location.EW_degree, geokret.loc_EW_minute
sql = """
SELECT COUNT(*)
FROM geokret
WHERE country_code IS NULL AND
location_id IS NOT NULL AND
state IN (0, 3)
"""
undef_country_count = sql2val(sql)
print "Count of geokrets with undefined country is %s" % undef_country_count
elapsed = time() - start
print "Elapsed time -->", elapsed
def main():
LOAD_CACHES = True
start = time()
yplib.setUp()
yplib.set_debugging(False)
# log in
r = yplib.post2('http://opencaching.pl/login.php',
(('LogMeIn', 'zaloguj'), ('email', 'kurianin'),
('password', 'gjhjkjy'), ('action', 'login'),
('target', 'index.php')))
soup = yplib.soup()
a = soup.find('a', text='kurianin')
if not a:
print 'Authorization failed'
return False
print 'OK'
## search page
#r = yplib.get('http://opencaching.pl/search.php')
#soup = yplib.soup()
# get wpt file
r = yplib.get(
'http://opencaching.pl/search.php?searchto=searchbyname&showresult=1&expert=0&output=HTML&sort=bycreated&f_inactive=1&f_ignored=1&f_userfound=1&f_userowner=1&f_watched=0&f_geokret=0&country=PL®ion=&cachetype=1111111110&cache_attribs=&cache_attribs_not=&cachesize_1=1&cachesize_2=1&cachesize_3=1&cachesize_4=1&cachesize_5=1&cachesize_6=1&cachesize_7=1&cachevote_1=-3&cachevote_2=3.000&cachenovote=1&cachedifficulty_1=1&cachedifficulty_2=5&cacheterrain_1=1&cacheterrain_2=5&cacherating=0&cachename=%25&cachename='
)
soup = yplib.soup(cp='utf8')
link_to_wpt = ''
#the_div = soup.find('div', {'class':"content2-pagetitle"})
wpt_link = re.compile('ocpl\d+\.wpt\?.+count\=max.*')
a_list = soup.findAll('a', {'class': "links", 'title': "Oziexplorer .wpt"})
if a_list:
for a in a_list:
if a.get('href') and wpt_link.match(a.get('href')):
link_to_wpt = a.get('href')
break
print link_to_wpt
if link_to_wpt:
r = yplib.get(link_to_wpt)
soup = yplib.soup(cp='utf8')
wpt = soup.text.split('\n')
else:
print 'oblom'
return
WPT_CODE = 10
WPT_LAT = 2
WPT_LON = 3
WPT_TITLE = 1
WPT_DATE = 4
MY_CONSUMER_KEY = 'fky3LF9xvWz9y7Gs3tZ6'
FIELDS = 'code|name|location|type|status|url|owner|date_created'
geocach_api_request = 'http://opencaching.pl/okapi/services/caches/geocache?cache_code=%s&consumer_key=%s&fields=%s'
geosite = Geosite.objects.get(code='OCPL')
print geosite
print len(wpt), 'points'
k = 0
uc = 0
nc = 0
for point in wpt:
k += 1
fields = point.split(',')
if fields[0] == '-1':
the_geothing = TheGeothing()
the_geothing.pid = 1
the_location = TheLocation()
lat_degree = float(fields[WPT_LAT])
the_location.NS_degree = lat_degree
#the_location.NS_minute = (abs(lat_degree) - abs(the_location.NS_degree)) * 60
lon_degree = float(fields[WPT_LON])
the_location.EW_degree = lon_degree
#the_location.EW_minute = (abs(lon_degree) - abs(the_location.EW_degree)) * 60
code_str = fields[WPT_CODE]
parts = code_str.split('/')
if len(parts) == 4:
cache_code = parts[0]
the_geothing.code = cache_code
the_geothing.name = fields[WPT_TITLE]
geothing_items = Geothing.objects.filter(
code=the_geothing.code, geosite=geosite)
if geothing_items.count() > 0:
geothing = geothing_items[0]
if the_geothing.name == geothing.name and not location_was_changed(
geothing.location, the_location):
continue
url = geocach_api_request % (cache_code, MY_CONSUMER_KEY,
FIELDS)
try:
response = urllib2.urlopen(url)
json_str = response.read()
cache_data = json.loads(json_str)
if cache_data.get('status') != 'Available':
continue
#print cache_data.get('type')
the_geothing.type_code = OCPL_TYPES.get(
cache_data.get('type'))
#print the_geothing.type_code
cache_url = cache_data.get('url')
if not cache_url:
continue
p = re.compile(u'OP([\dA-F]+)$')
dgs = p.findall(cache_url)
the_geothing.pid = int(dgs[0], 16)
owner_name = ''
if cache_data.get('owner'):
owner_name = cache_data.get('owner').get('username')
the_geothing.author = owner_name
date_created = cache_data.get('date_created')
if date_created:
date_created = date_created[:10]
parts = date_created.split('-')
if parts and len(parts) == 3:
dt = datetime(int(parts[0]), int(parts[1]),
int(parts[2]))
the_geothing.created_date = dt
except:
print
print 'exception.'
print url
print cache_data
#break
continue
if the_geothing.type_code in GEOCACHING_ONMAP_TYPES:
geothing = get_object_or_none(Geothing,
pid=the_geothing.pid,
geosite=geosite)
if geothing is not None:
update_geothing(geothing, the_geothing, the_location)
uc += 1
else:
create_new_geothing(the_geothing, the_location, geosite)
nc += 1
#break
sql = """
select COUNT(*)
FROM
(
select g.code as code, count(id) as cnt
from geothing g
group by g.code
having cnt > 1
) as tbl
"""
dc = sql2val(sql)
message = 'OK. updated %s, new %s, doubles %s' % (uc, nc, dc)
log('map_ocpl_caches', message)
elapsed = time() - start
print "Elapsed time -->", elapsed
def main():
WPT_CODE = 1
WPT_LAT = 2
WPT_LON = 3
WPT_TITLE = 10
WPT_DATE = 4
start = time()
geosite = Geosite.objects.get(code='SHUKACH')
yplib.setUp()
yplib.set_debugging(False)
r = yplib.post2('http://www.shukach.com/ru/karta?destination=karta',
(('form_build_id','form-ce43c02c68d4d8db1cb0e91745797d06'),
('name', 'gps-fun'),
('pass','vjlthybpfwbzwbz'),
('form_id', 'user_login_block')))
sql = """
DELETE FROM _temp_geothing
"""
execute_query(sql)
all_points_count = 0
for k in range(50):
ids = range(k*1000, (k+1)*1000)
#print k*1000, (k+1)*1000
ids_str = ','.join([str(id) for id in ids])
r = yplib.post2('http://www.shukach.com/export_wpt',
(('wptnids', ids_str), ))
wpt = yplib.cmd.show()
wpt = wpt.split('\n')
#print len(wpt)
if len(wpt) < 6:
continue
for point in wpt:
point = point.decode('cp1251').encode('utf-8')
pid = code = None
name = ''
created_date = None
author = type_code = ''
NS_degree = EW_degree = None
fields = point.split(',')
if fields[0].isdigit():
all_points_count += 1
p = re.compile('(\D+)(\d+)')
code = fields[WPT_CODE]
dgs = p.findall(code)
if dgs:
type_code = dgs[0][0]
pid = int(dgs[0][1])
if type_code in GEOCACHING_ONMAP_TYPES:
NS_degree = float(fields[WPT_LAT])
EW_degree = float(fields[WPT_LON])
p = re.compile(r'(.+)от(.+)')
dgs = p.findall(fields[WPT_TITLE])
if dgs:
title = dgs[0]
name = title[0].strip()
author = title[1].strip()
else:
name = fields[WPT_TITLE]
d = float(fields[WPT_DATE])
created_date = Dephi_date_to_python_date(d)
date_str = created_date.strftime('%Y-%m-%d %H:%M')
ns_str = '{0:.9}'.format(NS_degree)
ew_str = '{0:.9}'.format(EW_degree)
sql = """
INSERT INTO _temp_geothing
(pid, code, name, created_date, author,
type_code, NS_degree, EW_degree)
VALUES
({},'{}','{}','{}', '{}', '{}', {}, {})
""".format(
pid, code, name.replace("'", "\\'"),
date_str, author, type_code,
ns_str, ew_str)
execute_query(sql)
sql = "SELECT id FROM geosite WHERE code='SHUKACH'"
shukach_id = sql2val(sql)
# update existent geothings
sql = """
UPDATE geothing gt
LEFT JOIN _temp_geothing as t
ON gt.pid=t.pid
SET gt.created_date=t.created_date,
gt.name=t.name,
gt.author=t.author,
gt.type_code=t.type_code
WHERE gt.geosite_id={} AND
t.code IS NOT NULL AND
(gt.name != t.name OR
gt.author != t.author OR
gt.type_code != t.type_code)
""".format(shukach_id)
#print sql
updated_things = exec_sql(sql)
sql = """
UPDATE location as l
LEFT JOIN geothing as gt ON l.id=gt.location_id
LEFT JOIN _temp_geothing as t
ON gt.pid=t.pid
SET l.NS_degree=t.NS_degree,
l.EW_degree=t.EW_degree
WHERE gt.geosite_id={} AND
t.code IS NOT NULL AND
((ABS(l.NS_degree - t.NS_degree) > 0.00001) OR
(ABS(l.EW_degree - t.EW_degree) > 0.00001))
""".format(shukach_id)
updated_points = exec_sql(sql)
# list of id of removed geothings
sql = """
SELECT gt.id
FROM geothing gt
LEFT JOIN _temp_geothing as t
ON gt.pid=t.pid
WHERE gt.geosite_id={} AND t.code IS NULL
""".format(shukach_id)
removed = sql2table(sql)
new_count = 0
# insert new geothings
sql = """
SELECT t.pid, t.code, t.name, t.created_date, t.author,
t.country_code, t.type_code, t.NS_degree, t.EW_degree
FROM _temp_geothing as t
LEFT JOIN geothing gt ON gt.pid=t.pid AND gt.geosite_id={}
WHERE gt.pid IS NULL
""".format(shukach_id)
cursor = get_cursor(sql)
while True:
row = cursor.fetchone()
if row is None:
break
else:
sql = """
INSERT INTO location
(NS_degree, EW_degree)
VALUES
({}, {})
""".format(row[7], row[8])
execute_query(sql)
sql = "SELECT LAST_INSERT_ID()"
location_id = sql2val(sql)
sql = """
INSERT INTO geothing
(geosite_id, pid, code, name, created_date, author,
type_code, location_id, admin_code)
SELECT {}, t.pid, t.code, t.name, t.created_date, t.author,
t.type_code, {}, '777'
FROM _temp_geothing as t
WHERE t.pid={}
""".format(shukach_id, location_id, row[0])
execute_query(sql)
new_count += 1
message = 'OK. %s waypoints, updated %s waypoints, updated %s locations, new %s, removed %s' % (
all_points_count,
updated_things or 0,
updated_points or 0,
new_count,
len(removed))
print(message)
log('map_shukach', message)
elapsed = time() - start
print "Elapsed time -->", elapsed
return True