/[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 2059 by bh, Wed Feb 11 09:05:40 2004 UTC revision 2472 by bh, Thu Dec 16 15:18:57 2004 UTC
# Line 32  def psycopg_version(): Line 32  def psycopg_version():
32      return psycopg.__version__      return psycopg.__version__
33    
34  if psycopg is not None:  if psycopg is not None:
35        # type_map maps psycopg type objects.  It's a list of pairs since
36        # the psycopg type objects are unhashable.
37      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
38                  (psycopg.INTEGER, table.FIELDTYPE_INT),                  (psycopg.INTEGER, table.FIELDTYPE_INT),
39                    (psycopg.ROWID, table.FIELDTYPE_INT),
40                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]
41    
42        # _raw_type_map maps the postgresql type constants to Thuban type
43        # constants.  This is very low level and postgresql specific and
44        # should be used only when necessary.
45        _raw_type_map = {}
46        def _fill_raw_type_map():
47            for psycopg_type, thuban_type in type_map:
48                for value in psycopg_type.values:
49                    _raw_type_map[value] = thuban_type
50        _fill_raw_type_map()
51    
52    
53  def quote_identifier(ident):  def quote_identifier(ident):
54      """Return a quoted version of the identifier ident.      """Return a quoted version of the identifier ident.
# Line 135  class PostGISConnection: Line 148  class PostGISConnection:
148    
149      def GeometryTables(self):      def GeometryTables(self):
150          """Return a list with the names of all tables with a geometry column"""          """Return a list with the names of all tables with a geometry column"""
151    
152            # The query is basically taken from the psql v. 7.2.1.  When
153            # started with -E it prints the queries used for internal
154            # commands such as \d, which does mostly what we need here.
155          cursor = self.connection.cursor()          cursor = self.connection.cursor()
156          cursor.execute("SELECT f_table_name FROM geometry_columns;")          cursor.execute("SELECT c.relname FROM pg_class c"
157                           " WHERE c.relkind IN ('r', 'v')"
158                                 # Omit the system tables
159                                 " AND c.relname !~ '^pg_'"
160                                 # Omit the special PostGIS tables
161                                 " AND c.relname NOT IN ('geometry_columns',"
162                                                       " 'spatial_ref_sys')"
163                                " AND %d in (SELECT a.atttypid FROM pg_attribute a"
164                                           " WHERE a.attrelid = c.oid)"
165                           " ORDER BY c.relname;", (self.geometry_type,))
166          result = [row[0] for row in cursor.fetchall()]          result = [row[0] for row in cursor.fetchall()]
167          self.connection.commit()          self.connection.commit()
168          return result          return result
169    
170        def table_columns(self, tablename):
171            """Experimental: return information about the columns of a table
172    
173            Return value is a list of (name, type) pairs where name is the
174            name of the column and type either one of the field type columns
175            or the string 'geometry' indicating a geometry column.
176    
177            The intended use of this method is for table selection dialogs
178            which need to determine which columns are usable as id or
179            geometry columns respectively.  Suitable id columns will have
180            type FIELDTYPE_INT and geometry columns will have 'geometry'.
181            """
182            result = []
183            cursor = self.connection.cursor()
184    
185            # This query is taken basically from the \d command of psql
186            # 7.2.1
187            cursor.execute("SELECT a.attname, a.atttypid, a.attnum"
188                           " FROM pg_class c, pg_attribute a"
189                                " WHERE c.relname = %s AND a.attrelid = c.oid"
190                           " ORDER BY a.attnum;", (tablename,))
191    
192            for row in cursor.fetchall():
193                col_name, col_type, col_attnum = row
194                col = None
195                if col_attnum < 1:
196                    # It's a system column.  Only the OID is interesting
197                    # here
198                    if col_name == "oid":
199                        col = (col_name, _raw_type_map[col_type])
200                else:
201                    # If it's an integer
202                    thuban_type = _raw_type_map.get(col_type)
203                    if thuban_type is not None:
204                        col = (col_name, thuban_type)
205                    elif row[1] == self.geometry_type:
206                        col = (col_name, "geometry")
207                if col is not None:
208                    result.append(col)
209    
210            return result
211    
212      def cursor(self):      def cursor(self):
213          """Return a DB API 2.0 cursor for the database"""          """Return a DB API 2.0 cursor for the database"""
214          return self.connection.cursor()          return self.connection.cursor()
# Line 175  class PostGISTable: Line 243  class PostGISTable:
243      descriptions returned by Columns() and other methods.      descriptions returned by Columns() and other methods.
244      """      """
245    
246      def __init__(self, db, tablename):      def __init__(self, db, tablename, id_column = None):
247          """Initialize the PostGISTable.          """Initialize the PostGISTable.
248    
249          The db parameter should be an instance of PostGISConnection and          The db parameter should be an instance of PostGISConnection and
250          tablename the name of a table in the database represented by db.          tablename the name of a table in the database represented by db.
251    
252            The id_column parameter should be the name of a column in the
253            table that can be used to identify rows.  The column must have
254            the type integer and be unique and not null.
255    
256            For backwards compatibility reasons, the id_column parameter is
257            optional.  If not given the table must have a column called
258            'gid' which is used as the id_column.  New code should always
259            provide this parameter.
260          """          """
261          self.db = db          self.db = db
262          self.tablename = tablename          self.tablename = tablename
263          # Tablename quoted for use in SQL statements.          # Tablename quoted for use in SQL statements.
264          self.quoted_tablename = quote_identifier(tablename)          self.quoted_tablename = quote_identifier(tablename)
265    
266            if not id_column:
267                id_column = "gid"
268            self.id_column = id_column
269            # id column name quoted for use in SQL statements.
270            self.quoted_id_column = quote_identifier(id_column)
271    
272          # Map column names and indices to column objects.          # Map column names and indices to column objects.
273          self.column_map = {}          self.column_map = {}
274    
# Line 236  class PostGISTable: Line 319  class PostGISTable:
319          """Return the dbconnection used by the table"""          """Return the dbconnection used by the table"""
320          return self.db          return self.db
321    
322        def IDColumn(self):
323            """Return the column description object for the id column.
324    
325            If the oid column was used as the id column, the return value is
326            not one of the regular column objects that would be returned by
327            e.g. the Column() method, but it still has meaningful name
328            attribute.
329            """
330            if self.id_column == "oid":
331                return PostGISColumn(self.id_column, table.FIELDTYPE_INT, None)
332            return self.column_map[self.id_column]
333    
334      def TableName(self):      def TableName(self):
335          """Return the name of the table in the database"""          """Return the name of the table in the database"""
336          return self.tablename          return self.tablename
# Line 272  class PostGISTable: Line 367  class PostGISTable:
367      def RowIdToOrdinal(self, gid):      def RowIdToOrdinal(self, gid):
368          """Return the row ordinal given its id"""          """Return the row ordinal given its id"""
369          cursor = self.db.cursor()          cursor = self.db.cursor()
370          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"          cursor.execute("SELECT count(*) FROM %s WHERE %s < %d;"
371                         % (self.quoted_tablename, gid))                         % (self.quoted_tablename, self.quoted_id_column, gid))
372          return cursor.fetchone()[0]          return cursor.fetchone()[0]
373    
374      def RowOrdinalToId(self, num):      def RowOrdinalToId(self, num):
375          """Return the rowid for given its ordinal"""          """Return the rowid for given its ordinal"""
376          cursor = self.db.cursor()          cursor = self.db.cursor()
377          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
378                         % (self.quoted_tablename, num))                         % (self.quoted_id_column, self.quoted_tablename, num))
379          return cursor.fetchone()[0]          return cursor.fetchone()[0]
380    
381      def ReadRowAsDict(self, row, row_is_ordinal = 0):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
# Line 288  class PostGISTable: Line 383  class PostGISTable:
383          if row_is_ordinal:          if row_is_ordinal:
384              stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row              stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
385          else:          else:
386              stmt = self.query_stmt + " WHERE gid = %d" % row              stmt = self.query_stmt + " WHERE %s = %d" % (self.quoted_id_column,
387                                                             row)
388          cursor.execute(stmt)          cursor.execute(stmt)
389          result = {}          result = {}
390          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
# Line 302  class PostGISTable: Line 398  class PostGISTable:
398                      (self.column_map[col].quoted_name, self.quoted_tablename,                      (self.column_map[col].quoted_name, self.quoted_tablename,
399                       row))                       row))
400          else:          else:
401              stmt = ("SELECT %s FROM %s WHERE gid = %d" %              stmt = ("SELECT %s FROM %s WHERE %s = %d" %
402                      (self.column_map[col].quoted_name, self.quoted_tablename,                      (self.column_map[col].quoted_name, self.quoted_tablename,
403                       row))                       self.quoted_id_column, row))
404          cursor.execute(stmt)          cursor.execute(stmt)
405          return cursor.fetchone()[0]          return cursor.fetchone()[0]
406    
# Line 336  class PostGISTable: Line 432  class PostGISTable:
432              right_template = "%s"              right_template = "%s"
433              params = (right,)              params = (right,)
434    
435          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT %s FROM %s WHERE %s %s %s ORDER BY %s;" \
436                  % (self.quoted_tablename, left.quoted_name, comparison,                  % (self.quoted_id_column, self.quoted_tablename,
437                     right_template)                     left.quoted_name, comparison, right_template,
438                       self.quoted_id_column)
439    
440          cursor = self.db.cursor()          cursor = self.db.cursor()
441          cursor.execute(query, params)          cursor.execute(query, params)
# Line 381  class PostGISShape: Line 478  class PostGISShape:
478    
479  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
480                   "MULTIPOLYGON": SHAPETYPE_POLYGON,                   "MULTIPOLYGON": SHAPETYPE_POLYGON,
481                     "LINESTRING": SHAPETYPE_ARC,
482                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
483                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
484    
# Line 389  class PostGISShapeStore(PostGISTable): Line 487  class PostGISShapeStore(PostGISTable):
487    
488      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
489    
490      def __init__(self, db, tablename, geometry_column = None):      def __init__(self, db, tablename, id_column = "gid",
491                     geometry_column = None):
492          """Initialize the PostGISShapeStore.          """Initialize the PostGISShapeStore.
493    
494          The db parameter should be an instance of PostGISConnection and          The db parameter should be an instance of PostGISConnection and
495          tablename the name of a table in the database represented by db.          tablename the name of a table in the database represented by db.
496    
497            The id_column parameter should be the name of a column in the
498            table that can be used to identify rows.  The column must have
499            the type integer and be unique and not null.
500    
501          The geometry_column paramter, if given, should be the name of          The geometry_column paramter, if given, should be the name of
502          the geometry column to use.  If the name given is not a geometry          the geometry column to use.  If the name given is not a geometry
503          column, raise a ValueError.          column, raise a ValueError.
# Line 405  class PostGISShapeStore(PostGISTable): Line 508  class PostGISShapeStore(PostGISTable):
508          """          """
509          self.geometry_column = geometry_column          self.geometry_column = geometry_column
510          self.geometry_column_was_given = geometry_column is not None          self.geometry_column_was_given = geometry_column is not None
511          PostGISTable.__init__(self, db, tablename)          PostGISTable.__init__(self, db, tablename, id_column)
512    
513          # For convenience, we have a quoted version of the geometry          # For convenience, we have a quoted version of the geometry
514          # column in self.quoted_geo_col          # column in self.quoted_geo_col
515          self.quoted_geo_col = quote_identifier(self.geometry_column)          self.quoted_geo_col = quote_identifier(self.geometry_column)
516    
517      def _fetch_table_information(self):      def _fetch_table_information(self):
518          """Extend inherited method to retrieve the SRID"""          """Extend inherited method to retrieve the SRID and shape type"""
519          PostGISTable._fetch_table_information(self)          PostGISTable._fetch_table_information(self)
520    
521            # First, try to get it from the geometry_columns table.
522          cursor = self.db.cursor()          cursor = self.db.cursor()
523          cursor.execute("SELECT srid FROM geometry_columns"          cursor.execute("SELECT srid, type FROM geometry_columns"
524                         " WHERE f_table_name = %s AND f_geometry_column=%s",                         " WHERE f_table_name = %s AND f_geometry_column=%s",
525                         (self.tablename, self.geometry_column))                         (self.tablename, self.geometry_column))
526          self.srid = cursor.fetchone()[0]          row = cursor.fetchone()
527            if row is not None:
528                self.srid = row[0]
529                self.shape_type = shapetype_map.get(row[1])
530                return
531    
532            # The table is probably really a view and thus not in
533            # geometry_columns.  Use a different approach
534            cursor = self.db.cursor()
535            cursor.execute("SELECT DISTINCT SRID(%s) FROM %s;" %
536                           (quote_identifier(self.geometry_column),
537                            self.tablename))
538            row = cursor.fetchone()
539            if row is not None:
540                self.srid = row[0]
541                # Try to see whether there's another one
542                row = cursor.fetchone()
543                if row is not None:
544                    # There are at least two different srids.  We don't
545                    # support that
546                    self.srid = None
547    
548            cursor = self.db.cursor()
549            cursor.execute("SELECT DISTINCT GeometryType(%s) FROM %s;"
550                           % (quote_identifier(self.geometry_column),
551                              self.tablename))
552            row = cursor.fetchone()
553            if row is not None:
554                self.shape_type = shapetype_map.get(row[0])
555                # Try to see whether there's another one
556                row = cursor.fetchone()
557                if row is not None:
558                    # There are at least two different srids.  We don't
559                    # support that
560                    self.shape_type = None
561    
562      def _create_col_from_description(self, index, description):      def _create_col_from_description(self, index, description):
563          """Extend the inherited method to find geometry columns          """Extend the inherited method to find geometry columns
564    
565          If the column indicated by the paramters is a geometry column,          If the column indicated by the parameters is a geometry column,
566          record its name in self.geometry_column and a quoted version in          record its name in self.geometry_column and a quoted version in
567          self.quoted_geo_col.  In any case return the return value of the          self.quoted_geo_col.  In any case return the return value of the
568          inherited method.          inherited method.
# Line 460  class PostGISShapeStore(PostGISTable): Line 599  class PostGISShapeStore(PostGISTable):
599          """          """
600          return None          return None
601    
602        def GeometryColumn(self):
603            """Return the column description object for the geometry column
604    
605            There's currently no FIELDTYPE constant for this column, so the
606            return value is not a regular column object that could also be
607            returned from e.g. the Column() method.  Only the name attribute
608            of the return value is meaningful at the moment.
609            """
610            return PostGISColumn(self.geometry_column, None, None)
611    
612      def ShapeType(self):      def ShapeType(self):
613          """Return the type of the shapes in the shapestore."""          """Return the type of the shapes in the shapestore."""
614          cursor = self.db.cursor()          return self.shape_type
         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]  
615    
616      def RawShapeFormat(self):      def RawShapeFormat(self):
617          """Return the raw data format of the shape data.          """Return the raw data format of the shape data.
# Line 505  class PostGISShapeStore(PostGISTable): Line 648  class PostGISShapeStore(PostGISTable):
648    
649      def Shape(self, shapeid):      def Shape(self, shapeid):
650          cursor = self.db.cursor()          cursor = self.db.cursor()
651          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE %s=%d"
652                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename,
653                              self.quoted_id_column, shapeid))
654          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
655          cursor.close()          cursor.close()
656          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
657    
658      def AllShapes(self):      def AllShapes(self):
659          cursor = self.db.cursor()          cursor = self.db.cursor()
660          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"          cursor.execute("SELECT %s, AsText(%s) FROM %s ORDER BY %s"
661                         % (self.quoted_geo_col, self.quoted_tablename))                         % (self.quoted_id_column, self.quoted_geo_col,
662                              self.quoted_tablename, self.quoted_id_column))
663          while 1:          while 1:
664              result = cursor.fetchone()              result = cursor.fetchone()
665              if result is None:              if result is None:
# Line 530  class PostGISShapeStore(PostGISTable): Line 675  class PostGISShapeStore(PostGISTable):
675                  % (left, bottom, left, top, right, top, right, bottom,                  % (left, bottom, left, top, right, top, right, bottom,
676                     left, bottom))                     left, bottom))
677          cursor = self.db.cursor()          cursor = self.db.cursor()
678          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT %(gid)s, AsText(%(geom)s) FROM %(table)s"
679                       " WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"                       " WHERE %(geom)s && GeometryFromText('%(box)s', %(srid)d)"
680                         % (self.quoted_geo_col, self.quoted_tablename,                         " ORDER BY %(gid)s"
681                            self.quoted_geo_col, geom, self.srid))                         % {"table": self.quoted_tablename,
682                              "geom": self.quoted_geo_col,
683                              "gid": self.quoted_id_column,
684                              "box": geom,
685                              "srid": self.srid})
686          while 1:          while 1:
687              result = cursor.fetchone()              result = cursor.fetchone()
688              if result is None:              if result is None:
Legend:
Removed from v.2059  
changed lines
  Added in v.2472
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26