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