[GIS] How to install gdal postgresql driver in Ubuntu

gdalogrpostgis

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?

Best Answer

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 .

Related Question