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	# 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	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	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 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	"""Column description for a PostGISTable
139
140	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	def __init__(self, name, type, index):
148	self.name = name
149	self.quoted_name = quote_identifier(name)
150	self.type = type
151	self.index = index
152
153
154	class PostGISTable:
155
156	"""A Table in a PostGIS database
157
158	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	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	# 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	self.column_map = {}
177
178	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	cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
185	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	col = PostGISColumn(description[i][0], tabletyp,
191	len(self.columns))
192	break
193	else:
194	if description[i][1] == self.db.geometry_type:
195	self.geometry_column = description[i][0]
196	self.quoted_geo_col =quote_identifier(self.geometry_column)
197	# 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	% (", ".join([col.quoted_name
210	for col in self.columns]),
211	self.quoted_tablename))
212
213	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	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	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	cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
248	return cursor.fetchone()[0]
249
250	def RowIdToOrdinal(self, gid):
251	"""Return the row ordinal given its id"""
252	cursor = self.db.cursor()
253	cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
254	% (self.quoted_tablename, gid))
255	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	% (self.quoted_tablename, num))
262	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	result = {}
272	for col, value in zip(self.columns, cursor.fetchone()):
273	result[col.name] = value
274	return result
275
276	def ReadValue(self, row, col, row_is_ordinal = 0):
277	cursor = self.db.cursor()
278	if row_is_ordinal:
279	stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
280	(self.column_map[col].quoted_name, self.quoted_tablename,
281	row))
282	else:
283	stmt = ("SELECT %s FROM %s WHERE gid = %d" %
284	(self.column_map[col].quoted_name, self.quoted_tablename,
285	row))
286	cursor.execute(stmt)
287	return cursor.fetchone()[0]
288
289	def ValueRange(self, col):
290	cursor = self.db.cursor()
291	name = self.column_map[col].quoted_name
292	cursor.execute("SELECT min(%s), max(%s) FROM %s" %
293	(name, name, self.quoted_tablename))
294	return tuple(cursor.fetchone())
295
296	def UniqueValues(self, col):
297	cursor = self.db.cursor()
298	name = self.column_map[col].quoted_name
299	cursor.execute("SELECT %s FROM %s GROUP BY %s" %
300	(name, self.quoted_tablename, name))
301	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	right_template = right.quoted_name
312	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	% (self.quoted_tablename, left.quoted_name, comparison,
319	right_template)
320
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	"MULTIPOLYGON": SHAPETYPE_POLYGON,
362	"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	# 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	% (self.quoted_geo_col, self.quoted_tablename))
413	result = cursor.fetchone()
414	if result:
415	(minx, miny), (maxx, maxy) \
416	= wellknowntext.parse_wkt_thuban(result[0])[0]
417	return (minx, miny, maxx, maxy)
418	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	% (self.quoted_geo_col, self.quoted_tablename, shapeid))
425	wkt = cursor.fetchone()[0]
426	cursor.close()
427	return PostGISShape(shapeid, wkt)
428
429	def AllShapes(self):
430	cursor = self.db.cursor()
431	cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
432	% (self.quoted_geo_col, self.quoted_tablename))
433	while 1:
434	result = cursor.fetchone()
435	if result is None:
436	return
437	yield PostGISShape(result[0], result[1])
438
439
440	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	% (self.quoted_geo_col, self.quoted_tablename,
451	self.quoted_geo_col, geom))
452	while 1:
453	result = cursor.fetchone()
454	if result is None:
455	return
456	yield PostGISShape(result[0], result[1])
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision