Problem loading sqlite db.
Hello all,
Im receiving the following error when loading a sqlite3 spatial
layer as source data into tilemill.
Error: Sqlite Plugin: could not query table 'caop_almada' using
'PRAGMA table_info(caop_almada)'
Can someone point me any direction ?
Thanks
Jorge Vidinha
Comments are currently closed for this discussion. You can start a new one.
Support Staff 2 Posted by Tom MacWright on 03 Oct, 2011 03:31 PM
The table
caop_almadaexists? How was the sqlite file created - is it SQLite or SpatiaLite? If you runsqlite3 yourfile.sqliteand type.schemawhat do you get?3 Posted by Jorge Vidinha on 03 Oct, 2011 08:25 PM
Hi Tom, its SpatialLite it was created with Safe FME ETL.
I can see table on terminal with .tables ...
Jorge Vidinha
Support Staff 4 Posted by Dane on 03 Oct, 2011 10:35 PM
Jorge,
Can you make sure you are running the latest release of tilemill? It is 0.5.1. If you are already running that version can you report back exactly how you installed tilemill and mapnik?
5 Posted by Jorge Vidinha on 04 Oct, 2011 10:02 PM
Hi Dane,
im using 0.5.1(2917) installed with app installer on top of a previous version build on terminal with the steps (tilemill, mapnik builds ... ) described for installation by that time (not online anymore i think) i dont remember exactly all process.
Thanks
Jorge Vidinha
Support Staff 6 Posted by Dane on 04 Oct, 2011 10:06 PM
Jorge,
Okay, can you post your sqlite file please?
7 Posted by Jorge Vidinha on 04 Oct, 2011 10:11 PM
Sure
Support Staff 8 Posted by Dane on 04 Oct, 2011 11:07 PM
Jorge,
This sqlite file is different than any I've seen before. It appears that FME supports writing some difference kinds of geometry storage. The SQLite db reports 'FGF' for the geometry type which is not going to work (http://trac.osgeo.org/fdo/wiki/FDORfc16).
Also, gdal/ogr fails to convert this file, which indicates to me that something larger may be wrong with how it was authored:
$ ogrinfo /Users/dane/Documents/MapBox/data/caop_almada_LL84.sqlite -so -al INFO: Open of `/Users/dane/Documents/MapBox/data/caop_almada_LL84.sqlite' using driver `SQLite' successful. Layer name: caop_almada Geometry: Multi Point Feature Count: 11 Extent: (-9.262975, 38.550321) - (-9.128496, 38.688414) Layer SRS WKT: GEOGCS["GCS_WGS_1984", DATUM["D_WGS_1984", SPHEROID["WGS_1984",6378137,298.257223563]], PRIMEM["Greenwich",0], UNIT["Degree",0.017453292519943295]] FID Column = PRIMARYINDEX Geometry Column = Geometry AREA2008_1: Real (0.0) CONCELHO: String (0.0) DICO: String (0.0) DICOFRE: String (0.0) DISTRITO: String (0.0) FID: Integer (0.0) FREGUESIA: String (0.0) OUTRAS_INF: String (0.0) dane$ ogr2ogr test.shp /Users/dane/Documents/MapBox/data/caop_almada_LL84.sqlite ERROR 1: Attempt to write non-multipoint (POLYGON) geometry to multipoint shapefile. ERROR 1: Terminating translation prematurely after failed translation of layer caop_almada (use -skipfailures to skip errors)Support Staff 9 Posted by Dane on 04 Oct, 2011 11:09 PM
I recommend using FME to convert this to something other than sqlite, or trying to direct FME to use a GDAL/OGR compatible format for the sqlite file that uses OGC WKB for the geometry storage. I've never used FME myself otherwise I might have more helpful suggestions. Interested in what you find.
10 Posted by Jorge Vidinha on 05 Oct, 2011 05:14 PM
Ok thanks Dane,
Anyway its sounds strange to me why geometry type in ogrinfo is set to Multi Point when it seems to me it should be Polygon. is this ok ?
Thanks
Jorge Vidinha
Support Staff 11 Posted by Dane on 05 Oct, 2011 05:43 PM
yes, I think the multipoint vs polygon issue is an indication that ogr does not support FME based sqlite files that use the FGF/FDO geometry type, which is the basis of the problem.
Will White closed this discussion on 27 Oct, 2011 02:43 PM.