[mapserver-users] Re: Mapserver unnecessary slow with Spatialite

[Rahkonen Jukka]

> -----Alkuperäinen viesti-----
> Lähettäjä: Even Rouault [mailto:even.rouault at mines-paris.org] 
> Lähetetty: 30. elokuuta 2011 23:28
> Vastaanottaja: mapserver-users at lists.osgeo.org
> Kopio: Rahkonen Jukka
> Aihe: Re: [mapserver-users] Re: Mapserver unnecessary slow 
> with Spatialite
> 
> Le mardi 30 août 2011 11:07:57, Rahkonen Jukka a écrit :
> > Hi,
> > 
> > Even Rouault made a fix yesterday into the ogr sqlite 
> driver. It gave 11
> > times more speed when doing small BBOX selections from a 
> smallish table
> > with 90000 linestrings. These queries are very typical for 
> Mapserver and
> > thus when the gdal version behind Mapserver is updated 
> people should see
> > much more speedy Spatialite rendering. It was also 
> interesting that with
> > the same sample data selecting from Spatialite was 3 times 
> faster than
> > from PostGIS. However, there is probably still one problem. 
> Even wrote:
> > 
> > "  However, there is no gain to expect from the above 
> change for a request
> > such as : ogrinfo OSM_Finland.sqlite -sql "select geometry, osm_id
> > ,highway,ref, name, tunnel from osm_line where highway is not null"
> > -spat  389116 6677305 389579 6677661
> > 
> > Indeed, when you specify -sql, the driver (and to my 
> knowlegde, this is
> > true for all other OGR drivers) makes no attempt to merge 
> the spatial
> > filter, so the spatial filter is evaluated on OGR side, 
> which will be
> > rather slow. So, you have 2 possibilities, either use the 
> "-where highway
> > is not null" on a layer so that OGR can merge the attribute 
> and spatial
> > filters, or incorporate at hand the spatial filter inside 
> the SQL query
> > specified with -sql. "
> > 
> > It is very common and handy to define the DATA in Mapserver 
> mapfile with
> > SQL like the one used in the example. If this is going to 
> lead to slow
> > Mapserver-OGR-Spatialite queries also in the future, could 
> it be possible
> > to add some kind of Mapserver processing option that would 
> force the use
> > of Spatialite spatial index table? It might behave so that 
> if user adds a
> > directive PROCESSING "USE_ SPATIALITE_SPATIAL_INDEX=YES" 
> then Mapserver
> > would construct SQL by adding the AND ROWID IN stuff as 
> described in the
> > GDAL ticket http://trac.osgeo.org/gdal/ticket/4212. xmax, 
> xmin, ymax and
> > ymin would be captured from the WMS BBOX.
> 
> I'm not sure how this could be improved in mapserver, but I'm 
> thinking of an 
> easier solution that will require no code change. Why not using 
> sqlite/spatialite views to capture your request ? This way 
> the view will be 
> seen as a regular layer by OGR, and if you specify it as the 
> data in the file, 
> the spatial filter will be applied to it.
> 
> I've just added support for spatialite views in GDAL trunk 
> (r23011). Spatial 
> index of the base table is used to speed up spatial queries 
> on the view.
> 
> On your example, to create the spatial view :
> 
> ogrinfo berlin.sqlite -sql "create view 
> osm_line_highway_not_null as select 
> OGC_FID AS ROWID, GEOMETRY, osm_id ,highway,ref,name, tunnel 
> from osm_line 
> where highway is not null"
> 
> ogrinfo berlin.sqlite -sql "INSERT INTO 
> views_geometry_columns (view_name, 
> view_geometry, view_rowid, f_table_name, f_geometry_column) VALUES 
> ('osm_line_highway_not_null', 'GEOMETRY', 'ROWID', 
> 'osm_line', 'GEOMETRY')"
> 
> Note: you must call the primary key ROWID if you want QGIS to 
> be able to 
> display the view on the map. I'm not sure why this is 
> necessary (the OGR 
> driver works with other names), may be a limitation of the 
> qgis spatialite 
> driver.
> 
> And then you can use  osm_line_highway_not_null as a regular 
> layer for 
> mapserver.
> 

Alessandro was not totally happy with ogr and your solution. I have noticed that a well working and obviously valid Spatialite databases can be built in two or three steps:

1. Initial import with ogr2ogr
2. Tranform from the database created by ogr2ogr into a new with OpenLite utility http://www.gaia-gis.it/OpenLite/index.html
3. Create views if they are needed with Spatialite-GUI utility 

OpenLite should be able to convert directly from PostGIS but it has some bugs itself and therefore I had to use ogr2ogr instead.
This is what Alessandro wrote:

" #1) test DB: Berlin.sqlite (created by ogr2ogr)

#2) you already have successfully create a VIEW:

CREATE VIEW osm_line_highway_not_null AS
SELECT OGC_FID AS ROWID, GEOMETRY, osm_id ,highway,
  ref,name, tunnel
FROM osm_line
WHERE highway IS NOT NULL;

please note: this is (partially) invalid.
when you create some VIEW it's *always* a good
practice to explicitly assign an alias-name to
each column:

CREATE VIEW osm_line_highway_not_null AS
SELECT OGC_FID AS ROWID, GEOMETRY AS GEOMETRY,
  osm_id AS osm_id, highway AS highway,
  ref AS ref, name AS name, tunnel AS tunnel
FROM osm_line
WHERE highway IS NOT NULL;

more or less the same, a little bit more pedantic:
but this simple extra-care will save you some big
headaches afterwards (e.g. using the VIEW on QGIS).

#3) registering the VIEW on "views_geometry_columns":

INSERT INTO views_geometry_columns
  (view_name, view_geometry, view_rowid, f_table_name,
  f_geometry_column)
VALUES ('osm_line_highway_not_null', 'GEOMETRY', 'ROWID',
  'osm_line', 'GEOMETRY');
*** FAILURE *** "SQLite SQL error: foreign key mismatch"

attempting to execute yet another time the same identical
statement, this time using ogr2ogr, the INSERT will be
a successful one.

Really puzzling, isn't ?


POST MORTEM:
=====================
ogr2ogr have created a *broken* "geometry_columns" table:
there isn't any Primary Key defined at all :-(

this obviously happens because ogr2ogr doesn't invokes
(as required) InitSpatialMetadata() in order to correctly
initialize the DB: ogr2ogr directly invokes a generic
"CREATE TABLE ..." statement of its own: but following this
hazardous approach the metadata tables are incorrectly defined.

why we'll get a "foreign key mismatch" ?
really simple to explain: lacking the corresponding Primary
Key, the Foreign Key constraint will inexorably fail on
any case.

why ogr2ogr will succeed instead ?
this too is quite simple to explain: in order to
make Primary/Foreign Key constraints effective, an
appropriate directive is required to be executed:
PRAGMA foreign_keys = 1;
obviously ogr2ogr omits to perform this step, thus
posing the whole DB referential integrity at serious
risk.

conclusion: using the current implementation of
ogr2ogr in order to create any SpatiaLite DB
is a really hazardous option.
I've already prepared several patches for OGR:
and I hope to be able to release all them ASAP.

bye Sandro "

-Jukka Rahkonen-