[gdal-dev] SQLite driver hit and miss with spatial index
jan.heckman at gmail.com
Thu Apr 6 14:28:13 PDT 2017
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:
- 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
- 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
Error trying to convert to shapefile with OGC_FID:
F:\>ogr2ogr -f "ESRI Shapefile" -nlt POINT -select OGC_FID test.shp
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.
On Thu, Apr 6, 2017 at 2:30 PM, Frederik Ramm <frederik at remote.org> wrote:
> 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.
> Frederik Ramm ## eMail frederik at remote.org ## N49°00'09" E008°23'33"
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the gdal-dev