Is this spatialite (built with ogr2ogr) valid?

[Thomas Larsen Wessel]

Command for creating spatialite file:

ogr2ogr -f SQLite spatialite.db \

WFS:"https://foobar.io/wfs/foo@bar/baz/25832" \
foolayer \
-nln foolayer \
-t_srs EPSG:25832

If I run the command above and output to csv or gpkg it all appears fine, so I think I'm using ogr2ogr correctly and I think the WFS source (a PostGIS-database) is behaving correctly. The resulting spatialite file appears broken to me though. 

ogrinfo output seems fine:

    $ ogrinfo spatialite.db

    INFO: Open of `spatialite.db'
          using driver `SQLite' successful.
    1: foolayer (Line String)

But when looking inside the file with sqlite3, it only contains two auxillary rows (in lack of better word). Im used to seeing many more in spatialite files. And the spatial query returns every single row even though it should return zero rows. 

    $ sqlite3 spatialite.db
    SQLite version 3.37.2 2022-01-06 13:25:41
    Enter ".help" for usage hints.
    sqlite> select load_extension('mod_spatialite');
    sqlite> .tables
    foolayer          geometry_columns  spatial_ref_sys
    sqlite> select count(*) from foolayer where st_intersects(geometri, ST_GeomFromText('Polygon ((0 0, 100 100, 0 100, 0 0))'));
    12662

Can you confirm, that this is not valid? What is missing? Is this the expected output of ogr2ogr, or is this intended, and then I need to do further work on the spatialite file. 

Is there a tool for testing the validity of spatialite files?

Sincerely

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

On Fri, 14 Jul 2023 20:47:42 +0200, Thomas Larsen Wessel wrote:
> Command for creating spatialite file:
>
> ogr2ogr -f SQLite spatialite.db

> WFS:"https://foobar.io/wfs/foo@bar/baz/25832 [1]"

> foolayer
> -nln foolayer
> -t_srs EPSG:25832
>

Hi Thomas,

this question has nothing to do with SpatiaLite,
it should rather be placed on the GDAL mailing list.

as far as I can see your use of og2ogr seems wrong,
because just specifying "-f SQLite" is not enough to
select SpatiaLite, it should be instead

-f SQLite -dsco SPATIALITE=YES

remember: there are many different spatial formats
based on SQLite, and you can never assume that
SpatiaLite should be automatically selected.
you alway have to explicitly request for this.

> Is there a tool for testing the validity of spatialite files?
>

you can simply execute the following SQL query:

SELECT DISTINCT IsGeometryBlob(geom)
FROM my_table;

- if it return 1 (TRUE) all your geometries
are correctly encoded in the internal binary
BLOB format adopted by SpatiaLite

- otherwise some different format has been adopted

bye Sandro

[Thomas Larsen Wessel]

Thanks. That helped me a lot :)