def patch_it(name):
folder = settings.SCRIPTS_ROOT
f = open(folder+name, 'r')
text = f.read()
queries = text.split(';')
for sql in queries:
sql = sql.strip()
if not sql.startswith('SELECT') and not sql.startswith('select') and len(sql):
print
print sql
execute_query(sql)
def main():
start = time()
for cache in Cach.objects.all():
cache_stat, created = CachStat.objects.get_or_create(
cach=cache,
cach_pid=cache.pid)
cache_stat.calculate_points()
sql = """
insert into geocacher_search_stat
(geocacher_id, geocacher_pid, country, region)
select g.id, g.pid, c.name, gcs.name
from geocacher g
left join geocacher_search_stat gss
on g.pid = gss.geocacher_pid
left join geo_country c
on g.country_iso3 = c.iso3
left join geo_country_subject gcs
on c.iso = gcs.country_iso and g.admin_code = gcs.code
where gss.geocacher_pid is null
"""
execute_query(sql)
sql = """
update geocacher_search_stat gss
set
points=(
select ROUND(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_pid = gss.geocacher_pid
),
year_points=(
select ROUND(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 YEAR(lsc.found_date)=%s and
lsc.cacher_pid = gss.geocacher_pid
)
""" % date.today().year
execute_query(sql)
elapsed = time() - start
print "Elapsed time -->", elapsed
log('gcsu_rating', 'OK')
def main():
LOAD_CACHES = True
start = time()
GeoKret.objects.all().delete()
file = '/tmp/export2-full.xml'
# sanity checking, only work on wpt files
if file.endswith('.xml') == 0: sys.exit(-1)
print "Reading file: " + file
fh = open(file, 'r')
xml = fh.read()
fh.close()
sxml = ET.XML(xml)
all_krety = sxml.getchildren()[0]
for kret in all_krety:
geokret = GeoKret()
geokret.gkid = int(kret.get('id') or 0)
geokret.name = kret.text
geokret.distance = int(kret.get('dist') or 0)
location = Location()
if kret.get('lat') and kret.get('lon'):
location.NS_degree = float(kret.get('lat'))
location.EW_degree = float(kret.get('lon'))
location.save()
else:
location = None
geokret.location = location
geokret.waypoint = kret.get('waypoint')
geokret.owner_id = int(
kret.get('owner_id')) if kret.get('owner_id') else None
geokret.state = int(kret.get('state')) if kret.get('state') else None
type_ = kret.get('type') if len(kret.get('type')) else '0'
geokret.type_code = int(type_)
geokret.image = kret.get('image')
geokret.save()
#print kret.get('id'), kret.text, kret.get('dist'), kret.get('lat'), kret.get('lon'), kret.get('waypoint'), kret.get('owner_id'), kret.get('state'), kret.get('type'), kret.get('image')
# set country and admin subject
sql = """
UPDATE geokret gk
LEFT JOIN geothing t ON gk.waypoint = t.code
SET gk.country_code = t.country_code, gk.admin_code = t.admin_code
WHERE gk.waypoint IS NOT NULL AND gk.waypoint != '' AND t.id IS NOT NULL
"""
r = execute_query(sql)
elapsed = time() - start
print "Elapsed time -->", elapsed
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):
#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 main():
LOAD_CACHES = True
#LOAD_GEO_LOCATION = False
start = time()
sql = """
DELETE location
FROM location
LEFT JOIN geothing ON geothing.location_id=location.id
LEFT JOIN geosite ON geothing.geosite_id = geosite.id
WHERE geosite.code = 'GC_SU'
"""
r = execute_query(sql)
sql = """
DELETE geothing
FROM geothing
LEFT JOIN geosite ON geothing.geosite_id = geosite.id
WHERE geosite.code = 'GC_SU'
"""
r = execute_query(sql)
file = '/tmp/geocaching_su.wpt'
# sanity checking, only work on wpt files
if file.endswith('.wpt') == 0: sys.exit(-1)
print "Reading file: " + file
fh = codecs.open(file, 'r', "cp1251")
wpt = fh.readlines()
fh.close()
WPT_CODE = 1
WPT_LAT = 2
WPT_LON = 3
WPT_TITLE = 10
WPT_DATE = 4
geosite = Geosite.objects.get(code='GC_SU')
print geosite
print len(wpt), 'points'
for point in wpt:
print
print point
fields = point.split(',')
if fields[0].isdigit():
geothing = Geothing(geosite=geosite)
print geothing.geosite.url
for field in fields:
print field
l = Location()
lat_degree = float(fields[WPT_LAT])
l.NS_degree = int(lat_degree)
l.NS_minute = (abs(lat_degree) - abs(l.NS_degree)) * 60
lon_degree = float(fields[WPT_LON])
l.EW_degree = int(lon_degree)
l.EW_minute = (abs(lon_degree) - abs(l.EW_degree)) * 60
l.save()
geothing.location = l
p = re.compile('(\D+)(\d+)')
dgs = p.findall(fields[WPT_CODE])
if dgs:
code_data = dgs[0]
geothing.code = fields[WPT_CODE]
geothing.pid = int(code_data[1])
geothing.type_code = code_data[0]
p = re.compile(u'(.+)от(.+)')
dgs = p.findall(fields[WPT_TITLE])
if dgs:
title = dgs[0]
geothing.name = title[0]
geothing.author = title[1]
d = float(fields[WPT_DATE])
print Dephi_date_to_python_date(d)
geothing.created_date = Dephi_date_to_python_date(d)
if geothing.type_code in GEOCACHING_SU_ONMAP_TYPES:
geothing.save()
elapsed = time() - start
print "Elapsed time -->", elapsed
return
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():
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
def handle(self, *args, **options):
execute_query(RAWSQL['set_caches_grades'])
log(UPDATE_TYPE.set_caches_locations, 'OK')
return 'Grades of caches has updated'