[mapserver-users] Using spatialite with mapserver

Fri Aug 3 16:19:06 PDT 2012

Hi

the 200.000 limit was effectively an invalid geometries,

thx.

About the multi-geometry question.

I'm try-ing the option

DATA 'table(geometry)'

but I have a secondary problem.
Infact this sintax seem deny the access to other fields.
So was impossible to set a label.

I'm missing something ?

Thx,

Andrea

2012/8/3 Rahkonen Jukka <Jukka.Rahkonen at mmmtike.fi>

>  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>****
>
> 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 [
> mapserver-users-bounces at lists.osgeo.org] käyttäjän
> Rahkonen Jukka [Jukka.Rahkonen at mmmtike.fi] puolesta
> Lähetetty: 3. elokuuta 2012 0:27
> Vastaanottaja: Andrea Peri; 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 [
> mapserver-users-bounces at lists.osgeo.org] käyttäjän Andrea Peri [
> aperi2007 at gmail.com] puolesta
> Lähetetty: 2. elokuuta 2012 23:03
> Vastaanottaja: 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
> http://lists.osgeo.org/mailman/listinfo/mapserver-users****
>
>
>
>
> --
> -----------------
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -----------------****
>
> _______________________________________________
> mapserver-users mailing list
> 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/20120804/4ebe0c5c/attachment.htm>