/[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 1636 by bh, Fri Aug 22 17:20:29 2003 UTC revision 2057 by bh, Tue Feb 10 15:51:57 2004 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003 by Intevation GmbH  # Copyright (C) 2003, 2004 by Intevation GmbH
2  # Authors:  # Authors:
3  # Martin Mueller <[email protected]>  # Martin Mueller <[email protected]>
4  # Bernhard Herzog <[email protected]>  # Bernhard Herzog <[email protected]>
# Line 37  if psycopg is not None: Line 37  if psycopg is not None:
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 already 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):  class ConnectionError(Exception):
57    
58      """Class for exceptions occurring when establishing a Databse connection"""      """Class for exceptions occurring when establishing a Databse connection"""
# Line 99  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 119  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 139  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 169  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 191  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 230  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 279  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 287  class PostGISShapeStore(PostGISTable): Line 381  class PostGISShapeStore(PostGISTable):
381    
382      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
383    
384        def _fetch_table_information(self):
385            """Extend inherited method to retrieve the SRID"""
386            PostGISTable._fetch_table_information(self)
387            cursor = self.db.cursor()
388            cursor.execute("SELECT srid FROM geometry_columns"
389                           " WHERE f_table_name = %s", (self.tablename,))
390            self.srid = cursor.fetchone()[0]
391    
392      def Table(self):      def Table(self):
393          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
394          return self          return self
# Line 324  class PostGISShapeStore(PostGISTable): Line 426  class PostGISShapeStore(PostGISTable):
426          y=[]          y=[]
427          cursor = self.db.cursor()          cursor = self.db.cursor()
428          try:          try:
429              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
430                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
431              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
432                # calculate the bounding box by hand from that
433                cursor.execute("SELECT extent(%s) FROM %s;"
434                               % (self.quoted_geo_col, self.quoted_tablename))
435              result = cursor.fetchone()              result = cursor.fetchone()
436              while result:              if result[0]:
437                  result = result[0]                  (minx, miny), (maxx, maxy) \
438                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
439                  # 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()  
440          finally:          finally:
441              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
442    
443      def Shape(self, shapeid):      def Shape(self, shapeid):
444          cursor = self.db.cursor()          cursor = self.db.cursor()
445          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
446                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
447          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
448          cursor.close()          cursor.close()
449          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
450    
451        def AllShapes(self):
452            cursor = self.db.cursor()
453            cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
454                           % (self.quoted_geo_col, self.quoted_tablename))
455            while 1:
456                result = cursor.fetchone()
457                if result is None:
458                    return
459                yield PostGISShape(result[0], result[1])
460    
461    
462      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
463          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
464          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 367  class PostGISShapeStore(PostGISTable): Line 468  class PostGISShapeStore(PostGISTable):
468                     left, bottom))                     left, bottom))
469          cursor = self.db.cursor()          cursor = self.db.cursor()
470          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
471                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"
472                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
473                            self.geometry_column, geom))                            self.quoted_geo_col, geom, self.srid))
474          while 1:          while 1:
475              result = cursor.fetchone()              result = cursor.fetchone()
476              if result is None:              if result is None:
477                  raise StopIteration                  return
478              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1636  
changed lines
  Added in v.2057
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26