def main():
parser = argparse.ArgumentParser(description="Calculate the statistical mapping of OGD street types (edgecatego) to"
"OSM street types (highway).")
parser.add_argument("-H", "--hostname", dest="hostname", required=True, help="Host name or IP Address")
parser.add_argument("-d", "--database", dest="database", required=True, help="The name of the database")
parser.add_argument("-t", "--table", dest="table", required=True, help="The database table to read from")
parser.add_argument("-P", "--primary-key", dest="primary_key", required=True, help="The name of the primary key column")
parser.add_argument("-u", "--user", dest="user", required=False, help="The database user")
parser.add_argument("-p", "--password", dest="password", required=False, help="The database password")
args = parser.parse_args()
# Try to connect
try:
conn = psycopg2.connect(
host=args.hostname,
database=args.database,
user=args.user,
password=args.password
)
except Exception as e:
print("I am unable to connect to the database (%s)." % e.message)
sys.exit(1)
cur = conn.cursor()
try:
cur.execute("select %s from %s" % (args.primary_key, args.table))
except Exception as e:
print("I can't SELECT (%s)!" % e)
rows = cur.fetchall()
total = len(rows)
processed = 0
street_type_mapping = {}
progress.startprogress("Processing all streets")
for source_street in rows:
percent = processed / total * 100.0
progress.progress(round(percent, 0))
objectid = source_street[0]
statement = """
select
s.edgecatego as source_type,
l.highway as target_type,
sum(ST_Length(ST_Intersection(ST_Buffer(l.way, 10, 'endcap=flat join=round'), s.geom2))) as length
from planet_osm_line l
left join """ + args.table + """ s on (
ST_Intersects(l.way, ST_Envelope(s.geom2)) and
ST_Intersects(s.geom2, ST_Buffer(l.way, 10, 'endcap=flat join=round'))
)
where
l.highway is not null
and s.""" + args.primary_key + """ = %s
group by edgecatego, highway
"""
try:
cur.execute(statement, (objectid,))
results = cur.fetchall()
for result in results:
source_type = result[0]
target_type = result[1]
length = result[2]
if not source_type in street_type_mapping:
street_type_mapping[source_type] = {}
if target_type in street_type_mapping[source_type]:
street_type_mapping[source_type][target_type] += length
else:
street_type_mapping[source_type][target_type] = length
except Exception as e:
print("I can't SELECT (%s)!" % e)
sys.exit(1)
processed += 1
pprint(street_type_mapping)
def main():
parser = argparse.ArgumentParser(description="Look for streets in the OGD table that are not covered by "
"OpenStreetMap and write them into another table.")
parser.add_argument("-H", "--hostname", dest="hostname", required=False, help="Host name or IP Address")
parser.add_argument("-d", "--database", dest="database", required=True, help="The name of the database")
parser.add_argument("-r", "--region", dest="region", required=True, help="The region to extract streets for")
parser.add_argument("-t", "--table", dest="table", required=True, help="The database table to read from")
parser.add_argument("-P", "--primary-key", dest="primary_key", required=True, help="The name of the primary key column")
parser.add_argument("-n", "--name-column", dest="name_column", required=True, help="The name column")
parser.add_argument("-s", "--source-tag", dest="source_tag", required=True, help="The text that should be written into the OSM source tag")
parser.add_argument("-u", "--user", dest="user", required=False, help="The database user")
parser.add_argument("-p", "--password", dest="password", required=False, help="The database password")
args = parser.parse_args()
show_progress = False
# Read and parse the street type mapping file
street_mapping_select = "null, null,"
with open(os.path.join(os.path.realpath(os.path.join(os.getcwd(), os.path.dirname(__file__))), "street-type-mapping.json")) as data_file:
data = json.load(data_file)
if args.region in data:
region_data = data[args.region]
street_mapping_select = "case "
for highway_mapping_key in region_data["highway"]:
street_mapping_select += "when s.%s='%s' then '%s'\n" % ("edgecatego", highway_mapping_key, region_data["highway"][highway_mapping_key])
street_mapping_select += "end as highway, "
street_mapping_select += "case "
for fixme_mapping_key in region_data["fixme"]:
street_mapping_select += "when s.%s='%s' then '%s'\n" % ("edgecatego", fixme_mapping_key, region_data["fixme"][fixme_mapping_key])
street_mapping_select += "end as fixme, "
# Try to connect
try:
conn = psycopg2.connect(
host=args.hostname,
database=args.database,
user=args.user,
password=args.password
)
except Exception as e:
print("I am unable to connect to the database (%s)." % e.message)
sys.exit(1)
cur = conn.cursor()
try:
cur.execute("""
select """ + args.primary_key + """ from """ + args.table + """
where """ + args.primary_key + """ not in (
select """ + args.primary_key + """ from """ + args.table + """_uncovered
)
""")
except Exception as e:
print("I can't SELECT the not-yet-calculated streets (%s)!" % e)
rows = cur.fetchall()
total = len(rows)
processed = 0
statement = """
insert into """ + args.table + """_uncovered
select objectid, name, highway, fixme, geom, source, round(cast((sum(intersection_length) / ogd_length * 100.0) as numeric), 0) as coverage
from
(select
s.""" + args.primary_key + """ as objectid,
s.""" + args.name_column + """ as name,
""" + street_mapping_select + """
ST_AsEWKT(s.geom2) as geom,
cast('""" + args.source_tag + """' as text) as source,
ST_Length(ST_Intersection(l.buffer, s.geom2)) as intersection_length,
ST_Length(s.geom2) as ogd_length
from osm_street_buffer l
right join """ + args.table + """ s on (
ST_Intersects(l.way, ST_Envelope(s.geom2)))
where s.""" + args.primary_key + """ = %s
group by """ + args.primary_key + """, """ + args.name_column + """, s.edgecatego, s.geom2, intersection_length) as subquery
group by objectid, name, highway, fixme, geom, source, ogd_length;
"""
if show_progress:
progress.startprogress("Processing all streets")
for source_street in rows:
if show_progress:
percent = processed / total * 100.0
progress.progress(round(percent, 0))
objectid = source_street[0]
try:
cur.execute(statement, (objectid,))
conn.commit()
except Exception as e:
print("I can't INSERT the data (%s)!" % e)
sys.exit(1)
processed += 1
if show_progress:
progress.endprogress()