[gdal-dev] SQLite driver hit and miss with spatial index

[Jan Heckman]

Hi,
Out of interest, I tried the tack of converting to shapefile with a .dbf
only containing the primary index OGC_FID.
This might provide an alternative for indexed access to the geometry. I see
it isn't needed anymore, but anyway,
for info and comparison:
What worked:
- shapefile record count 25201411 agrees with sqlite table.
- shapefile size (.shp): 672 MB (recordcount * 28 + 100).
- shapefile index (.sbn): 194 MB. That's quite a bit more compact than the
spatialite index?
- apparently immediate response when zooming in or inquiring by clicking
(did not measure response time accurately)
What went wrong:
- could not select the primary index for conversion using ogr2ogr ...
-select OGC_FID ...
- Got incomplete conversion when using spatialite_gui with spatiaLite
version 4.3.0.a, conversion to DBF.
  recordcount of the conversion: 19927604

This using OSGEOW prompt of Qgis 2.18.0 (with GDAL 2.1.1, released
2016/07/07), on a windows 7 machine.

Perhaps worthwhile to use the (no-index.sqlite) dataset to look into the
conversion.
Error trying to convert to shapefile with OGC_FID:
F:\>ogr2ogr -f "ESRI Shapefile" -nlt POINT -select OGC_FID test.shp
j:/downloads/no-index.sqlite
ERROR 1: Field 'OGC_FID' not found in source layer.
ERROR 1: Terminating translation prematurely after failed
translation of layer osmi_addresses_nodes_with_addresses (use -skipfailures
to skip errors)

Is the OGC_FID primary index identical to a shapefile FID, that is, not in
the table, but really a counter?
So, I'm not sure I can properly link points to the sqlite data, but
otherwise it looks doable.
Jan

On Thu, Apr 6, 2017 at 2:30 PM, Frederik Ramm <frederik at remote.org> wrote:

> Hi,
>
>    while what I wrote in the previous post is still interesting and a
> bit strange, I have meanwhile upgraded to GDAL 2 packages from the
> ubuntugis-unstable repository for Xenial, and now the behaviour is as I
> would expect it: The ogr2ogr -spat command *with* indexes takes
> practically no time (0.05 seconds), and the -spat command *without*
> index takes about 40 seconds as before.
>
> Sorry for the noise then.
>
> Bye
> Frederik
>
> --
> Frederik Ramm  ##  eMail frederik at remote.org  ##  N49°00'09" E008°23'33"
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170406/0f999e28/attachment.html>