Thuban/Model/postgisdb.py

# Copyright (C) 2003 by Intevation GmbH
# Authors:
# Martin Mueller <[email protected]>
# Bernhard Herzog <[email protected]>
#
# This program is free software under the GPL (>=v2)
# Read the file COPYING coming with the software for details.

"""Basic interface to a PostGIS database"""

from __future__ import generators

try:
    import psycopg
except ImportError:
    psycopg = None

import table
import wellknowntext

from data import SHAPETYPE_POLYGON, SHAPETYPE_ARC, SHAPETYPE_POINT, RAW_WKT

def has_postgis_support():
    """Return whether this Thuban instance supports PostGIS connections

    Having PostGIS support means that the psycopg module can be
    imported.
    """
    return psycopg is not None

if psycopg is not None:
    type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
                (psycopg.INTEGER, table.FIELDTYPE_INT),
                (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]

class PostGISConnection:

    """Represent a PostGIS database

    A PostGISConnection instance has the following public attributes:

    dbname -- The name of the database
    host, port -- Host and port to connect to
    user -- The user name to connect as.

    All of these attributes are strings and may be empty strings to
    indicate default values.
    """

    def __init__(self, dbname, host="", user="", password="", dbtype="",
                 port=""):
        self.dbname = dbname
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.dbtype = dbtype
        self.connect()

    def connect(self):
        """Internal: Establish the database connection"""
        params = []
        for name in ("host", "port", "dbname", "user", "password"):
            val = getattr(self, name)
            if val:
                params.append("%s=%s" % (name, val))
        self.connection = psycopg.connect(" ".join(params))

        # determine the OID for the geometry type. This is PostGIS
        # specific.
        cursor = self.connection.cursor()
        cursor.execute("SELECT OID, typname FROM pg_type WHERE"
                       +" typname = 'geometry'")
        row = cursor.fetchone()
        self.connection.commit()
        if row is not None:
            self.geometry_type = row[0]
        else:
            raise ValueError("Can't determine postgres type of geometries")

    def BriefDescription(self):
        """Return a brief, one-line description of the connection

        The return value is suitable for a list box of all database
        connections.
        """
        return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
                % self.__dict__)

    def Close(self):
        """Close the database connection"""
        self.connection.close()

    def GeometryTables(self):
        """Return a list with the names of all tables with a geometry column"""
        cursor = self.connection.cursor()
        cursor.execute("SELECT f_table_name FROM geometry_columns;")
        result = [row[0] for row in cursor.fetchall()]
        self.connection.commit()
        return result

    def cursor(self):
        """Return a DB API 2.0 cursor for the database"""
        return self.connection.cursor()


class PostGISColumn:

    """Column description for a PostGISTable"""

    def __init__(self, name, type, index):
        self.name = name
        self.type = type
        self.index = index


class PostGISTable:

    """A Table in a PostGIS database"""

    def __init__(self, db, tablename):
        """Initialize the PostGISTable.

        The db parameter should be an instance of PostGISConnection and
        tablename the name of a table in the database represented by db.
        """
        self.db = db
        self.tablename = tablename
        self.column_map = {}
        self._fetch_table_information()

    def _fetch_table_information(self):
        """Internal: Update information about the table"""
        self.columns = []
        cursor = self.db.cursor()
        cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)
        description = cursor.description

        for i in range(len(description)):
            for pgtyp, tabletyp in type_map:
                if pgtyp == description[i][1]:
                    col = PostGISColumn(description[i][0], tabletyp, i)
                    break
            else:
                if description[i][1] == self.db.geometry_type:
                    self.geometry_column = description[i][0]
                # No matching table type. Ignore the column.
                # FIXME: We should at least print a warning about
                # ignored columns
                continue
            self.columns.append(col)

        for col in self.columns:
            self.column_map[col.name] = col
            self.column_map[col.index] = col

        # Build query string for ReadRowAsDict
        self.query_stmt = ("SELECT %s from %s"
                           % (", ".join([col.name for col in self.columns]),
                              self.tablename))

    def Dependencies(self):
        """Return an empty tuple because a PostGISTable depends on nothing else
        """
        return ()

    def NumColumns(self):
        return len(self.columns)

    def Columns(self):
        return self.columns

    def Column(self, col):
        return self.column_map[col]

    def HasColumn(self, col):
        return self.column_map.has_key(col)

    def NumRows(self):
        cursor = self.db.cursor()
        cursor.execute("SELECT count(*) FROM %s" % self.tablename)
        return cursor.fetchone()[0]

    def ReadRowAsDict(self, row):
        cursor = self.db.cursor()
        cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)
        result = {}
        for col, value in zip(self.columns, cursor.fetchone()):
            result[col.name] = value
        return result

    def ReadValue(self, row, col):
        cursor = self.db.cursor()
        cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
                       (self.column_map[col].name, self.tablename, row))
        return cursor.fetchone()[0]

    def ValueRange(self, col):
        cursor = self.db.cursor()
        name = self.column_map[col].name
        cursor.execute("SELECT min(%s), max(%s) FROM %s" %
                       (name, name, self.tablename))
        return tuple(cursor.fetchone())

    def UniqueValues(self, col):
        cursor = self.db.cursor()
        name = self.column_map[col].name
        cursor.execute("SELECT %s FROM %s GROUP BY %s" %
                       (name, self.tablename, name))
        return [row[0] for row in cursor.fetchall()]

    def SimpleQuery(self, left, comparison, right):
        if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
            raise ValueError("Comparison operator %r not allowed" % comparison)

        if comparison == "==":
            comparison = "="

        if isinstance(right, PostGISColumn):
            right_template = right.name
            params = ()
        else:
            right_template = "%s"
            params = (right,)

        query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
                % (self.tablename, left.name, comparison, right_template)

        cursor = self.db.cursor()
        cursor.execute(query, params)
        result = []
        while 1:
            row = cursor.fetchone()
            if row is None:
                break
            result.append(row[0])
        return result


class PostGISShape:

    def __init__(self, shapeid, data):
        self.shapeid = shapeid
        self.data = data

    def compute_bbox(self):
        """
        Return the bounding box of the shape as a tuple (minx,miny,maxx,maxy)
        """
        xs = []
        ys = []
        for part in self.Points():
            for x, y in part:
                xs.append(x)
                ys.append(y)
        return (min(xs), min(ys), max(xs), max(ys))

    def ShapeID(self):
        return self.shapeid

    def Points(self):
        return wellknowntext.parse_wkt_thuban(self.data)

    def RawData(self):
        return self.data


shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
                 "MULTILINESTRING": SHAPETYPE_ARC,
                 "POINT": SHAPETYPE_POINT}


class PostGISShapeStore(PostGISTable):

    """Shapestore interface to a table in a PostGIS database"""

    def Table(self):
        """Return self since a PostGISShapeStore is its own table."""
        return self

    def OrigShapeStore(self):
        """Return None since the PostGISShapeStore is not derived from another
        """
        return None

    def ShapeType(self):
        """Return the type of the shapes in the shapestore."""
        cursor = self.db.cursor()
        cursor.execute("SELECT type FROM geometry_columns WHERE"
                       " f_table_name=%s", (self.tablename,))
        result = cursor.fetchone()[0]
        cursor.close()
        return shapetype_map[result]

    def RawShapeFormat(self):
        """Return the raw data format of the shape data.

        For the PostGISShapeStore this is RAW_WKT.
        """
        return RAW_WKT

    def NumShapes(self):
        # The number of shapes is the same as the number of rows,
        # assuming that the geometry can't be NULL.
        return self.NumRows()

    def BoundingBox(self):
        """Return the bounding box of all shapes in the postgis table"""
        minx = miny = maxx = maxy = None
        x=[]
        y=[]
        cursor = self.db.cursor()
        try:
            stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"
                    % (self.geometry_column, self.tablename))
            cursor.execute(stmt)
            result = cursor.fetchone()
            while result:
                result = result[0]
                # Here we must do some parsing through the result string
                # to get the points out of the polygon which representes
                # the bounding box The first and the last point of a
                # polygon are identical
                result = result.split("(")[2]
                result = result.split(")")[0]
                points = result.split(",")
                del points[4] # Remove the last point
                for point in points:
                    px, py = point.split()
                    x.append(float(px))
                    y.append(float(py))
                result = cursor.fetchone()
        finally:
            cursor.close()
        if not x:
            # Empty table
            return None
        return (min(x), min(y), max(x), max(y))

    def Shape(self, shapeid):
        cursor = self.db.cursor()
        cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
                       % (self.geometry_column, self.tablename, shapeid))
        wkt = cursor.fetchone()[0]
        cursor.close()
        return PostGISShape(shapeid, wkt)

    def ShapesInRegion(self, bbox):
        """Generate all shapes overlapping the region given by bbox."""
        # IMPORTANT:This will work for PostGIS < 0.8
        left, bottom, right, top = bbox
        geom = (("POLYGON((" + ", ".join(["%f %f"] * 5) + "))")
                % (left, bottom, left, top, right, top, right, bottom,
                   left, bottom))
        cursor = self.db.cursor()
        cursor.execute("SELECT gid, AsText(%s) FROM %s"
                     " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"
                       % (self.geometry_column, self.tablename,
                          self.geometry_column, geom))
        while 1:
            result = cursor.fetchone()
            if result is None:
                raise StopIteration
            yield PostGISShape(result[0], result[1])
1	bh	1605	# Copyright (C) 2003 by Intevation GmbH
2			# Authors:
3			# Martin Mueller <[email protected]>
4			# Bernhard Herzog <[email protected]>
5			#
6			# This program is free software under the GPL (>=v2)
7			# Read the file COPYING coming with the software for details.
8
9			"""Basic interface to a PostGIS database"""
10
11			from __future__ import generators
12
13			try:
14			import psycopg
15			except ImportError:
16			psycopg = None
17
18			import table
19			import wellknowntext
20
21			from data import SHAPETYPE_POLYGON, SHAPETYPE_ARC, SHAPETYPE_POINT, RAW_WKT
22
23	bh	1625	def has_postgis_support():
24			"""Return whether this Thuban instance supports PostGIS connections
25	bh	1605
26	bh	1625	Having PostGIS support means that the psycopg module can be
27			imported.
28			"""
29			return psycopg is not None
30
31	bh	1605	if psycopg is not None:
32			type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
33			(psycopg.INTEGER, table.FIELDTYPE_INT),
34			(psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]
35
36			class PostGISConnection:
37
38			"""Represent a PostGIS database
39
40			A PostGISConnection instance has the following public attributes:
41
42			dbname -- The name of the database
43			host, port -- Host and port to connect to
44			user -- The user name to connect as.
45
46			All of these attributes are strings and may be empty strings to
47			indicate default values.
48			"""
49
50			def __init__(self, dbname, host="", user="", password="", dbtype="",
51			port=""):
52			self.dbname = dbname
53			self.host = host
54			self.port = port
55			self.user = user
56			self.password = password
57			self.dbtype = dbtype
58	bh	1620	self.connect()
59
60			def connect(self):
61			"""Internal: Establish the database connection"""
62	bh	1605	params = []
63			for name in ("host", "port", "dbname", "user", "password"):
64			val = getattr(self, name)
65			if val:
66			params.append("%s=%s" % (name, val))
67			self.connection = psycopg.connect(" ".join(params))
68
69			# determine the OID for the geometry type. This is PostGIS
70			# specific.
71			cursor = self.connection.cursor()
72			cursor.execute("SELECT OID, typname FROM pg_type WHERE"
73			+" typname = 'geometry'")
74			row = cursor.fetchone()
75			self.connection.commit()
76			if row is not None:
77			self.geometry_type = row[0]
78			else:
79			raise ValueError("Can't determine postgres type of geometries")
80
81	bh	1620	def BriefDescription(self):
82			"""Return a brief, one-line description of the connection
83
84			The return value is suitable for a list box of all database
85			connections.
86			"""
87			return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
88			% self.__dict__)
89
90	bh	1605	def Close(self):
91			"""Close the database connection"""
92			self.connection.close()
93
94			def GeometryTables(self):
95			"""Return a list with the names of all tables with a geometry column"""
96			cursor = self.connection.cursor()
97			cursor.execute("SELECT f_table_name FROM geometry_columns;")
98			result = [row[0] for row in cursor.fetchall()]
99			self.connection.commit()
100			return result
101
102			def cursor(self):
103			"""Return a DB API 2.0 cursor for the database"""
104			return self.connection.cursor()
105
106
107
108			class PostGISColumn:
109
110			"""Column description for a PostGISTable"""
111
112			def __init__(self, name, type, index):
113			self.name = name
114			self.type = type
115			self.index = index
116
117
118			class PostGISTable:
119
120			"""A Table in a PostGIS database"""
121
122			def __init__(self, db, tablename):
123			"""Initialize the PostGISTable.
124
125			The db parameter should be an instance of PostGISConnection and
126			tablename the name of a table in the database represented by db.
127			"""
128			self.db = db
129			self.tablename = tablename
130			self.column_map = {}
131			self._fetch_table_information()
132
133			def _fetch_table_information(self):
134			"""Internal: Update information about the table"""
135			self.columns = []
136			cursor = self.db.cursor()
137			cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)
138			description = cursor.description
139
140			for i in range(len(description)):
141			for pgtyp, tabletyp in type_map:
142			if pgtyp == description[i][1]:
143			col = PostGISColumn(description[i][0], tabletyp, i)
144			break
145			else:
146			if description[i][1] == self.db.geometry_type:
147			self.geometry_column = description[i][0]
148			# No matching table type. Ignore the column.
149			# FIXME: We should at least print a warning about
150			# ignored columns
151			continue
152			self.columns.append(col)
153
154			for col in self.columns:
155			self.column_map[col.name] = col
156			self.column_map[col.index] = col
157
158			# Build query string for ReadRowAsDict
159			self.query_stmt = ("SELECT %s from %s"
160			% (", ".join([col.name for col in self.columns]),
161			self.tablename))
162
163			def Dependencies(self):
164			"""Return an empty tuple because a PostGISTable depends on nothing else
165			"""
166			return ()
167
168			def NumColumns(self):
169			return len(self.columns)
170
171			def Columns(self):
172			return self.columns
173
174			def Column(self, col):
175			return self.column_map[col]
176
177			def HasColumn(self, col):
178			return self.column_map.has_key(col)
179
180			def NumRows(self):
181			cursor = self.db.cursor()
182			cursor.execute("SELECT count(*) FROM %s" % self.tablename)
183			return cursor.fetchone()[0]
184
185			def ReadRowAsDict(self, row):
186			cursor = self.db.cursor()
187			cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)
188			result = {}
189			for col, value in zip(self.columns, cursor.fetchone()):
190			result[col.name] = value
191			return result
192
193			def ReadValue(self, row, col):
194			cursor = self.db.cursor()
195			cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
196			(self.column_map[col].name, self.tablename, row))
197			return cursor.fetchone()[0]
198
199			def ValueRange(self, col):
200			cursor = self.db.cursor()
201			name = self.column_map[col].name
202			cursor.execute("SELECT min(%s), max(%s) FROM %s" %
203			(name, name, self.tablename))
204			return tuple(cursor.fetchone())
205
206			def UniqueValues(self, col):
207			cursor = self.db.cursor()
208			name = self.column_map[col].name
209			cursor.execute("SELECT %s FROM %s GROUP BY %s" %
210			(name, self.tablename, name))
211			return [row[0] for row in cursor.fetchall()]
212
213			def SimpleQuery(self, left, comparison, right):
214			if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
215			raise ValueError("Comparison operator %r not allowed" % comparison)
216
217			if comparison == "==":
218			comparison = "="
219
220			if isinstance(right, PostGISColumn):
221			right_template = right.name
222			params = ()
223			else:
224			right_template = "%s"
225			params = (right,)
226
227			query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
228			% (self.tablename, left.name, comparison, right_template)
229
230			cursor = self.db.cursor()
231			cursor.execute(query, params)
232			result = []
233			while 1:
234			row = cursor.fetchone()
235			if row is None:
236			break
237			result.append(row[0])
238			return result
239
240
241			class PostGISShape:
242
243			def __init__(self, shapeid, data):
244			self.shapeid = shapeid
245			self.data = data
246
247			def compute_bbox(self):
248			"""
249			Return the bounding box of the shape as a tuple (minx,miny,maxx,maxy)
250			"""
251			xs = []
252			ys = []
253			for part in self.Points():
254			for x, y in part:
255			xs.append(x)
256			ys.append(y)
257			return (min(xs), min(ys), max(xs), max(ys))
258
259			def ShapeID(self):
260			return self.shapeid
261
262			def Points(self):
263			return wellknowntext.parse_wkt_thuban(self.data)
264
265			def RawData(self):
266			return self.data
267
268
269			shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
270			"MULTILINESTRING": SHAPETYPE_ARC,
271			"POINT": SHAPETYPE_POINT}
272
273
274			class PostGISShapeStore(PostGISTable):
275
276			"""Shapestore interface to a table in a PostGIS database"""
277
278			def Table(self):
279			"""Return self since a PostGISShapeStore is its own table."""
280			return self
281
282			def OrigShapeStore(self):
283			"""Return None since the PostGISShapeStore is not derived from another
284			"""
285			return None
286
287			def ShapeType(self):
288			"""Return the type of the shapes in the shapestore."""
289			cursor = self.db.cursor()
290			cursor.execute("SELECT type FROM geometry_columns WHERE"
291			" f_table_name=%s", (self.tablename,))
292			result = cursor.fetchone()[0]
293			cursor.close()
294			return shapetype_map[result]
295
296			def RawShapeFormat(self):
297			"""Return the raw data format of the shape data.
298
299			For the PostGISShapeStore this is RAW_WKT.
300			"""
301			return RAW_WKT
302
303			def NumShapes(self):
304			# The number of shapes is the same as the number of rows,
305			# assuming that the geometry can't be NULL.
306			return self.NumRows()
307
308			def BoundingBox(self):
309			"""Return the bounding box of all shapes in the postgis table"""
310			minx = miny = maxx = maxy = None
311			x=[]
312			y=[]
313			cursor = self.db.cursor()
314			try:
315			stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"
316			% (self.geometry_column, self.tablename))
317			cursor.execute(stmt)
318			result = cursor.fetchone()
319			while result:
320			result = result[0]
321			# Here we must do some parsing through the result string
322			# to get the points out of the polygon which representes
323			# the bounding box The first and the last point of a
324			# polygon are identical
325			result = result.split("(")[2]
326			result = result.split(")")[0]
327			points = result.split(",")
328			del points[4] # Remove the last point
329			for point in points:
330			px, py = point.split()
331			x.append(float(px))
332			y.append(float(py))
333			result = cursor.fetchone()
334			finally:
335			cursor.close()
336			if not x:
337			# Empty table
338			return None
339			return (min(x), min(y), max(x), max(y))
340
341			def Shape(self, shapeid):
342			cursor = self.db.cursor()
343			cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
344			% (self.geometry_column, self.tablename, shapeid))
345			wkt = cursor.fetchone()[0]
346			cursor.close()
347			return PostGISShape(shapeid, wkt)
348
349			def ShapesInRegion(self, bbox):
350			"""Generate all shapes overlapping the region given by bbox."""
351			# IMPORTANT:This will work for PostGIS < 0.8
352			left, bottom, right, top = bbox
353			geom = (("POLYGON((" + ", ".join(["%f %f"] * 5) + "))")
354			% (left, bottom, left, top, right, top, right, bottom,
355			left, bottom))
356			cursor = self.db.cursor()
357			cursor.execute("SELECT gid, AsText(%s) FROM %s"
358			" WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"
359			% (self.geometry_column, self.tablename,
360			self.geometry_column, geom))
361			while 1:
362			result = cursor.fetchone()
363			if result is None:
364			raise StopIteration
365			yield PostGISShape(result[0], result[1])
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision