[gdal-dev] Spatialite tables with multiple geometry columns

[Jukka Rahkonen]

Hi,

A Mapserver user was trying to use a Spatialite db which had tables with
multiple geometry columns and I got interested in having a little try because
sometimes it could be handy to have both the polygons and their centroids
readily available from the same table.

However, it looks that currently there is no way to deal with the additional
geometry columns. I made a small database for testing and it is here
http://latuviitta.org/documents/multigeometry_db.sqlite

Table 'multigeom' has two geometry columns, one containing polygons and the
other containing centroid points. I made also two spatial views so that
view_geom1 is picking only polygon geometries and view_geom2 only points. I
believe I have made everything fine on the Spatialite side and Spatialite-gui
previews all the geometries correctly. However, it is only possible to handle
the first geometry column with OGR.

The ogrinfo report below shows that both geometries from the table are found and
geometry types are right. For view_geom2 wrong geometry type is reported.

C:\ohjelmat\GDAL_dev>ogrinfo multigeometry_db.sqlite
INFO: Open of `multigeometry_db.sqlite'
      using driver `SQLite' successful.
1: multigeom (Multi Polygon)
2: multigeom (Point)
3: view_geom1 (Multi Polygon)
4: view_geom2 (Multi Polygon)

I could not discover how I could select layer '2: multigeom (Point)' because it
has the same name 'multigeom' as the first layer. By doing 
ogrinfo multigeometry_db.sqlite multigeom 
the first geometry gets selected always. 

Spatial view for geom2 does not work either because OGR is looking at the first
geometry column of the main table

ogrinfo multigeometry_db.sqlite view_geom2
INFO: Open of `multigeometry_db.sqlite'
      using driver `SQLite' successful.
ERROR 1: Underlying layer multigeom for view view_geom2 has not
expected geometry column name (geom1 instead of geom2)

The Spatialite metadata is correct in views_geometry_columns

1	view_geom1	geom1	ROWID	multigeom	geom1
2	view_geom2	geom2	ROWID	multigeom	geom2

There seems to be something to improve both with tables and views. For tables it
looks that layers should just get individual layer names so they could be
selected, like 'multigeom(1)' and 'multigeom(2)' but I fear it is not that
simple. With views SQLite driver should be made to not expect the first geometry
column name it finds from the geometry_columns table but check other lines too.
If I delete the row for geom1 from geometry_colums of place row for geom2 before
it the view_geom2 comes usable for OGR (and view_geom1 unusable).

Regards,

-Jukka Rahkonen-