To work with PostGIS Databases in Python you need to use psycopg2 python module. This is automatically installed when you got Postgres+Postgis in your system.
As you work in a Linux system, you can create an individual user (instead 'postgres' user) and database for your test. As superuser:
# su - postgres
$ psql
psql (8.4.17)
Digite «help» to get help.
postgres=#
Next, I created my new user with privileges of superuser for being able to add postgis extension in new database (in this case utah).
postgres=# CREATE USER your_user WITH PASSWORD 'your_password' SUPERUSER;
CREATE ROLE
postgres=# CREATE DATABASE utah OWNER zeito;
CREATE DATABASE
After created database utah, I used pgadmin3 to create a new connection and to add postgis extension to this database.
Afterward, I tried out next script (change asterisks for your password and your correspondents parameters) in Python Console of QGIS:
import psycopg2
try:
conn = psycopg2.connect(dbname='utah',
host='localhost',
port=5432,
user='zeito',
password='********')
except:
print "I am unable to connect to the database"
cur = conn.cursor()
layer = iface.activeLayer()
feats = [feat for feat in layer.getFeatures()]
ls = feats[0].geometry().asWkb().encode('hex')
# Send it to PostGIS
cur.execute('CREATE TABLE route(geom geometry, name text)')
cur.execute(
'INSERT INTO route(geom, name)'
'VALUES (ST_SetSRID(%(geom)s::geometry, %(srid)s), %(name)s)',
{'geom': ls, 'srid': 32612, 'name': 'route'})
conn.commit() # save data
print "Done"
to put 'route' layer into database 'utah' (active layer in layers panel of QGIS):
After running the code at Python Console of QGIS (observe at above image that it ended with word 'Done' and any error):
I clicked in 'elephant' icon, 'Manage Layers Toolbar' of QGIS, for adding new postgis layer; as it can be observed at next image. It worked.
Editing Note:
This is my ogr2ogr solution and it works perfectly (with another shapefile):
import os
dbname = 'utah'
host = 'localhost'
user = 'zeito'
password = 'my_password'
port = '5432'
command = "ogr2ogr -lco GEOMETRY_NAME=geom -a_srs epsg:32612 \
-f 'PostgreSQL' PG:'dbname=" + dbname + \
" host=" + host + \
" user=" + user + \
" password=" + password + \
" port=" + port + "'" + \
" /home/zeito/pyqgis_data/line1.shp"
os.system(command)
Best Answer
You can use PostGIS's
ST_GeomFromGeoJSONto bring in just the geometry part of the GeoJSON.Better yet, you can use
ogr2ogrto import the entire JSON document:ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.json" -nln destination_table -append(I haven't tested this with your data, add a comment if you have issues.)