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

Even Rouault even.rouault at mines-paris.org
Tue Aug 30 16:28:22 EDT 2011


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.

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


More information about the mapserver-users mailing list