[mapserver-users] Using spatialite with mapserver

Fri Aug 3 01:38:27 PDT 2012

Hi,

I correct myself, support for multiple geometry columns is not a bug but a missing feature. I just wrote about it into GDAL-dev list. Let's see what happens.

When it comes to error with 'limit 200000' I believe that the real problem is in a different place.  I bet that there is a faulty geometry somewhere between 20000 and 200000 geometries. In that case OGR throws an error and stops. For Mapserver users it would be extremely much better if OGR in such case just skips the error and continues. I know the issue because it is common with OpenSteetMap polygons. As a workaround I have myself just deleted all the non-valid geometries from Spatialite with "delete from table where ST_IsValid(geometry)=0".

-Jukka Rahkonen-

Andrea Peri wrote:

>This looks like an OGR bug, I have correct info in views_geometry_columns (refering to
>geom2 of the main table) and in geometry_columns (both geom1 and geom2 has
>records).
>
>For now, forget multiple geometries and create new single geometry tables and enjoy the
>speed of Spatialite and Mapserver.
>
>-Jukka Rahkonen-

thx Jukka,

Is an unpleasant situation this ogr bug.
My table af many fields attribute . it is about 15 Gbyte (gasp!)
so split it in two tables so separate the geometries is not a beatiful action. :)

But I'm notice another question.

Infact after remove the secondary geometries (sigh), I can see that there is a strange liit due to numer of elements.
Infact Mapserver work with this sql:

    DATA 'SELECT field1, field2, field3, geometry from table LIMIT 20000'

but don't work with this other sql:

    DATA 'SELECT field1, field2, field3, geometry from table LIMIT 200000'

Seem there is a max number of elements retrieval.
The strange is that with this second sql MapServer don't do nothing.
Simply it end its work without generate no map.
No error log report is write.

Perhaps there is a setting to say how much memory mapserver (or ogr) must use for resultset from DB ?

Thx.

2012/8/2 Rahkonen Jukka <Jukka.Rahkonen at mmmtike.fi<mailto:Jukka.Rahkonen at mmmtike.fi>>
Hi again,

Tried what ogrinfo sees in case of two geometries. It does not look good.

FID Column = rowid
Geometry Column = geom1
geom2: Binary (0.0)
OGRFeature(test2):1
  geom2 (Binary) = 0001110F0000958B843BA9EC354126C76C9BD95A5A41958B843BA9EC35412
6C76C9BD95A...
  POINT (1436841.2324912299 6908774.4285142776)

OGRFeature(test2):2
  geom2 (Binary) = 0001110F000081AE1AFBAEEF3541FB1B8FFA035B5A4181AE1AFBAEEF3541F
B1B8FFA035B...
  POINT (1437614.9808758798 6908943.9149846984)

So that means than only the first geometry can be user with OGR. Next I tried to create a spatial view with spatialite-gui by selecting only geom2 into the view. This fails too

D:\ms4w\data>ogrinfo berlin.sqlite test3
INFO: Open of `berlin.sqlite'
      using driver `SQLite' successful.
ERROR 1: Underlying layer test2 for view test3 has not expected geometry column
name (geom1 instead of geom2)

Layer name: test3
Geometry: Unknown (any)
Feature Count: 0
Layer SRS WKT:
(unknown)
FID Column = ROWID
Geometry Column = geom2

This looks like an OGR bug, I have correct info in views_geometry_columns (refering to geom2 of the main table) and in geometry_columns (both geom1 and geom2 has records).

For now, forget multiple geometries and create new single geometry tables and enjoy the speed of Spatialite and Mapserver.

-Jukka Rahkonen-

________________________________________
Lähettäjä: mapserver-users-bounces at lists.osgeo.org<mailto:mapserver-users-bounces at lists.osgeo.org> [mapserver-users-bounces at lists.osgeo.org<mailto:mapserver-users-bounces at lists.osgeo.org>] käyttäjän Rahkonen Jukka [Jukka.Rahkonen at mmmtike.fi<mailto:Jukka.Rahkonen at mmmtike.fi>] puolesta
Lähetetty: 3. elokuuta 2012 0:27
Vastaanottaja: Andrea Peri; mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>
Aihe: Re: [mapserver-users] Using spatialite with mapserver

Hi,

The SELECT alternative works fine for me but I do not have multiple geometries. I made a very fast test on Spatialite side and it seems to be possible to run "Recover geometry column" and "Create spatial index" for multiple geometries but I did not test the result with Mapserver.

I would recommend you to check first your geometry_columns table and see it there is a line for the geometry you want to use and if spatial index is OK. If they are OK make a simplified test. Create a single geometry table as
CREATE table test as
SELECT geom1, attr1... FROM...
Do "Recover geometry column" and "Create spatial index" things for the new table and have a try with Mapserver.

GDAL version must be 1.9 or higher or things will be far too slow.
This document may also be useful for you
http://latuviitta.org/documents/Super_easy_WMS_server.pdf

-Jukka Rahkonen-

________________________________
Lähettäjä: mapserver-users-bounces at lists.osgeo.org<mailto:mapserver-users-bounces at lists.osgeo.org> [mapserver-users-bounces at lists.osgeo.org<mailto:mapserver-users-bounces at lists.osgeo.org>] käyttäjän Andrea Peri [aperi2007 at gmail.com<mailto:aperi2007 at gmail.com>] puolesta
Lähetetty: 2. elokuuta 2012 23:03
Vastaanottaja: mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>
Aihe: [mapserver-users] Using spatialite with mapserver

Hi,
I'm try-ing to use spatialite with mapserver.

is this mapfile configuration ok for spatialite ?

    CONNECTIONTYPE OGR
    CONNECTION '/..path-to../spatialite_file.sqlite'
    DATA 'SELECT field1, field2, field3, geometry from table'

It seem don't work.

Instead work perfectly this other configuration:

    CONNECTIONTYPE OGR
    CONNECTION '/home/vector/vettoriali/db_catasto/regione_toscana/adt_toscana.sqlite'
    DATA 'table'

Unfortunately this second configuration don't allow to choose the geometry field , necessary when spatialite table as two or more geometry field.

Thx,

--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------
_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/mapserver-users

--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20120803/7bdd7032/attachment.htm>