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

[Rahkonen Jukka]

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.

SELECT geometry, osm_id, highway,ref, name, tunnel 
FROM osm_line 
WHERE highway IS NOT NULL AND ROWID IN (
  SELECT pkid FROM idx_osm_line_GEOMETRY
  WHERE xmax > 1487400 AND xmin < 1487500 
    AND ymax > 6894200 AND ymin < 6894300);


If somebody is interested in having a try with Mapserver and Spatialite there is a sample database at http://188.64.1.61/tiedostot/berlin_2011_08_25.zip and corresponding mapfiles at http://latuviitta.org/documents/osm_maps.zip
Use with MS4W should be as easy as unzipping the berlin.sqlite file into directory \ms4w\data\ and mapfiles into directory \ms4w\osm_maps\. After that the Berlin OpenStreetMap WMS should be ready at http://localhost/cgi-bin/mapserv.exe?map=/ms4w/osm_maps/osm_wms.map

Because the spatial index issue the service will be rather slow.

-Jukka Rahkonen-


> -----Alkuperäinen viesti-----
> Lähettäjä: Rahkonen Jukka 
> Lähetetty: 28. elokuuta 2011 23:56
> Vastaanottaja: MapServer
> Aihe: Mapserver unnecessary slow with Spatialite
> 
> Hi,
> 
> I have been playing a bit with Mapserver 6.0 and Spatialite 
> and I was a bit surprised about how slow this combination is 
> with any bigger Spatialite database. I asked for an opinion 
> from Mr. Alesandro Furieri who wrote me some hints for making 
> efficient queries which are utilising spatial index.
> 
> This query comes basically from OGR Spatialite driver page 
> http://www.gdal.org/ogr/drv_sqlite.html
> 
> SELECT geometry, osm_id, highway, ref, name, tunnel
> FROM osm_line
> WHERE highway IS NOT NULL AND
>   MBRIntersects(geometry,
>     BuildMBR(1487400, 6894200, 1487500, 6894300))
> ORDER BY z_order;
> 
> OGR documentation says that query is using spatial index. 
> However, Alesandro writes that it does not and suggests 
> another SQL which is 20 times faster.
> 
> Alesandro wrote:
> " Berlin dataset, your original SQL: I've simply adjusted
> the MBR coords.
> Execution time: 397 millis
> Please note: this layer contains very few rows (< 100k):
> so not using the Spatial Index imposes a very bland (but
> still noticeable) overhead.
> 
> =================
> 
> SELECT geometry, osm_id, highway,ref, name, tunnel
> FROM osm_line
> WHERE highway IS NOT NULL AND ROWID IN (
>   SELECT pkid FROM idx_osm_line_GEOMETRY
>   WHERE xmax > 1487400 AND xmin < 1487500
>     AND ymax > 6894200 AND ymin < 6894300)
> ORDER BY z_order;
> 
> Same query: this time using the Spatial Index.
> Execution time: 17 millis ... really a lot better :-D 
> and if applied to some larger dataset (> 1 million rows)
> the difference will become absolutely dramatic, as you
> are already experiencing in the Finland test case.
> 
> SpatiaLite isn't PostGIS: you *must* explicitly write
> your SQL Queries in such a way to access the corresponding
> Spatial Index table as appropriate.
> 
> Please note well: in SQLite/SpatiaLite the R*Tree
> Spatial Index simply is another table between many
> others.
> The SQL engine has absolutely no idea that a strict
> correlation exists between the "geometry" table and
> the corresponding R*Tree.
> So you are explicitly required to define an explicit
> sub-query in order to inquiry the R*Tree. "
> 
> It seems that Mapserver, or at least the version shipped with 
> MS4W, does not use Spatialite in an efficient way.  With big 
> Spatialite tables and small bounding boxes when spatial index 
> can effectively limit the query it should be possible to make 
> Mapserver to render at least 10 times faster. Is anybody 
> interested in having a look on this? Or is this perhaps more 
> suitable question for gdal-dev mailing list?
> 
> -Jukka Rahkonen-