How to install gdal postgresql driver in Ubuntu

Question

I want to use Python to load PostGIS table by ogr. But after I try ogr.open in python (it works if loading a shapefile), I can only get null value.

Now I have tried GDAL debug, the output shows like this: GDAL: In GDALDestroy - unloading GDAL shared library. and I have found that the problem is lack of postgresql driver. So how should I install it in ubuntu?

Please edit the question to contain your code. You should also include exact versions of all software in use. — Vince, Jul 22 '16 at 02:18
It could be that you are not allowed to connect to your database. Did you check your pg_hba.conf? Otherwise check your db status or log if the connection is open there. The connection should stay open the way you use it in your code until the program is closed and you should have "idle in transaction" in your server status for this connection. — Matte, Jul 22 '16 at 06:21
I can connect through datagrip (a client software) at the same ip address, so I suppose that it is not the configuration problem of server. Thanks anyway. — natsuapo, Jul 22 '16 at 06:31
By the way, I have found this question: http://gis.stackexchange.com/questions/13734/gdal-python-bindings-not-picking-up-ogr-postgresql-driver shows that some extra libraries are needed for connecting to PostGIS. But still it is in Windows. — natsuapo, Jul 22 '16 at 06:35

score 2 · Answer 1 · edited Apr 13 '17 at 12:33

2

OGR method, courtesy of this page:

from osgeo import ogr
conx = ogr.Open('PG:dbname=my_db user=postgres password=12345678')

sql = 'SELECT * FROM table_name LIMIT 10;'
for row in conx.ExecuteSQL(sql):
    print row.GetField(0)  #gets first column, usually the id

Another way: Adapted from here. Doesn't use ogr but it does let you access the data a bit more easily IMHO.

import psycopg2
import sys

conx = None
conx = psycopg2.connect(database='my_db', user='postgres', password='12345678') 
curx = conx.cursor()

#Prints version number
curx.execute('SELECT version()')          
ver = curx.fetchone()
print ver

#Prints first row of table
curx.execute('SELECT * FROM my_db LIMIT 1')          
onerow = curx.fetchone()
print onerow

If you're doing this in qgis, you might want to look at Load a specific spatial data from a postGIS table or Load PostGIS layer/table to QGIS canvas using Python .

edited Apr 13 '17 at 12:33

Community

1

answered Jul 22 '16 at 02:31

weiji14

1,751
13
31

Thank you for your answer but the former one doesn't work for me. – natsuapo Jul 22 '16 at 03:38
1

If you post your full database structure, that might help. E.g. dbname, user, password(as ***), host, port. – weiji14 Jul 22 '16 at 03:51
It still doesn't work. I think maybe I need some extra libraries to execute it? – natsuapo Jul 22 '16 at 03:55

score 0 · Answer 2 · answered Aug 09 '16 at 02:32

0

I found out the cause is that I use Anaconda, and if I build GDAL by myself, the problem can be solved.

answered Aug 09 '16 at 02:32

natsuapo

593
4
19

score 0 · Answer 3 · answered Oct 14 '16 at 21:06

If you are using the anaconda gdal package (installed via conda install gdal , then using psycopg2 is the way to go. According to this anaconda issue/feature request, no anaconda gdal package includes a postgis driver. I checked reinstalling from https://pypi.python.org/pypi/GDAL/ via pip install gdal --no-cache-dir, but that seemed to be an identical package.

How to install gdal postgresql driver in Ubuntu

3 Answers3