Load kml geojson osm shp files in Postgis

In my last post I talk how to convert from kml to GeoJson
Now, I want to load this file in a Spatial and Geographic Objects for PostgreSQL (postgis)
I read in many sites that you can convert from osm 2 postgres, then I will try to convert to osm file with geojsontoosm.

# sudo  npm install -g geojsontoosm

npm ERR! enoent ENOENT: no such file or directory, open '/tmp/npm-15537-43d2f472/unpack-edff7974/package.json'
npm ERR! enoent ENOENT: no such file or directory, open '/tmp/npm-15537-43d2f472/unpack-edff7974/package.json'
npm ERR! enoent This is most likely not a problem with npm itself
npm ERR! enoent and is related to npm not being able to find a file.
npm ERR! enoent 

To install, I used the git files:

# git clone https://github.com/tyrasd/geojsontoosm.git
# cd geojsontoosm

# ./geojsontoosm 
module.js:471
    throw err;
    ^

Error: Cannot find module 'jxon'
Error: Cannot find module 'optimist'
Error: Cannot find module 'geojson-stream'
Error: Cannot find module 'concat-stream'

We going to install these modules

# sudo  npm install jxon optimist geojson-stream concat-stream
# ./geojsontoosm file.geojson > file.osm

Now, I'll try to convert to postgres with osm2po

# wget http://osm2po.de/releases/osm2po-5.2.43.zip
# unzip osm2po-5.2.43.zip

# java -Xmx1408m -jar osm2po-core-5.2.43-signed.jar prefix=output_folder_name file.osm postp.0.class=de.cm.osm2po.plugins.postp.PgRoutingWriter

FATAL Exception at Fri Apr 06 23:02:45 CLST 2018
      Message: java.lang.RuntimeException: OSM Node-ID out of range: -1
      Class: java.lang.RuntimeException
      Stacktrace:
      de.cm.osm2po.converter.parser.OsmXmlParser.parse(OsmXmlParser.java:90)
      de.cm.osm2po.Main.runTiler(Main.java:241)
      de.cm.osm2po.Main.run(Main.java:161)
      de.cm.osm2po.Main.main(Main.java:77)
FATAL Log statistics: FATAL:1

If you try to load the file.osm in QGIS you will see that the file doesn't load data, for this reason, osm2po doesn't work well.

I'll try to convert directly from geojson to postgres:

# sudo npm install -g geojson2pgsql@0.0.1

Now, we need to create a database with a postgis extension and load on it the sql.

# createdb testdb
createdb: could not connect to database template1: FATAL:  role "username" does not exist

You must create a user for postgres

# sudo -u postgres createuser -s username 
# createdb testdb
# psql -d testdb -c "create extension postgis"
# DATABASE_URL=postgres://localhost/testdb geojson2pgsql file.geojson file

file.geojson:2
    "type": "FeatureCollection",
          ^

SyntaxError: Unexpected token :

As you can see,  geojson2pgsql doesn't provide a correct output.
The last chance, is to convert from kml to postgres. First, we need to convert kml to shape file:

# ogr2ogr -f "ESRI Shapefile" file.shp file.kml              
Warning 6: Normalized/laundered field name: 'description' to 'descriptio'
Warning 6: Field timestamp create as date field, though DateTime requested.
Warning 6: Field begin create as date field, though DateTime requested.
Warning 6: Field end create as date field, though DateTime requested.
Warning 6: Normalized/laundered field name: 'altitudeMode' to 'altitudeMo'
ERROR 1: Attempt to write non-point (LINESTRING) geometry to point shapefile.
ERROR 1: Unable to write feature 7 from layer Napnet.
ERROR 1: Terminating translation prematurely after failed
translation of layer Napnet (use -skipfailures to skip errors)

To avoid this error, you must delete points in the kml file.

# ogr2ogr -f "ESRI Shapefile" file.shp file.kml                                         
Warning 6: Normalized/laundered field name: 'description' to 'descriptio'
Warning 6: Field timestamp create as date field, though DateTime requested.
Warning 6: Field begin create as date field, though DateTime requested.
Warning 6: Field end create as date field, though DateTime requested.

Warning 6: Normalized/laundered field name: 'altitudeMode' to 'altitudeMo'

Now, you can convert the shape file to sql

# shp2pgsql -s 4326 -I -c -W UTF-8 file.shp file > file.sql
Shapefile type: ArcZ

Postgis type: MULTILINESTRING[4]

And finally load it in postgres:

# psql 'host=localhost port=5432 dbname=testdb user=username' < file.sql
# psql -d testdb postgres
# \d file

# SELECT name FROM file;

Enjoy it!