Re: [mapserver-users] Using spatialite with mapserver
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 2' but don't work with this other sql: DATA 'SELECT field1, field2, field3, geometry from table LIMIT 20' 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.rahko...@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) = 0001110F958B843BA9EC354126C76C9BD95A5A41958B843BA9EC35412 6C76C9BD95A... POINT (1436841.2324912299 6908774.4285142776) OGRFeature(test2):2 geom2 (Binary) = 0001110F81AE1AFBAEEF3541FB1B8FFA035B5A4181AE1AFBAEEF3541F 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\dataogrinfo 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-boun...@lists.osgeo.org [ mapserver-users-boun...@lists.osgeo.org] k#228;ytt#228;j#228;n Rahkonen Jukka [jukka.rahko...@mmmtike.fi] puolesta Lähetetty: 3. elokuuta 2012 0:27 Vastaanottaja: Andrea Peri; mapserver-users@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-boun...@lists.osgeo.org [ mapserver-users-boun...@lists.osgeo.org] käyttäjän Andrea Peri [ aperi2...@gmail.com] puolesta Lähetetty: 2. elokuuta 2012 23:03 Vastaanottaja: mapserver-users@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@lists.osgeo.org http
Re: [mapserver-users] Using spatialite with mapserver
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 20' I believe that the real problem is in a different place. I bet that there is a faulty geometry somewhere between 2 and 20 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 2' but don't work with this other sql: DATA 'SELECT field1, field2, field3, geometry from table LIMIT 20' 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.rahko...@mmmtike.fimailto:jukka.rahko...@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) = 0001110F958B843BA9EC354126C76C9BD95A5A41958B843BA9EC35412 6C76C9BD95A... POINT (1436841.2324912299 6908774.4285142776) OGRFeature(test2):2 geom2 (Binary) = 0001110F81AE1AFBAEEF3541FB1B8FFA035B5A4181AE1AFBAEEF3541F 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\dataogrinfo 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-boun...@lists.osgeo.orgmailto:mapserver-users-boun...@lists.osgeo.org [mapserver-users-boun...@lists.osgeo.orgmailto:mapserver-users-boun...@lists.osgeo.org] k#228;ytt#228;j#228;n Rahkonen Jukka [jukka.rahko...@mmmtike.fimailto:jukka.rahko...@mmmtike.fi] puolesta Lähetetty: 3. elokuuta 2012 0:27 Vastaanottaja: Andrea Peri; mapserver-users@lists.osgeo.orgmailto:mapserver-users@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-boun...@lists.osgeo.orgmailto:mapserver-users-boun...@lists.osgeo.org [mapserver-users-boun...@lists.osgeo.orgmailto:mapserver-users-boun...@lists.osgeo.org
Re: [mapserver-users] Using spatialite with mapserver
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.rahko...@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 20’ I believe that the real problem is in a different place. I bet that there is a faulty geometry somewhere between 2 and 20 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 2' but don't work with this other sql: DATA 'SELECT field1, field2, field3, geometry from table LIMIT 20' 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.rahko...@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) = 0001110F958B843BA9EC354126C76C9BD95A5A41958B843BA9EC35412 6C76C9BD95A... POINT (1436841.2324912299 6908774.4285142776) OGRFeature(test2):2 geom2 (Binary) = 0001110F81AE1AFBAEEF3541FB1B8FFA035B5A4181AE1AFBAEEF3541F 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\dataogrinfo 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-boun...@lists.osgeo.org [ mapserver-users-boun...@lists.osgeo.org] k#228;ytt#228;j#228;n Rahkonen Jukka [jukka.rahko...@mmmtike.fi] puolesta Lähetetty: 3. elokuuta 2012 0:27 Vastaanottaja: Andrea Peri; mapserver-users@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
[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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
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-boun...@lists.osgeo.org [mapserver-users-boun...@lists.osgeo.org] käyttäjän Andrea Peri [aperi2...@gmail.com] puolesta Lähetetty: 2. elokuuta 2012 23:03 Vastaanottaja: mapserver-users@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Using spatialite with mapserver
On Thu, Aug 2, 2012 at 10:03 PM, Andrea Peri aperi2...@gmail.com wrote: 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. I think you just need to select the geometry field in the DATA parameter (at least this is the case for PostGIS and Oracle Spatial), so for your case try: DATA 'SELECT geometry from table' cheers p -- Paolo Corti Geospatial software developer web: http://www.paolocorti.net twitter: @capooti skype: capooti ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Using spatialite with mapserver
I an writing this night faster than I am thinking, sorry for that. It came into my mind to delete the row for geom1 from the geometry_columns and now it is possible to use geom2. OGR takes the first matching row from geometry_columns and because row for geom1 does not exist row for geom2 is found and everything works fine. However, this workaround makes only one geometry at a time usable for OGR and Mapserver because the first registered geometry column must match. ogrinfo: FID Column = ROWID Geometry Column = geom2 OGRFeature(test3):1 POINT (1436841.2324912299 6908774.4285142776) OGRFeature(test3):2 POINT (1437614.9808758798 6908943.9149846984) OGRFeature(test3):3 POINT (1438331.5778339701 6909101.5655383449) -Jukka- Lähettäjä: mapserver-users-boun...@lists.osgeo.org [mapserver-users-boun...@lists.osgeo.org] k#228;ytt#228;j#228;n Rahkonen Jukka [jukka.rahko...@mmmtike.fi] puolesta Lähetetty: 3. elokuuta 2012 0:46 Vastaanottaja: Andrea Peri; mapserver-users@lists.osgeo.org Aihe: Re: [mapserver-users] Using spatialite with mapserver 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) = 0001110F958B843BA9EC354126C76C9BD95A5A41958B843BA9EC35412 6C76C9BD95A... POINT (1436841.2324912299 6908774.4285142776) OGRFeature(test2):2 geom2 (Binary) = 0001110F81AE1AFBAEEF3541FB1B8FFA035B5A4181AE1AFBAEEF3541F 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\dataogrinfo 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-boun...@lists.osgeo.org [mapserver-users-boun...@lists.osgeo.org] k#228;ytt#228;j#228;n Rahkonen Jukka [jukka.rahko...@mmmtike.fi] puolesta Lähetetty: 3. elokuuta 2012 0:27 Vastaanottaja: Andrea Peri; mapserver-users@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-boun...@lists.osgeo.org [mapserver-users-boun...@lists.osgeo.org] käyttäjän Andrea Peri [aperi2...@gmail.com] puolesta Lähetetty: 2. elokuuta 2012 23:03 Vastaanottaja: mapserver-users@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
