Opened 5 years ago

Closed 4 years ago

#3068 closed defect (invalid)

Arbitrary "coord_dimension" for geometry_columns with unconstrained 4D point

Reported by: strk Owned by: pramsey
Priority: medium Milestone: PostGIS 2.2.0
Component: postgis Version: 2.0.x
Keywords: Cc: robe, pramsey

Description

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...

Change History (17)

comment:1 Changed 5 years ago by strk

Cc: robe added

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 ?

comment:2 Changed 5 years ago by strk

Cc: pramsey added

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

comment:3 Changed 5 years ago by 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.

comment:4 Changed 5 years ago by 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 ?

comment:5 Changed 5 years ago by strk

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

comment:6 Changed 5 years ago by 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).

comment:7 Changed 5 years ago by robe

Milestone: PostGIS 2.0.7PostGIS 2.2.0

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

comment:8 Changed 5 years ago by 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.

comment:9 Changed 5 years ago by 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)

comment:10 Changed 5 years ago by 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

comment:11 Changed 5 years ago by strk

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

comment:12 Changed 5 years ago by 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.

comment:13 Changed 5 years ago by 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).

comment:14 Changed 5 years ago by robe

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

comment:15 Changed 5 years ago by robe

Forgot to add GvSIG

comment:16 Changed 5 years ago by 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...)

comment:17 Changed 4 years ago by pramsey

Resolution: invalid
Status: newclosed

Geometry_columns just got rewritten for performance.

Note: See TracTickets for help on using tickets.