I'm rather new to the GIS world and even newer to Spatialite and trying to create a new DB for our application. I would like to create spatial index(es) on the geometry column(s), but our geometry BLOB's have always existed in a single column as WKT. After doing some research, it looks like you need a separate column for points, lines, polygons, multipoint, etc.
I'm assuming this from Recipe #6 in the Spatialite Cookbook which states that you need to create your table first, then use AddGeometryColumn to actually add the column which will contain the geometry. In creating this column, one of the items you specific is the GeometryType.
I would much rather keep a single geometry column that could handle all of the shape types, but if the only way to create spatial indexes is to have separate columns, I'll go that route.
Folks before me on this project created their own RTree tables and index to pull data that was within an MBR. My goal is to get rid of the home-grown stuff and use Spatialite for our queries. We have a number of shape files that contain points, lines, multilines, and polygons and I would rather insert their BLOB (WKT) into a single geometry column vs. 1 for each type. Our main queries are to find all shapes that are within an MBR.
Best Answer
The first thing to recognise is that that maybe you don't need a spatial index. My rule-of-thumb says that anything that is in a table with less than about 10K records will be fast enough to check with a full table scan. If you're dealing with less than this number, you can just check the bounding rectangle directly against the test condition, and not worry about the (indirect) check via RTree (SpatiaLite or otherwise).
Simplicity is a win.
Unless of course, the query is too slow. Then you need the index.
It is conventional to split up your tables by geometry type, as Micha pointed out in his comment. However I don't see any reason why you should not use a spatial index on a GeometryCollection geometry type.
Lets look at an example. Firstly, we're going to need data. I don't have your data (obviously), but there are some good public data sets, including Natural Earth 1:10M cultural vectors. I imported some of the shapefiles from that data set into SpatiaLite, using the
spatialite
command line tool:I imported data into a few different tables to provide different geometry types. The bad news is that there aren't really that many entries in that data set - nothing with more than about 70K. That just doesn't take that long to scan, which makes it hard to do timing. So then I imported the allCountries composite set from geonames:
So now we have some data to work with.
Lets create a new table:
And add a geometry column supporting mixed geometry types:
Now we can merge in the data we have:
Oh, of course, we need to make the Geometry types match:
Maybe mix in some point data from the Natural Earth set:
Mix in that big GeoNames set:
This will probably take a while.
Now lets get some of the data back (see blog post linked earlier if this query isn't obvious), without an index:
And the same query, but with an index:
If that is confusing, remember that the index is really a table of bounding boxes:
There is nothing in there about the geometry type - its just a bounding box, assisted by RTree*.
Note that SpatiaLite will work with the GeometryCollection geometry type, but some applications may not. If you want to be able to edit in a range of applications, stick to the common POINT, LINESTRING, POLYGON types.