/[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 1662 by bh, Wed Aug 27 13:51:01 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 153  class PostGISTable: Line 172  class PostGISTable:
172                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.name for col in self.columns]),
173                                self.tablename))                                self.tablename))
174    
175        def DBConnection(self):
176            """Return the dbconnection used by the table"""
177            return self.db
178    
179        def TableName(self):
180            """Return the name of the table in the database"""
181            return self.tablename
182    
183        def Title(self):
184            """Return the title of the table.
185    
186            The title is currently fixed and equal to the tablename
187            """
188            return self.tablename
189    
190      def Dependencies(self):      def Dependencies(self):
191          """Return an empty tuple because a PostGISTable depends on nothing else          """Return an empty tuple because a PostGISTable depends on nothing else
192          """          """
# Line 175  class PostGISTable: Line 209  class PostGISTable:
209          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.tablename)
210          return cursor.fetchone()[0]          return cursor.fetchone()[0]
211    
212      def ReadRowAsDict(self, row):      def RowIdToOrdinal(self, gid):
213            """Return the row ordinal given its id"""
214          cursor = self.db.cursor()          cursor = self.db.cursor()
215          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
216                           % (self.tablename, gid))
217            return cursor.fetchone()[0]
218    
219        def RowOrdinalToId(self, num):
220            """Return the rowid for given its ordinal"""
221            cursor = self.db.cursor()
222            cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
223                           % (self.tablename, num))
224            return cursor.fetchone()[0]
225    
226        def ReadRowAsDict(self, row, row_is_ordinal = 0):
227            cursor = self.db.cursor()
228            if row_is_ordinal:
229                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
230            else:
231                stmt = self.query_stmt + " WHERE gid = %d" % row
232            cursor.execute(stmt)
233          result = {}          result = {}
234          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
235              result[col.name] = value              result[col.name] = value
236          return result          return result
237    
238      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
239          cursor = self.db.cursor()          cursor = self.db.cursor()
240          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
241                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
242                        (self.column_map[col].name, self.tablename, row))
243            else:
244                stmt = ("SELECT %s FROM %s WHERE gid = %d" %
245                        (self.column_map[col].name, self.tablename, row))
246            cursor.execute(stmt)
247          return cursor.fetchone()[0]          return cursor.fetchone()[0]
248    
249      def ValueRange(self, col):      def ValueRange(self, col):
# Line 260  class PostGISShape: Line 317  class PostGISShape:
317    
318    
319  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
320                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
321                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
322                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
323    
# Line 305  class PostGISShapeStore(PostGISTable): Line 363  class PostGISShapeStore(PostGISTable):
363          y=[]          y=[]
364          cursor = self.db.cursor()          cursor = self.db.cursor()
365          try:          try:
366              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
367                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
368              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
369                # calculate the bounding box by hand from that
370                cursor.execute("SELECT extent(%s) FROM %s;"
371                               % (self.geometry_column, self.tablename))
372              result = cursor.fetchone()              result = cursor.fetchone()
373              while result:              if result:
374                  result = result[0]                  (minx, miny), (maxx, maxy) \
375                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
376                  # 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()  
377          finally:          finally:
378              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
379    
380      def Shape(self, shapeid):      def Shape(self, shapeid):
381          cursor = self.db.cursor()          cursor = self.db.cursor()
# Line 339  class PostGISShapeStore(PostGISTable): Line 385  class PostGISShapeStore(PostGISTable):
385          cursor.close()          cursor.close()
386          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
387    
388        def AllShapes(self):
389            cursor = self.db.cursor()
390            cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
391                           % (self.geometry_column, self.tablename))
392            while 1:
393                result = cursor.fetchone()
394                if result is None:
395                    return
396                yield PostGISShape(result[0], result[1])
397    
398    
399      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
400          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
401          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 354  class PostGISShapeStore(PostGISTable): Line 411  class PostGISShapeStore(PostGISTable):
411          while 1:          while 1:
412              result = cursor.fetchone()              result = cursor.fetchone()
413              if result is None:              if result is None:
414                  raise StopIteration                  return
415              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1620  
changed lines
  Added in v.1662
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26