/[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 1620 by bh, Wed Aug 20 13:14:22 2003 UTC revision 1693 by bh, Mon Sep 1 11:23:26 2003 UTC
# 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    class ConnectionError(Exception):
41    
42        """Class for exceptions occurring when establishing a Databse connection"""
43    
44    
45  class PostGISConnection:  class PostGISConnection:
46    
47      """Represent a PostGIS database      """Represent a PostGIS database
# Line 57  class PostGISConnection: Line 73  class PostGISConnection:
73              val = getattr(self, name)              val = getattr(self, name)
74              if val:              if val:
75                  params.append("%s=%s" % (name, val))                  params.append("%s=%s" % (name, val))
76          self.connection = psycopg.connect(" ".join(params))          try:
77                self.connection = psycopg.connect(" ".join(params))
78            except psycopg.OperationalError, val:
79                raise ConnectionError(str(val))
80    
81          # determine the OID for the geometry type. This is PostGIS          # determine the OID for the geometry type. This is PostGIS
82          # specific.          # specific.
# Line 110  class PostGISColumn: Line 129  class PostGISColumn:
129    
130  class PostGISTable:  class PostGISTable:
131    
132      """A Table in a PostGIS database"""      """A Table in a PostGIS database
133    
134        A PostgreSQL table may contain columns with types not (yet)
135        supported by Thuban. Instances of this class ignore those columns
136        and pretend they don't exist, i.e. they won't show up in the column
137        descriptions returned by Columns() and other methods.
138        """
139    
140      def __init__(self, db, tablename):      def __init__(self, db, tablename):
141          """Initialize the PostGISTable.          """Initialize the PostGISTable.
# Line 133  class PostGISTable: Line 158  class PostGISTable:
158          for i in range(len(description)):          for i in range(len(description)):
159              for pgtyp, tabletyp in type_map:              for pgtyp, tabletyp in type_map:
160                  if pgtyp == description[i][1]:                  if pgtyp == description[i][1]:
161                      col = PostGISColumn(description[i][0], tabletyp, i)                      col = PostGISColumn(description[i][0], tabletyp,
162                                            len(self.columns))
163                      break                      break
164              else:              else:
165                  if description[i][1] == self.db.geometry_type:                  if description[i][1] == self.db.geometry_type:
# Line 153  class PostGISTable: Line 179  class PostGISTable:
179                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.name for col in self.columns]),
180                                self.tablename))                                self.tablename))
181    
182        def DBConnection(self):
183            """Return the dbconnection used by the table"""
184            return self.db
185    
186        def TableName(self):
187            """Return the name of the table in the database"""
188            return self.tablename
189    
190        def Title(self):
191            """Return the title of the table.
192    
193            The title is currently fixed and equal to the tablename
194            """
195            return self.tablename
196    
197      def Dependencies(self):      def Dependencies(self):
198          """Return an empty tuple because a PostGISTable depends on nothing else          """Return an empty tuple because a PostGISTable depends on nothing else
199          """          """
# Line 175  class PostGISTable: Line 216  class PostGISTable:
216          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.tablename)
217          return cursor.fetchone()[0]          return cursor.fetchone()[0]
218    
219      def ReadRowAsDict(self, row):      def RowIdToOrdinal(self, gid):
220            """Return the row ordinal given its id"""
221          cursor = self.db.cursor()          cursor = self.db.cursor()
222          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
223                           % (self.tablename, gid))
224            return cursor.fetchone()[0]
225    
226        def RowOrdinalToId(self, num):
227            """Return the rowid for given its ordinal"""
228            cursor = self.db.cursor()
229            cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
230                           % (self.tablename, num))
231            return cursor.fetchone()[0]
232    
233        def ReadRowAsDict(self, row, row_is_ordinal = 0):
234            cursor = self.db.cursor()
235            if row_is_ordinal:
236                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
237            else:
238                stmt = self.query_stmt + " WHERE gid = %d" % row
239            cursor.execute(stmt)
240          result = {}          result = {}
241          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
242              result[col.name] = value              result[col.name] = value
243          return result          return result
244    
245      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
246          cursor = self.db.cursor()          cursor = self.db.cursor()
247          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
248                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
249                        (self.column_map[col].name, self.tablename, row))
250            else:
251                stmt = ("SELECT %s FROM %s WHERE gid = %d" %
252                        (self.column_map[col].name, self.tablename, row))
253            cursor.execute(stmt)
254          return cursor.fetchone()[0]          return cursor.fetchone()[0]
255    
256      def ValueRange(self, col):      def ValueRange(self, col):
# Line 260  class PostGISShape: Line 324  class PostGISShape:
324    
325    
326  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
327                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
328                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
329                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
330    
# Line 305  class PostGISShapeStore(PostGISTable): Line 370  class PostGISShapeStore(PostGISTable):
370          y=[]          y=[]
371          cursor = self.db.cursor()          cursor = self.db.cursor()
372          try:          try:
373              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
374                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
375              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
376                # calculate the bounding box by hand from that
377                cursor.execute("SELECT extent(%s) FROM %s;"
378                               % (self.geometry_column, self.tablename))
379              result = cursor.fetchone()              result = cursor.fetchone()
380              while result:              if result:
381                  result = result[0]                  (minx, miny), (maxx, maxy) \
382                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
383                  # 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()  
384          finally:          finally:
385              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
386    
387      def Shape(self, shapeid):      def Shape(self, shapeid):
388          cursor = self.db.cursor()          cursor = self.db.cursor()
# Line 339  class PostGISShapeStore(PostGISTable): Line 392  class PostGISShapeStore(PostGISTable):
392          cursor.close()          cursor.close()
393          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
394    
395        def AllShapes(self):
396            cursor = self.db.cursor()
397            cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
398                           % (self.geometry_column, self.tablename))
399            while 1:
400                result = cursor.fetchone()
401                if result is None:
402                    return
403                yield PostGISShape(result[0], result[1])
404    
405    
406      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
407          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
408          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 354  class PostGISShapeStore(PostGISTable): Line 418  class PostGISShapeStore(PostGISTable):
418          while 1:          while 1:
419              result = cursor.fetchone()              result = cursor.fetchone()
420              if result is None:              if result is None:
421                  raise StopIteration                  return
422              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1620  
changed lines
  Added in v.1693
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26