/[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 1946 by bh, Thu Nov 13 18:56:41 2003 UTC
# 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 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):  class ConnectionError(Exception):
57    
58      """Class for exceptions occurring when establishing a Databse connection"""      """Class for exceptions occurring when establishing a Databse connection"""
# Line 119  class PostGISConnection: Line 135  class PostGISConnection:
135    
136  class PostGISColumn:  class PostGISColumn:
137    
138      """Column description for a PostGISTable"""      """Column description for a PostGISTable
139    
140        In addition to the normal column object attributes name, type and
141        index, PostGISColumn objects have a quoted_name attribute which
142        contains a quoted version of name for use in SQL statements. The
143        quoted_name attribute is mainly intended for internal use by the
144        PostGISTable class.
145        """
146    
147      def __init__(self, name, type, index):      def __init__(self, name, type, index):
148          self.name = name          self.name = name
149            self.quoted_name = quote_identifier(name)
150          self.type = type          self.type = type
151          self.index = index          self.index = index
152    
153    
154  class PostGISTable:  class PostGISTable:
155    
156      """A Table in a PostGIS database"""      """A Table in a PostGIS database
157    
158        A PostgreSQL table may contain columns with types not (yet)
159        supported by Thuban. Instances of this class ignore those columns
160        and pretend they don't exist, i.e. they won't show up in the column
161        descriptions returned by Columns() and other methods.
162        """
163    
164      def __init__(self, db, tablename):      def __init__(self, db, tablename):
165          """Initialize the PostGISTable.          """Initialize the PostGISTable.
# Line 139  class PostGISTable: Line 169  class PostGISTable:
169          """          """
170          self.db = db          self.db = db
171          self.tablename = tablename          self.tablename = tablename
172            # Tablename quoted for use in SQL statements.
173            self.quoted_tablename = quote_identifier(tablename)
174    
175            # Map column names and indices to column objects.
176          self.column_map = {}          self.column_map = {}
177    
178          self._fetch_table_information()          self._fetch_table_information()
179    
180      def _fetch_table_information(self):      def _fetch_table_information(self):
181          """Internal: Update information about the table"""          """Internal: Update information about the table"""
182          self.columns = []          self.columns = []
183          cursor = self.db.cursor()          cursor = self.db.cursor()
184          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
185          description = cursor.description          description = cursor.description
186    
187          for i in range(len(description)):          for i in range(len(description)):
188              for pgtyp, tabletyp in type_map:              for pgtyp, tabletyp in type_map:
189                  if pgtyp == description[i][1]:                  if pgtyp == description[i][1]:
190                      col = PostGISColumn(description[i][0], tabletyp, i)                      col = PostGISColumn(description[i][0], tabletyp,
191                                            len(self.columns))
192                      break                      break
193              else:              else:
194                  if description[i][1] == self.db.geometry_type:                  if description[i][1] == self.db.geometry_type:
195                      self.geometry_column = description[i][0]                      self.geometry_column = description[i][0]
196                        self.quoted_geo_col =quote_identifier(self.geometry_column)
197                  # No matching table type. Ignore the column.                  # No matching table type. Ignore the column.
198                  # FIXME: We should at least print a warning about                  # FIXME: We should at least print a warning about
199                  # ignored columns                  # ignored columns
# Line 169  class PostGISTable: Line 206  class PostGISTable:
206    
207          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
208          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
209                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
210                                self.tablename))                                           for col in self.columns]),
211                                  self.quoted_tablename))
212    
213        def DBConnection(self):
214            """Return the dbconnection used by the table"""
215            return self.db
216    
217        def TableName(self):
218            """Return the name of the table in the database"""
219            return self.tablename
220    
221        def Title(self):
222            """Return the title of the table.
223    
224            The title is currently fixed and equal to the tablename
225            """
226            return self.tablename
227    
228      def Dependencies(self):      def Dependencies(self):
229          """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 244  class PostGISTable:
244    
245      def NumRows(self):      def NumRows(self):
246          cursor = self.db.cursor()          cursor = self.db.cursor()
247          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
248            return cursor.fetchone()[0]
249    
250        def RowIdToOrdinal(self, gid):
251            """Return the row ordinal given its id"""
252            cursor = self.db.cursor()
253            cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
254                           % (self.quoted_tablename, gid))
255          return cursor.fetchone()[0]          return cursor.fetchone()[0]
256    
257      def ReadRowAsDict(self, row):      def RowOrdinalToId(self, num):
258            """Return the rowid for given its ordinal"""
259          cursor = self.db.cursor()          cursor = self.db.cursor()
260          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
261                           % (self.quoted_tablename, num))
262            return cursor.fetchone()[0]
263    
264        def ReadRowAsDict(self, row, row_is_ordinal = 0):
265            cursor = self.db.cursor()
266            if row_is_ordinal:
267                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
268            else:
269                stmt = self.query_stmt + " WHERE gid = %d" % row
270            cursor.execute(stmt)
271          result = {}          result = {}
272          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
273              result[col.name] = value              result[col.name] = value
274          return result          return result
275    
276      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
277          cursor = self.db.cursor()          cursor = self.db.cursor()
278          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
279                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
280                        (self.column_map[col].quoted_name, self.quoted_tablename,
281                         row))
282            else:
283                stmt = ("SELECT %s FROM %s WHERE gid = %d" %
284                        (self.column_map[col].quoted_name, self.quoted_tablename,
285                         row))
286            cursor.execute(stmt)
287          return cursor.fetchone()[0]          return cursor.fetchone()[0]
288    
289      def ValueRange(self, col):      def ValueRange(self, col):
290          cursor = self.db.cursor()          cursor = self.db.cursor()
291          name = self.column_map[col].name          name = self.column_map[col].quoted_name
292          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
293                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
294          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
295    
296      def UniqueValues(self, col):      def UniqueValues(self, col):
297          cursor = self.db.cursor()          cursor = self.db.cursor()
298          name = self.column_map[col].name          name = self.column_map[col].quoted_name
299          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
300                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
301          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
302    
303      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 230  class PostGISTable: Line 308  class PostGISTable:
308              comparison = "="              comparison = "="
309    
310          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
311              right_template = right.name              right_template = right.quoted_name
312              params = ()              params = ()
313          else:          else:
314              right_template = "%s"              right_template = "%s"
315              params = (right,)              params = (right,)
316    
317          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
318                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_tablename, left.quoted_name, comparison,
319                       right_template)
320    
321          cursor = self.db.cursor()          cursor = self.db.cursor()
322          cursor.execute(query, params)          cursor.execute(query, params)
# Line 279  class PostGISShape: Line 358  class PostGISShape:
358    
359    
360  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
361                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
362                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
363                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
364    
# Line 324  class PostGISShapeStore(PostGISTable): Line 404  class PostGISShapeStore(PostGISTable):
404          y=[]          y=[]
405          cursor = self.db.cursor()          cursor = self.db.cursor()
406          try:          try:
407              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
408                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
409              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
410                # calculate the bounding box by hand from that
411                cursor.execute("SELECT extent(%s) FROM %s;"
412                               % (self.quoted_geo_col, self.quoted_tablename))
413              result = cursor.fetchone()              result = cursor.fetchone()
414              while result:              if result:
415                  result = result[0]                  (minx, miny), (maxx, maxy) \
416                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
417                  # 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()  
418          finally:          finally:
419              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
420    
421      def Shape(self, shapeid):      def Shape(self, shapeid):
422          cursor = self.db.cursor()          cursor = self.db.cursor()
423          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
424                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
425          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
426          cursor.close()          cursor.close()
427          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
428    
429        def AllShapes(self):
430            cursor = self.db.cursor()
431            cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
432                           % (self.quoted_geo_col, self.quoted_tablename))
433            while 1:
434                result = cursor.fetchone()
435                if result is None:
436                    return
437                yield PostGISShape(result[0], result[1])
438    
439    
440      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
441          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
442          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 368  class PostGISShapeStore(PostGISTable): Line 447  class PostGISShapeStore(PostGISTable):
447          cursor = self.db.cursor()          cursor = self.db.cursor()
448          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
449                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"
450                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
451                            self.geometry_column, geom))                            self.quoted_geo_col, geom))
452          while 1:          while 1:
453              result = cursor.fetchone()              result = cursor.fetchone()
454              if result is None:              if result is None:
455                  raise StopIteration                  return
456              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1636  
changed lines
  Added in v.1946
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26