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	bh	2057	# Copyright (C) 2003, 2004 by Intevation GmbH
2	bh	1605	# 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	bh	2057	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	bh	1946
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	1955	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	bh	1605	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	bh	1946	"""Column description for a PostGISTable
153	bh	1605
154	bh	1946	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	bh	1605	def __init__(self, name, type, index):
162			self.name = name
163	bh	1946	self.quoted_name = quote_identifier(name)
164	bh	1605	self.type = type
165			self.index = index
166
167
168			class PostGISTable:
169
170	bh	1693	"""A Table in a PostGIS database
171	bh	1605
172	bh	1693	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	bh	1605	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	bh	1946	# 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	bh	1605	self.column_map = {}
191	bh	1946
192	bh	1605	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	bh	1946	cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
199	bh	1605	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	bh	1693	col = PostGISColumn(description[i][0], tabletyp,
205			len(self.columns))
206	bh	1605	break
207			else:
208			if description[i][1] == self.db.geometry_type:
209			self.geometry_column = description[i][0]
210	bh	1946	self.quoted_geo_col =quote_identifier(self.geometry_column)
211	bh	1605	# 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	bh	1946	% (", ".join([col.quoted_name
224			for col in self.columns]),
225			self.quoted_tablename))
226	bh	1605
227	bh	1638	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	bh	1658	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	bh	1605	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	bh	1946	cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
262	bh	1605	return cursor.fetchone()[0]
263
264	bh	1662	def RowIdToOrdinal(self, gid):
265			"""Return the row ordinal given its id"""
266	bh	1605	cursor = self.db.cursor()
267	bh	1662	cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
268	bh	1946	% (self.quoted_tablename, gid))
269	bh	1662	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	bh	1946	% (self.quoted_tablename, num))
276	bh	1662	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	bh	1605	result = {}
286			for col, value in zip(self.columns, cursor.fetchone()):
287			result[col.name] = value
288			return result
289
290	bh	1662	def ReadValue(self, row, col, row_is_ordinal = 0):
291	bh	1605	cursor = self.db.cursor()
292	bh	1662	if row_is_ordinal:
293			stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
294	bh	1946	(self.column_map[col].quoted_name, self.quoted_tablename,
295			row))
296	bh	1662	else:
297			stmt = ("SELECT %s FROM %s WHERE gid = %d" %
298	bh	1946	(self.column_map[col].quoted_name, self.quoted_tablename,
299			row))
300	bh	1662	cursor.execute(stmt)
301	bh	1605	return cursor.fetchone()[0]
302
303			def ValueRange(self, col):
304			cursor = self.db.cursor()
305	bh	1946	name = self.column_map[col].quoted_name
306	bh	1605	cursor.execute("SELECT min(%s), max(%s) FROM %s" %
307	bh	1946	(name, name, self.quoted_tablename))
308	bh	1605	return tuple(cursor.fetchone())
309
310			def UniqueValues(self, col):
311			cursor = self.db.cursor()
312	bh	1946	name = self.column_map[col].quoted_name
313	bh	1605	cursor.execute("SELECT %s FROM %s GROUP BY %s" %
314	bh	1946	(name, self.quoted_tablename, name))
315	bh	1605	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	bh	1946	right_template = right.quoted_name
326	bh	1605	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	bh	1946	% (self.quoted_tablename, left.quoted_name, comparison,
333			right_template)
334	bh	1605
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	bh	1656	"MULTIPOLYGON": SHAPETYPE_POLYGON,
376	bh	1605	"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	bh	2057	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	bh	1605	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	bh	1660	# 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	bh	1946	% (self.quoted_geo_col, self.quoted_tablename))
435	bh	1605	result = cursor.fetchone()
436	bh	2057	if result[0]:
437	bh	1660	(minx, miny), (maxx, maxy) \
438			= wellknowntext.parse_wkt_thuban(result[0])[0]
439			return (minx, miny, maxx, maxy)
440	bh	1605	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	bh	1946	% (self.quoted_geo_col, self.quoted_tablename, shapeid))
447	bh	1605	wkt = cursor.fetchone()[0]
448			cursor.close()
449			return PostGISShape(shapeid, wkt)
450
451	bh	1658	def AllShapes(self):
452			cursor = self.db.cursor()
453			cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
454	bh	1946	% (self.quoted_geo_col, self.quoted_tablename))
455	bh	1658	while 1:
456			result = cursor.fetchone()
457			if result is None:
458			return
459			yield PostGISShape(result[0], result[1])
460
461
462	bh	1605	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	bh	2057	" WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"
472	bh	1946	% (self.quoted_geo_col, self.quoted_tablename,
473	bh	2057	self.quoted_geo_col, geom, self.srid))
474	bh	1605	while 1:
475			result = cursor.fetchone()
476			if result is None:
477	bh	1658	return
478	bh	1605	yield PostGISShape(result[0], result[1])
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision