def point_coordinates( workspace, publ_type, name, *, point_id, crs, exp_coordinates, precision, ): assert publ_type == LAYER_TYPE query = f'''with transformed as (select st_transform(wkb_geometry, %s) point from {workspace}.{name} where point_id = %s) select st_x(point), st_y(point) from transformed ;''' with app.app_context(): to_srid = db_util.get_srid(crs) coordinates = db_util.run_query(query, (to_srid, point_id)) assert len(coordinates) == 1, coordinates coordinates = coordinates[0] for i in range(0, 1): assert abs( exp_coordinates[i] - coordinates[i] ) <= precision, f'exp_coordinates={exp_coordinates}, coordinates={coordinates}'
def fill_project_template(workspace, layer, layer_uuid, layer_qml, crs, epsg_codes, extent, source_type): wms_crs_list_values = "\n".join((f"<value>{code}</value>" for code in epsg_codes)) db_schema = workspace layer_name = layer db_table = layer creation_iso_datetime = datetime.datetime.utcnow().replace(microsecond=0).isoformat() template_path = get_project_template_path() with open(template_path, 'r') as template_file: template_str = template_file.read() return template_str.format( db_name=settings.LAYMAN_PG_DBNAME, db_host=settings.LAYMAN_PG_HOST, db_port=settings.LAYMAN_PG_PORT, db_user=settings.LAYMAN_PG_USER, db_password=settings.LAYMAN_PG_PASSWORD, source_type=source_type, db_schema=db_schema, db_table=db_table, layer_name=layer_name, layer_uuid=layer_uuid, layer_qml=layer_qml, wms_crs_list_values=wms_crs_list_values, creation_iso_datetime=creation_iso_datetime, extent=extent_to_xml_string(extent), srid=db_util.get_srid(crs), qgis_template_spatialrefsys=crs_def.CRSDefinitions[crs].qgis_template_spatialrefsys, )
def fill_layer_template(workspace, layer, uuid, native_bbox, crs, qml_xml, source_type, attrs_to_ensure): db_schema = workspace layer_name = layer wkb_type = source_type qml_geometry = get_qml_geometry_from_qml(qml_xml) db_table = layer template_path = get_layer_template_path() with open(template_path, 'r') as template_file: template_str = template_file.read() skeleton_xml_str = template_str.format( db_name=settings.LAYMAN_PG_DBNAME, db_host=settings.LAYMAN_PG_HOST, db_port=settings.LAYMAN_PG_PORT, db_user=settings.LAYMAN_PG_USER, db_password=settings.LAYMAN_PG_PASSWORD, source_type=source_type, db_schema=db_schema, db_table=db_table, layer_name=layer_name, layer_uuid=uuid, wkb_type=wkb_type, qml_geometry=qml_geometry, extent=extent_to_xml_string(native_bbox), default_action_canvas_value='{00000000-0000-0000-0000-000000000000}', srid=db_util.get_srid(crs), qgis_template_spatialrefsys=crs_def.CRSDefinitions[crs].qgis_template_spatialrefsys, ) launder_attribute_names(qml_xml) ensure_attributes_in_qml(qml_xml, attrs_to_ensure) parser = ET.XMLParser(remove_blank_text=True) layer_xml = ET.fromstring(skeleton_xml_str.encode('utf-8'), parser=parser) layer_el_tags = [el.tag for el in layer_xml.xpath('/maplayer/*')] for qml_el in qml_xml.xpath('/qgis/*'): # print(f"qml_el={qml_el.tag}") tag = qml_el.tag if tag in layer_el_tags: if tag in ELEMENTS_TO_REWRITE: layer_el = layer_xml.xpath(f'/maplayer/{tag}')[0] layer_el.getparent().replace(layer_el, copy.deepcopy(qml_el)) else: raise LaymanError(47, data=f'Element {tag} already present in layer template.') else: layer_xml.append(copy.deepcopy(qml_el)) skip_attrs = ['version'] qml_root = qml_xml.getroot() for attr_name, attr_value in qml_root.attrib.items(): if attr_name in skip_attrs: continue layer_xml.attrib[attr_name] = attr_value full_xml_str = ET.tostring(layer_xml, encoding='unicode', pretty_print=True) return full_xml_str
def transform(bbox, crs_from, crs_to): if is_empty(bbox): return None, None, None, None srid_from = db_util.get_srid(crs_from) srid_to = db_util.get_srid(crs_to) world_bounds = crs_def.CRSDefinitions[crs_to].world_bounds.get(crs_from) if world_bounds: bbox = ( min(max(bbox[0], world_bounds[0]), world_bounds[2]), min(max(bbox[1], world_bounds[1]), world_bounds[3]), max(min(bbox[2], world_bounds[2]), world_bounds[0]), max(min(bbox[3], world_bounds[3]), world_bounds[1]), ) query = f''' with tmp as (select ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(%s, %s), ST_Point(%s, %s)), %s), %s) bbox) select st_xmin(bbox), st_ymin(bbox), st_xmax(bbox), st_ymax(bbox) from tmp ;''' params = tuple(bbox) + ( srid_from, srid_to, ) result = db_util.run_query(query, params)[0] max_bbox = crs_def.CRSDefinitions[crs_to].max_bbox result = ( min(max(result[0], max_bbox[0]), max_bbox[2]), min(max(result[1], max_bbox[1]), max_bbox[3]), max(min(result[2], max_bbox[2]), max_bbox[0]), max(min(result[3], max_bbox[3]), max_bbox[1]), ) if max_bbox else result return result
def set_bbox(workspace, publication_type, publication, bbox, crs, ): max_bbox = crs_def.CRSDefinitions[crs].max_bbox if crs else None cropped_bbox = ( min(max(bbox[0], max_bbox[0]), max_bbox[2]), min(max(bbox[1], max_bbox[1]), max_bbox[3]), max(min(bbox[2], max_bbox[2]), max_bbox[0]), max(min(bbox[3], max_bbox[3]), max_bbox[1]), ) if not bbox_util.is_empty(bbox) and max_bbox else bbox srid = db_util.get_srid(crs) query = f'''update {DB_SCHEMA}.publications set bbox = ST_MakeBox2D(ST_Point(%s, %s), ST_Point(%s ,%s)), srid = %s where type = %s and name = %s and id_workspace = (select w.id from {DB_SCHEMA}.workspaces w where w.name = %s);''' params = cropped_bbox + (srid, publication_type, publication, workspace,) db_util.run_statement(query, params)
def get_publication_infos_with_metainfo(workspace_name=None, pub_type=None, style_type=None, reader=None, writer=None, limit=None, offset=None, full_text_filter=None, bbox_filter=None, bbox_filter_crs=None, order_by_list=None, ordering_full_text=None, ordering_bbox=None, ordering_bbox_crs=None, ): order_by_list = order_by_list or [] full_text_tsquery = db_util.to_tsquery_string(full_text_filter) if full_text_filter else None full_text_like = '%' + full_text_filter + '%' if full_text_filter else None ordering_full_text_tsquery = db_util.to_tsquery_string(ordering_full_text) if ordering_full_text else None ordering_bbox_srid = db_util.get_srid(ordering_bbox_crs) filtering_bbox_srid = db_util.get_srid(bbox_filter_crs) where_params_def = [ (workspace_name, 'w.name = %s', (workspace_name,)), (pub_type, 'p.type = %s', (pub_type,)), (style_type, 'p.style_type::text = %s', (style_type,)), (reader and not is_user_with_name(reader), 'p.everyone_can_read = TRUE', tuple()), (is_user_with_name(reader), f"""(p.everyone_can_read = TRUE or (u.id is not null and w.name = %s) or EXISTS(select 1 from {DB_SCHEMA}.rights r inner join {DB_SCHEMA}.users u2 on r.id_user = u2.id inner join {DB_SCHEMA}.workspaces w2 on w2.id = u2.id_workspace where r.id_publication = p.id and r.type = 'read' and w2.name = %s))""", (reader, reader,)), (writer and not is_user_with_name(writer), 'p.everyone_can_write = TRUE', tuple()), (is_user_with_name(writer), f"""(p.everyone_can_write = TRUE or (u.id is not null and w.name = %s) or EXISTS(select 1 from {DB_SCHEMA}.rights r inner join {DB_SCHEMA}.users u2 on r.id_user = u2.id inner join {DB_SCHEMA}.workspaces w2 on w2.id = u2.id_workspace where r.id_publication = p.id and r.type = 'write' and w2.name = %s))""", (writer, writer,)), (full_text_filter, '(_prime_schema.my_unaccent(p.title) @@ to_tsquery(unaccent(%s))' 'or lower(unaccent(p.title)) like lower(unaccent(%s)))', (full_text_tsquery, full_text_like,)), (bbox_filter, 'ST_TRANSFORM(ST_SetSRID(p.bbox, p.srid), %s) && ST_MakeBox2D(ST_MakePoint(%s, %s), ST_MakePoint(%s, %s))', (filtering_bbox_srid, ) + bbox_filter if bbox_filter else None, ), ] order_by_definition = { consts.ORDER_BY_FULL_TEXT: ('ts_rank_cd(_prime_schema.my_unaccent(p.title), to_tsquery(unaccent(%s))) DESC', (ordering_full_text_tsquery,)), consts.ORDER_BY_TITLE: ("regexp_replace(lower(unaccent(p.title)), '[^a-zA-Z0-9 ]', '', 'g') ASC", tuple()), consts.ORDER_BY_LAST_CHANGE: ('updated_at DESC', tuple()), consts.ORDER_BY_BBOX: (""" -- A∩B / (A + B) CASE -- if there is any intersection WHEN ST_TRANSFORM(ST_SetSRID(p.bbox, p.srid), %s) && ST_SetSRID(ST_MakeBox2D(ST_MakePoint(%s, %s), ST_MakePoint(%s, %s)), %s) THEN -- in cases, when area of intersection is 0, we want it rank higher than no intersection GREATEST(st_area(st_intersection(ST_TRANSFORM(ST_SetSRID(p.bbox, p.srid), %s), ST_SetSRID(ST_MakeBox2D(ST_MakePoint(%s, %s), ST_MakePoint(%s, %s)), %s))), 0.00001) -- we have to solve division by 0 / (GREATEST(st_area(ST_TRANSFORM(ST_SetSRID(p.bbox, p.srid), %s)), 0.00001) + GREATEST(st_area(ST_SetSRID(ST_MakeBox2D(ST_MakePoint(%s, %s), ST_MakePoint(%s, %s)), %s)), 0.00001) ) -- if there is no intersection, result is 0 in all cases ELSE 0 END DESC """, (ordering_bbox_srid, ) + ordering_bbox + (ordering_bbox_srid, ) + (ordering_bbox_srid, ) + ordering_bbox + (ordering_bbox_srid, ) + (ordering_bbox_srid, ) + ordering_bbox + (ordering_bbox_srid, ) if ordering_bbox else tuple()), } assert all(ordering_item in order_by_definition.keys() for ordering_item in order_by_list) ######################################################### # SELECT clause select_clause = f""" select p.id as id_publication, w.name as workspace_name, p.type, p.name, p.title, p.uuid::text, p.style_type, p.updated_at, ST_XMIN(p.bbox) as xmin, ST_YMIN(p.bbox) as ymin, ST_XMAX(p.bbox) as xmax, ST_YMAX(p.bbox) as ymax, p.srid as srid, (select rtrim(concat(case when u.id is not null then w.name || ',' end, string_agg(w2.name, ',') || ',', case when p.everyone_can_read then %s || ',' end ), ',') from {DB_SCHEMA}.rights r inner join {DB_SCHEMA}.users u2 on r.id_user = u2.id inner join {DB_SCHEMA}.workspaces w2 on w2.id = u2.id_workspace where r.id_publication = p.id and r.type = 'read') can_read_users, (select rtrim(concat(case when u.id is not null then w.name || ',' end, string_agg(w2.name, ',') || ',', case when p.everyone_can_write then %s || ',' end ), ',') from {DB_SCHEMA}.rights r inner join {DB_SCHEMA}.users u2 on r.id_user = u2.id inner join {DB_SCHEMA}.workspaces w2 on w2.id = u2.id_workspace where r.id_publication = p.id and r.type = 'write') can_write_users, count(*) OVER() AS full_count from {DB_SCHEMA}.workspaces w inner join {DB_SCHEMA}.publications p on p.id_workspace = w.id left join {DB_SCHEMA}.users u on u.id_workspace = w.id """ select_params = (ROLE_EVERYONE, ROLE_EVERYONE, ) ######################################################### # WHERE clause where_params = tuple() where_parts = list() for (value, where_part, params, ) in where_params_def: if value: where_parts.append(where_part) where_params = where_params + params where_clause = '' if where_parts: where_clause = 'WHERE ' + '\n AND '.join(where_parts) + '\n' ######################################################### # ORDER BY clause order_by_params = tuple() order_by_parts = list() for order_by_part in order_by_list: order_by_parts.append(order_by_definition[order_by_part][0]) order_by_params = order_by_params + order_by_definition[order_by_part][1] order_by_parts.append('w.name ASC') order_by_parts.append('p.name ASC') order_by_clause = 'ORDER BY ' + ', '.join(order_by_parts) ######################################################### # Pagination clause pagination_params = tuple() pagination_clause = '' if limit is not None: assert limit >= 0 assert isinstance(limit, int) pagination_clause = pagination_clause + f' LIMIT {limit} ' if offset is not None: assert offset >= 0 assert isinstance(offset, int) pagination_clause = pagination_clause + f' OFFSET {offset} ' ######################################################### # Put it together sql_params = select_params + where_params + order_by_params + pagination_params select = select_clause + where_clause + order_by_clause + pagination_clause values = db_util.run_query(select, sql_params) # print(f'get_publication_infos:\n\nselect={select}\n\nsql_params={sql_params}\n\n&&&&&&&&&&&&&&&&&') infos = {(workspace_name, type, publication_name,): {'id': id_publication, 'name': publication_name, 'title': title, 'uuid': uuid, 'type': type, 'style_type': style_type, 'updated_at': updated_at, 'native_bounding_box': [xmin, ymin, xmax, ymax], 'native_crs': db_util.get_crs(srid) if srid else None, 'access_rights': {'read': can_read_users.split(','), 'write': can_write_users.split(',')} } for id_publication, workspace_name, type, publication_name, title, uuid, style_type, updated_at, xmin, ymin, xmax, ymax, srid, can_read_users, can_write_users, _ in values} infos = {key: {**value, 'bounding_box': list(bbox_util.transform(value['native_bounding_box'], value['native_crs'], DEFAULT_BBOX_CRS)) if value['native_bounding_box'][0] and value['native_crs'] and DEFAULT_BBOX_CRS != value['native_crs'] else value['native_bounding_box'], } for key, value in infos.items()} if values: total_count = values[0][-1] else: count_clause = f""" select count(*) AS full_count from {DB_SCHEMA}.workspaces w inner join {DB_SCHEMA}.publications p on p.id_workspace = w.id left join {DB_SCHEMA}.users u on u.id_workspace = w.id """ sql_params = where_params select = count_clause + where_clause count = db_util.run_query(select, sql_params) total_count = count[0][-1] if infos: start = offset + 1 if offset else 1 content_range = (start, start + len(infos) - 1) else: content_range = (0, 0) result = {'items': infos, 'total_count': total_count, 'content_range': content_range, } return result