GDAL/OGR Spatialite driver and spatial index
Jukka Rahkonen
I was rendering maps with Mapserver from Spatialite database and the
performance did not feel optimal. It appeared that OGR SQLite/
Spatialite driver cannot utilise Spatialite spatial index. There is
now an open ticket about the issue http://trac.osgeo.org/gdal/ticket/4212.
Once it is fixed there is a reason to believe that Mapserver and many
other programs using GDAL/OGR will be very much faster in doing
bounding box filtering.
-Jukka Rahkonen-
Jukka Rahkonen
On 29 elo, 14:42, Jukka Rahkonen <jukka.rahko...@latuviitta.fi> wrote:
> Hi,
>
> I was rendering maps with Mapserver from Spatialite database and the
> performance did not feel optimal. It appeared that OGR SQLite/
> Spatialite driver cannot utilise Spatialite spatial index. There is
> other programs using GDAL/OGR will be very much faster in doing
> bounding box filtering.
selections with OGR than before the fix. It is interesting that after
the fix OGR is three times faster in cutting data from Spatialite than
from PostGIS with the same data.
-Jukka Rahkonen-
a.fu...@lqt.it
> times faster in cutting data from Spatialite
> than from PostGIS with the same data.
>
Great :-D
bye Sandro
Roman
Roman
Jukka Rahkonen
The details are in the ticket http://trac.osgeo.org/gdal/ticket/4212 and
in the fix http://trac.osgeo.org/gdal/changeset/23008
I am not a programmer but it looks to me like after the fix an index table
named index_[tablename]_[geometrycolumnname] is searched and if it is not
used then WHERE MBRIntersects() method is used.
Please contact Even Rouault through gdal-dev mailing list if you think
there is something to improve. Even did the fix in a few hours yesterday.
I know very little of Spatialite. Does it matter if there is a mixture of
polygons and multipolygons in one table? How about
points/multipoints/linestrings/multilinestrings? Database was created from
OpenStreetMap data by importing it first into PostGIS with osm2pgsql
program and then with ogr2ogr into Spatialite. Polygons and multipolygons
seem to live in peace in PostGIS and shapefiles and I am mostly working
with Oracle which can keep whatever geometries in the same table. It is
possible to select polygons and multipolygons apart with ogr2ogr if it is
necessary, but it would mean for example dublicating all the Mapserver
mapfile layer definitions for polygon layers.
-Jukka Rahkonen-
> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to
> spatialite-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/spatialite-users?hl=en.
>
>
Felix Obermaier
enabled if any.
Otherwise the query might run against an out of date RTRee?
Cheers
FObermaier
Jukka Rahkonen <jukka.r...@latuviitta.fi> hat am 30. August 2011 um 13:58
geschrieben:
Jukka Rahkonen
Do you mean checking if spatial_index_enabled=1 in the geometry_columns
table and if it is not then discard the index? I can act as a man in the
middle to write this info into gdal-dev list but hopefully someone with
better knowledge than me could do it instead. Probably there is even more
to think about.
-Jukka-
Roman
The details are in the ticket http://trac.osgeo.org/gdal/ticket/4212 and
in the fix http://trac.osgeo.org/gdal/changeset/23008
I am not a programmer but it looks to me like after the fix an index table
named index_[tablename]_[geometrycolumnname] is searched and if it is not
used then WHERE MBRIntersects() method is used.
I know very little of Spatialite. Does it matter if there is a mixture of
polygons and multipolygons in one table? How about
points/multipoints/linestrings/multilinestrings? Database was created from
OpenStreetMap data by importing it first into PostGIS with osm2pgsql
program and then with ogr2ogr into Spatialite. Polygons and multipolygons
seem to live in peace in PostGIS and shapefiles and I am mostly working
with Oracle which can keep whatever geometries in the same table. It is
possible to select polygons and multipolygons apart with ogr2ogr if it is
necessary, but it would mean for example dublicating all the Mapserver
mapfile layer definitions for polygon layers.
a.fu...@lqt.it
> it just populates table with BLOBs that look like spatialite
>
Yes, confirmed.
The current version of the spatialite's OGR driver
has many severe issues.
Shortly said, you cannot safely use OGR to create a
SpatiaLite own DB, because this will simply produce
a broken (invalid, inconsistent) DB.
I've already developed a full patchset for OGR, and
I hope to be able to release the code to the GDAL
project ASAP
really sorry for the delay, but I'm only one, and not
yet able to follow more than three or four different
projects at the same time ;-)
now my immediate priority is fixing v.3.0.0: patching
OGR comes immediately after this.
bye Sandro
Jukka Rahkonen
> Spatialite does not allow mixture of different types of geometries in a
> single column. The culprit is ogr2ogr which can create invalid database,
> it
> just populates table with BLOBs that look like spatialite geometries (more
> on this here:
> http://groups.google.com/group/spatialite-users/browse_thread/thread/fbd19b33454ceaf2
Ok, I will then use the ogr2ogr -nlt MULTIPOLYGON option as a routine.
Otherwise situation does not look so bad with Spatialite databases created
with gdal 1.8. QGIS and Spatialite-gui are quite happy with them, despite
the polygon layers.
This user seems to give as a data store creation option -dsco
FORMAT=SPATIALITE but it is actually layer creation option (-lco) in
gdal/ogr. Perhaps that made the trouble.
> So you should convert all polygons to multipolygons. Similar problem
> discussed here:
> http://groups.google.com/group/spatialite-users/browse_thread/thread/7abb637590d5be72
Thanks, I'll try it.
-Jukka-
> Roman
Jaak Laineste
> polygons and multipolygons in one table? How about
> points/multipoints/linestrings/multilinestrings? Database was created from
> OpenStreetMap data by importing it first into PostGIS with osm2pgsql
> program and then with ogr2ogr into Spatialite. Polygons and multipolygons
> seem to live in peace in PostGIS and shapefiles and I am mostly working
> with Oracle which can keep whatever geometries in the same table. It is
> possible to select polygons and multipolygons apart with ogr2ogr if it is
> necessary, but it would mean for example dublicating all the Mapserver
> mapfile layer definitions for polygon layers.
Old topic, but worth to be cleared. Different formats have different
requirements. According to my knowledge:
- OSM - topological data model, instead of lines/polygons/points you
have nodes,ways and relations what you can combine for points (node),
lines (way), polygons (closed way) or more complex (relation) objects;
different objects (e.g. point and polygon) can be linked to single
object with relation, using softly predefined schema. One "hardcoded"
coordinate system (wgs84).
- PostGIS supports "GEOMETRY" type for spatial column in metadata,
then you can have mix of types. If you specifiy specific type (e.g.
POINT) for column then you cannot have mixture anymore. Also it
supports multiple spatial columns per table, each can have different
projection and type
- Oracle Spatial - can have mix and multiple columns, similar to PostGIS
- SpatiaLite - can have multiple columns with different projections,
but one column must have one data type.
- MapInfo .tab - can have 1 column, but mix of types in the column (no
predefinition). One projection per table.
- Shapefile - most rigid, only 1 spatial column, only one type per
file (table/layer), one projection per table.
I cannot tell without testing how PostGIS and Oracle Spatial will
hande mix of projections per one column (if you leave projection
undefined), but it would be quite bad idea to create this situation in
real life anyway.
--
Jaak
a.fu...@lqt.it
>
yes, correct
... and different types, if you wish to do so
> but one column must have one data type
>
not completely true: you can define a "standard" type
(POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,
MULTIPOLYGON or GEOMETRYCOLLECTION), and consequently
strict type checking will then be enforced as a constraint.
but you can define a generic GEOMETRY type as well,
thus actually suppressing any type check: for a generic
GEOMETRY-type column any valid geometry is assumed
to be acceptable, irrespectively of type.
So after all SpatiaLite and PostGIS are quite the same.
Anyway using "generic" types (GEOMETRYCOLLECTION and
GEOMETRY) isn't IMHO a too smart option: AFAIK the
wide majority of GIS visualizers and editors will
refuse at all to handle "mixed-types".
bye sandro
Jukka Rahkonen
linestrings/multilinestrings, or polygons/multipolygons together those
mixtures do not so often cause problems. About the editors, at least
OpenJUMP can handle all simple feature geometry types on the same
layer. For transferring data into other systems layers usually need to
be split by geometry types, though.
-Jukka Rahkonen-
Jaak Laineste
>>
>> Anyway using "generic" types (GEOMETRYCOLLECTION and
>> GEOMETRY) isn't IMHO a too smart option: AFAIK the
>> wide majority of GIS visualizers and editors will
>> refuse at all to handle "mixed-types".
Ok, clear; this is clients problem then. But what about Spatialite
itself? Does it create proper spatial index in these mixed layer
cases?
Doyou have specific list of GIS tools which use Spatialite and have
issues with it? At least some gis2shapefile converters can nicely
create separate Shapefiles for each type (layer_point.shp
layer_line.shp etc).
Is there a list of Spatialite-enabled tools anyway - from which
tool/spatialite version and key limitations?
> However, because the shapefile can hold points/multipoints,
> linestrings/multilinestrings, or polygons/multipolygons together those
> mixtures do not so often cause problems. About the editors, at least
> OpenJUMP can handle all simple feature geometry types on the same
> layer. For transferring data into other systems layers usually need to
> be split by geometry types, though.
I think you mean that if layer type is multilinestring in Shapefile,
then you can always have objects which happen to have just one line.
So technically it is not really mixture - it is one strict type
multilinestring. Check out Shapefile format - it has just one byte to
define "geometry type of objects".
There seems to be two generations of GIS geometry handling:
a) Old ESRI style is to keep layers for one type of objects only. This
is from old times when you had no collections, just simple
point/line/polygon type options. It is cleaner from data consistency
point of view (avoids spagetti), and also probably was better to be
optimized back in 80ties or so. This style is hardcoded to Shapefile
format.
b) Newer GIS handlers do not have this restriction: object types are
written to each objects and layer type validation is put to the top of
it, just if you need it.
As most GIS packages, also QGIS and other freewares etc are built
around industry standard Shapefile, and by people with ESRI GIS
knowledge, then they have this limitation as legacy, and this tends to
replicate in viewers, converters etc. When I first did Spatialite
reader for Android then also I used geometry type from metadata and
had the limitations, but eventually it was minimum overhead just to
read object types from individual objects and skip layer metadata.
--
Jaak
a.fu...@lqt.it
> ... shapefile can hold points/multipoints,
> linestrings/multilinestrings, or polygons/multipolygons together
>
the same is for SpatiaLite (and PostGIS ...):
you simply have to declare a column of the MULTIxx type
recall: a simple-type can contain one single elementary
geometry; a multi-type can cantain an arbitrary collection
of elementary geometries, and at least one is required.
So each simple-type can be represented as (or casted to)
the corresponding multi-type.
example:
CREATE TABLE t (id INTEGER PRIMARY KEY);
SELECT AddGeometryColumn('t', 'g', 4326,
'MULTILINESTRING', 'XY');
INSERT INTO t (id, g) VALUES (1, ST_GeomFromText(
'MULTILINESTRING((1 1, 2 2), (3 3, 4 4))', 4326));
INSERT INTO t (id, g) VALUES (2, ST_GeomFromText(
'MULTILINESTRING((5 5, 6 6))', 4326));
INSERT INTO t (id, g) VALUES (3, CastToMulti(
ST_GeomFromText('LINESTRING(7 7, 8 8)', 4326)));
[pay close attention at brackets positioning]
a) the first inserted geom contains 2 lines
b) the second one contains a single line, but
is formally represented as a MultiLinestring
c) the third one simply is a Linestring, but after
invoking CastToMulti() actually becomes a
MultiLinestring
so we are allowed to safely insert all them into
the same MULTILINESTRING column.
> at least OpenJUMP can handle all simple feature geometry types on
> the same layer.
really interesting: QGIS has not this capability.
> For transferring data into other systems layers
> usually need to be split by geometry types, though.
>
an absolutely plain task using SpatialSQL: e.g.
SELECT * FROM my_table
WHERE ST_GeometryType(g) = 'POINT';
bye Sandro
a.fu...@lqt.it
> Ok, clear; this is clients problem then. But what about Spatialite
> itself? Does it create proper spatial index in these mixed layer
> cases?
>
yes: Spatial Indexing simply depends on the MBR aka BBOX;
and each geometry surely has its own MBR, irrespectively
of actual type.
> At least some gis2shapefile converters can nicely
> create separate Shapefiles for each type (layer_point.shp
> layer_line.shp etc).
>
look at the spatialite's own tools: both spatialite (CLI)
and spatialite_tool have this capapility
> Is there a list of Spatialite-enabled tools anyway - from which
> tool/spatialite version and key limitations?
>
AFAIK there isn't; just few sparse hints:
- obviously, the spatialite's own supporting
tools (both CLI and GUI)
- GDAL/OGR (some limitations in current implementation)
> When I first did Spatialite reader for Android then also
> I used geometry type from metadata
>
very sound and wise option :-D
> but eventually it was minimum overhead just to read object
> types from individual objects and skip layer metadata.
>
may well be a valid "quick&dirty" option for some very
specific purpose (yes, it works ...)
anyway, not at all a recommended practice in more general
terms; the standard OGC-SFS model strongly relies upon
metadata.
By-passing metadata is potentially harmfull in many ways,
and can cause serious compatibility issues.
bye Sandro
Jukka Rahkonen
On 9 syys, 16:41, a.furi...@lqt.it wrote:
> On Fri, 9 Sep 2011 05:29:23 -0700 (PDT), Jukka Rahkonen wrote
> > the same layer.
>
> really interesting: QGIS has not this capability.
Java Topology Suite and thus Geos and Mr. Martin Davis was heavily
involved. Pretty nice vector tool even as totally memory bound it
cannot handle very big datasets. Alike Spatialite it is lightweight
(about 14 MB nowadays) and powerful.