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