Arbitrary "coord_dimension" for geometry_columns with unconstrained 4D point

[strk]

Creating a table with an unconstrained geometry reports an arbitrary "coord_dimension" of 2:

strk=# select * from geometry_columns where f_table_name = 'p4d';
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type   
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 strk            | public         | p4d          | g                 |               2 |    0 | GEOMETRY
(1 row)

I think the correct value there would be NULL instead.

For the record, QGIS trusts the value and skips the Force2D step, failing to load any similar table…

[strk]

The definition for the "coord_dimension" is:

    COALESCE(NULLIF(postgis_typmod_dims(a.atttypmod),2),                        
             postgis_constraint_dims(n.nspname, c.relname, a.attname),          
             2) AS coord_dimension,  

Why not dropping the COALESCE and letting it be NULL instead ? Regina ?

[strk]

I think part of the issue is that postgis_typmod_dims(-1) returns 2 rather than NULL, why is it that ?

[robe]

As I recall I think we did that for backward compatibility because we were afraid of breaking applications if we had NULL returned. NULL has never been returned by a geometry_columns table in the history of PostGIS and I think it might have been set to not allow nulls.

[strk]

How about using 0 for the dimensions then ? We have the special values GEOMETRY for type and 0 for srid, but nothing special for dimensions to mean "unconstrained". Would 0 be preferrable ?

[strk]

I shall note that postgis_constraint_dims() returns NULL for unconstrained fields, which I like more.

[strk]

With r13298 (trunk/2.2.0dev) I've committed a change to postgis_typmod_dims to return NULL. The geometry_columns output is still unchanged to gather more opinions first.

The changeset includes an update to the geometry_column view definition to not assume a return of 2 from postgis_typmod means unconstrained, which sounds like a bug on itself (it works because the query later assumes 2 if the column is not constrained by checks, so it refuses first typmod constraint, fails to find check-based constraints and defaults to 2).

[robe]

I wouldn't change behavior like this in a micro. Is it the function or the view or both that is bothering you? The thinking was that for people who don't constrain their geometry columns in some way (and for views based on tables that only have constraints), coord dimension of 2 is the most likely assumption. I think that still holds true.

However I'd be willing to change if we test enough third-party tools to confirm it does more good than harm. I still don't think this is an appropriate change for a micro — so pushing this to 2.2

[strk]

Ok for 2.2.0+ only. I'm actually bothered by geometry_columns in that QGIS trusts the "2" answer and then skips a required Force2D wrap (tested from 1.8 to 2.8). I think mapserver always wraps in Force2D.

[robe]

I should add, I'm okay with touching the postgis_typmod_dims even in micro. Now I am relooking at what you are saying. THAT IS A BUG, and would probably significantly improve the performance of the view if we changed it (to not assume 2). The constraint should only kick in if absolutely postgis_typmod_dims is known to return no value, as it stands it's probably kicking in for most any table (a 2D which I think is a real performance killer)

[strk]

I don't have many typmod-constrained 2d fields to check the actual speed improvement. I wouldn't bet on it being a big one, given we're loading system catalogues nontheless. Can you show some numbers with "EXPLAIN ANALYZE SELECT coord_dimension FROM geometry_column" before and after the patch is applied ?

In any case, I'm ok to backport changeset r13298 back to 2.0 and will do now

[strk]

r13299 and r13300 in 2.1 branch (2.1.6), r13301 in 2.0 branch (2.0.7)

[strk]

robe which tools do we consider important ? I tested QGIS to be equivalently unhappy about reporting 2 or 4 or null while type is reported as "GEOMETRY" (really a QGIS bug, fixed in recent development versions).

When the _type_ is known (ie: "POINT" in geometry_columns) then reporting 4 for unconstrained ndims makes QGIS 2.6 able to load both 2d and 4d rows from that table.

[strk]

For completeness, QGIS 2.6 is NOT happy with a NULL return, when the type is known (won't show 4d points in that case). So as long as QGIS is concerned, returning 4 for unconstrained is the best choice (beside bugs).

[robe]

Things I consider import QGIS, GeoServer, MapServer, OpenJump, Safe FME, and Gasp ESRI ArcGIS (ArcMap and their arcserver stuff).

[robe]

Forgot to add GvSIG

[strk]

MapServer doesn't query geometry_columns at all: ​https://github.com/mapserver/mapserver/blob/rel-6-4-1/mappostgis.c#L2340 I'm not setup to test the others (nor all of them have source code available…)

[pramsey]

Geometry_columns just got rewritten for performance.