/[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 2095 by bh, Wed Feb 11 09:05:40 2004 UTC revision 2096 by bh, Thu Mar 11 13:50:53 2004 UTC
# 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 272  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 288  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 302  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 336  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 389  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, geometry_column = None):      def __init__(self, db, tablename, id_column = "gid",
403                     geometry_column = None):
404          """Initialize the PostGISShapeStore.          """Initialize the PostGISShapeStore.
405    
406          The db parameter should be an instance of PostGISConnection and          The db parameter should be an instance of PostGISConnection and
407          tablename the name of a table in the database represented by db.          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          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          the geometry column to use.  If the name given is not a geometry
415          column, raise a ValueError.          column, raise a ValueError.
# Line 405  class PostGISShapeStore(PostGISTable): Line 420  class PostGISShapeStore(PostGISTable):
420          """          """
421          self.geometry_column = geometry_column          self.geometry_column = geometry_column
422          self.geometry_column_was_given = geometry_column is not None          self.geometry_column_was_given = geometry_column is not None
423          PostGISTable.__init__(self, db, tablename)          PostGISTable.__init__(self, db, tablename, id_column)
424    
425          # For convenience, we have a quoted version of the geometry          # For convenience, we have a quoted version of the geometry
426          # column in self.quoted_geo_col          # column in self.quoted_geo_col
# Line 423  class PostGISShapeStore(PostGISTable): Line 438  class PostGISShapeStore(PostGISTable):
438      def _create_col_from_description(self, index, description):      def _create_col_from_description(self, index, description):
439          """Extend the inherited method to find geometry columns          """Extend the inherited method to find geometry columns
440    
441          If the column indicated by the paramters is a geometry column,          If the column indicated by the parameters is a geometry column,
442          record its name in self.geometry_column and a quoted version in          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          self.quoted_geo_col.  In any case return the return value of the
444          inherited method.          inherited method.
# Line 505  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 530  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', %d) 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, self.srid))                         % {"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.2095  
changed lines
  Added in v.2096
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26