CGI: Force geometries to 2D before exporting as GeoJSON.

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

1 files changed, 35 insertions, 8 deletions

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)