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	# Copyright (C) 2003, 2004 by Intevation GmbH
2	# Authors:
3	# Martin Mueller <[email protected]>
4	# Bernhard Herzog <[email protected]>
5	#
6	# This program is free software under the GPL (>=v2)
7	# Read the file COPYING coming with the software for details.
8
9	"""Basic interface to a PostGIS database"""
10
11	from __future__ import generators
12
13	try:
14	import psycopg
15	except ImportError:
16	psycopg = None
17
18	import table
19	import wellknowntext
20
21	from data import SHAPETYPE_POLYGON, SHAPETYPE_ARC, SHAPETYPE_POINT, RAW_WKT
22
23	def has_postgis_support():
24	"""Return whether this Thuban instance supports PostGIS connections
25
26	Having PostGIS support means that the psycopg module can be
27	imported.
28	"""
29	return psycopg is not None
30
31	def psycopg_version():
32	return psycopg.__version__
33
34	if psycopg is not None:
35	type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
36	(psycopg.INTEGER, table.FIELDTYPE_INT),
37	(psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]
38
39
40	def quote_identifier(ident):
41	"""Return a quoted version of the identifier ident.
42
43	The return value is a string that can be put directly into an SQL
44	statement. The quoted identifier is surrounded by double quotes and
45	any double quotes already in the input value are converted to two
46	double quotes. Examples:
47
48	>>> quote_identifier("abc\"def")
49	'"abc""def"'
50	>>> quote_identifier("abc def")
51	'"abc def"'
52	"""
53	return '"' + '""'.join(ident.split('"')) + '"'
54
55
56	class ConnectionError(Exception):
57
58	"""Class for exceptions occurring when establishing a Databse connection"""
59
60
61	class PostGISConnection:
62
63	"""Represent a PostGIS database
64
65	A PostGISConnection instance has the following public attributes:
66
67	dbname -- The name of the database
68	host, port -- Host and port to connect to
69	user -- The user name to connect as.
70
71	All of these attributes are strings and may be empty strings to
72	indicate default values.
73	"""
74
75	def __init__(self, dbname, host="", user="", password="", dbtype="",
76	port=""):
77	self.dbname = dbname
78	self.host = host
79	self.port = port
80	self.user = user
81	self.password = password
82	self.dbtype = dbtype
83	self.connect()
84
85	def connect(self):
86	"""Internal: Establish the database connection"""
87	params = []
88	for name in ("host", "port", "dbname", "user", "password"):
89	val = getattr(self, name)
90	if val:
91	params.append("%s=%s" % (name, val))
92	try:
93	self.connection = psycopg.connect(" ".join(params))
94	except psycopg.OperationalError, val:
95	raise ConnectionError(str(val))
96
97	# determine the OID for the geometry type. This is PostGIS
98	# specific.
99	cursor = self.connection.cursor()
100	cursor.execute("SELECT OID, typname FROM pg_type WHERE"
101	+" typname = 'geometry'")
102	row = cursor.fetchone()
103	self.connection.commit()
104	if row is not None:
105	self.geometry_type = row[0]
106	else:
107	raise ValueError("Can't determine postgres type of geometries")
108
109	def BriefDescription(self):
110	"""Return a brief, one-line description of the connection
111
112	The return value is suitable for a list box of all database
113	connections.
114	"""
115	return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
116	% self.__dict__)
117
118	def MatchesParameters(self, parameters):
119	"""Return whether the connection matches the dictionary of parameters
120
121	Return whether instatiating the connection with the given
122	parameters would establish essentially the same connection as
123	self. The connection is essentially the same if the same
124	database (identified by host, port and databasename) is accessed
125	as the same user.
126	"""
127	return (parameters["host"] == self.host
128	and parameters["port"] == self.port
129	and parameters["dbname"] == self.dbname
130	and parameters["user"] == self.user)
131
132	def Close(self):
133	"""Close the database connection"""
134	self.connection.close()
135
136	def GeometryTables(self):
137	"""Return a list with the names of all tables with a geometry column"""
138	cursor = self.connection.cursor()
139	cursor.execute("SELECT f_table_name FROM geometry_columns;")
140	result = [row[0] for row in cursor.fetchall()]
141	self.connection.commit()
142	return result
143
144	def cursor(self):
145	"""Return a DB API 2.0 cursor for the database"""
146	return self.connection.cursor()
147
148
149
150	class PostGISColumn:
151
152	"""Column description for a PostGISTable
153
154	In addition to the normal column object attributes name, type and
155	index, PostGISColumn objects have a quoted_name attribute which
156	contains a quoted version of name for use in SQL statements. The
157	quoted_name attribute is mainly intended for internal use by the
158	PostGISTable class.
159	"""
160
161	def __init__(self, name, type, index):
162	self.name = name
163	self.quoted_name = quote_identifier(name)
164	self.type = type
165	self.index = index
166
167
168	class PostGISTable:
169
170	"""A Table in a PostGIS database
171
172	A PostgreSQL table may contain columns with types not (yet)
173	supported by Thuban. Instances of this class ignore those columns
174	and pretend they don't exist, i.e. they won't show up in the column
175	descriptions returned by Columns() and other methods.
176	"""
177
178	def __init__(self, db, tablename, id_column = "gid"):
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	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	"""
188	self.db = db
189	self.tablename = tablename
190	# Tablename quoted for use in SQL statements.
191	self.quoted_tablename = quote_identifier(tablename)
192
193	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	# Map column names and indices to column objects.
198	self.column_map = {}
199
200	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	cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
207	description = cursor.description
208
209	for i in range(len(description)):
210	col = self._create_col_from_description(i, description[i])
211	if col is not None:
212	self.columns.append(col)
213
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	% (", ".join([col.quoted_name
221	for col in self.columns]),
222	self.quoted_tablename))
223
224	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	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	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	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	cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
278	return cursor.fetchone()[0]
279
280	def RowIdToOrdinal(self, gid):
281	"""Return the row ordinal given its id"""
282	cursor = self.db.cursor()
283	cursor.execute("SELECT count(*) FROM %s WHERE %s < %d;"
284	% (self.quoted_tablename, self.quoted_id_column, gid))
285	return cursor.fetchone()[0]
286
287	def RowOrdinalToId(self, num):
288	"""Return the rowid for given its ordinal"""
289	cursor = self.db.cursor()
290	cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
291	% (self.quoted_id_column, self.quoted_tablename, num))
292	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	stmt = self.query_stmt + " WHERE %s = %d" % (self.quoted_id_column,
300	row)
301	cursor.execute(stmt)
302	result = {}
303	for col, value in zip(self.columns, cursor.fetchone()):
304	result[col.name] = value
305	return result
306
307	def ReadValue(self, row, col, row_is_ordinal = 0):
308	cursor = self.db.cursor()
309	if row_is_ordinal:
310	stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
311	(self.column_map[col].quoted_name, self.quoted_tablename,
312	row))
313	else:
314	stmt = ("SELECT %s FROM %s WHERE %s = %d" %
315	(self.column_map[col].quoted_name, self.quoted_tablename,
316	self.quoted_id_column, row))
317	cursor.execute(stmt)
318	return cursor.fetchone()[0]
319
320	def ValueRange(self, col):
321	cursor = self.db.cursor()
322	name = self.column_map[col].quoted_name
323	cursor.execute("SELECT min(%s), max(%s) FROM %s" %
324	(name, name, self.quoted_tablename))
325	return tuple(cursor.fetchone())
326
327	def UniqueValues(self, col):
328	cursor = self.db.cursor()
329	name = self.column_map[col].quoted_name
330	cursor.execute("SELECT %s FROM %s GROUP BY %s" %
331	(name, self.quoted_tablename, name))
332	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	right_template = right.quoted_name
343	params = ()
344	else:
345	right_template = "%s"
346	params = (right,)
347
348	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
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	"MULTIPOLYGON": SHAPETYPE_POLYGON,
394	"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	def __init__(self, db, tablename, id_column = "gid",
403	geometry_column = None):
404	"""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	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	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	PostGISTable.__init__(self, db, tablename, id_column)
424
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	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	" WHERE f_table_name = %s AND f_geometry_column=%s",
435	(self.tablename, self.geometry_column))
436	self.srid = cursor.fetchone()[0]
437
438	def _create_col_from_description(self, index, description):
439	"""Extend the inherited method to find geometry columns
440
441	If the column indicated by the parameters is a geometry column,
442	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	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	" f_table_name=%s AND f_geometry_column=%s",
483	(self.tablename, self.geometry_column))
484	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	# 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	% (self.quoted_geo_col, self.quoted_tablename))
513	result = cursor.fetchone()
514	if result[0]:
515	(minx, miny), (maxx, maxy) \
516	= wellknowntext.parse_wkt_thuban(result[0])[0]
517	return (minx, miny, maxx, maxy)
518	finally:
519	cursor.close()
520
521	def Shape(self, shapeid):
522	cursor = self.db.cursor()
523	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	wkt = cursor.fetchone()[0]
527	cursor.close()
528	return PostGISShape(shapeid, wkt)
529
530	def AllShapes(self):
531	cursor = self.db.cursor()
532	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	while 1:
536	result = cursor.fetchone()
537	if result is None:
538	return
539	yield PostGISShape(result[0], result[1])
540
541
542	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	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	while 1:
559	result = cursor.fetchone()
560	if result is None:
561	return
562	yield PostGISShape(result[0], result[1])
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision