def copy_region_from_osm(conn, region_id, name=None, parent_id='not_passed'):
errors, warnings = [], []
with conn.cursor() as cursor:
# Check if this id already in use
cursor.execute(f"SELECT name FROM {borders_table} WHERE id = %s",
(region_id,))
if cursor.rowcount > 0:
name = cursor.fetchone()[0]
errors.append(f"Region with id={region_id} already exists under name '{name}'")
return errors, warnings
name_expr = f"'{name}'" if name else "name"
parent_id_expr = f"{parent_id}" if isinstance(parent_id, int) else "NULL"
cursor.execute(f"""
INSERT INTO {borders_table}
(id, geom, name, parent_id, modified, count_k)
SELECT osm_id, way, {name_expr}, {parent_id_expr}, now(), -1
FROM {osm_table}
WHERE osm_id = %s
""", (region_id,)
)
if parent_id == 'not_passed':
assign_region_to_lowest_parent(conn, region_id)
try:
update_border_mwm_size_estimation(conn, region_id)
except Exception as e:
warnings.append(str(e))
return errors, warnings
def _make_country_structure(conn, country_osm_id):
regions = {} # osm_id: { 'name': name,
# 'mwm_size_est': size,
# 'parent_id': parent_id }
country_name = get_osm_border_name_by_osm_id(conn, country_osm_id)
country_data = regions.setdefault(country_osm_id, {})
country_data['name'] = country_name
# TODO: country_data['mwm_size_est'] = ...
_create_regions(conn, [country_osm_id], regions)
if country_initial_levels.get(country_name):
admin_levels = country_initial_levels[country_name]
prev_admin_levels = [2] + admin_levels[:-1]
prev_region_ids = [country_osm_id]
for admin_level, prev_level in zip(admin_levels, prev_admin_levels):
if not prev_region_ids:
raise CountryStructureException(
f"Empty prev_region_ids at {country_name}, "
f"AL={admin_level}, prev-AL={prev_level}"
)
subregion_ids = _find_subregions(conn, prev_region_ids,
admin_level, regions)
_create_regions(conn, subregion_ids, regions)
prev_region_ids = subregion_ids
warning = None
if len(regions) == 1:
try:
update_border_mwm_size_estimation(conn, country_osm_id)
except Exception as e:
warning = str(e)
return warning
def copy_from_osm():
osm_id = int(request.args.get('id'))
name = request.args.get('name')
name_sql = f"'{name}'" if name else "'name'"
borders_table = config.BORDERS_TABLE
osm_table = config.OSM_TABLE
with g.conn.cursor() as cursor:
# Check if this id already in use
cursor.execute(f"SELECT id FROM {borders_table} WHERE id = %s",
(osm_id,))
rec = cursor.fetchone()
if rec and rec[0]:
return jsonify(status=f"Region with id={osm_id} already exists")
cursor.execute(f"""
INSERT INTO {borders_table} (id, geom, name, modified, count_k)
SELECT osm_id, way, {name_sql}, now(), -1
FROM {osm_table}
WHERE osm_id = %s
""", (osm_id,)
)
assign_region_to_lowest_parent(osm_id)
warnings = []
try:
update_border_mwm_size_estimation(g.conn, osm_id)
except Exception as e:
warnings.append(str(e))
g.conn.commit()
return jsonify(status='ok', warnings=warnings)
def simple_split_endpoint():
"""Split into 2/4 parts with straight lines"""
region_id = int(request.args.get('id'))
with g.conn.cursor() as cursor:
cursor.execute(f"""
SELECT name, mwm_size_est
FROM {config.BORDERS_TABLE}
WHERE id = %s""", (region_id,))
if cursor.rowcount == 0:
return jsonify(status=f"Region {region_id} not found")
name, mwm_size_est = cursor.fetchone()
if mwm_size_est is None:
mwm_size_est = update_border_mwm_size_estimation(g.conn, region_id)
if mwm_size_est is not None:
return jsonify(status='MWM size estimation was updated')
else:
return jsonify(status="Cannot esitmate region mwm size")
region = {
'id': region_id,
'name': name,
'mwm_size_est': mwm_size_est,
}
if simple_split(g.conn, region):
g.conn.commit()
return jsonify(status='ok')
return jsonify(status="Can't split region into parts")
def save_region_structure_to_db(conn, region_id):
r_data = regions[region_id]
if r_data.get('merged') == True:
return
copy_region_from_osm(conn,
region_id,
parent_id=r_data['parent_id'],
mwm_size_est=r_data.get('mwm_size_est'))
if r_data.get('has_lost_subregions') or r_data.get('is_leaf'):
region_container = {
k: v
for k, v in regions.items() if k == region_id
}
region_data = region_container[region_id]
mwm_size_est = update_border_mwm_size_estimation(conn, region_id)
region_data['mwm_size_est'] = mwm_size_est
if (mwm_size_est is not None
and mwm_size_est > MWM_SIZE_THRESHOLD):
simple_split(conn, region_data)
else:
children_ids = set(r['id'] for r in regions.values()
if r['parent_id'] == region_id)
children_in_clusters = set(
itertools.chain.from_iterable(
cl['subregion_ids']
for cl in r_data.get('clusters', {}).values()))
standalone_children_ids = children_ids - children_in_clusters
if 'clusters' in r_data:
save_clusters_to_db(conn, region_id)
for ch_id in standalone_children_ids:
save_region_structure_to_db(conn, ch_id)
def chop_largest_or_farthest():
region_id = int(request.args.get('id'))
borders_table = config.BORDERS_TABLE
with g.conn.cursor() as cursor:
cursor.execute(f"""SELECT ST_NumGeometries(geom)
FROM {borders_table}
WHERE id = {region_id}""")
res = cursor.fetchone()
if not res or res[0] < 2:
return jsonify(status='border should have more than one outer ring')
free_id1 = get_free_id()
free_id2 = free_id1 - 1
cursor.execute(f"""
INSERT INTO {borders_table} (id, parent_id, name, disabled,
modified, geom)
SELECT id, region_id, name, disabled, modified, geom FROM
(
(WITH w AS (SELECT name, disabled, (ST_Dump(geom)).geom AS g
FROM {borders_table} WHERE id = {region_id})
(SELECT {free_id1} id, {region_id} region_id,
name||'_main' as name, disabled,
now() as modified, g as geom, ST_Area(g) as a
FROM w ORDER BY a DESC LIMIT 1)
UNION ALL
SELECT {free_id2} id, {region_id} region_id,
name||'_small' as name, disabled,
now() as modified, ST_Collect(g) AS geom,
ST_Area(ST_Collect(g)) as a
FROM (SELECT name, disabled, g, ST_Area(g) AS a
FROM w ORDER BY a DESC OFFSET 1) ww
GROUP BY name, disabled)
) x"""
)
warnings = []
for border_id in (free_id1, free_id2):
try:
update_border_mwm_size_estimation(g.conn, border_id)
except Exception as e:
warnings.append(str(e))
g.conn.commit()
return jsonify(status='ok', warnings=warnings)
def split():
# TODO:
# Try avoid unloading split parts with DB query and then loading them into DB,
# thus avoid binary -> text -> binary geometry conversion. Do splitting with one query instead.
# DB sequence for free_id may be necessary. Create global sequence in DB for that?
region_id = int(request.args.get('id'))
line = request.args.get('line')
save_region = (request.args.get('save_region') == 'true')
borders_table = config.BORDERS_TABLE
warnings = []
with g.conn.cursor() as cursor:
# check that we're splitting a single polygon
cursor.execute(f"""
SELECT ST_NumGeometries(geom) FROM {borders_table} WHERE id = %s
""", (region_id,)
)
res = cursor.fetchone()
if not res or res[0] != 1:
return jsonify(status='border should have one outer ring')
cursor.execute(f"""
SELECT ST_AsText(
(ST_Dump(ST_Split(geom, ST_GeomFromText(%s, {config.SRID})))).geom)
FROM {borders_table}
WHERE id = %s
""", (line, region_id)
)
if cursor.rowcount > 1:
# no use of doing anything if the polygon wasn't modified
geometries = []
for res in cursor:
geometries.append(res[0])
# get region properties and delete old border
cursor.execute(f"""
SELECT name, parent_id, disabled FROM {borders_table} WHERE id = %s
""", (region_id,))
name, parent_id, disabled = cursor.fetchone()
if save_region:
parent_id = region_id
else:
cursor.execute(f"DELETE FROM {borders_table} WHERE id = %s",
(region_id,))
base_name = name
# insert new geometries
counter = 1
new_ids = []
free_id = get_free_id()
for geom in geometries:
cursor.execute(f"""
INSERT INTO {borders_table} (id, name, geom, disabled,
count_k, modified, parent_id)
VALUES (%s, %s, {round_geometry(f'ST_GeomFromText(%s, {config.SRID})')}, %s, -1,
now(), %s)
""", (free_id, f'{base_name}_{counter}', geom,
disabled, parent_id)
)
new_ids.append(free_id)
counter += 1
free_id -= 1
g.conn.commit()
for border_id in new_ids:
#try:
update_border_mwm_size_estimation(g.conn, border_id)
#except Exception as e:
# warnings.append(str(e))
g.conn.commit()
return jsonify(status='ok', warnings=warnings)
def split():
region_id = int(request.args.get('id'))
line = request.args.get('line')
save_region = (request.args.get('save_region') == 'true')
borders_table = config.BORDERS_TABLE
with g.conn.cursor() as cursor:
# check that we're splitting a single polygon
cursor.execute(f"""
SELECT ST_NumGeometries(geom) FROM {borders_table} WHERE id = %s
""", (region_id,)
)
res = cursor.fetchone()
if not res or res[0] != 1:
return jsonify(status='border should have one outer ring')
cursor.execute(f"""
SELECT ST_AsText(
(ST_Dump(ST_Split(geom, ST_GeomFromText(%s, 4326)))).geom)
FROM {borders_table}
WHERE id = %s
""", (line, region_id)
)
if cursor.rowcount > 1:
# no use of doing anything if the polygon wasn't modified
geometries = []
for res in cursor:
geometries.append(res[0])
# get region properties and delete old border
cursor.execute(f"""
SELECT name, parent_id, disabled FROM {borders_table} WHERE id = %s
""", (region_id,))
name, parent_id, disabled = cursor.fetchone()
if save_region:
parent_id = region_id
else:
cursor.execute(f"DELETE FROM {borders_table} WHERE id = %s",
(region_id,))
base_name = name
# insert new geometries
counter = 1
new_ids = []
free_id = get_free_id()
for geom in geometries:
cursor.execute(f"""
INSERT INTO {borders_table} (id, name, geom, disabled,
count_k, modified, parent_id)
VALUES (%s, %s, ST_GeomFromText(%s, 4326), %s, -1,
now(), %s)
""", (free_id, f'{base_name}_{counter}', geom,
disabled, parent_id)
)
new_ids.append(free_id)
counter += 1
free_id -= 1
warnings = []
for border_id in new_ids:
try:
update_border_mwm_size_estimation(g.conn, border_id)
except Exception as e:
warnings.append(str(e))
g.conn.commit()
return jsonify(status='ok', warnings=warnings)
def split_into_4_parts(conn, region):
bbox = get_region_bbox(conn, region['id'])
mid_lon = (bbox[2] + bbox[0]) / 2
mid_lat = (bbox[3] + bbox[1]) / 2
min_lat = bbox[1]
max_lat = bbox[3]
min_lon = bbox[0]
max_lon = bbox[2]
position_tag_X = f"(ST_XMin(geom) + ST_XMax(geom)) / 2 < {mid_lon}"
position_tag_Y = f"(ST_YMin(geom) + ST_YMax(geom)) / 2 < {mid_lat}"
line_sql = (
"LINESTRING("
f"{min_lon} {mid_lat},"
f"{max_lon} {mid_lat},"
f"{max_lon} {min_lat},"
f"{mid_lon} {min_lat},"
f"{mid_lon} {max_lat}"
")"
)
# 4 quadrants are defined by a pair of (position_tag_X, position_tag_Y)
name_tags = {
(True, True) : 'southwest',
(True, False) : 'northwest',
(False, True) : 'southeast',
(False, False): 'northeast'
}
with conn.cursor() as cursor:
with conn.cursor() as insert_cursor:
query = f"""
SELECT ST_AsText(ST_CollectionExtract(ST_MakeValid(ST_Collect(geom)), 3)) AS geom,
{position_tag_X},
{position_tag_Y}
FROM (
SELECT
(ST_DUMP(
ST_Split(
(
SELECT geom FROM {borders_table}
WHERE id = {region['id']}
),
ST_GeomFromText('{line_sql}', {SRID})
)
)
).geom as geom
) q
GROUP BY {position_tag_X}, {position_tag_Y}
"""
cursor.execute(query)
if cursor.rowcount < 2:
return False
free_id = get_free_id()
used_ids = []
for geom, is_lower_X, is_lower_Y in cursor:
name_tag = name_tags[(is_lower_X, is_lower_Y)]
insert_cursor.execute(f"""
INSERT INTO {borders_table} (id, name, parent_id, geom,
modified, count_k, mwm_size_est)
VALUES (
{free_id},
%s,
{region['id']},
{round_geometry(f'ST_GeomFromText(%s, {SRID})')},
now(),
-1,
NULL
)
""", (f"{region['name']}_{name_tag}", geom)
)
used_ids.append(free_id)
free_id -= 1
for b_id in used_ids:
update_border_mwm_size_estimation(conn, b_id)
return True
def split_into_2_parts(conn, region):
bbox = get_region_bbox(conn, region['id'])
width = bbox[2] - bbox[0]
height = bbox[3] - bbox[1]
split_vertically = (width > height)
if split_vertically:
mid_lon = (bbox[2] + bbox[0]) / 2
min_lat = bbox[1]
max_lat = bbox[3]
line_sql = f"LINESTRING({mid_lon} {min_lat}, {mid_lon} {max_lat})"
position_tag = f"(ST_XMin(geom) + ST_XMax(geom)) / 2 < {mid_lon}"
name_tags = ('west', 'east')
else:
mid_lat = (bbox[3] + bbox[1]) / 2
min_lon = bbox[0]
max_lon = bbox[2]
line_sql = f"LINESTRING({min_lon} {mid_lat}, {max_lon} {mid_lat})"
position_tag = f"(ST_YMin(geom) + ST_YMax(geom)) / 2 < {mid_lat}"
name_tags = ('south', 'north')
free_id = get_free_id()
ids = (free_id, free_id - 1)
with conn.cursor() as cursor:
with conn.cursor() as insert_cursor:
cursor.execute(f"""
SELECT ST_AsText(ST_CollectionExtract(ST_MakeValid(ST_Collect(geom)), 3)) AS geom,
{position_tag} AS is_lower
FROM (
SELECT
(ST_DUMP(
ST_Split(
(
SELECT geom FROM {borders_table}
WHERE id = {region['id']}
),
ST_GeomFromText('{line_sql}', {SRID})
)
)
).geom as geom
) q
GROUP BY {position_tag}
ORDER BY 2 DESC
""")
if cursor.rowcount < 2:
return False
for i, ((geom, is_lower), b_id, name_tag) in enumerate(zip(cursor, ids, name_tags)):
insert_cursor.execute(f"""
INSERT INTO {borders_table} (id, name, parent_id, geom,
modified, count_k, mwm_size_est)
VALUES (
{b_id},
%s,
{region['id']},
{round_geometry(f'ST_GeomFromText(%s, {SRID})')},
now(),
-1,
NULL
)
""", (f"{region['name']}_{name_tag}", geom)
)
for b_id in ids:
update_border_mwm_size_estimation(conn, b_id)
return True
