[gdal-dev] Spatialite tables with multiple geometry columns

[Even Rouault]

Selon Jukka Rahkonen <jukka.rahkonen at mmmtike.fi>:

Multi geometry support per table isn't really supported for now. For the PG
driver, we expose layer names as "table_name(geometry_column_name)". I think the
same convention could be used for sqlite. In your example, it would be
"multigeom(geom1)" and "multigeom(geom2)". The reasons for the view_geom2 view
to fail is exactly the same as why you didn't manage to query the multigeom with
geom2. The code of views does a GetLayerByName("multigeom"), and so only gets
the first layer.

> 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-
>
>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>