/[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 1955 by bh, Tue Nov 18 15:37:31 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 41  def quote_identifier(ident): Line 41  def quote_identifier(ident):
41      """Return a quoted version of the identifier ident.      """Return a quoted version of the identifier ident.
42    
43      The return value is a string that can be put directly into an SQL      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      statement.  The quoted identifier is surrounded by double quotes and
45      any double quotes alread in the input value are converted to two      any double quotes already in the input value are converted to two
46      double quotes. Examples:      double quotes.  Examples:
47    
48      >>> quote_identifier("abc\"def")      >>> quote_identifier("abc\"def")
49      '"abc""def"'      '"abc""def"'
# Line 175  class PostGISTable: Line 175  class PostGISTable:
175      descriptions returned by Columns() and other methods.      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.          # Tablename quoted for use in SQL statements.
191          self.quoted_tablename = quote_identifier(tablename)          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.          # Map column names and indices to column objects.
198          self.column_map = {}          self.column_map = {}
199    
# Line 199  class PostGISTable: Line 207  class PostGISTable:
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,                  self.columns.append(col)
                                         len(self.columns))  
                     break  
             else:  
                 if description[i][1] == self.db.geometry_type:  
                     self.geometry_column = description[i][0]  
                     self.quoted_geo_col =quote_identifier(self.geometry_column)  
                 # No matching table type. Ignore the column.  
                 # FIXME: We should at least print a warning about  
                 # ignored columns  
                 continue  
             self.columns.append(col)  
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 224  class PostGISTable: Line 221  class PostGISTable:
221                                           for col in self.columns]),                                           for col in self.columns]),
222                                self.quoted_tablename))                                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):      def DBConnection(self):
244          """Return the dbconnection used by the table"""          """Return the dbconnection used by the table"""
245          return self.db          return self.db
# Line 264  class PostGISTable: Line 280  class PostGISTable:
280      def RowIdToOrdinal(self, gid):      def RowIdToOrdinal(self, gid):
281          """Return the row ordinal given its id"""          """Return the row ordinal given its id"""
282          cursor = self.db.cursor()          cursor = self.db.cursor()
283          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"          cursor.execute("SELECT count(*) FROM %s WHERE %s < %d;"
284                         % (self.quoted_tablename, gid))                         % (self.quoted_tablename, self.quoted_id_column, gid))
285          return cursor.fetchone()[0]          return cursor.fetchone()[0]
286    
287      def RowOrdinalToId(self, num):      def RowOrdinalToId(self, num):
288          """Return the rowid for given its ordinal"""          """Return the rowid for given its ordinal"""
289          cursor = self.db.cursor()          cursor = self.db.cursor()
290          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
291                         % (self.quoted_tablename, num))                         % (self.quoted_id_column, self.quoted_tablename, num))
292          return cursor.fetchone()[0]          return cursor.fetchone()[0]
293    
294      def ReadRowAsDict(self, row, row_is_ordinal = 0):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
# Line 280  class PostGISTable: Line 296  class PostGISTable:
296          if row_is_ordinal:          if row_is_ordinal:
297              stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row              stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
298          else:          else:
299              stmt = self.query_stmt + " WHERE gid = %d" % row              stmt = self.query_stmt + " WHERE %s = %d" % (self.quoted_id_column,
300                                                             row)
301          cursor.execute(stmt)          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()):
# Line 294  class PostGISTable: Line 311  class PostGISTable:
311                      (self.column_map[col].quoted_name, self.quoted_tablename,                      (self.column_map[col].quoted_name, self.quoted_tablename,
312                       row))                       row))
313          else:          else:
314              stmt = ("SELECT %s FROM %s WHERE gid = %d" %              stmt = ("SELECT %s FROM %s WHERE %s = %d" %
315                      (self.column_map[col].quoted_name, self.quoted_tablename,                      (self.column_map[col].quoted_name, self.quoted_tablename,
316                       row))                       self.quoted_id_column, row))
317          cursor.execute(stmt)          cursor.execute(stmt)
318          return cursor.fetchone()[0]          return cursor.fetchone()[0]
319    
# Line 328  class PostGISTable: Line 345  class PostGISTable:
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.quoted_tablename, left.quoted_name, comparison,                  % (self.quoted_id_column, self.quoted_tablename,
350                     right_template)                     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 381  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 394  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 425  class PostGISShapeStore(PostGISTable): Line 511  class PostGISShapeStore(PostGISTable):
511              cursor.execute("SELECT extent(%s) FROM %s;"              cursor.execute("SELECT extent(%s) FROM %s;"
512                             % (self.quoted_geo_col, self.quoted_tablename))                             % (self.quoted_geo_col, self.quoted_tablename))
513              result = cursor.fetchone()              result = cursor.fetchone()
514              if result:              if result[0]:
515                  (minx, miny), (maxx, maxy) \                  (minx, miny), (maxx, maxy) \
516                        = wellknowntext.parse_wkt_thuban(result[0])[0]                        = wellknowntext.parse_wkt_thuban(result[0])[0]
517                  return (minx, miny, maxx, maxy)                  return (minx, miny, maxx, maxy)
# Line 434  class PostGISShapeStore(PostGISTable): Line 520  class PostGISShapeStore(PostGISTable):
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.quoted_geo_col, self.quoted_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):      def AllShapes(self):
531          cursor = self.db.cursor()          cursor = self.db.cursor()
532          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"          cursor.execute("SELECT %s, AsText(%s) FROM %s ORDER BY %s"
533                         % (self.quoted_geo_col, self.quoted_tablename))                         % (self.quoted_id_column, self.quoted_geo_col,
534                              self.quoted_tablename, self.quoted_id_column))
535          while 1:          while 1:
536              result = cursor.fetchone()              result = cursor.fetchone()
537              if result is None:              if result is None:
# Line 459  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.quoted_geo_col, self.quoted_tablename,                         " ORDER BY %(gid)s"
553                            self.quoted_geo_col, 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:
Legend:
Removed from v.1955  
changed lines
  Added in v.2096
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26