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	# 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	def has_postgis_support():
24	"""Return whether this Thuban instance supports PostGIS connections
25
26	Having PostGIS support means that the psycopg module can be
27	imported.
28	"""
29	return psycopg is not None
30
31	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	self.connect()
59
60	def connect(self):
61	"""Internal: Establish the database connection"""
62	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	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	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