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

[Stephen Woodbridge]

On 2/15/2012 7:04 AM, Rahkonen Jukka wrote:
> Even Rouault wrote:
>>
>> Le mardi 14 février 2012 09:24:15, Rahkonen Jukka a écrit :
>>> Rahkonen Jukka wrote:
>>>> Even Rouault wrote:
>>>>>> I've commited improvements in GDAL trunk for both points (
>>>>>> http://trac.osgeo.org/gdal/changeset/23944 and
>>>>>> http://trac.osgeo.org/gdal/changeset/23945 ) that make the
>>>>
>>>> above request go
>>>>
>>>>>> from 1.7 sec to 0.54 sec . I don't think there's any more
>>>>
>>>> significant speed
>>>>
>>>>>> gain to expect now (at least on OGR side).
>>>>>
>>>>> Actually, I found a new improvement (committed in
>>>>> http://trac.osgeo.org/gdal/changeset/23946) that make the
>>>>
>>>> query time go down
>>>>
>>>>> to 0.07 sec (*) ! There was a slowness when opening the
>>>>
>>>> layer due to the
>>>>
>>>>> presence of an ORDER BY clause in the DATA string, that was
>>>>
>>>> evaluated before
>>>>
>>>>> setting the spatial filter.
>>>>>
>>>>> (*) For comparison, I translated your database to Postgis,
>>>>
>>>> and set-up the
>>>>
>>>>> connection mapfile to use that Postgis database (still
>>>>
>>>> through OGR, not the
>>>>
>>>>> MapServer Postgis backend). The same request with OGR PG is
>>>>
>>>> run in 0.18 sec.
>>>>
>>>> Awesome!  About 25 times faster in the evening than it was in
>>>> the morning. My opinion is that because Spatialite seems to be,
>>>> at least in this case, more that two times faster backend for
>>>> Mapserver than PostGIS it should lead to some intensive and
>>>> controlled testing now.
>>>
>>> I updated the cgi-bin directory of my MS4W 3.0.4beta1 with
>> the mapserv.exe
>>> and dll files taken from the brand new binaries from
>>> http://www.gisinternals.com/sdk/ (contains Mapserver and
>> GDAL revisions
>>> r13144, r23972, respectively).
>>>
>>> As a result Mapserver is really serving me 10, or 25 or
>> even 40 times
>>> faster than it did before the update measured as WMS throughput
>>> (images/minute). The times include rendering times and all
>> the lags in the
>>> system so the difference tells exactly how the end user
>> feels it. Greatest
>>> enhancement is with close zooms because then the apatial index
>>> bites hardest. And at least with my computer Spatialite backend
>>> is clearly faster than PostGIS with the same data and it may be
>>> even
>> faster than
>>> shapefiles but it is too early to say that really. But this
>> is absolutely
>>> something worth more testing.
>>
>> I tested a bit with shapefiles with your Berlin OSM database. Even
>> after adding spatial index (.qix) and attribute index (.idm&  .idn)
>> on fields in where clauses, it is a bit slower than spatialite. If
>> you remove all columns from the DBF that are not necessary for the
>> rendering, it improves things a bit again. You can also try the new
>> special SQL command "RESIZE table_name" for shapefiles I've
>> committed yesterday (see http://gdal.org/ogr/drv_shapefile.html )
>> to adjust columns to their minimum needed size.
>
> I prepared a new Spatialite database from the OSM data of Finland. It
> contains 230000 points, 480000 lines and 440000 polygons and takes
> 700 MB on a disk. I made indexes for everything that is used in the
> WHERE selections and the result is just wonderful.  If there is just
> few features to render the msDrawMap() takes just 0.01 - 0.10 seconds
> per layer (1000x800 sized output). And my computer is eight year old
> Windows WP with IDE disks and 1 GB of memory. Rendering lots of
> features takes naturally longer time but I do believe that
> technically GDAL/OGR/Mapserver side is in the order now and there is
> not much room for further improvements there.
>
> Best way for improving the speed of the service from this point is to
> generalize the data for rendering at small scales. Spatialite has
> Simplify() and SimplifyPreserveTopolygy() functions so it will be
> very easy to create simplified versions of the data into the same
> database. Features which are unreasonably small for rendering at the
> target scale can be sorted out at the same. I can imagine that one
> day some clever individual will write a tool that automatically runs
> SQL script which creates a set of new tables with simplified
> geometries into Spatialite and at the same time writes a
> corresponding mapfile which collects those tables as scale dependent
> layers under one Mapserver layer group.
>
> By the way, it would be very nice if the full stop at
> TopologyExceptions could be avoided. My Spatialite databases are
> created by ogr2ogr and self-intersections did not stop OGR then so
> perhaps it can be made to tolerate them also in this case. Now I
> fixed this problem in a crude way by running "DELETE from osm_polygon
> where ST_IsValid(GEOMETRY)<=0".

There is a hack in postGIS that might work here:

update osm_polygon set GEOMETRY=ST_Buffer(GEOMERTY, 0.0) where 
ST_IsValid(GEOMETRY)<=0;

This will fix some if not all the polygons. Then run your delete 
afterwards to clean out the rest. This may/may not work with SpatiaLite.

-Steve