[mapserver-users] Does Mapserver utilise Spatialite spatial index correctly?

Sat Feb 11 04:08:07 EST 2012

Hi,

Sorry about long message but I like pretty much about the idea to be able to send Mapserver, mapfiles and data in one single package so that the end user would only need to unzip, launch Mapserver and enjoy. With the package like the one I am pointing later in this message it is already possible, but the resulting service is not fast enough. However, GDAL is pretty fast with Spatialite so I believe and hope that there is just some minory thing to adjust on the Mapserver side for making it ten times faster with Spatialite than it is now.

I prepared a package for demonstrating why I believe that Mapserver does not make queries in the best possible way.

The package is rather big because it contains the whole MS4W 3.0.4beta1 and mapfiles and a Spatilite database from OpenStreetMap data. It is ready to use on Windows after doing these steps:

- Dowload from http://latuviitta.org/documents/MS_GDAL_Spatialite_test.zip
- Unzip to a root of any disk drive
- Open a command window, go to [disk_letter]:\ms4w\apache\bin and give command "httpd"

First, make a query with ogrinfo. GDAL 1.9.0 is included in the package, just go to \ms4w\ first and run the setenv.bat first.

D:\ms4w\data>ogrinfo -so berlin.sqlite osm_line -spat 1496458.316816 6889766.373
154 1497120.112054 6890295.209530

This will be pretty fast.

Now send the following url with a browser. It will make Mapserver to query the same layer 

http://localhost:8060/cgi-bin/mapserv.exe?map=/ms4w/osm_maps/osm_wms.map&REQUEST=GetMap&SERVICE=WMS&VERSION=1.1.1&WIDTH=663&HEIGHT=530&LAYERS=roadsclose_01&TRANSPARENT=TRUE&FORMAT=image%2Fpng&BBOX=1496457.8169701756,6889765.873308353,1497120.611899381,6890295.70937544&SRS=EPSG:3857&STYLES=

I suppose it will take a few seconds before the image is ready.  For me it took 9,1 seconds. Here is the full listing about what I got into log file with DEBUG 5 

[Sat Feb 11 10:06:00 2012].726000 msOGRFileOpen(\ms4w\data\berlin.sqlite)...
[Sat Feb 11 10:06:00 2012].730000 OGROPen(\ms4w\data\berlin.sqlite)
[Sat Feb 11 10:06:03 2012].732000 msConnPoolRegister(roadsclose_01,\ms4w\data\berlin.sqlite,03771718)
[Sat Feb 11 10:06:06 2012].435000 msOGRFileWhichShapes: Setting spatial filter to 1496458.316816 6889766.373154 1497120.112054 6890295.209530
[Sat Feb 11 10:06:09 2012].52000 msOGRFileNextShape: Returning shape=801, tile=0
[Sat Feb 11 10:06:09 2012].369000 msOGRFileNextShape: Returning shape=29054, tile=0
[Sat Feb 11 10:06:09 2012].371000 msOGRFileNextShape: Returning shape=29154, tile=0
[Sat Feb 11 10:06:09 2012].372000 msOGRFileNextShape: Returning shape=29173, tile=0
[Sat Feb 11 10:06:09 2012].373000 msOGRFileNextShape: Returning shape=29275, tile=0
[Sat Feb 11 10:06:09 2012].377000 msOGRFileNextShape: Returning shape=29483, tile=0
[Sat Feb 11 10:06:09 2012].378000 msOGRFileNextShape: Returning shape=29485, tile=0
[Sat Feb 11 10:06:09 2012].378000 msOGRFileNextShape: Returning shape=29504, tile=0
[Sat Feb 11 10:06:09 2012].379000 msOGRFileNextShape: Returning shape=29579, tile=0
[Sat Feb 11 10:06:09 2012].380000 msOGRFileNextShape: Returning shape=29645, tile=0
[Sat Feb 11 10:06:09 2012].380000 msOGRFileNextShape: Returning shape=29657, tile=0
[Sat Feb 11 10:06:09 2012].381000 msOGRFileNextShape: Returning shape=29658, tile=0
[Sat Feb 11 10:06:09 2012].381000 msOGRFileNextShape: Returning shape=29674, tile=0
[Sat Feb 11 10:06:09 2012].381000 msOGRFileNextShape: Returning shape=29680, tile=0
[Sat Feb 11 10:06:09 2012].381000 msOGRFileNextShape: Returning shape=29681, tile=0
[Sat Feb 11 10:06:09 2012].381000 msOGRFileNextShape: Returning shape=29695, tile=0
[Sat Feb 11 10:06:09 2012].383000 msOGRFileNextShape: Returning shape=29754, tile=0
[Sat Feb 11 10:06:09 2012].384000 msOGRFileNextShape: Returning shape=29793, tile=0
[Sat Feb 11 10:06:09 2012].385000 msOGRFileNextShape: Returning shape=29802, tile=0
[Sat Feb 11 10:06:09 2012].385000 msOGRFileNextShape: Returning shape=29817, tile=0
[Sat Feb 11 10:06:09 2012].385000 msOGRFileNextShape: Returning shape=29818, tile=0
[Sat Feb 11 10:06:09 2012].386000 msOGRFileNextShape: Returning shape=29819, tile=0
[Sat Feb 11 10:06:09 2012].387000 msOGRFileNextShape: Returning shape=29911, tile=0
[Sat Feb 11 10:06:09 2012].387000 msOGRFileNextShape: Returning shape=29924, tile=0
[Sat Feb 11 10:06:09 2012].387000 msOGRFileNextShape: Returning shape=29926, tile=0
[Sat Feb 11 10:06:09 2012].387000 msOGRFileNextShape: Returning shape=29929, tile=0
[Sat Feb 11 10:06:09 2012].388000 msOGRFileNextShape: Returning shape=29955, tile=0
[Sat Feb 11 10:06:09 2012].388000 msOGRFileNextShape: Returning shape=29969, tile=0
[Sat Feb 11 10:06:09 2012].388000 msOGRFileNextShape: Returning shape=29970, tile=0
[Sat Feb 11 10:06:09 2012].389000 msOGRFileNextShape: Returning shape=29972, tile=0
[Sat Feb 11 10:06:09 2012].389000 msOGRFileNextShape: Returning shape=29973, tile=0
[Sat Feb 11 10:06:09 2012].389000 msOGRFileNextShape: Returning shape=29991, tile=0
[Sat Feb 11 10:06:09 2012].389000 msOGRFileNextShape: Returning shape=30022, tile=0
[Sat Feb 11 10:06:09 2012].390000 msOGRFileNextShape: Returning shape=30065, tile=0
[Sat Feb 11 10:06:09 2012].390000 msOGRFileNextShape: Returning shape=30084, tile=0
[Sat Feb 11 10:06:09 2012].392000 msOGRFileNextShape: Returning shape=30174, tile=0
[Sat Feb 11 10:06:09 2012].393000 msOGRFileNextShape: Returning shape=30175, tile=0
[Sat Feb 11 10:06:09 2012].393000 msOGRFileNextShape: Returning shape=30176, tile=0
[Sat Feb 11 10:06:09 2012].394000 msOGRFileNextShape: Returning shape=30198, tile=0
[Sat Feb 11 10:06:09 2012].395000 msOGRFileNextShape: Returning shape=30255, tile=0
[Sat Feb 11 10:06:09 2012].702000 msOGRFileNextShape: Returning shape=57673, tile=0
[Sat Feb 11 10:06:09 2012].707000 msOGRFileNextShape: Returning shape=57891, tile=0
[Sat Feb 11 10:06:09 2012].708000 msOGRFileNextShape: Returning shape=57929, tile=0
[Sat Feb 11 10:06:09 2012].708000 msOGRFileNextShape: Returning shape=57937, tile=0
[Sat Feb 11 10:06:09 2012].709000 msOGRFileNextShape: Returning shape=57938, tile=0
[Sat Feb 11 10:06:09 2012].710000 msOGRFileNextShape: Returning shape=57965, tile=0
[Sat Feb 11 10:06:09 2012].775000 msOGRFileNextShape: Returning shape=62981, tile=0
[Sat Feb 11 10:06:09 2012].862000 msOGRFileNextShape: Returning shape=69883, tile=0
[Sat Feb 11 10:06:09 2012].864000 msOGRFileNextShape: Returning shape=69972, tile=0
[Sat Feb 11 10:06:09 2012].864000 msOGRFileNextShape: Returning shape=69973, tile=0
[Sat Feb 11 10:06:09 2012].869000 msOGRFileNextShape: Returning shape=70356, tile=0
[Sat Feb 11 10:06:09 2012].869000 msOGRFileNextShape: Returning shape=70359, tile=0
[Sat Feb 11 10:06:09 2012].869000 msOGRFileNextShape: Returning shape=70361, tile=0
[Sat Feb 11 10:06:09 2012].874000 msOGRFileNextShape: Returning shape=70781, tile=0
[Sat Feb 11 10:06:09 2012].874000 msOGRFileNextShape: Returning shape=70782, tile=0
[Sat Feb 11 10:06:09 2012].874000 msOGRFileNextShape: Returning shape=70783, tile=0
[Sat Feb 11 10:06:09 2012].888000 msOGRFileNextShape: Returning MS_DONE (no more shapes)
[Sat Feb 11 10:06:09 2012].889000 msOGRLayerClose(\ms4w\data\berlin.sqlite).
[Sat Feb 11 10:06:09 2012].889000 msOGRFileClose(\ms4w\data\berlin.sqlite,-1).
[Sat Feb 11 10:06:09 2012].889000 msConnPoolRelease(roadsclose_01,\ms4w\data\berlin.sqlite,03771718)
[Sat Feb 11 10:06:09 2012].889000 msConnPoolClose(\ms4w\data\berlin.sqlite,03771718)
[Sat Feb 11 10:06:09 2012].898000 msDrawMap(): Layer 16 (roadsclose_01), 9.174s
[Sat Feb 11 10:06:10 2012].3000 freeLayer(): freeing layer at 036FF3F8.

-Jukka Rahkonen-

________________________________________
Lähettäjä: Even Rouault [even.rouault at mines-paris.org]
Lähetetty: 10. helmikuuta 2012 19:05
Vastaanottaja: Lime, Steve D (DNR)
Kopio: Rahkonen Jukka; 'mapserver-users at lists.osgeo.org'
Aihe: RE: [mapserver-users] Does Mapserver utilise Spatialite spatial index correctly?

Selon "Lime, Steve D (DNR)" <Steve.Lime at state.mn.us>:

> The changeset in OGR is in the spatialite driver so it doesn't look like a
> program using the OGR APIs would need to do anything special. Someone
> familiar with the OGR driver will have to weigh in I suspect...

Does the OGR backend of MapServer always use OGR_L_SetSpatialFilter() ? I've not
MapServer source code under my eyes right now, but I'm wondering if there's not
a different code path according to if you specify in the mapfile a OGR layer
name or a OGR SQL.

>
> Steve
>
> From: mapserver-users-bounces at lists.osgeo.org
> [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Rahkonen Jukka
> Sent: Friday, February 10, 2012 9:36 AM
> To: 'mapserver-users at lists.osgeo.org'
> Subject: Re: [mapserver-users] Does Mapserver utilise Spatialite spatial
> index correctly?
>
> Hi,
>
> OK, lets put it this way:
> Is it sure that Mapserver is firing the BBOX query so that GDAL/OGR
> understands to follow the path that is implemented in
> http://trac.osgeo.org/gdal/changeset/23008
> instead of making an inefficient MBRIntersects()  query or some other slow
> query?
>
> I do not believe that a user like me has a possibility to check what really
> happens with Spatialite like I can do with PostGIS and Oracle because
> Spatialite does not collect a log file.
>
> -Jukka Rahkonen-
>
> Lime, Steve D (DNR)  wrote:
> There is no native support for Spatialite in MapServer, it's all through
> GDAL/OGR.
>
> Steve
> ________________________________
> From: mapserver-users-bounces at lists.osgeo.org
> [mapserver-users-bounces at lists.osgeo.org] on behalf of Rahkonen Jukka
> [Jukka.Rahkonen at mmmtike.fi]
> Sent: Friday, February 10, 2012 9:12 AM
> To: 'mapserver-users at lists.osgeo.org'
> Subject: [mapserver-users] Does Mapserver utilise Spatialite spatial index
> correctly?
> Hi,
>
> GDAL was made to utilise Spatialite spatial index effectively if it exists
> with ticket
> http://trac.osgeo.org/gdal/ticket/4212
> After this change ogr2ogr with -spat bounding box query is much faster.
> However, when testing MS4W 3.0.4beta1 which comes with GDAL 1.9, the
> rendering speed of Spatialite data with Mapserver is pretty slow. It is
> evenly slow when zoomed out and zoomed in which makes me believe that the
> spatial index is perhaps not used.
>
> Could it be that Mapserver does not utilise the improved method that was
> implemented for GDAL 1.9 when it is doing spatial queries from Spatialite?
>
> -Jukka Rahkonen-
>