[mapserver-users] Re: Mapserver unnecessary slow with Spatialite
Rahkonen Jukka
Jukka.Rahkonen at mmmtike.fi
Wed Aug 31 01:09:31 EDT 2011
Hi,
I did have the views on my mind but I thought that if they were used ogr would perhaps loose the spatial index of the Spatialite table. Good if views will work. It feels like a solid solution even it means playing both with the mapfile and on the Spatialite side when creating new layers.
-Jukka Rahkonen-
Even Rouault wrote:
>
> 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