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


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