geo: geometry_columns / geography_columns don't use correct type for 3D geometries

[timgraham]

Describe the problem

The value in the type column for 3D geometries of the geometry_columns and geography_columns tables doesn't match PostGIS.

To Reproduce

Create a table with geometry columns like POINTZ, LINESTRINGZ, POLYGONZ, and check what entries are populated in the type column of geometry_columns.

CREATE TABLE inspectapp_fields3d (
    id INT8 NOT NULL DEFAULT unique_rowid(),
    "point" GEOMETRY(POINTZ,4326) NOT NULL,
    pointg GEOGRAPHY(POINTZ,4326) NOT NULL,
    line GEOMETRY(LINESTRINGZ,4326) NOT NULL,
    poly GEOMETRY(POLYGONZ,4326) NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    INVERTED INDEX inspectapp_fields3d_point_8e4c3179_id ("point"),
    INVERTED INDEX inspectapp_fields3d_pointg_2202a946_id (pointg),
    INVERTED INDEX inspectapp_fields3d_line_ef25c07a_id (line),
    INVERTED INDEX inspectapp_fields3d_poly_97e9595b_id (poly),
    FAMILY "primary" (id, "point", pointg, line, poly)
)

SELECT t. f_geometry_column, t.coord_dimension, t.srid, t.type FROM (
    SELECT * FROM geometry_columns
    UNION ALL
    SELECT * FROM geography_columns
) AS t WHERE t.f_table_name = 'inspectapp_fields3d'

  f_geometry_column | coord_dimension | srid |    type
--------------------+-----------------+------+--------------
  point             |               3 | 4326 | POINTZ
  line              |               3 | 4326 | LINESTRINGZ
  poly              |               3 | 4326 | POLYGONZ
  pointg            |               3 | 4326 | POINTZ

Expected behavior

PostGIS result:

  f_geometry_column | coord_dimension | srid |    type
--------------------+-----------------+------+--------------
  point             |               3 | 4326 | POINT
  line              |               3 | 4326 | LINESTRING
  poly              |               3 | 4326 | POLYGON
  pointg            |               3 | 4326 | PointZ

The values in the type columns should match this list of OGR Geometry Types.

Environment:

• CockroachDB version: v21.2.0-alpha.00000000-4558-g049aff1646
• Build Time: 2021/09/28 12:50:23

Additional context

This is from a Django test.

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: django)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[otan]

the list you mentioned is not what shows up (e.g. you have LineString, but the pg output is LINESTRING)...

[timgraham]

Correct. The matching between the values from the database and the Python list (OGRGeomType._types) is case insensitive.

[otan]

Resolved by #70856, backports in the future