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 ConnectionError(Exception):

    """Class for exceptions occurring when establishing a Databse connection"""


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))
        try:
            self.connection = psycopg.connect(" ".join(params))
        except psycopg.OperationalError, val:
            raise ConnectionError(str(val))

        # 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 DBConnection(self):
        """Return the dbconnection used by the table"""
        return self.db

    def TableName(self):
        """Return the name of the table in the database"""
        return self.tablename

    def Title(self):
        """Return the title of the table.

        The title is currently fixed and equal to the tablename
        """
        return 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 RowIdToOrdinal(self, gid):
        """Return the row ordinal given its id"""
        cursor = self.db.cursor()
        cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
                       % (self.tablename, gid))
        return cursor.fetchone()[0]

    def RowOrdinalToId(self, num):
        """Return the rowid for given its ordinal"""
        cursor = self.db.cursor()
        cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
                       % (self.tablename, num))
        return cursor.fetchone()[0]

    def ReadRowAsDict(self, row, row_is_ordinal = 0):
        cursor = self.db.cursor()
        if row_is_ordinal:
            stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
        else:
            stmt = self.query_stmt + " WHERE gid = %d" % row
        cursor.execute(stmt)
        result = {}
        for col, value in zip(self.columns, cursor.fetchone()):
            result[col.name] = value
        return result

    def ReadValue(self, row, col, row_is_ordinal = 0):
        cursor = self.db.cursor()
        if row_is_ordinal:
            stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
                    (self.column_map[col].name, self.tablename, row))
        else:
            stmt = ("SELECT %s FROM %s WHERE gid = %d" %
                    (self.column_map[col].name, self.tablename, row))
        cursor.execute(stmt)
        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,
                 "MULTIPOLYGON": 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:
            # Using the extent function is postgis specific. An OGC
            # Simple Features compliant solution would be to use a query
            # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
            # calculate the bounding box by hand from that
            cursor.execute("SELECT extent(%s) FROM %s;"
                           % (self.geometry_column, self.tablename))
            result = cursor.fetchone()
            if result:
                (minx, miny), (maxx, maxy) \
                      = wellknowntext.parse_wkt_thuban(result[0])[0]
                return (minx, miny, maxx, maxy)
        finally:
            cursor.close()

    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 AllShapes(self):
        cursor = self.db.cursor()
        cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
                       % (self.geometry_column, self.tablename))
        while 1:
            result = cursor.fetchone()
            if result is None:
                return
            yield PostGISShape(result[0], result[1])


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