/[thuban]/branches/WIP-pyshapelib-bramz/Thuban/Model/postgisdb.py
ViewVC logotype

Diff of /branches/WIP-pyshapelib-bramz/Thuban/Model/postgisdb.py

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1605 by bh, Tue Aug 19 11:00:40 2003 UTC revision 2096 by bh, Thu Mar 11 13:50:53 2004 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003 by Intevation GmbH  # Copyright (C) 2003, 2004 by Intevation GmbH
2  # Authors:  # Authors:
3  # Martin Mueller <[email protected]>  # Martin Mueller <[email protected]>
4  # Bernhard Herzog <[email protected]>  # Bernhard Herzog <[email protected]>
# Line 20  import wellknowntext Line 20  import wellknowntext
20    
21  from data import SHAPETYPE_POLYGON, SHAPETYPE_ARC, SHAPETYPE_POINT, RAW_WKT  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:  if psycopg is not None:
35      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
36                  (psycopg.INTEGER, table.FIELDTYPE_INT),                  (psycopg.INTEGER, table.FIELDTYPE_INT),
37                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]                  (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:  class PostGISConnection:
62    
63      """Represent a PostGIS database      """Represent a PostGIS database
# Line 48  class PostGISConnection: Line 80  class PostGISConnection:
80          self.user = user          self.user = user
81          self.password = password          self.password = password
82          self.dbtype = dbtype          self.dbtype = dbtype
83            self.connect()
84    
85        def connect(self):
86            """Internal: Establish the database connection"""
87          params = []          params = []
88          for name in ("host", "port", "dbname", "user", "password"):          for name in ("host", "port", "dbname", "user", "password"):
89              val = getattr(self, name)              val = getattr(self, name)
90              if val:              if val:
91                  params.append("%s=%s" % (name, val))                  params.append("%s=%s" % (name, val))
92          self.connection = psycopg.connect(" ".join(params))          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          # determine the OID for the geometry type. This is PostGIS
98          # specific.          # specific.
# Line 67  class PostGISConnection: Line 106  class PostGISConnection:
106          else:          else:
107              raise ValueError("Can't determine postgres type of geometries")              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):      def Close(self):
133          """Close the database connection"""          """Close the database connection"""
134          self.connection.close()          self.connection.close()
# Line 87  class PostGISConnection: Line 149  class PostGISConnection:
149    
150  class PostGISColumn:  class PostGISColumn:
151    
152      """Column description for a PostGISTable"""      """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):      def __init__(self, name, type, index):
162          self.name = name          self.name = name
163            self.quoted_name = quote_identifier(name)
164          self.type = type          self.type = type
165          self.index = index          self.index = index
166    
167    
168  class PostGISTable:  class PostGISTable:
169    
170      """A Table in a PostGIS database"""      """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):      def __init__(self, db, tablename, id_column = "gid"):
179          """Initialize the PostGISTable.          """Initialize the PostGISTable.
180    
181          The db parameter should be an instance of PostGISConnection and          The db parameter should be an instance of PostGISConnection and
182          tablename the name of a table in the database represented by db.          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          self.db = db
189          self.tablename = tablename          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 = {}          self.column_map = {}
199    
200          self._fetch_table_information()          self._fetch_table_information()
201    
202      def _fetch_table_information(self):      def _fetch_table_information(self):
203          """Internal: Update information about the table"""          """Internal: Update information about the table"""
204          self.columns = []          self.columns = []
205          cursor = self.db.cursor()          cursor = self.db.cursor()
206          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
207          description = cursor.description          description = cursor.description
208    
209          for i in range(len(description)):          for i in range(len(description)):
210              for pgtyp, tabletyp in type_map:              col = self._create_col_from_description(i, description[i])
211                  if pgtyp == description[i][1]:              if col is not None:
212                      col = PostGISColumn(description[i][0], tabletyp, i)                  self.columns.append(col)
                     break  
             else:  
                 if description[i][1] == self.db.geometry_type:  
                     self.geometry_column = description[i][0]  
                 # No matching table type. Ignore the column.  
                 # FIXME: We should at least print a warning about  
                 # ignored columns  
                 continue  
             self.columns.append(col)  
213    
214          for col in self.columns:          for col in self.columns:
215              self.column_map[col.name] = col              self.column_map[col.name] = col
# Line 137  class PostGISTable: Line 217  class PostGISTable:
217    
218          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
219          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
220                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
221                                self.tablename))                                           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):      def Dependencies(self):
259          """Return an empty tuple because a PostGISTable depends on nothing else          """Return an empty tuple because a PostGISTable depends on nothing else
# Line 159  class PostGISTable: Line 274  class PostGISTable:
274    
275      def NumRows(self):      def NumRows(self):
276          cursor = self.db.cursor()          cursor = self.db.cursor()
277          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          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]          return cursor.fetchone()[0]
286    
287      def ReadRowAsDict(self, row):      def RowOrdinalToId(self, num):
288            """Return the rowid for given its ordinal"""
289          cursor = self.db.cursor()          cursor = self.db.cursor()
290          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          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 = {}          result = {}
303          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
304              result[col.name] = value              result[col.name] = value
305          return result          return result
306    
307      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
308          cursor = self.db.cursor()          cursor = self.db.cursor()
309          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
310                         (self.column_map[col].name, self.tablename, row))              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]          return cursor.fetchone()[0]
319    
320      def ValueRange(self, col):      def ValueRange(self, col):
321          cursor = self.db.cursor()          cursor = self.db.cursor()
322          name = self.column_map[col].name          name = self.column_map[col].quoted_name
323          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
324                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
325          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
326    
327      def UniqueValues(self, col):      def UniqueValues(self, col):
328          cursor = self.db.cursor()          cursor = self.db.cursor()
329          name = self.column_map[col].name          name = self.column_map[col].quoted_name
330          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
331                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
332          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
333    
334      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 198  class PostGISTable: Line 339  class PostGISTable:
339              comparison = "="              comparison = "="
340    
341          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
342              right_template = right.name              right_template = right.quoted_name
343              params = ()              params = ()
344          else:          else:
345              right_template = "%s"              right_template = "%s"
346              params = (right,)              params = (right,)
347    
348          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT %s FROM %s WHERE %s %s %s ORDER BY %s;" \
349                  % (self.tablename, left.name, comparison, right_template)                  % (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()          cursor = self.db.cursor()
354          cursor.execute(query, params)          cursor.execute(query, params)
# Line 247  class PostGISShape: Line 390  class PostGISShape:
390    
391    
392  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
393                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
394                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
395                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
396    
# Line 255  class PostGISShapeStore(PostGISTable): Line 399  class PostGISShapeStore(PostGISTable):
399    
400      """Shapestore interface to a table in a PostGIS database"""      """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):      def Table(self):
470          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
471          return self          return self
# Line 268  class PostGISShapeStore(PostGISTable): Line 479  class PostGISShapeStore(PostGISTable):
479          """Return the type of the shapes in the shapestore."""          """Return the type of the shapes in the shapestore."""
480          cursor = self.db.cursor()          cursor = self.db.cursor()
481          cursor.execute("SELECT type FROM geometry_columns WHERE"          cursor.execute("SELECT type FROM geometry_columns WHERE"
482                         " f_table_name=%s", (self.tablename,))                         " f_table_name=%s AND f_geometry_column=%s",
483                           (self.tablename, self.geometry_column))
484          result = cursor.fetchone()[0]          result = cursor.fetchone()[0]
485          cursor.close()          cursor.close()
486          return shapetype_map[result]          return shapetype_map[result]
# Line 292  class PostGISShapeStore(PostGISTable): Line 504  class PostGISShapeStore(PostGISTable):
504          y=[]          y=[]
505          cursor = self.db.cursor()          cursor = self.db.cursor()
506          try:          try:
507              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
508                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
509              cursor.execute(stmt)              # 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()              result = cursor.fetchone()
514              while result:              if result[0]:
515                  result = result[0]                  (minx, miny), (maxx, maxy) \
516                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
517                  # to get the points out of the polygon which representes                  return (minx, miny, maxx, maxy)
                 # the bounding box The first and the last point of a  
                 # polygon are identical  
                 result = result.split("(")[2]  
                 result = result.split(")")[0]  
                 points = result.split(",")  
                 del points[4] # Remove the last point  
                 for point in points:  
                     px, py = point.split()  
                     x.append(float(px))  
                     y.append(float(py))  
                 result = cursor.fetchone()  
518          finally:          finally:
519              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
520    
521      def Shape(self, shapeid):      def Shape(self, shapeid):
522          cursor = self.db.cursor()          cursor = self.db.cursor()
523          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE %s=%d"
524                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename,
525                              self.quoted_id_column, shapeid))
526          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
527          cursor.close()          cursor.close()
528          return PostGISShape(shapeid, wkt)          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):      def ShapesInRegion(self, bbox):
543          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
544          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 334  class PostGISShapeStore(PostGISTable): Line 547  class PostGISShapeStore(PostGISTable):
547                  % (left, bottom, left, top, right, top, right, bottom,                  % (left, bottom, left, top, right, top, right, bottom,
548                     left, bottom))                     left, bottom))
549          cursor = self.db.cursor()          cursor = self.db.cursor()
550          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT %(gid)s, AsText(%(geom)s) FROM %(table)s"
551                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %(geom)s && GeometryFromText('%(box)s', %(srid)d)"
552                         % (self.geometry_column, self.tablename,                         " ORDER BY %(gid)s"
553                            self.geometry_column, geom))                         % {"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:          while 1:
559              result = cursor.fetchone()              result = cursor.fetchone()
560              if result is None:              if result is None:
561                  raise StopIteration                  return
562              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1605  
changed lines
  Added in v.2096
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26