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	# 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	self.connect()
52
53	def connect(self):
54	"""Internal: Establish the database connection"""
55	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	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	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