ogr2ogr PostGIS to SpatiaLite: convert hstore to json

Question

I used this command to export PostGIS tables to SpatiaLite with ogr2ogr

ogr2ogr -f SQLite -progress -dsco SPATIALITE=yes ./mapfile/psql_tags.sqlite PG:"host=localhost port=5432 dbname=gis user=postgres password=123" "planet_osm_line"

The data in PostGIS was originally imported with osm2pgsql (from OSM pbf). The table has tags column with hstore type.

ogr2ogr exports tags column as text in the format :

"wikidata"=>"Q23942158"

When performing a query in sqlite/SpatiaLite, I got

malformed JSON  encountered during parsing of layer 'landuse-overlay'

Is there a way to export hstore type columns (PostGIS) as json in (SQLite)?

(Note: I can convert htore to json within PostGIS, my question is how to convert hstore to json using ogr2ogr)

Answer 1

Yes. This can be done in two ways :

1. Direct export (using ogr2ogr):

   ogr2ogr -f SQLite -progress -dsco SPATIALITE=yes ./testmap /psql_tags2.sqlite PG:"host=localhost port=5432 dbname=gis user=postgres password=123" -nln "planet_osm_line" -sql "select *, hstore_to_json(tags) as tags1 from planet_osm_line"

-nln here is to preserve the table name on the destination. The original "tags" column will exported too. If we do not need "tags" on the destination we can either list all columns except "tags", ~70 columns with hstore_to_json(tags) as tags or use DDL on the destination as (SQLite > 3.35.0):

ALTER TABLE planet_osm_line DROP COLUMN tags;
ALTER TABLE planet_osm_line RENAME COLUMN tags1 TO tags;

2. Modify column on source (before export)

   ALTER TABLE planet_osm_line ADD COLUMN tags1 json; UPDATE planet_osm_line SET tags1=hstore_to_json(tags); ALTER TABLE planet_osm_line DROP COLUMN tags; ALTER TABLE planet_osm_line RENAME COLUMN tags1 TO tags;

The new "tags" column can then be queried as json in SQLite.