From adfc32cb8eac8cc8962efea725de2134a653e83b Mon Sep 17 00:00:00 2001 From: Guilhem Moulin Date: Sun, 25 May 2025 15:59:20 +0200 Subject: CGI: Force geometries to 2D before exporting as GeoJSON. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit And skip geometry columns other than the first one. Some sources (svk:stolpar, nvr:SPA_Rikstackande, nvr:OSPAR, svk:stationsomraden, nvr:HELCOM, nvr:SCI_Rikstackande) come as 3D and we don't flatten them during import. Dropping the Z (and/or M) component during GeoJSON export avoids transfering data which will anyway be ignored by OpenLayer. The overhead of ST_Force2D() seems negligible in comparison with ST_AsGeoJSON(). Querying 100000 random features in each table and measuring yields the following. Simple query ============ SELECT convert_to(ST_AsGeoJSON(m.*,geom_column=>'wkb_geometry',pretty_bool=>'f'),'UTF8') AS "GeoJSON" FROM postgis."nvr:SCI_Rikstackande" m WHERE m."ogc_fid" = %s nvr:HELCOM Time: min=0.000s, max=0.002s, avg=0.000s Size: min=1.24kiB, max=24.23kiB, avg=9.47kiB nvr:SCI_Rikstackande Time: min=0.000s, max=0.663s, avg=0.002s Size: min=0.84kiB, max=61613.04kiB, avg=259.67kiB (Yes that's >50MiB, cf. SE0820430 Torne och Kalix älvsystem.) nvr:SPA_Rikstackande Time: min=0.000s, max=0.008s, avg=0.001s Size: min=1.36kiB, max=392.37kiB, avg=35.23kiB svk:stolpar Time: min=0.000s, max=0.003s, avg=0.000s Size: min=0.19kiB, max=0.20kiB, avg=0.19kiB svk:stationsomraden Time: min=0.000s, max=0.003s, avg=0.000s Size: min=0.35kiB, max=1.22kiB, avg=0.56kiB sks:UtfordAvverk Time: min=0.000s, max=0.023s, avg=0.001s Size: min=0.51kiB, max=1117.39kiB, avg=9.75kiB Advanced query ============== WITH feature AS ( SELECT m."ogc_fid", m.… ST_Force2D(m."wkb_geometry") AS "wkb_geometry", %s AS layer_group, %s AS layer FROM postgis."nvr:SCI_Rikstackande" m WHERE m."ogc_fid" = %s ) SELECT convert_to(ST_AsGeoJSON(feature.*,geom_column=>'wkb_geometry',pretty_bool=>'f'),'UTF8') AS "GeoJSON" FROM feature (We add `layer_group` and `layer` to the later, which adds 48bytes — the size of `, "layer_group": "layer_group", "layer": "layer"` to the output.) nvr:HELCOM Time: min=0.000s, max=0.004s, avg=0.000s Size: min=1.21kiB, max=22.62kiB, avg=8.86kiB nvr:SCI_Rikstackande Time: min=0.000s, max=0.893s, avg=0.002s Size: min=0.88kiB, max=57799.83kiB, avg=243.62kiB nvr:SPA_Rikstackande Time: min=0.000s, max=0.011s, avg=0.001s Size: min=1.37kiB, max=367.70kiB, avg=33.13kiB svk:stolpar Time: min=0.000s, max=0.010s, avg=0.000s Size: min=0.23kiB, max=0.23kiB, avg=0.23kiB svk:stationsomraden Time: min=0.000s, max=0.004s, avg=0.000s Size: min=0.38kiB, max=1.21kiB, avg=0.58kiB sks:UtfordAvverk (already in 2D so skip ST_Force2D() call) Time: min=0.000s, max=0.085s, avg=0.001s Size: min=0.55kiB, max=1117.44kiB, avg=9.80kiB --- webmap-cgi | 43 +++++++++++++++++++++++++++++++++++-------- 1 file changed, 35 insertions(+), 8 deletions(-) (limited to 'webmap-cgi') diff --git a/webmap-cgi b/webmap-cgi index 86a0566..019f0bf 100755 --- a/webmap-cgi +++ b/webmap-cgi @@ -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) -- cgit v1.2.3