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 <[email protected]>
Lähetetty: 25. elokuuta 2017 22:14
Vastaanottaja: [email protected]
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
_______________________________________________
mapserver-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
