2

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)

1 Answer 1

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;
  1. 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.

2
  • In your question you wrote "Note: I can convert hstore to json within PostGIS, my question is how to convert hstore to json using ogr2ogr". Both of your methods, which are actually the same method, use PostGIS's hstore_to_json() to convert the tags to json inside PostGIS. ogr2ogr is not doing it. How to do this with PostGIS was already answered: stackoverflow.com/questions/33732529/…
    – hgb
    Apr 24, 2022 at 21:22
  • I was hoping ogr2ogr has a way to handle conversion since OGR dialect knows hstore "e.g. hstore_get_value". Unfortunately, the documentation does not help. The linked answer did it inside postgis. My question was to do it while exporting. I posted my finding (first method) as a possible way so others may find feasible.
    – alsaleem
    Apr 24, 2022 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.