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	# 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	def psycopg_version():
32	return psycopg.__version__
33
34	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	self.connect()
62
63	def connect(self):
64	"""Internal: Establish the database connection"""
65	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	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	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