Querying OGR-created files

[pr]

I would like to use SpatiaLite to query some files that have been
created by OGR, but am just getting null values.

When I go into a simple test file with 1 linestring, it tells me
"FDO-OGR Spatial Metadata detected"
and
spatialite> select CheckSpatialMetaData();
2
so it seems to pick up the fdo/ogr format correctly.

spatialite> select * from
geometry_columns;
lines|GEOMETRY|2|2|50000|WKB

Normal sqlite functions work fine. However, if I try using the
SpatiaLite functions, such as:
select AsText(GEOMETRY),NumPoints(GEOMETRY) from lines;
I just get null values.

This should work, no? Or am I missing something?

[a.fu...@lqt.it]

Hi,

SpatiaLite automatically detects (at connect time)
if the DB contains any FDO-styled geometry.
In this case you'll get the message:
"FDO-OGR Spatial Metadata detected"

Please note: SpatiaLite automatically wraps any
FDO-OGR table using the VirtualFDO extension,
so that you actually have *two* different tables:

<table-name>
corresponds to the original FDO-OGR table (containing
exotic binary geometries, obviously not recognized
by SpatiaLite, and thus returning NULLs)

fdo_<table_name> [i.e. the table name prefixed by "fdo_"]
this is the VirtualFDO table, automatically enforcing
any required format conversion in order to return
valid SpatiaLite's own binary geometries.

So you *must* ignore the original FDO-OGR table in
SpatiaLite, using the "fdo_something" Virtual Table
instead.
e.g. assuming you have an FDO-OGR table named "lines",
then you have to perform the following query:

select AsText(GEOMETRY),NumPoints(GEOMETRY) from fdo_lines;

bye
Sandro

[pr]

On Mar 3, 4:50 pm, a.furi...@lqt.it wrote:
> e.g. assuming you have an FDO-OGR table named "lines",
> then you have to perform the following query:
>
> select AsText(GEOMETRY),NumPoints(GEOMETRY) from fdo_lines;  

I get "SQL error: no such table: fdo_lines"

I'm using 2.3.0 (Debian binary), but I see that 2.3.1 "fixed a stupid
initialization bug disabling VirtualFDO". Does that mean I have to use
2.3.1 for this to work?

Is any of this documented anywhere?

[pr]

ok, it looks like what I have to do is:
- use 2.3.1
- do "select AutoFDOStart();" which creates the fdo_lines table
then it works!

[pr]

there seems to be an incompatibility in that the functions that use
spatial_ref_sys like Transform() expect there to be a column proj4text
which doesn't exist in FDO files (which have srtext with the
projection definition in wkt).

Easy enough to get around, as you just have to add a column called
proj4text with the def in proj4 format.

Also, if you have this column but it's empty -> segmentation fault -
oops ;-(

[pr]

I can't get anywhere with this. I just get segmentation faults trying
to read fdo_lines. Even just doing simple queries without using any
spatialite functions:
select feature_id from lines where feature_id=22;
works fine
select feature_id from fdo_lines where feature_id=22;
fails segmentation fault. messages log says:
segfault at c ip b755ecf2 sp bfe6ae80 error 4 in libspatialite.so.
1.1.3[b7529000+ab000]

I am using the Linux binary of libspatialite with sqlite 3.6.14 and
".load libspatialite"

I've also tried with 2.4, tried dropping fdo_lines and running "select
AutoFDOStart();" again, and tried several variations on the 2 spatial
tables, but always get the same result.

[pr]

and it's not just with virtual fdo that I'm getting segfaults

I decided to delete the downloaded libspatialite and go back to the
Debian 2.3.0 package. I was thinking I could convert my existing
linestrings by creating a new spatialite file, attaching my existing
file and doing something like

create table newlines as select feature_id, LineFromText(wkt_geometry)
from old.lines

but this fails with segmentation fault too, at exactly the same point,
so it seems spatialite doesn't like something in my file(s). Can
anyone suggest what might be causing this? I'm running out of ideas.