PostGIS geometry type estimated wrong in presence of NULL value

[strk]

When exporting layers from PostGIS to Shapefile and reading the geometry type from the first record, NULL geometries aren't skip with the result that ogr2ogr _assumes_ the geometry type is a linestring and fails with anything which isn't a linestring.

Would it make sense to construct the query to look for the first non-null geometry to fetch both type and SRID instead ?

[Even Rouault]

The thing is that even if the first row contains a geometry the PostGIS driver will not currently use it to determine the geometry type (the fact that the shapefile driver tries to guess the geometry type from the first geometry it is fed with is an implementation detail of this particular driver). And this might be a good idea not to do it actually. You could have a layer with mixed geometry types, so reporting wkbUnknown is probably the best thing to do (the work done in #5131 to guess the SRID is a bit different in the sense that it would be particularly convoluted to mix geometries with different SRID in the same column).

[strk]

Note that the focus here is about NULL, not mixed. If the first geometry in the result is NULL not only you fail to understand type but also SRID.

So you're saying that the shapefile driver will be able to read a per-feature type value ? Is the same possible also for SRID ?

[Even Rouault]

So you're saying that the shapefile driver will be able to read a per-feature type value ?

Not sure to understand your question. But my answer is : if a new shapefile layer has been created with a layer geometry type = wkbUnknown, the shapefile driver will look at the feature provided during the fist call to CreateFeature() to determine the actual layer geometry type (since shapefiles must have a clearly defined geometry type). Later features are expected to be of the same geometry type.

Is the same possible also for SRID ?

No, the SRS must be provided at layer creation.

[Jukka Rahkonen]

Could you clarify this problem a bit? I believe that the PostGIS layer was not registered into geometry_columns or otherwise the geometry type and SRID could be found from there. But you (strk) wrote that NULL was found and therefore GDAL assumes that geometry type is LINESTRING. How could that be possible?

Add some SQL for creating a test table and inserting a few geometries into it so others can also have a try easily.

[strk]

I guess it was possible because geometry_columns was not readable, or the geometry typemod did not enforce a type. I don't have time to create a reproducible setup right now, but I think the above two possibilities should both trigger the behavior. As Even confirmed above, GDAL determines the actual layer geometry type from the first call to CreateFeature, I think what happened is that the first such call had a NULL value for the geometry field.

[Jukka Rahkonen]

If the geometry type is not defined in geometry_columns or enforced by typemod, doesn't it mean that GDAL has no other totally reliable way for getting the geometry type than reading the whole table? It is still possible, though not so common with PostGIS because of limitations of common GIS programs, that geometry type is just general "geometry" which accepts all kind of geometry. In that case not even the first not null geometry would tell the truth about the real geometry type because next geometries can be of some other type.

If metadata is missing the generally working best guess would be to set geometry type simply into "geometry". Unfortunately that would not work with saving data into shapefiles or even to Spatialite, GML etc. formats which support this because aim is possibly to use data with QGIS etc. which can't handle general (any) geometry type.

Am I right that you suggest that if geometry type of PostGIS table/view can't be found from the metadata, then GDAL should check the first not null geometry and use the geometry type and SRID of that? Do you suggest the same also for Oracle and Spatialite etc?

[strk]

Using the first not-null to extract type would surely be one good step forward.

As you say this does still not mean all the others would be of the same type; how to handle that I'm not sure. One way could be to completely skip those features and WARN, hinting about how to set an explicit filter by type; but I dunno what the current general way to deal with mixed-type or mixed-SRID datasources.

[Even Rouault]

Related ticket #6983

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.