Thuban/Model/postgisdb.py

# Copyright (C) 2003, 2004 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 already 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 MatchesParameters(self, parameters):
        """Return whether the connection matches the dictionary of parameters

        Return whether instatiating the connection with the given
        parameters would establish essentially the same connection as
        self. The connection is essentially the same if the same
        database (identified by host, port and databasename) is accessed
        as the same user.
        """
        return (parameters["host"] == self.host
                and parameters["port"] == self.port
                and parameters["dbname"] == self.dbname
                and parameters["user"] == self.user)

    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 _fetch_table_information(self):
        """Extend inherited method to retrieve the SRID"""
        PostGISTable._fetch_table_information(self)
        cursor = self.db.cursor()
        cursor.execute("SELECT srid FROM geometry_columns"
                       " WHERE f_table_name = %s", (self.tablename,))
        self.srid = cursor.fetchone()[0]

    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[0]:
                (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', %d) ORDER BY gid"
                       % (self.quoted_geo_col, self.quoted_tablename,
                          self.quoted_geo_col, geom, self.srid))
        while 1:
            result = cursor.fetchone()
            if result is None:
                return
            yield PostGISShape(result[0], result[1])
1	# Copyright (C) 2003, 2004 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	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 already 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	class ConnectionError(Exception):
57
58	"""Class for exceptions occurring when establishing a Databse connection"""
59
60
61	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	self.connect()
84
85	def connect(self):
86	"""Internal: Establish the database connection"""
87	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	try:
93	self.connection = psycopg.connect(" ".join(params))
94	except psycopg.OperationalError, val:
95	raise ConnectionError(str(val))
96
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	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	def MatchesParameters(self, parameters):
119	"""Return whether the connection matches the dictionary of parameters
120
121	Return whether instatiating the connection with the given
122	parameters would establish essentially the same connection as
123	self. The connection is essentially the same if the same
124	database (identified by host, port and databasename) is accessed
125	as the same user.
126	"""
127	return (parameters["host"] == self.host
128	and parameters["port"] == self.port
129	and parameters["dbname"] == self.dbname
130	and parameters["user"] == self.user)
131
132	def Close(self):
133	"""Close the database connection"""
134	self.connection.close()
135
136	def GeometryTables(self):
137	"""Return a list with the names of all tables with a geometry column"""
138	cursor = self.connection.cursor()
139	cursor.execute("SELECT f_table_name FROM geometry_columns;")
140	result = [row[0] for row in cursor.fetchall()]
141	self.connection.commit()
142	return result
143
144	def cursor(self):
145	"""Return a DB API 2.0 cursor for the database"""
146	return self.connection.cursor()
147
148
149
150	class PostGISColumn:
151
152	"""Column description for a PostGISTable
153
154	In addition to the normal column object attributes name, type and
155	index, PostGISColumn objects have a quoted_name attribute which
156	contains a quoted version of name for use in SQL statements. The
157	quoted_name attribute is mainly intended for internal use by the
158	PostGISTable class.
159	"""
160
161	def __init__(self, name, type, index):
162	self.name = name
163	self.quoted_name = quote_identifier(name)
164	self.type = type
165	self.index = index
166
167
168	class PostGISTable:
169
170	"""A Table in a PostGIS database
171
172	A PostgreSQL table may contain columns with types not (yet)
173	supported by Thuban. Instances of this class ignore those columns
174	and pretend they don't exist, i.e. they won't show up in the column
175	descriptions returned by Columns() and other methods.
176	"""
177
178	def __init__(self, db, tablename):
179	"""Initialize the PostGISTable.
180
181	The db parameter should be an instance of PostGISConnection and
182	tablename the name of a table in the database represented by db.
183	"""
184	self.db = db
185	self.tablename = tablename
186	# Tablename quoted for use in SQL statements.
187	self.quoted_tablename = quote_identifier(tablename)
188
189	# Map column names and indices to column objects.
190	self.column_map = {}
191
192	self._fetch_table_information()
193
194	def _fetch_table_information(self):
195	"""Internal: Update information about the table"""
196	self.columns = []
197	cursor = self.db.cursor()
198	cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
199	description = cursor.description
200
201	for i in range(len(description)):
202	for pgtyp, tabletyp in type_map:
203	if pgtyp == description[i][1]:
204	col = PostGISColumn(description[i][0], tabletyp,
205	len(self.columns))
206	break
207	else:
208	if description[i][1] == self.db.geometry_type:
209	self.geometry_column = description[i][0]
210	self.quoted_geo_col =quote_identifier(self.geometry_column)
211	# No matching table type. Ignore the column.
212	# FIXME: We should at least print a warning about
213	# ignored columns
214	continue
215	self.columns.append(col)
216
217	for col in self.columns:
218	self.column_map[col.name] = col
219	self.column_map[col.index] = col
220
221	# Build query string for ReadRowAsDict
222	self.query_stmt = ("SELECT %s from %s"
223	% (", ".join([col.quoted_name
224	for col in self.columns]),
225	self.quoted_tablename))
226
227	def DBConnection(self):
228	"""Return the dbconnection used by the table"""
229	return self.db
230
231	def TableName(self):
232	"""Return the name of the table in the database"""
233	return self.tablename
234
235	def Title(self):
236	"""Return the title of the table.
237
238	The title is currently fixed and equal to the tablename
239	"""
240	return self.tablename
241
242	def Dependencies(self):
243	"""Return an empty tuple because a PostGISTable depends on nothing else
244	"""
245	return ()
246
247	def NumColumns(self):
248	return len(self.columns)
249
250	def Columns(self):
251	return self.columns
252
253	def Column(self, col):
254	return self.column_map[col]
255
256	def HasColumn(self, col):
257	return self.column_map.has_key(col)
258
259	def NumRows(self):
260	cursor = self.db.cursor()
261	cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
262	return cursor.fetchone()[0]
263
264	def RowIdToOrdinal(self, gid):
265	"""Return the row ordinal given its id"""
266	cursor = self.db.cursor()
267	cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
268	% (self.quoted_tablename, gid))
269	return cursor.fetchone()[0]
270
271	def RowOrdinalToId(self, num):
272	"""Return the rowid for given its ordinal"""
273	cursor = self.db.cursor()
274	cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
275	% (self.quoted_tablename, num))
276	return cursor.fetchone()[0]
277
278	def ReadRowAsDict(self, row, row_is_ordinal = 0):
279	cursor = self.db.cursor()
280	if row_is_ordinal:
281	stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
282	else:
283	stmt = self.query_stmt + " WHERE gid = %d" % row
284	cursor.execute(stmt)
285	result = {}
286	for col, value in zip(self.columns, cursor.fetchone()):
287	result[col.name] = value
288	return result
289
290	def ReadValue(self, row, col, row_is_ordinal = 0):
291	cursor = self.db.cursor()
292	if row_is_ordinal:
293	stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
294	(self.column_map[col].quoted_name, self.quoted_tablename,
295	row))
296	else:
297	stmt = ("SELECT %s FROM %s WHERE gid = %d" %
298	(self.column_map[col].quoted_name, self.quoted_tablename,
299	row))
300	cursor.execute(stmt)
301	return cursor.fetchone()[0]
302
303	def ValueRange(self, col):
304	cursor = self.db.cursor()
305	name = self.column_map[col].quoted_name
306	cursor.execute("SELECT min(%s), max(%s) FROM %s" %
307	(name, name, self.quoted_tablename))
308	return tuple(cursor.fetchone())
309
310	def UniqueValues(self, col):
311	cursor = self.db.cursor()
312	name = self.column_map[col].quoted_name
313	cursor.execute("SELECT %s FROM %s GROUP BY %s" %
314	(name, self.quoted_tablename, name))
315	return [row[0] for row in cursor.fetchall()]
316
317	def SimpleQuery(self, left, comparison, right):
318	if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
319	raise ValueError("Comparison operator %r not allowed" % comparison)
320
321	if comparison == "==":
322	comparison = "="
323
324	if isinstance(right, PostGISColumn):
325	right_template = right.quoted_name
326	params = ()
327	else:
328	right_template = "%s"
329	params = (right,)
330
331	query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
332	% (self.quoted_tablename, left.quoted_name, comparison,
333	right_template)
334
335	cursor = self.db.cursor()
336	cursor.execute(query, params)
337	result = []
338	while 1:
339	row = cursor.fetchone()
340	if row is None:
341	break
342	result.append(row[0])
343	return result
344
345
346	class PostGISShape:
347
348	def __init__(self, shapeid, data):
349	self.shapeid = shapeid
350	self.data = data
351
352	def compute_bbox(self):
353	"""
354	Return the bounding box of the shape as a tuple (minx,miny,maxx,maxy)
355	"""
356	xs = []
357	ys = []
358	for part in self.Points():
359	for x, y in part:
360	xs.append(x)
361	ys.append(y)
362	return (min(xs), min(ys), max(xs), max(ys))
363
364	def ShapeID(self):
365	return self.shapeid
366
367	def Points(self):
368	return wellknowntext.parse_wkt_thuban(self.data)
369
370	def RawData(self):
371	return self.data
372
373
374	shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
375	"MULTIPOLYGON": SHAPETYPE_POLYGON,
376	"MULTILINESTRING": SHAPETYPE_ARC,
377	"POINT": SHAPETYPE_POINT}
378
379
380	class PostGISShapeStore(PostGISTable):
381
382	"""Shapestore interface to a table in a PostGIS database"""
383
384	def _fetch_table_information(self):
385	"""Extend inherited method to retrieve the SRID"""
386	PostGISTable._fetch_table_information(self)
387	cursor = self.db.cursor()
388	cursor.execute("SELECT srid FROM geometry_columns"
389	" WHERE f_table_name = %s", (self.tablename,))
390	self.srid = cursor.fetchone()[0]
391
392	def Table(self):
393	"""Return self since a PostGISShapeStore is its own table."""
394	return self
395
396	def OrigShapeStore(self):
397	"""Return None since the PostGISShapeStore is not derived from another
398	"""
399	return None
400
401	def ShapeType(self):
402	"""Return the type of the shapes in the shapestore."""
403	cursor = self.db.cursor()
404	cursor.execute("SELECT type FROM geometry_columns WHERE"
405	" f_table_name=%s", (self.tablename,))
406	result = cursor.fetchone()[0]
407	cursor.close()
408	return shapetype_map[result]
409
410	def RawShapeFormat(self):
411	"""Return the raw data format of the shape data.
412
413	For the PostGISShapeStore this is RAW_WKT.
414	"""
415	return RAW_WKT
416
417	def NumShapes(self):
418	# The number of shapes is the same as the number of rows,
419	# assuming that the geometry can't be NULL.
420	return self.NumRows()
421
422	def BoundingBox(self):
423	"""Return the bounding box of all shapes in the postgis table"""
424	minx = miny = maxx = maxy = None
425	x=[]
426	y=[]
427	cursor = self.db.cursor()
428	try:
429	# Using the extent function is postgis specific. An OGC
430	# Simple Features compliant solution would be to use a query
431	# like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
432	# calculate the bounding box by hand from that
433	cursor.execute("SELECT extent(%s) FROM %s;"
434	% (self.quoted_geo_col, self.quoted_tablename))
435	result = cursor.fetchone()
436	if result[0]:
437	(minx, miny), (maxx, maxy) \
438	= wellknowntext.parse_wkt_thuban(result[0])[0]
439	return (minx, miny, maxx, maxy)
440	finally:
441	cursor.close()
442
443	def Shape(self, shapeid):
444	cursor = self.db.cursor()
445	cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
446	% (self.quoted_geo_col, self.quoted_tablename, shapeid))
447	wkt = cursor.fetchone()[0]
448	cursor.close()
449	return PostGISShape(shapeid, wkt)
450
451	def AllShapes(self):
452	cursor = self.db.cursor()
453	cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
454	% (self.quoted_geo_col, self.quoted_tablename))
455	while 1:
456	result = cursor.fetchone()
457	if result is None:
458	return
459	yield PostGISShape(result[0], result[1])
460
461
462	def ShapesInRegion(self, bbox):
463	"""Generate all shapes overlapping the region given by bbox."""
464	# IMPORTANT:This will work for PostGIS < 0.8
465	left, bottom, right, top = bbox
466	geom = (("POLYGON((" + ", ".join(["%f %f"] * 5) + "))")
467	% (left, bottom, left, top, right, top, right, bottom,
468	left, bottom))
469	cursor = self.db.cursor()
470	cursor.execute("SELECT gid, AsText(%s) FROM %s"
471	" WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"
472	% (self.quoted_geo_col, self.quoted_tablename,
473	self.quoted_geo_col, geom, self.srid))
474	while 1:
475	result = cursor.fetchone()
476	if result is None:
477	return
478	yield PostGISShape(result[0], result[1])
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision