def run(self, osmosis, parser, mapping, db_schema, default_table_base_name,
time):
"""
@return if data loaded in data base
"""
table_base_name = self.table_name or default_table_base_name
if len(table_base_name) <= 63: # 63 max postgres relation name
table = table_base_name
else:
table = table_base_name[-(63 - 10):] + hashlib.md5(
table_base_name).hexdigest()[-10:]
osmosis.run(
"CREATE TABLE IF NOT EXISTS meta (name character varying(255) NOT NULL, update integer, bbox character varying(1024) )"
)
self.data = False
def setDataTrue():
self.data = True
osmosis.run0(
"SELECT * FROM meta WHERE name='%s' AND update=%s" % (table, time),
lambda res: setDataTrue())
if not self.data:
osmosis.logger.log(u"Load source into database")
osmosis.run("DROP TABLE IF EXISTS %s" % table)
if not self.create:
header = parser.header()
if header:
if header != True:
self.create = ",".join(
map(lambda c: "\"%s\" VARCHAR(65534)" % c[0:50],
header))
else:
raise AssertionError("No table schema provided")
osmosis.run(sql_schema % {"schema": db_schema})
if self.create:
osmosis.run("CREATE TABLE %s (%s)" % (table, self.create))
parser.import_(table, self.srid, osmosis)
osmosis.run("DELETE FROM meta WHERE name = '%s'" % table)
osmosis.run("INSERT INTO meta VALUES ('%s', %s, NULL)" %
(table, time))
osmosis.run0("COMMIT")
osmosis.run0("BEGIN")
parser.close()
# Convert
country_hash = osmosis.config.db_schema.split(
'_')[-1][0:10] + hashlib.md5(
osmosis.config.db_schema).hexdigest()[-4:]
if len(
default_table_base_name + '_' + country_hash
) <= 63 - 2 - 3: # 63 max postgres relation name, 3 is index name prefix
tableOfficial = default_table_base_name + '_' + country_hash + "_o"
else:
tableOfficial = (default_table_base_name + '_' + country_hash
)[-(63 - 2 - 3 - 4):] + '_o' + hashlib.md5(
default_table_base_name).hexdigest()[-4:]
self.data = False
def setData(res):
self.data = res
osmosis.run0(
"SELECT bbox FROM meta WHERE name='%s' AND bbox IS NOT NULL AND update IS NOT NULL AND update=%s"
% (tableOfficial, time), lambda res: setData(res))
if not self.data:
self.pip = PointInPolygon.PointInPolygon(
self.polygon_id) if self.srid and self.polygon_id else None
osmosis.logger.log(u"Convert data to tags")
osmosis.run(sql_schema % {"schema": db_schema})
osmosis.run(sql00 % {"official": tableOfficial})
giscurs = osmosis.gisconn.cursor(
cursor_factory=psycopg2.extras.DictCursor)
giscurs_getpoint = osmosis.gisconn.cursor(
cursor_factory=psycopg2.extras.DictCursor)
def insertOfficial(res):
x = self.xFunction(res[0])
y = self.yFunction(res[1])
if (not self.pip or (x and y)) and self.where(res):
is_pip = False
if self.pip:
giscurs_getpoint.execute(
"SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(%(x)s, %(y)s), %(SRID)s), 4326))"
% {
"x": x,
"y": y,
"SRID": self.srid
})
lonLat = self.osmosis.get_points(
giscurs_getpoint.fetchone()[0])[0]
lonLat = [float(lonLat["lon"]), float(lonLat["lat"])]
is_pip = self.pip.point_inside_polygon(
lonLat[0], lonLat[1])
if not self.pip or is_pip:
for k in res.iterkeys():
if res[k] != None and isinstance(
res[k], basestring):
res[k] = ' '.join(res[k].split(
)) # Strip and remove duplicate space
tags = mapping.generate.tagFactory(res)
tags[1].update(tags[0])
giscurs.execute(
sql02.replace("%(official)s", tableOfficial), {
"ref":
tags[1].get(mapping.osmRef)
if mapping.osmRef != "NULL" else None,
"tags":
tags[1],
"tags1":
tags[0],
"fields":
dict(
zip(
dict(res).keys(),
map(lambda x: unicode(x),
dict(res).values()))),
"lon":
lonLat[0] if is_pip else None,
"lat":
lonLat[1] if is_pip else None
})
if isinstance(self.x, tuple):
self.x = self.x[0]
else:
self.x = "\"%s\"" % self.x
if isinstance(self.y, tuple):
self.y = self.y[0]
else:
self.y = "\"%s\"" % self.y
if self.uniq:
l = ','.join(map(lambda v: '"%s"' % v, self.uniq))
distinct = "DISTINCT ON (%s)" % l
order_by = "ORDER BY %s" % l
else:
distinct = order_by = ""
osmosis.run0(
(sql01_ref if mapping.osmRef != "NULL" else sql01_geo) % {
"table": table,
"x": self.x,
"y": self.y,
"where": self.formatCSVSelect(),
"distinct": distinct,
"order_by": order_by
}, insertOfficial)
if self.srid:
giscurs.execute(
"SELECT ST_AsText(ST_Envelope(ST_Extent(geom::geometry))::geography) FROM %s"
% tableOfficial)
self.bbox = giscurs.fetchone()[0]
else:
self.bbox = None
osmosis.run(sql03 % {"official": tableOfficial})
giscurs_getpoint.close()
giscurs.close()
osmosis.run("DELETE FROM meta WHERE name='%s'" % tableOfficial)
if self.bbox != None:
osmosis.run("INSERT INTO meta VALUES ('%s', %s, '%s')" %
(tableOfficial, time, self.bbox))
osmosis.run0("COMMIT")
osmosis.run0("BEGIN")
else:
self.bbox = self.data[0]
if not (self.srid and not self.bbox): # Abort condition
return tableOfficial
def run(self, osmosis, parser, mapping, db_schema, default_table_base_name, time):
"""
@return if data loaded in data base
"""
table_base_name = self.table_name or default_table_base_name
if len(table_base_name) <= 63: # 63 max postgres relation name
table = table_base_name
else:
table = table_base_name[-(63-10):]+hexastablehash(table_base_name)[-10:]
osmosis.run("CREATE UNLOGGED TABLE IF NOT EXISTS meta (name character varying(255) NOT NULL, update integer, bbox character varying(1024) )")
self.data = False
def setDataTrue():
self.data = True
osmosis.run0("SELECT * FROM meta WHERE name='{0}' AND update={1}".format(table, time), lambda res: setDataTrue())
if not self.data:
osmosis.logger.log(u"Load source into database")
osmosis.run("DROP TABLE IF EXISTS {0}".format(table))
if not self.create:
header = parser.header()
if header:
if header is not True:
self.create = ",".join(map(lambda c: "\"{0}\" VARCHAR(65534)".format(c[0:50]), header))
else:
raise AssertionError("No table schema provided")
osmosis.run(sql_schema.format(schema = db_schema))
if self.create:
osmosis.run("CREATE UNLOGGED TABLE {0} ({1})".format(table, self.create))
parser.import_(table, self.srid, osmosis)
osmosis.run("DELETE FROM meta WHERE name = '{0}'".format(table))
osmosis.run("INSERT INTO meta VALUES ('{0}', {1}, NULL)".format(table, time))
osmosis.run0("COMMIT")
osmosis.run0("BEGIN")
parser.close()
# Convert
country_hash = osmosis.config.db_schema.split('_')[-1][0:10] + hexastablehash(osmosis.config.db_schema)[-4:]
if len(default_table_base_name + '_' + country_hash) <= 63-2-3: # 63 max postgres relation name, 3 is index name prefix
tableOfficial = default_table_base_name + '_' + country_hash + "_o"
else:
tableOfficial = (default_table_base_name + '_' + country_hash)[-(63-2-3-4):] + '_o' + hexastablehash(default_table_base_name)[-4:]
self.data = False
def setData(res):
self.data = res
osmosis.run0("SELECT bbox FROM meta WHERE name='{0}' AND bbox IS NOT NULL AND update IS NOT NULL AND update={1}".format(tableOfficial, time), lambda res: setData(res))
if not self.data:
self.pip = PointInPolygon.PointInPolygon(self.polygon_id) if self.srid and self.polygon_id else None
if self.pip:
if Transformer:
transformer = Transformer.from_crs(self.srid, 4326, always_xy = True)
else: # pyproj < 2.1
transformer = None
osmosis.logger.log(u"Convert data to tags")
osmosis.run(sql_schema.format(schema = db_schema))
osmosis.run(sql00.format(official = tableOfficial))
giscurs = osmosis.gisconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
giscurs_getpoint = osmosis.gisconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
mult_space = re.compile(r'\s+')
def insertOfficial(res):
if not self.where(res):
return
res = self.map(res)
x = self.xFunction(res['_x'])
y = self.yFunction(res['_y'])
if not self.pip or (x and y):
is_pip = False
if self.pip:
if transformer:
lonLat = transformer.transform(x, y)
else:
giscurs_getpoint.execute("SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint({x}, {y}), {srid}), 4326))".format(x=x, y=y, srid=self.srid))
lonLat = self.osmosis.get_points(giscurs_getpoint.fetchone()[0])[0]
lonLat = [float(lonLat["lon"]), float(lonLat["lat"])]
is_pip = self.pip.point_inside_polygon(lonLat[0], lonLat[1])
if not self.pip or is_pip:
for k in res.keys():
try:
res[k] = mult_space.sub(' ', res[k].strip()) # Strip and remove duplicate space
except AttributeError:
pass
tags = mapping.generate.tagFactory(res)
tags[1].update(tags[0])
giscurs.execute(sql02.format(official = tableOfficial), {
"ref": tags[1].get(mapping.osmRef) if mapping.osmRef != "NULL" else None,
"tags": tags[1],
"tags1": tags[0],
"fields": dict(zip(dict(res).keys(), map(lambda s: (s is None and None) or u'{0}'.format(s), dict(res).values()))),
"lon": lonLat[0] if is_pip else None, "lat": lonLat[1] if is_pip else None
})
if isinstance(self.x, tuple):
self.x = self.x[0]
else:
self.x = "\"{0}\"".format(self.x)
if isinstance(self.y, tuple):
self.y = self.y[0]
else:
self.y = "\"{0}\"".format(self.y)
if self.uniq:
l = ','.join(map(lambda v: '"{0}"'.format(v), self.uniq))
distinct = "DISTINCT ON ({0})".format(l)
order_by = "ORDER BY {0}".format(l)
else:
distinct = order_by = ""
osmosis.run0((sql01_ref if mapping.osmRef != "NULL" else sql01_geo).format(table = table, x = self.x, y = self.y, where = self.formatCSVSelect(), distinct = distinct, order_by = order_by), insertOfficial)
osmosis.run(sql02b.format(official = tableOfficial))
if self.srid:
giscurs.execute("SELECT ST_AsText(ST_Envelope(ST_Extent(geom::geometry))::geography) FROM {0}".format(tableOfficial))
self.bbox = giscurs.fetchone()[0]
else:
self.bbox = None
osmosis.run(sql03a.format(official = tableOfficial))
osmosis.run(sql03b.format(official = tableOfficial))
giscurs_getpoint.close()
giscurs.close()
osmosis.run("DELETE FROM meta WHERE name='{0}'".format(tableOfficial))
if self.bbox is not None:
osmosis.run("INSERT INTO meta VALUES ('{0}', {1}, '{2}')".format(tableOfficial, time, self.bbox))
osmosis.run0("COMMIT")
osmosis.run0("BEGIN")
else:
self.bbox = self.data[0]
if not(self.srid and not self.bbox): # Abort condition
return tableOfficial
