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)]


def quote_identifier(ident):
    """Return a quoted version of the identifier ident.

    The return value is a string that can be put directly into an SQL
    statement. The quoted identifier is surrounded by double quotes and
    any double quotes alread in the input value are converted to two
    double quotes. Examples:

    >>> quote_identifier("abc\"def")
    '"abc""def"'
    >>> quote_identifier("abc def")
    '"abc def"'
    """
    return '"' + '""'.join(ident.split('"')) + '"'


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

    In addition to the normal column object attributes name, type and
    index, PostGISColumn objects have a quoted_name attribute which
    contains a quoted version of name for use in SQL statements. The
    quoted_name attribute is mainly intended for internal use by the
    PostGISTable class.
    """

    def __init__(self, name, type, index):
        self.name = name
        self.quoted_name = quote_identifier(name)
        self.type = type
        self.index = index


class PostGISTable:

    """A Table in a PostGIS database

    A PostgreSQL table may contain columns with types not (yet)
    supported by Thuban. Instances of this class ignore those columns
    and pretend they don't exist, i.e. they won't show up in the column
    descriptions returned by Columns() and other methods.
    """

    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
        # Tablename quoted for use in SQL statements.
        self.quoted_tablename = quote_identifier(tablename)

        # Map column names and indices to column objects.
        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.quoted_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,
                                        len(self.columns))
                    break
            else:
                if description[i][1] == self.db.geometry_type:
                    self.geometry_column = description[i][0]
                    self.quoted_geo_col =quote_identifier(self.geometry_column)
                # 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.quoted_name
                                         for col in self.columns]),
                              self.quoted_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.quoted_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.quoted_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.quoted_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].quoted_name, self.quoted_tablename,
                     row))
        else:
            stmt = ("SELECT %s FROM %s WHERE gid = %d" %
                    (self.column_map[col].quoted_name, self.quoted_tablename,
                     row))
        cursor.execute(stmt)
        return cursor.fetchone()[0]

    def ValueRange(self, col):
        cursor = self.db.cursor()
        name = self.column_map[col].quoted_name
        cursor.execute("SELECT min(%s), max(%s) FROM %s" %
                       (name, name, self.quoted_tablename))
        return tuple(cursor.fetchone())

    def UniqueValues(self, col):
        cursor = self.db.cursor()
        name = self.column_map[col].quoted_name
        cursor.execute("SELECT %s FROM %s GROUP BY %s" %
                       (name, self.quoted_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.quoted_name
            params = ()
        else:
            right_template = "%s"
            params = (right,)

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