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	# 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