Re: [SpatiaLite-Users] spatialite_osm_raw - unrecoverable error while parsing c:\osm_data\us-west.osm.pbf

[Brad Hards]

On Saturday, 26 February 2022 11:48:55 AM AEDT Jim wrote:
> I just downloaded the "us-west-osm-latest.pbf" file from the OSM servers.
I guess you mean the Geofabrik download server

> I then attempted to run spatialite_osm_raw as follows:
>
> -----------------------------
> C:\Tools\spatialite-tools>spatialite_osm_raw.exe -o
> c:\osm_data\us-west.osm.pbf -d c:\osm_data\us-west-pois.sqlite
> SQLite version: 3.34.1
> SpatiaLite version: 5.0.1
>
> unrecoverable error while parsing c:\osm_data\us-west.osm.pbf

I can't reproduce this on Linux:

$ spatialite_osm_raw -o us-west-latest.osm.pbf -d us-west-latest.pois.sqlite
SQLite version: 3.34.1
SpatiaLite version: 5.0.1

inserted 301801285 nodes
19669685 tags
inserted 26785441 ways
108686418 tags
336555817 node-refs
inserted 239501 relations
1021424 tags
2572385 refs

It also ran OK under wine64:

$ wine64 ./spatialite_osm_raw.exe -o us-west-latest.osm.pbf -d us-west-
latest.pois.sqlit
SQLite version: 3.34.1
SpatiaLite version: 5.0.1

0009:fixme:ntdll:NtLockFile I/O completion on lock not implemented yet
inserted 301801285 nodes
19669685 tags
inserted 26785441 ways
108686418 tags
336555817 node-refs
inserted 239501 relations
1021424 tags
2572385 refs


I can think of a couple of options, in order of more likely to less likely:
1. Bad download of the data. The sha1sum of us-west-latest.osm.pbf that I
used:
27c30b872ecb6eed36f622d4378ed590f496efae us-west-latest.osm.pbf

2. You are trying to use a 32 bit version of the windows tools, and that might
not be enough. Or you don't have enough memory / swap space. This is more
likely if it ran for a while, then dies.
Make sure you are using http://www.gaia-gis.it/gaia-sins/windows-bin-amd64/

3. There is something else corrupted in your spatialite install - missing
libraries maybe. This is more likely if it dies pretty much straight away.

4. A bug that is specific to windows in spatialite that doesn't show up under
wine64

5. Something else (hardware or software) is broken in your windows 10 install
that only shows up for this specific case.

HTH

Brad

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

On Sat, 26 Feb 2022 17:53:48 +1100, Brad Hards wrote:
> 2. You are trying to use a 32 bit version of the windows tools, and
> that might not be enough.
>

Hi Brad,

you are absolutely right.

Jim is obviously using a 32 bit version (target CPU: i686-w64-mingw32);
using the corresponding 64 bit executable (target CPU:
x86_64-w64-mingw32)
anything runs nicely even on Windows 10

please note: this one is an exceptionally huge dataset.
the PBF file alone has a size of 2,2GB and the output SQLite DB has an
astonishing size of 53.3GB

definitely too much for the limited capabilities of 32 bit software
that's physically unable to address more than 2GB of memory

bye Sandro

[Jim]

Hi all,

 All is fine since I installed the 64bit Windows apps. Quite frankly I should have known better.

 Thanks to all for the assistance.

 BTW, if anyone has thoughts the SQL for extracting nodes &tags from the "raw" database that contain the amenity key, I would appreciate the input.

 Regards,

Jim

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

On Sat, 26 Feb 2022 09:56:29 -0800 (PST), Jim wrote:
> BTW, if anyone has thoughts the SQL for extracting nodes &tags from
> the "raw" database that contain the amenity key, I would appreciate
> the input.
>

-- starting a Transaction
BEGIN;

-- creating a Spatial Table
CREATE TABLE osm_amenities (
node_id INTEGER PRIMARY KEY,
type TEXT NOT NULL);

-- adding a Geometry Column
SELECT AddGeometryColumn('osm_amenities', 'geom', 4326, 'POINT', 'XY');

-- creating a Spatial Index
SELECT CreateSpatialIndex('osm_amenities', 'geom');

-- populating the table by extracting all OSM amenities
INSERT INTO osm_amenities (node_id, type, geom)
SELECT t.node_id, t.v, geometry
FROM osm_node_tags AS t
JOIN osm_nodes AS n ON (t.node_id = n.node_id)
WHERE t.k = 'amenity';

-- confirming the pending Transaction
COMMIT;

[Jim]

Sandro,

My thanks.

Regards,

Jim