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

def psycopg_version():
    return psycopg.__version__

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