[mapserver-users] Mapserver 7.0.6 from MS4W does not find spatial index from Spatialite

[Even Rouault]

On vendredi 25 août 2017 12:32:14 CEST Rahkonen Jukka (MML) wrote:
> Hi,
> 
> I have a Spatialite database which has a valid and consistent spatial index
> if I ask spatialite-gui to check it. However Mapserver 7.0.6 from MS4W
> 3.2.2 does not find it.
> 
> Layer is defined as
> 
> CONNECTIONTYPE OGR
> DEBUG 5
> CONNECTION "\ms4w_data\apps\wms-wfs.sqlite"
> DATA "select * from states"
> 
> [Fri Aug 25 15:17:41 2017].225000 OGROPen(\ms4w_data\apps\wms-wfs.sqlite)
> [Fri Aug 25 15:17:41 2017].235000
> msConnPoolRegister(states,\ms4w_data\apps\wms-wfs.sqlite,00A0F2D0) [Fri Aug
> 25 15:17:41 2017].236000 msOGRFileOpen(): Layer select * from states has
> spatial index disabled [Fri Aug 25 15:17:41 2017].236000
> msOGRFileWhichShapes: SQL = select * from states WHERE 
> MbrIntersects("Geometry",
> BuildMbr(-110.556532,27.919128,-78.556643,44.907341)).
> 
> I wonder what is the check that is performed. Obviously not either of these
> two:
> 
> select spatial_index_enabled from geometry_columns where
> f_table_name='states'; returns 1
> select checkspatialindex('states','geometry');


There's this test that is done:

      char* pszRequest = NULL;
      pszRequest = msStringConcatenate(pszRequest,
          "SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'idx_");
      pszRequest = msStringConcatenate(pszRequest,
                                       psInfo->pszSpatialFilterTableName);
      pszRequest = msStringConcatenate(pszRequest, "_");
      pszRequest = msStringConcatenate(pszRequest,
                                    OGR_L_GetGeometryColumn(psInfo->hLayer));
      pszRequest = msStringConcatenate(pszRequest, "'");

So it checks the existence of idx_{tablename}_{geometryname} table, but in a case sensitive 
way. Here I see from the MbrIntersects() call that the geometry name is Geometry, so I guess 
it tries idx_states_Geometry, but that actual name must be idx_states_geometry.

Can you retry by putting the table and geometry column name in lower case ?

If that works, the test should likely be fixed to be case insensitive.


> returns 1
> 
> -Jukka Rahkonen-


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20170825/7a15eb7c/attachment.html>