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, id_column = "gid"):
        """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.

        The id_column parameter should be the name of a column in the
        table that can be used to identify rows.  The column must have
        the type integer and be unique and not null.
        """
        self.db = db
        self.tablename = tablename
        # Tablename quoted for use in SQL statements.
        self.quoted_tablename = quote_identifier(tablename)

        self.id_column = id_column
        # id column name quoted for use in SQL statements.
        self.quoted_id_column = quote_identifier(id_column)

        # 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)):
            col = self._create_col_from_description(i, description[i])
            if col is not None:
                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 _create_col_from_description(self, index, description):
        """Return the column object for the column described by description

        The parameter index is the index of the column.  The description
        is a sequence taken from the cursor's description attribute for
        the column.  That means description[0] is the name of the column
        and description[1] the type.

        Return None if the column can't be represented for some reason,
        e.g. because its type is not yet supported or needs to be
        treated in some special way.  Derived classes may extend this
        method.
        """
        for pgtyp, tabletyp in type_map:
            if pgtyp == description[1]:
                return PostGISColumn(description[0], tabletyp,
                                     len(self.columns))
        return None

    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 %s < %d;"
                       % (self.quoted_tablename, self.quoted_id_column, gid))
        return cursor.fetchone()[0]

    def RowOrdinalToId(self, num):
        """Return the rowid for given its ordinal"""
        cursor = self.db.cursor()
        cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
                       % (self.quoted_id_column, 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 %s = %d" % (self.quoted_id_column,
                                                         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 %s = %d" %
                    (self.column_map[col].quoted_name, self.quoted_tablename,
                     self.quoted_id_column, 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 %s FROM %s WHERE %s %s %s ORDER BY %s;" \
                % (self.quoted_id_column, self.quoted_tablename,
                   left.quoted_name, comparison, right_template,
                   self.quoted_id_column)

        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 __init__(self, db, tablename, id_column = "gid",
                 geometry_column = None):
        """Initialize the PostGISShapeStore.

        The db parameter should be an instance of PostGISConnection and
        tablename the name of a table in the database represented by db.

        The id_column parameter should be the name of a column in the
        table that can be used to identify rows.  The column must have
        the type integer and be unique and not null.

        The geometry_column paramter, if given, should be the name of
        the geometry column to use.  If the name given is not a geometry
        column, raise a ValueError.

        If no geometry_column is given, the table must have exactly one
        geometry column.  If it has more than one and the
        geometry_column is not given, a ValueError will be raised.
        """
        self.geometry_column = geometry_column
        self.geometry_column_was_given = geometry_column is not None
        PostGISTable.__init__(self, db, tablename, id_column)

        # For convenience, we have a quoted version of the geometry
        # column in self.quoted_geo_col
        self.quoted_geo_col = quote_identifier(self.geometry_column)

    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 AND f_geometry_column=%s",
                       (self.tablename, self.geometry_column))
        self.srid = cursor.fetchone()[0]

    def _create_col_from_description(self, index, description):
        """Extend the inherited method to find geometry columns

        If the column indicated by the parameters is a geometry column,
        record its name in self.geometry_column and a quoted version in
        self.quoted_geo_col.  In any case return the return value of the
        inherited method.
        """
        col = PostGISTable._create_col_from_description(self, index,
                                                        description)
        col_name, col_type = description[:2]
        if self.geometry_column_was_given:
            if (col_name == self.geometry_column
                and col_type != self.db.geometry_type):
                raise TypeError("Column %s in %s is not a geometry column"
                                % (self.geometry_column, self.tablename))
        else:
            if col is None:
                if description[1] == self.db.geometry_type:
                    # The column is a geometry column.  If the name of
                    # the geometry column was not given to the
                    # constructor, and we encounter two geometry
                    # columns, raise a value error
                    if self.geometry_column is None:
                        self.geometry_column = description[0]
                    else:
                        raise TypeError("Table %s has two geometry columns"
                                        " and no column name was given"
                                        % (self.tablename,))
        return col

    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 AND f_geometry_column=%s",
                       (self.tablename, self.geometry_column))
        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 %s=%d"
                       % (self.quoted_geo_col, self.quoted_tablename,
                          self.quoted_id_column, shapeid))
        wkt = cursor.fetchone()[0]
        cursor.close()
        return PostGISShape(shapeid, wkt)

    def AllShapes(self):
        cursor = self.db.cursor()
        cursor.execute("SELECT %s, AsText(%s) FROM %s ORDER BY %s"
                       % (self.quoted_id_column, self.quoted_geo_col,
                          self.quoted_tablename, self.quoted_id_column))
        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)s, AsText(%(geom)s) FROM %(table)s"
                     " WHERE %(geom)s && GeometryFromText('%(box)s', %(srid)d)"
                       " ORDER BY %(gid)s"
                       % {"table": self.quoted_tablename,
                          "geom": self.quoted_geo_col,
                          "gid": self.quoted_id_column,
                          "box": geom,
                          "srid": 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	2096	def __init__(self, db, tablename, id_column = "gid"):
179	bh	1605	"""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	bh	2096
184			The id_column parameter should be the name of a column in the
185			table that can be used to identify rows. The column must have
186			the type integer and be unique and not null.
187	bh	1605	"""
188			self.db = db
189			self.tablename = tablename
190	bh	1946	# Tablename quoted for use in SQL statements.
191			self.quoted_tablename = quote_identifier(tablename)
192
193	bh	2096	self.id_column = id_column
194			# id column name quoted for use in SQL statements.
195			self.quoted_id_column = quote_identifier(id_column)
196
197	bh	1946	# Map column names and indices to column objects.
198	bh	1605	self.column_map = {}
199	bh	1946
200	bh	1605	self._fetch_table_information()
201
202			def _fetch_table_information(self):
203			"""Internal: Update information about the table"""
204			self.columns = []
205			cursor = self.db.cursor()
206	bh	1946	cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
207	bh	1605	description = cursor.description
208
209			for i in range(len(description)):
210	bh	2059	col = self._create_col_from_description(i, description[i])
211			if col is not None:
212			self.columns.append(col)
213	bh	1605
214			for col in self.columns:
215			self.column_map[col.name] = col
216			self.column_map[col.index] = col
217
218			# Build query string for ReadRowAsDict
219			self.query_stmt = ("SELECT %s from %s"
220	bh	1946	% (", ".join([col.quoted_name
221			for col in self.columns]),
222			self.quoted_tablename))
223	bh	1605
224	bh	2059	def _create_col_from_description(self, index, description):
225			"""Return the column object for the column described by description
226
227			The parameter index is the index of the column. The description
228			is a sequence taken from the cursor's description attribute for
229			the column. That means description[0] is the name of the column
230			and description[1] the type.
231
232			Return None if the column can't be represented for some reason,
233			e.g. because its type is not yet supported or needs to be
234			treated in some special way. Derived classes may extend this
235			method.
236			"""
237			for pgtyp, tabletyp in type_map:
238			if pgtyp == description[1]:
239			return PostGISColumn(description[0], tabletyp,
240			len(self.columns))
241			return None
242
243	bh	1638	def DBConnection(self):
244			"""Return the dbconnection used by the table"""
245			return self.db
246
247			def TableName(self):
248			"""Return the name of the table in the database"""
249			return self.tablename
250
251	bh	1658	def Title(self):
252			"""Return the title of the table.
253
254			The title is currently fixed and equal to the tablename
255			"""
256			return self.tablename
257
258	bh	1605	def Dependencies(self):
259			"""Return an empty tuple because a PostGISTable depends on nothing else
260			"""
261			return ()
262
263			def NumColumns(self):
264			return len(self.columns)
265
266			def Columns(self):
267			return self.columns
268
269			def Column(self, col):
270			return self.column_map[col]
271
272			def HasColumn(self, col):
273			return self.column_map.has_key(col)
274
275			def NumRows(self):
276			cursor = self.db.cursor()
277	bh	1946	cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
278	bh	1605	return cursor.fetchone()[0]
279
280	bh	1662	def RowIdToOrdinal(self, gid):
281			"""Return the row ordinal given its id"""
282	bh	1605	cursor = self.db.cursor()
283	bh	2096	cursor.execute("SELECT count(*) FROM %s WHERE %s < %d;"
284			% (self.quoted_tablename, self.quoted_id_column, gid))
285	bh	1662	return cursor.fetchone()[0]
286
287			def RowOrdinalToId(self, num):
288			"""Return the rowid for given its ordinal"""
289			cursor = self.db.cursor()
290	bh	2096	cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
291			% (self.quoted_id_column, self.quoted_tablename, num))
292	bh	1662	return cursor.fetchone()[0]
293
294			def ReadRowAsDict(self, row, row_is_ordinal = 0):
295			cursor = self.db.cursor()
296			if row_is_ordinal:
297			stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
298			else:
299	bh	2096	stmt = self.query_stmt + " WHERE %s = %d" % (self.quoted_id_column,
300			row)
301	bh	1662	cursor.execute(stmt)
302	bh	1605	result = {}
303			for col, value in zip(self.columns, cursor.fetchone()):
304			result[col.name] = value
305			return result
306
307	bh	1662	def ReadValue(self, row, col, row_is_ordinal = 0):
308	bh	1605	cursor = self.db.cursor()
309	bh	1662	if row_is_ordinal:
310			stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
311	bh	1946	(self.column_map[col].quoted_name, self.quoted_tablename,
312			row))
313	bh	1662	else:
314	bh	2096	stmt = ("SELECT %s FROM %s WHERE %s = %d" %
315	bh	1946	(self.column_map[col].quoted_name, self.quoted_tablename,
316	bh	2096	self.quoted_id_column, row))
317	bh	1662	cursor.execute(stmt)
318	bh	1605	return cursor.fetchone()[0]
319
320			def ValueRange(self, col):
321			cursor = self.db.cursor()
322	bh	1946	name = self.column_map[col].quoted_name
323	bh	1605	cursor.execute("SELECT min(%s), max(%s) FROM %s" %
324	bh	1946	(name, name, self.quoted_tablename))
325	bh	1605	return tuple(cursor.fetchone())
326
327			def UniqueValues(self, col):
328			cursor = self.db.cursor()
329	bh	1946	name = self.column_map[col].quoted_name
330	bh	1605	cursor.execute("SELECT %s FROM %s GROUP BY %s" %
331	bh	1946	(name, self.quoted_tablename, name))
332	bh	1605	return [row[0] for row in cursor.fetchall()]
333
334			def SimpleQuery(self, left, comparison, right):
335			if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
336			raise ValueError("Comparison operator %r not allowed" % comparison)
337
338			if comparison == "==":
339			comparison = "="
340
341			if isinstance(right, PostGISColumn):
342	bh	1946	right_template = right.quoted_name
343	bh	1605	params = ()
344			else:
345			right_template = "%s"
346			params = (right,)
347
348	bh	2096	query = "SELECT %s FROM %s WHERE %s %s %s ORDER BY %s;" \
349			% (self.quoted_id_column, self.quoted_tablename,
350			left.quoted_name, comparison, right_template,
351			self.quoted_id_column)
352	bh	1605
353			cursor = self.db.cursor()
354			cursor.execute(query, params)
355			result = []
356			while 1:
357			row = cursor.fetchone()
358			if row is None:
359			break
360			result.append(row[0])
361			return result
362
363
364			class PostGISShape:
365
366			def __init__(self, shapeid, data):
367			self.shapeid = shapeid
368			self.data = data
369
370			def compute_bbox(self):
371			"""
372			Return the bounding box of the shape as a tuple (minx,miny,maxx,maxy)
373			"""
374			xs = []
375			ys = []
376			for part in self.Points():
377			for x, y in part:
378			xs.append(x)
379			ys.append(y)
380			return (min(xs), min(ys), max(xs), max(ys))
381
382			def ShapeID(self):
383			return self.shapeid
384
385			def Points(self):
386			return wellknowntext.parse_wkt_thuban(self.data)
387
388			def RawData(self):
389			return self.data
390
391
392			shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
393	bh	1656	"MULTIPOLYGON": SHAPETYPE_POLYGON,
394	bh	1605	"MULTILINESTRING": SHAPETYPE_ARC,
395			"POINT": SHAPETYPE_POINT}
396
397
398			class PostGISShapeStore(PostGISTable):
399
400			"""Shapestore interface to a table in a PostGIS database"""
401
402	bh	2096	def __init__(self, db, tablename, id_column = "gid",
403			geometry_column = None):
404	bh	2059	"""Initialize the PostGISShapeStore.
405
406			The db parameter should be an instance of PostGISConnection and
407			tablename the name of a table in the database represented by db.
408
409	bh	2096	The id_column parameter should be the name of a column in the
410			table that can be used to identify rows. The column must have
411			the type integer and be unique and not null.
412
413	bh	2059	The geometry_column paramter, if given, should be the name of
414			the geometry column to use. If the name given is not a geometry
415			column, raise a ValueError.
416
417			If no geometry_column is given, the table must have exactly one
418			geometry column. If it has more than one and the
419			geometry_column is not given, a ValueError will be raised.
420			"""
421			self.geometry_column = geometry_column
422			self.geometry_column_was_given = geometry_column is not None
423	bh	2096	PostGISTable.__init__(self, db, tablename, id_column)
424	bh	2059
425			# For convenience, we have a quoted version of the geometry
426			# column in self.quoted_geo_col
427			self.quoted_geo_col = quote_identifier(self.geometry_column)
428
429	bh	2057	def _fetch_table_information(self):
430			"""Extend inherited method to retrieve the SRID"""
431			PostGISTable._fetch_table_information(self)
432			cursor = self.db.cursor()
433			cursor.execute("SELECT srid FROM geometry_columns"
434	bh	2059	" WHERE f_table_name = %s AND f_geometry_column=%s",
435			(self.tablename, self.geometry_column))
436	bh	2057	self.srid = cursor.fetchone()[0]
437
438	bh	2059	def _create_col_from_description(self, index, description):
439			"""Extend the inherited method to find geometry columns
440
441	bh	2096	If the column indicated by the parameters is a geometry column,
442	bh	2059	record its name in self.geometry_column and a quoted version in
443			self.quoted_geo_col. In any case return the return value of the
444			inherited method.
445			"""
446			col = PostGISTable._create_col_from_description(self, index,
447			description)
448			col_name, col_type = description[:2]
449			if self.geometry_column_was_given:
450			if (col_name == self.geometry_column
451			and col_type != self.db.geometry_type):
452			raise TypeError("Column %s in %s is not a geometry column"
453			% (self.geometry_column, self.tablename))
454			else:
455			if col is None:
456			if description[1] == self.db.geometry_type:
457			# The column is a geometry column. If the name of
458			# the geometry column was not given to the
459			# constructor, and we encounter two geometry
460			# columns, raise a value error
461			if self.geometry_column is None:
462			self.geometry_column = description[0]
463			else:
464			raise TypeError("Table %s has two geometry columns"
465			" and no column name was given"
466			% (self.tablename,))
467			return col
468
469	bh	1605	def Table(self):
470			"""Return self since a PostGISShapeStore is its own table."""
471			return self
472
473			def OrigShapeStore(self):
474			"""Return None since the PostGISShapeStore is not derived from another
475			"""
476			return None
477
478			def ShapeType(self):
479			"""Return the type of the shapes in the shapestore."""
480			cursor = self.db.cursor()
481			cursor.execute("SELECT type FROM geometry_columns WHERE"
482	bh	2059	" f_table_name=%s AND f_geometry_column=%s",
483			(self.tablename, self.geometry_column))
484	bh	1605	result = cursor.fetchone()[0]
485			cursor.close()
486			return shapetype_map[result]
487
488			def RawShapeFormat(self):
489			"""Return the raw data format of the shape data.
490
491			For the PostGISShapeStore this is RAW_WKT.
492			"""
493			return RAW_WKT
494
495			def NumShapes(self):
496			# The number of shapes is the same as the number of rows,
497			# assuming that the geometry can't be NULL.
498			return self.NumRows()
499
500			def BoundingBox(self):
501			"""Return the bounding box of all shapes in the postgis table"""
502			minx = miny = maxx = maxy = None
503			x=[]
504			y=[]
505			cursor = self.db.cursor()
506			try:
507	bh	1660	# Using the extent function is postgis specific. An OGC
508			# Simple Features compliant solution would be to use a query
509			# like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
510			# calculate the bounding box by hand from that
511			cursor.execute("SELECT extent(%s) FROM %s;"
512	bh	1946	% (self.quoted_geo_col, self.quoted_tablename))
513	bh	1605	result = cursor.fetchone()
514	bh	2057	if result[0]:
515	bh	1660	(minx, miny), (maxx, maxy) \
516			= wellknowntext.parse_wkt_thuban(result[0])[0]
517			return (minx, miny, maxx, maxy)
518	bh	1605	finally:
519			cursor.close()
520
521			def Shape(self, shapeid):
522			cursor = self.db.cursor()
523	bh	2096	cursor.execute("SELECT AsText(%s) FROM %s WHERE %s=%d"
524			% (self.quoted_geo_col, self.quoted_tablename,
525			self.quoted_id_column, shapeid))
526	bh	1605	wkt = cursor.fetchone()[0]
527			cursor.close()
528			return PostGISShape(shapeid, wkt)
529
530	bh	1658	def AllShapes(self):
531			cursor = self.db.cursor()
532	bh	2096	cursor.execute("SELECT %s, AsText(%s) FROM %s ORDER BY %s"
533			% (self.quoted_id_column, self.quoted_geo_col,
534			self.quoted_tablename, self.quoted_id_column))
535	bh	1658	while 1:
536			result = cursor.fetchone()
537			if result is None:
538			return
539			yield PostGISShape(result[0], result[1])
540
541
542	bh	1605	def ShapesInRegion(self, bbox):
543			"""Generate all shapes overlapping the region given by bbox."""
544			# IMPORTANT:This will work for PostGIS < 0.8
545			left, bottom, right, top = bbox
546			geom = (("POLYGON((" + ", ".join(["%f %f"] * 5) + "))")
547			% (left, bottom, left, top, right, top, right, bottom,
548			left, bottom))
549			cursor = self.db.cursor()
550	bh	2096	cursor.execute("SELECT %(gid)s, AsText(%(geom)s) FROM %(table)s"
551			" WHERE %(geom)s && GeometryFromText('%(box)s', %(srid)d)"
552			" ORDER BY %(gid)s"
553			% {"table": self.quoted_tablename,
554			"geom": self.quoted_geo_col,
555			"gid": self.quoted_id_column,
556			"box": geom,
557			"srid": self.srid})
558	bh	1605	while 1:
559			result = cursor.fetchone()
560			if result is None:
561	bh	1658	return
562	bh	1605	yield PostGISShape(result[0], result[1])
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision