Mapserver 7.0.6 from MS4W does not find spatial index from Spatialite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Mapserver 7.0.6 from MS4W does not find spatial index from Spatialite

jratike80

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');

returns 1

 

-Jukka Rahkonen-

 


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Mapserver 7.0.6 from MS4W does not find spatial index from Spatialite

Even Rouault-2

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Mapserver 7.0.6 from MS4W does not find spatial index from Spatialite

jratike80

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