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

[Rahkonen Jukka (MML)]

Hi,


My db was made with spatialite-gui and it creates geometry column 'Geometry' and index as


CREATE VIRTUAL TABLE "idx_states_Geometry" USING rtree(
pkid, xmin, xmax, ymin, ymax)


So I think that your guess is not correct.


I copied the database with ogr2ogr and then the spatial index is found as can be seen from the Mapserver log:


[Fri Aug 25 23:26:06 2017].755000 msOGRFileOpen(): Layer 0 has spatial index enabled
[Fri Aug 25 23:26:06 2017].755000 msOGRFileWhichShapes: Setting spatial filter to -124.70843703125 21.84359421875 -66.99236296875 52.48410578125


Column in now 'GEOMETRY' and index

CREATE VIRTUAL TABLE "idx_states_GEOMETRY" USING rtree(pkid, xmin, xmax, ymin, ymax)


I can't guess what goes wrong. I believe that it would be safe to compare the names with "tolower". You can use case sensitive names in SQLite ('Geometry' or 'GEOMETRY') but you can't create both 'Geometry' and 'GEOMETRY' into the same table if I remember right.


-Jukka-




________________________________
Lähettäjä: Even Rouault <even.rouault at spatialys.com>
Lähetetty: 25. elokuuta 2017 22:14
Vastaanottaja: mapserver-users at lists.osgeo.org
Kopio: Rahkonen Jukka (MML)
Aihe: Re: [mapserver-users] Mapserver 7.0.6 from MS4W does not find spatial index from Spatialite


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/e7dfec79/attachment.htm>