diff options
| -rwxr-xr-x | webmap-cgi | 43 |
1 files changed, 35 insertions, 8 deletions
@@ -60,7 +60,7 @@ def get_query_map(layernames : set[str]) -> dict[str,bytes]: # pylint: disable-next=no-member with PG_CONN.cursor(binary=True, scrollable=False, withhold=False) as cur: for layername in layernames: - cur.execute('SELECT f_geometry_column ' + cur.execute('SELECT f_geometry_column, coord_dimension ' 'FROM ' + common.escape_identifier(SCHEMA_NAME) + '.geometry_columns ' 'WHERE f_table_schema = %s AND f_table_name = %s', params=(SCHEMA_NAME, layername), @@ -68,12 +68,17 @@ def get_query_map(layernames : set[str]) -> dict[str,bytes]: resp = cur.fetchone() if resp is None: continue - geom_col = resp[0] + geom_cols = [ resp[0] ] + force2d = resp[1] > 2 + resp = cur.fetchone() if resp is not None: logging.warning('Table "%s" has multiple geometry colums, ' 'only considering "%s"', - layername, geom_col) + layername, geom_cols[0]) + while resp is not None: + geom_cols.append( resp[0] ) + resp = cur.fetchone() # https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns cur.execute('SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type ' @@ -99,19 +104,41 @@ def get_query_map(layernames : set[str]) -> dict[str,bytes]: layername) continue + column_names = [] + cur.execute('SELECT column_name FROM information_schema.columns ' + 'WHERE table_schema = %s AND table_name = %s', + params=(SCHEMA_NAME, layername), + prepare=False) + resp = cur.fetchone() + while resp is not None: + c = resp[0] + if c != pkey_col and c not in geom_cols: + column_names.append(c) + resp = cur.fetchone() + # TODO[trixie] id_column => 'ogc_fid' (but don't hardcode either) - query = 'WITH lyr AS (' - query += 'SELECT m.*, %s AS layer_group, %s AS layer ' + query = 'WITH feature AS (' + query += 'SELECT m.' + common.escape_identifier(pkey_col) + ',' + for column_name in column_names: + query += 'm.' + common.escape_identifier(column_name) + ',' + if force2d: + query += 'ST_Force2D(m.' + common.escape_identifier(geom_cols[0]) + ') ' + query += 'AS ' + common.escape_identifier(geom_cols[0]) + ',' + else: + query += 'm.' + common.escape_identifier(geom_cols[0]) + ',' + query += '%s AS layer_group,%s AS layer ' query += 'FROM ' + common.escape_identifier(SCHEMA_NAME) + '.' query += common.escape_identifier(layername) + ' m ' query += 'WHERE m.' + common.escape_identifier(pkey_col) + ' = %s' query += ') ' + # letting PostgreSQL convert to UTF-8 is likely faster than if we were doing + # it ourselves query += 'SELECT convert_to(' - query += 'ST_AsGeoJSON(lyr.*,' - query += 'geom_column=>' + common.escape_literal_str(geom_col) + ',' + query += 'ST_AsGeoJSON(feature.*,' + query += 'geom_column=>' + common.escape_literal_str(geom_cols[0]) + ',' query += 'pretty_bool=>\'f\'),' query += '\'UTF8\') AS "GeoJSON" ' - query += 'FROM lyr' + query += 'FROM feature' # The query never returns more than one row since we filter on a single FID. # Don't try to be clever and batch queries in an IN set or ANY as we # want to preserve the order in the response (so the feature(s) |