/[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 1631 by bh, Fri Aug 22 16:01:14 2003 UTC revision 1955 by bh, Tue Nov 18 15:37:31 2003 UTC
# Line 36  if psycopg is not None: Line 36  if psycopg is not None:
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    def quote_identifier(ident):
41        """Return a quoted version of the identifier ident.
42    
43        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
45        any double quotes alread in the input value are converted to two
46        double quotes. Examples:
47    
48        >>> quote_identifier("abc\"def")
49        '"abc""def"'
50        >>> quote_identifier("abc def")
51        '"abc def"'
52        """
53        return '"' + '""'.join(ident.split('"')) + '"'
54    
55    
56    class ConnectionError(Exception):
57    
58        """Class for exceptions occurring when establishing a Databse connection"""
59    
60    
61  class PostGISConnection:  class PostGISConnection:
62    
63      """Represent a PostGIS database      """Represent a PostGIS database
# Line 67  class PostGISConnection: Line 89  class PostGISConnection:
89              val = getattr(self, name)              val = getattr(self, name)
90              if val:              if val:
91                  params.append("%s=%s" % (name, val))                  params.append("%s=%s" % (name, val))
92          self.connection = psycopg.connect(" ".join(params))          try:
93                self.connection = psycopg.connect(" ".join(params))
94            except psycopg.OperationalError, val:
95                raise ConnectionError(str(val))
96    
97          # determine the OID for the geometry type. This is PostGIS          # determine the OID for the geometry type. This is PostGIS
98          # specific.          # specific.
# Line 90  class PostGISConnection: Line 115  class PostGISConnection:
115          return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"          return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
116                  % self.__dict__)                  % self.__dict__)
117    
118        def MatchesParameters(self, parameters):
119            """Return whether the connection matches the dictionary of parameters
120    
121            Return whether instatiating the connection with the given
122            parameters would establish essentially the same connection as
123            self. The connection is essentially the same if the same
124            database (identified by host, port and databasename) is accessed
125            as the same user.
126            """
127            return (parameters["host"] == self.host
128                    and parameters["port"] == self.port
129                    and parameters["dbname"] == self.dbname
130                    and parameters["user"] == self.user)
131    
132      def Close(self):      def Close(self):
133          """Close the database connection"""          """Close the database connection"""
134          self.connection.close()          self.connection.close()
# Line 110  class PostGISConnection: Line 149  class PostGISConnection:
149    
150  class PostGISColumn:  class PostGISColumn:
151    
152      """Column description for a PostGISTable"""      """Column description for a PostGISTable
153    
154        In addition to the normal column object attributes name, type and
155        index, PostGISColumn objects have a quoted_name attribute which
156        contains a quoted version of name for use in SQL statements. The
157        quoted_name attribute is mainly intended for internal use by the
158        PostGISTable class.
159        """
160    
161      def __init__(self, name, type, index):      def __init__(self, name, type, index):
162          self.name = name          self.name = name
163            self.quoted_name = quote_identifier(name)
164          self.type = type          self.type = type
165          self.index = index          self.index = index
166    
167    
168  class PostGISTable:  class PostGISTable:
169    
170      """A Table in a PostGIS database"""      """A Table in a PostGIS database
171    
172        A PostgreSQL table may contain columns with types not (yet)
173        supported by Thuban. Instances of this class ignore those columns
174        and pretend they don't exist, i.e. they won't show up in the column
175        descriptions returned by Columns() and other methods.
176        """
177    
178      def __init__(self, db, tablename):      def __init__(self, db, tablename):
179          """Initialize the PostGISTable.          """Initialize the PostGISTable.
# Line 130  class PostGISTable: Line 183  class PostGISTable:
183          """          """
184          self.db = db          self.db = db
185          self.tablename = tablename          self.tablename = tablename
186            # Tablename quoted for use in SQL statements.
187            self.quoted_tablename = quote_identifier(tablename)
188    
189            # Map column names and indices to column objects.
190          self.column_map = {}          self.column_map = {}
191    
192          self._fetch_table_information()          self._fetch_table_information()
193    
194      def _fetch_table_information(self):      def _fetch_table_information(self):
195          """Internal: Update information about the table"""          """Internal: Update information about the table"""
196          self.columns = []          self.columns = []
197          cursor = self.db.cursor()          cursor = self.db.cursor()
198          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
199          description = cursor.description          description = cursor.description
200    
201          for i in range(len(description)):          for i in range(len(description)):
202              for pgtyp, tabletyp in type_map:              for pgtyp, tabletyp in type_map:
203                  if pgtyp == description[i][1]:                  if pgtyp == description[i][1]:
204                      col = PostGISColumn(description[i][0], tabletyp, i)                      col = PostGISColumn(description[i][0], tabletyp,
205                                            len(self.columns))
206                      break                      break
207              else:              else:
208                  if description[i][1] == self.db.geometry_type:                  if description[i][1] == self.db.geometry_type:
209                      self.geometry_column = description[i][0]                      self.geometry_column = description[i][0]
210                        self.quoted_geo_col =quote_identifier(self.geometry_column)
211                  # No matching table type. Ignore the column.                  # No matching table type. Ignore the column.
212                  # FIXME: We should at least print a warning about                  # FIXME: We should at least print a warning about
213                  # ignored columns                  # ignored columns
# Line 160  class PostGISTable: Line 220  class PostGISTable:
220    
221          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
222          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
223                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
224                                self.tablename))                                           for col in self.columns]),
225                                  self.quoted_tablename))
226    
227        def DBConnection(self):
228            """Return the dbconnection used by the table"""
229            return self.db
230    
231        def TableName(self):
232            """Return the name of the table in the database"""
233            return self.tablename
234    
235        def Title(self):
236            """Return the title of the table.
237    
238            The title is currently fixed and equal to the tablename
239            """
240            return self.tablename
241    
242      def Dependencies(self):      def Dependencies(self):
243          """Return an empty tuple because a PostGISTable depends on nothing else          """Return an empty tuple because a PostGISTable depends on nothing else
# Line 182  class PostGISTable: Line 258  class PostGISTable:
258    
259      def NumRows(self):      def NumRows(self):
260          cursor = self.db.cursor()          cursor = self.db.cursor()
261          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
262            return cursor.fetchone()[0]
263    
264        def RowIdToOrdinal(self, gid):
265            """Return the row ordinal given its id"""
266            cursor = self.db.cursor()
267            cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
268                           % (self.quoted_tablename, gid))
269            return cursor.fetchone()[0]
270    
271        def RowOrdinalToId(self, num):
272            """Return the rowid for given its ordinal"""
273            cursor = self.db.cursor()
274            cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
275                           % (self.quoted_tablename, num))
276          return cursor.fetchone()[0]          return cursor.fetchone()[0]
277    
278      def ReadRowAsDict(self, row):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
279          cursor = self.db.cursor()          cursor = self.db.cursor()
280          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          if row_is_ordinal:
281                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
282            else:
283                stmt = self.query_stmt + " WHERE gid = %d" % row
284            cursor.execute(stmt)
285          result = {}          result = {}
286          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
287              result[col.name] = value              result[col.name] = value
288          return result          return result
289    
290      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
291          cursor = self.db.cursor()          cursor = self.db.cursor()
292          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
293                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
294                        (self.column_map[col].quoted_name, self.quoted_tablename,
295                         row))
296            else:
297                stmt = ("SELECT %s FROM %s WHERE gid = %d" %
298                        (self.column_map[col].quoted_name, self.quoted_tablename,
299                         row))
300            cursor.execute(stmt)
301          return cursor.fetchone()[0]          return cursor.fetchone()[0]
302    
303      def ValueRange(self, col):      def ValueRange(self, col):
304          cursor = self.db.cursor()          cursor = self.db.cursor()
305          name = self.column_map[col].name          name = self.column_map[col].quoted_name
306          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
307                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
308          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
309    
310      def UniqueValues(self, col):      def UniqueValues(self, col):
311          cursor = self.db.cursor()          cursor = self.db.cursor()
312          name = self.column_map[col].name          name = self.column_map[col].quoted_name
313          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
314                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
315          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
316    
317      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 221  class PostGISTable: Line 322  class PostGISTable:
322              comparison = "="              comparison = "="
323    
324          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
325              right_template = right.name              right_template = right.quoted_name
326              params = ()              params = ()
327          else:          else:
328              right_template = "%s"              right_template = "%s"
329              params = (right,)              params = (right,)
330    
331          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
332                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_tablename, left.quoted_name, comparison,
333                       right_template)
334    
335          cursor = self.db.cursor()          cursor = self.db.cursor()
336          cursor.execute(query, params)          cursor.execute(query, params)
# Line 270  class PostGISShape: Line 372  class PostGISShape:
372    
373    
374  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
375                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
376                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
377                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
378    
# Line 315  class PostGISShapeStore(PostGISTable): Line 418  class PostGISShapeStore(PostGISTable):
418          y=[]          y=[]
419          cursor = self.db.cursor()          cursor = self.db.cursor()
420          try:          try:
421              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
422                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
423              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
424                # calculate the bounding box by hand from that
425                cursor.execute("SELECT extent(%s) FROM %s;"
426                               % (self.quoted_geo_col, self.quoted_tablename))
427              result = cursor.fetchone()              result = cursor.fetchone()
428              while result:              if result:
429                  result = result[0]                  (minx, miny), (maxx, maxy) \
430                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
431                  # 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()  
432          finally:          finally:
433              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
434    
435      def Shape(self, shapeid):      def Shape(self, shapeid):
436          cursor = self.db.cursor()          cursor = self.db.cursor()
437          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
438                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
439          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
440          cursor.close()          cursor.close()
441          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
442    
443        def AllShapes(self):
444            cursor = self.db.cursor()
445            cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
446                           % (self.quoted_geo_col, self.quoted_tablename))
447            while 1:
448                result = cursor.fetchone()
449                if result is None:
450                    return
451                yield PostGISShape(result[0], result[1])
452    
453    
454      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
455          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
456          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 359  class PostGISShapeStore(PostGISTable): Line 461  class PostGISShapeStore(PostGISTable):
461          cursor = self.db.cursor()          cursor = self.db.cursor()
462          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
463                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"
464                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
465                            self.geometry_column, geom))                            self.quoted_geo_col, geom))
466          while 1:          while 1:
467              result = cursor.fetchone()              result = cursor.fetchone()
468              if result is None:              if result is None:
469                  raise StopIteration                  return
470              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1631  
changed lines
  Added in v.1955
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26