/[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 1605 by bh, Tue Aug 19 11:00:40 2003 UTC revision 2059 by bh, Wed Feb 11 09:05:40 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 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    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):
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 48  class PostGISConnection: Line 80  class PostGISConnection:
80          self.user = user          self.user = user
81          self.password = password          self.password = password
82          self.dbtype = dbtype          self.dbtype = dbtype
83            self.connect()
84    
85        def connect(self):
86            """Internal: Establish the database connection"""
87          params = []          params = []
88          for name in ("host", "port", "dbname", "user", "password"):          for name in ("host", "port", "dbname", "user", "password"):
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 67  class PostGISConnection: Line 106  class PostGISConnection:
106          else:          else:
107              raise ValueError("Can't determine postgres type of geometries")              raise ValueError("Can't determine postgres type of geometries")
108    
109        def BriefDescription(self):
110            """Return a brief, one-line description of the connection
111    
112            The return value is suitable for a list box of all database
113            connections.
114            """
115            return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
116                    % 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 87  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 107  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:              col = self._create_col_from_description(i, description[i])
203                  if pgtyp == description[i][1]:              if col is not None:
204                      col = PostGISColumn(description[i][0], tabletyp, i)                  self.columns.append(col)
                     break  
             else:  
                 if description[i][1] == self.db.geometry_type:  
                     self.geometry_column = description[i][0]  
                 # No matching table type. Ignore the column.  
                 # FIXME: We should at least print a warning about  
                 # ignored columns  
                 continue  
             self.columns.append(col)  
205    
206          for col in self.columns:          for col in self.columns:
207              self.column_map[col.name] = col              self.column_map[col.name] = col
# Line 137  class PostGISTable: Line 209  class PostGISTable:
209    
210          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
211          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
212                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
213                                self.tablename))                                           for col in self.columns]),
214                                  self.quoted_tablename))
215    
216        def _create_col_from_description(self, index, description):
217            """Return the column object for the column described by description
218    
219            The parameter index is the index of the column.  The description
220            is a sequence taken from the cursor's description attribute for
221            the column.  That means description[0] is the name of the column
222            and description[1] the type.
223    
224            Return None if the column can't be represented for some reason,
225            e.g. because its type is not yet supported or needs to be
226            treated in some special way.  Derived classes may extend this
227            method.
228            """
229            for pgtyp, tabletyp in type_map:
230                if pgtyp == description[1]:
231                    return PostGISColumn(description[0], tabletyp,
232                                         len(self.columns))
233            return None
234    
235        def DBConnection(self):
236            """Return the dbconnection used by the table"""
237            return self.db
238    
239        def TableName(self):
240            """Return the name of the table in the database"""
241            return self.tablename
242    
243        def Title(self):
244            """Return the title of the table.
245    
246            The title is currently fixed and equal to the tablename
247            """
248            return self.tablename
249    
250      def Dependencies(self):      def Dependencies(self):
251          """Return an empty tuple because a PostGISTable depends on nothing else          """Return an empty tuple because a PostGISTable depends on nothing else
# Line 159  class PostGISTable: Line 266  class PostGISTable:
266    
267      def NumRows(self):      def NumRows(self):
268          cursor = self.db.cursor()          cursor = self.db.cursor()
269          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
270            return cursor.fetchone()[0]
271    
272        def RowIdToOrdinal(self, gid):
273            """Return the row ordinal given its id"""
274            cursor = self.db.cursor()
275            cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
276                           % (self.quoted_tablename, gid))
277            return cursor.fetchone()[0]
278    
279        def RowOrdinalToId(self, num):
280            """Return the rowid for given its ordinal"""
281            cursor = self.db.cursor()
282            cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
283                           % (self.quoted_tablename, num))
284          return cursor.fetchone()[0]          return cursor.fetchone()[0]
285    
286      def ReadRowAsDict(self, row):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
287          cursor = self.db.cursor()          cursor = self.db.cursor()
288          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          if row_is_ordinal:
289                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
290            else:
291                stmt = self.query_stmt + " WHERE gid = %d" % row
292            cursor.execute(stmt)
293          result = {}          result = {}
294          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
295              result[col.name] = value              result[col.name] = value
296          return result          return result
297    
298      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
299          cursor = self.db.cursor()          cursor = self.db.cursor()
300          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
301                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
302                        (self.column_map[col].quoted_name, self.quoted_tablename,
303                         row))
304            else:
305                stmt = ("SELECT %s FROM %s WHERE gid = %d" %
306                        (self.column_map[col].quoted_name, self.quoted_tablename,
307                         row))
308            cursor.execute(stmt)
309          return cursor.fetchone()[0]          return cursor.fetchone()[0]
310    
311      def ValueRange(self, col):      def ValueRange(self, col):
312          cursor = self.db.cursor()          cursor = self.db.cursor()
313          name = self.column_map[col].name          name = self.column_map[col].quoted_name
314          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
315                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
316          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
317    
318      def UniqueValues(self, col):      def UniqueValues(self, col):
319          cursor = self.db.cursor()          cursor = self.db.cursor()
320          name = self.column_map[col].name          name = self.column_map[col].quoted_name
321          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
322                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
323          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
324    
325      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 198  class PostGISTable: Line 330  class PostGISTable:
330              comparison = "="              comparison = "="
331    
332          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
333              right_template = right.name              right_template = right.quoted_name
334              params = ()              params = ()
335          else:          else:
336              right_template = "%s"              right_template = "%s"
337              params = (right,)              params = (right,)
338    
339          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
340                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_tablename, left.quoted_name, comparison,
341                       right_template)
342    
343          cursor = self.db.cursor()          cursor = self.db.cursor()
344          cursor.execute(query, params)          cursor.execute(query, params)
# Line 247  class PostGISShape: Line 380  class PostGISShape:
380    
381    
382  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
383                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
384                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
385                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
386    
# Line 255  class PostGISShapeStore(PostGISTable): Line 389  class PostGISShapeStore(PostGISTable):
389    
390      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
391    
392        def __init__(self, db, tablename, geometry_column = None):
393            """Initialize the PostGISShapeStore.
394    
395            The db parameter should be an instance of PostGISConnection and
396            tablename the name of a table in the database represented by db.
397    
398            The geometry_column paramter, if given, should be the name of
399            the geometry column to use.  If the name given is not a geometry
400            column, raise a ValueError.
401    
402            If no geometry_column is given, the table must have exactly one
403            geometry column.  If it has more than one and the
404            geometry_column is not given, a ValueError will be raised.
405            """
406            self.geometry_column = geometry_column
407            self.geometry_column_was_given = geometry_column is not None
408            PostGISTable.__init__(self, db, tablename)
409    
410            # For convenience, we have a quoted version of the geometry
411            # column in self.quoted_geo_col
412            self.quoted_geo_col = quote_identifier(self.geometry_column)
413    
414        def _fetch_table_information(self):
415            """Extend inherited method to retrieve the SRID"""
416            PostGISTable._fetch_table_information(self)
417            cursor = self.db.cursor()
418            cursor.execute("SELECT srid FROM geometry_columns"
419                           " WHERE f_table_name = %s AND f_geometry_column=%s",
420                           (self.tablename, self.geometry_column))
421            self.srid = cursor.fetchone()[0]
422    
423        def _create_col_from_description(self, index, description):
424            """Extend the inherited method to find geometry columns
425    
426            If the column indicated by the paramters is a geometry column,
427            record its name in self.geometry_column and a quoted version in
428            self.quoted_geo_col.  In any case return the return value of the
429            inherited method.
430            """
431            col = PostGISTable._create_col_from_description(self, index,
432                                                            description)
433            col_name, col_type = description[:2]
434            if self.geometry_column_was_given:
435                if (col_name == self.geometry_column
436                    and col_type != self.db.geometry_type):
437                    raise TypeError("Column %s in %s is not a geometry column"
438                                    % (self.geometry_column, self.tablename))
439            else:
440                if col is None:
441                    if description[1] == self.db.geometry_type:
442                        # The column is a geometry column.  If the name of
443                        # the geometry column was not given to the
444                        # constructor, and we encounter two geometry
445                        # columns, raise a value error
446                        if self.geometry_column is None:
447                            self.geometry_column = description[0]
448                        else:
449                            raise TypeError("Table %s has two geometry columns"
450                                            " and no column name was given"
451                                            % (self.tablename,))
452            return col
453    
454      def Table(self):      def Table(self):
455          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
456          return self          return self
# Line 268  class PostGISShapeStore(PostGISTable): Line 464  class PostGISShapeStore(PostGISTable):
464          """Return the type of the shapes in the shapestore."""          """Return the type of the shapes in the shapestore."""
465          cursor = self.db.cursor()          cursor = self.db.cursor()
466          cursor.execute("SELECT type FROM geometry_columns WHERE"          cursor.execute("SELECT type FROM geometry_columns WHERE"
467                         " f_table_name=%s", (self.tablename,))                         " f_table_name=%s AND f_geometry_column=%s",
468                           (self.tablename, self.geometry_column))
469          result = cursor.fetchone()[0]          result = cursor.fetchone()[0]
470          cursor.close()          cursor.close()
471          return shapetype_map[result]          return shapetype_map[result]
# Line 292  class PostGISShapeStore(PostGISTable): Line 489  class PostGISShapeStore(PostGISTable):
489          y=[]          y=[]
490          cursor = self.db.cursor()          cursor = self.db.cursor()
491          try:          try:
492              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
493                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
494              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
495                # calculate the bounding box by hand from that
496                cursor.execute("SELECT extent(%s) FROM %s;"
497                               % (self.quoted_geo_col, self.quoted_tablename))
498              result = cursor.fetchone()              result = cursor.fetchone()
499              while result:              if result[0]:
500                  result = result[0]                  (minx, miny), (maxx, maxy) \
501                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
502                  # 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()  
503          finally:          finally:
504              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
505    
506      def Shape(self, shapeid):      def Shape(self, shapeid):
507          cursor = self.db.cursor()          cursor = self.db.cursor()
508          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
509                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
510          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
511          cursor.close()          cursor.close()
512          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
513    
514        def AllShapes(self):
515            cursor = self.db.cursor()
516            cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
517                           % (self.quoted_geo_col, self.quoted_tablename))
518            while 1:
519                result = cursor.fetchone()
520                if result is None:
521                    return
522                yield PostGISShape(result[0], result[1])
523    
524    
525      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
526          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
527          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 335  class PostGISShapeStore(PostGISTable): Line 531  class PostGISShapeStore(PostGISTable):
531                     left, bottom))                     left, bottom))
532          cursor = self.db.cursor()          cursor = self.db.cursor()
533          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
534                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"
535                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
536                            self.geometry_column, geom))                            self.quoted_geo_col, geom, self.srid))
537          while 1:          while 1:
538              result = cursor.fetchone()              result = cursor.fetchone()
539              if result is None:              if result is None:
540                  raise StopIteration                  return
541              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1605  
changed lines
  Added in v.2059
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26