def insertPOI(conn, loglevel=0):
log.add ('start insert poi', level=loglevel, file=file_log)
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# time последнего объекта и удаленные
cur.execute("""
SELECT MAX(tstamp) as nupd, (SELECT MAX(deleted_at) FROM deleted_entries) as ndel
FROM
(
SELECT MAX(tstamp) as tstamp
FROM relations
UNION
SELECT MAX(updated_at)
FROM relations
UNION
SELECT MAX(tstamp)
FROM ways
UNION
SELECT MAX(updated_at)
FROM ways
UNION
SELECT MAX(tstamp)
FROM nodes
) as t1
""")
whereTime = cur.fetchone()
if not whereTime['ndel']:
whereTime['ndel'] = datetime.datetime.now()
catalog(cur, loglevel=loglevel+1)
addr(cur, loglevel=loglevel+1)
conn.commit()
cur = conn.cursor()
log.add ('clear table ershkus_poi', level=loglevel+1, file=file_log)
cur.execute("DELETE FROM ershkus_poi")
log.add ('copy table ershkus_poi_c to ershkus_poi', level=loglevel+1, file=file_log)
cur.execute("""
INSERT INTO ershkus_poi(
id, class, tags, name_ru, operator, tags_ru, opening_hours, class_ru, addr_region, addr_district, addr_city, addr_full_name, index_name, addr_region_id, addr_district_id, addr_city_id, addr_house_id, addr_in_id, addr_in_type, brand, phone, fax, website, addr_village, addr_street, addr_house, c_geom, addr_country, email, description, wikipedia, osm_id)
SELECT
id, class, tags, name_ru, operator, tags_ru, opening_hours, class_ru, addr_region, addr_district, addr_city, addr_full_name, index_name, addr_region_id, addr_district_id, addr_city_id, addr_house_id, addr_in_id, addr_in_type, brand, phone, fax, website, addr_village, addr_street, addr_house, c_geom, addr_country, email, description, wikipedia, osm_id
FROM
ershkus_poi_c
;
""")
conn.commit()
# сохраним текущую позицию
utils.saveDate(whereTime=whereTime, file=fupd_time, key_config='datepoi')
# f = open(conf.workactual + fupd_time,'w')
# f.write(json.dumps(whereTime, default=utils.jsondumps))
# f.close()
log.add ('end insert poi', level=loglevel, file=file_log)
def insert_poi(conn, log_level=0):
log.add('start insert poi', level=log_level, file=file_log)
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# time последнего объекта и удаленные
cur.execute("""
SELECT MAX(tstamp) as nupd, (SELECT MAX(deleted_at) FROM deleted_entries) as ndel
FROM
(
SELECT MAX(tstamp) as tstamp
FROM relations
UNION
SELECT MAX(updated_at)
FROM relations
UNION
SELECT MAX(tstamp)
FROM ways
UNION
SELECT MAX(updated_at)
FROM ways
UNION
SELECT MAX(tstamp)
FROM nodes
) as t1
""")
whereTime = cur.fetchone()
if not whereTime['ndel']:
whereTime['ndel'] = datetime.datetime.now()
catalog(cur, loglevel=log_level + 1)
addr(cur, loglevel=log_level + 1)
conn.commit()
# сохраним текущую позицию
utils.saveDate(whereTime=whereTime, file=fupd_time, key_config='datepoi')
# f = open(conf.workactual + fupd_time,'w')
# f.write(json.dumps(whereTime, default=utils.jsondumps))
# f.close()
log.add('end insert poi', level=log_level, file=file_log)
def insertPOI(conn, loglevel=0):
log.add ('start insert poi', level=loglevel, file=file_log)
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# time последнего объекта и удаленные
cur.execute("""
SELECT MAX(tstamp) as nupd, (SELECT MAX(deleted_at) FROM deleted_entries) as ndel
FROM
(
SELECT MAX(tstamp) as tstamp
FROM relations
UNION
SELECT MAX(updated_at)
FROM relations
UNION
SELECT MAX(tstamp)
FROM ways
UNION
SELECT MAX(updated_at)
FROM ways
UNION
SELECT MAX(tstamp)
FROM nodes
) as t1
""")
whereTime = cur.fetchone()
if not whereTime['ndel']:
whereTime['ndel'] = datetime.datetime.now()
catalog(cur, loglevel=loglevel+1)
addr(cur, loglevel=loglevel+1)
conn.commit()
# сохраним текущую позицию
utils.saveDate(whereTime=whereTime, file=fupd_time, key_config='datepoi')
# f = open(conf.workactual + fupd_time,'w')
# f.write(json.dumps(whereTime, default=utils.jsondumps))
# f.close()
log.add ('end insert poi', level=loglevel, file=file_log)
def updatePOI(conn, loglevel=0):
log.add ('start update poi', level=loglevel, file=file_log)
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
if not os.path.exists(conf.workactual + fupd_time):
log.add ('no exists file `'+conf.workactual + fupd_time+'`', level=loglevel+1, file=file_log)
return
# time последнего объекта и удаленные
log.add ('last time edit obj and delete', level=loglevel, file=file_log)
cur.execute("""
SELECT MAX(deleted_at) as ndel
FROM deleted_entries
""")
whereTime = cur.fetchone()
if not whereTime['ndel']:
whereTime['ndel'] = datetime.datetime.now()
arrObjTime = [['relations','tstamp'],['relations','updated_at'],['ways','tstamp'],['ways','updated_at'],['nodes','tstamp']]
editTime = 0
for nObj in arrObjTime:
cur.execute("""
SELECT MAX("""+nObj[1]+""") as tstamp
FROM """+nObj[0]+"""
""")
nTime = cur.fetchone()['tstamp']
if editTime:
if editTime < nTime:
editTime = nTime
else:
editTime = nTime
whereTime['nupd'] = editTime
# добавим прошлую позицию
f = open(conf.workactual + fupd_time,'r')
fjson = json.loads(f.read(), object_hook=utils.jsonloads)
f.close()
whereTime['lupd'] = fjson['nupd']
whereTime['ldel'] = fjson['ndel']
cur.execute("""
SELECT max(id) as maxid
FROM ershkus_poi
""")
lastID = cur.fetchone()['maxid']+1
# удаление удаленных ПОИ
cur.execute("""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
type || osm_id
FROM
deleted_entries
WHERE
deleted_at>=%(ldel)s AND deleted_at<=%(ndel)s
)
""", whereTime)
# удаление изменёных ПОИ
cur.execute("""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
'n' || id
FROM
nodes
WHERE
(tstamp>=%(lupd)s AND tstamp<=%(nupd)s)
)
""", whereTime)
cur.execute("""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
'w' || id
FROM
ways
WHERE
(tstamp>=%(lupd)s AND tstamp<=%(nupd)s)
OR (updated_at>=%(lupd)s AND updated_at<=%(nupd)s)
)
""", whereTime)
cur.execute("""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
'r' || id
FROM
relations
WHERE
(tstamp>=%(lupd)s AND tstamp<=%(nupd)s)
OR (updated_at>=%(lupd)s AND updated_at<=%(nupd)s)
)
""", whereTime)
catalog(cur, True, whereTime, loglevel=loglevel+1)
addr(cur, lastID, loglevel=loglevel+1)
conn.commit()
# сохраним текущую позицию
utils.saveDate(whereTime=whereTime, file=fupd_time, key_config='datepoi')
# log.add ('save time', level=loglevel+1, file=file_log)
# f = open(conf.workactual + fupd_time,'w')
# f.write(json.dumps(whereTime, default=utils.jsondumps))
# f.close()
log.add ('end update poi', level=loglevel, file=file_log)
def update_poi(conn, log_level=0):
log.add('start update poi', level=log_level, file=file_log)
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
if not os.path.exists(conf.workactual + fupd_time):
log.add('no exists file `' + conf.workactual + fupd_time + '`',
level=log_level + 1,
file=file_log)
return
# time последнего объекта и удаленные
log.add('last time edit obj and delete', level=log_level, file=file_log)
cur.execute("""
SELECT MAX(deleted_at) as ndel
FROM deleted_entries
""")
whereTime = cur.fetchone()
if not whereTime['ndel']:
whereTime['ndel'] = datetime.datetime.now()
arrObjTime = [['relations', 'tstamp'], ['relations', 'updated_at'],
['ways', 'tstamp'], ['ways', 'updated_at'],
['nodes', 'tstamp']]
editTime = 0
for nObj in arrObjTime:
cur.execute("""
SELECT MAX(""" + nObj[1] + """) as tstamp
FROM """ + nObj[0] + """
""")
nTime = cur.fetchone()['tstamp']
if editTime:
if editTime < nTime:
editTime = nTime
else:
editTime = nTime
whereTime['nupd'] = editTime
# добавим прошлую позицию
f = open(conf.workactual + fupd_time, 'r')
fjson = json.loads(f.read(), object_hook=utils.jsonloads)
f.close()
whereTime['lupd'] = fjson['nupd']
whereTime['ldel'] = fjson['ndel']
cur.execute("""
SELECT max(id) as maxid
FROM ershkus_poi
""")
lastID = cur.fetchone()['maxid'] + 1
# удаление удаленных ПОИ
cur.execute(
"""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
type || osm_id
FROM
deleted_entries
WHERE
deleted_at>=%(ldel)s AND deleted_at<=%(ndel)s
)
""", whereTime)
# удаление изменёных ПОИ
cur.execute(
"""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
'n' || id
FROM
nodes
WHERE
(tstamp>=%(lupd)s AND tstamp<=%(nupd)s)
)
""", whereTime)
cur.execute(
"""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
'w' || id
FROM
ways
WHERE
(tstamp>=%(lupd)s AND tstamp<=%(nupd)s)
OR (updated_at>=%(lupd)s AND updated_at<=%(nupd)s)
)
""", whereTime)
cur.execute(
"""
DELETE
FROM ershkus_poi
WHERE
osm_id in
(
SELECT
'r' || id
FROM
relations
WHERE
(tstamp>=%(lupd)s AND tstamp<=%(nupd)s)
OR (updated_at>=%(lupd)s AND updated_at<=%(nupd)s)
)
""", whereTime)
catalog(cur, True, whereTime, loglevel=log_level + 1)
addr(cur, lastID, loglevel=log_level + 1)
conn.commit()
# сохраним текущую позицию
utils.saveDate(whereTime=whereTime, file=fupd_time, key_config='datepoi')
# log.add ('save time', level=loglevel+1, file=file_log)
# f = open(conf.workactual + fupd_time,'w')
# f.write(json.dumps(whereTime, default=utils.jsondumps))
# f.close()
log.add('end update poi', level=log_level, file=file_log)