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