/[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

trunk/thuban/Thuban/Model/postgisdb.py revision 1620 by bh, Wed Aug 20 13:14:22 2003 UTC branches/WIP-pyshapelib-bramz/Thuban/Model/postgisdb.py revision 2734 by bramz, Thu Mar 1 12:42:59 2007 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003 by Intevation GmbH  # Copyright (C) 2003, 2004, 2005 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 maps psycopg type objects.  It's a list of pairs since
36        # the psycopg type objects are unhashable.
37      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
38                  (psycopg.INTEGER, table.FIELDTYPE_INT),                  (psycopg.INTEGER, table.FIELDTYPE_INT),
39                    (psycopg.ROWID, table.FIELDTYPE_INT),
40                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]
41    
42        # _raw_type_map maps the postgresql type constants to Thuban type
43        # constants.  This is very low level and postgresql specific and
44        # should be used only when necessary.
45        _raw_type_map = {}
46        def _fill_raw_type_map():
47            for psycopg_type, thuban_type in type_map:
48                for value in psycopg_type.values:
49                    _raw_type_map[value] = thuban_type
50        _fill_raw_type_map()
51    
52    
53    def quote_identifier(ident):
54        """Return a quoted version of the identifier ident.
55    
56        The return value is a string that can be put directly into an SQL
57        statement.  The quoted identifier is surrounded by double quotes and
58        any double quotes already in the input value are converted to two
59        double quotes.  Examples:
60    
61        >>> quote_identifier("abc\"def")
62        '"abc""def"'
63        >>> quote_identifier("abc def")
64        '"abc def"'
65        """
66        return '"' + '""'.join(ident.split('"')) + '"'
67    
68    
69    class ConnectionError(Exception):
70    
71        """Class for exceptions occurring when establishing a Databse connection"""
72    
73    
74  class PostGISConnection:  class PostGISConnection:
75    
76      """Represent a PostGIS database      """Represent a PostGIS database
# Line 57  class PostGISConnection: Line 102  class PostGISConnection:
102              val = getattr(self, name)              val = getattr(self, name)
103              if val:              if val:
104                  params.append("%s=%s" % (name, val))                  params.append("%s=%s" % (name, val))
105          self.connection = psycopg.connect(" ".join(params))          try:
106                self.connection = psycopg.connect(" ".join(params))
107            except psycopg.OperationalError, val:
108                raise ConnectionError(str(val))
109    
110            # Use autocommit mode.  For simple reading of the database it's
111            # sufficient and we don't have to care much about error
112            # handling.  Without autocommit, an errors during a cursor's
113            # execute method requires a rollback on the connection,
114            # otherwise later queries with the same or other cursors sharing
115            # the same connection will lead to further errors ("ERROR:
116            # current transaction is aborted, commands ignored until end of
117            # transaction block")
118            self.connection.autocommit()
119    
120          # determine the OID for the geometry type. This is PostGIS          # determine the OID for the geometry type. This is PostGIS
121          # specific.          # specific.
# Line 80  class PostGISConnection: Line 138  class PostGISConnection:
138          return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"          return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
139                  % self.__dict__)                  % self.__dict__)
140    
141        def MatchesParameters(self, parameters):
142            """Return whether the connection matches the dictionary of parameters
143    
144            Return whether instatiating the connection with the given
145            parameters would establish essentially the same connection as
146            self. The connection is essentially the same if the same
147            database (identified by host, port and databasename) is accessed
148            as the same user.
149            """
150            return (parameters["host"] == self.host
151                    and parameters["port"] == self.port
152                    and parameters["dbname"] == self.dbname
153                    and parameters["user"] == self.user)
154    
155      def Close(self):      def Close(self):
156          """Close the database connection"""          """Close the database connection"""
157          self.connection.close()          self.connection.close()
158    
159      def GeometryTables(self):      def GeometryTables(self):
160          """Return a list with the names of all tables with a geometry column"""          """Return a list with the names of all tables with a geometry column"""
161    
162            # The query is basically taken from the psql v. 7.2.1.  When
163            # started with -E it prints the queries used for internal
164            # commands such as \d, which does mostly what we need here.
165          cursor = self.connection.cursor()          cursor = self.connection.cursor()
166          cursor.execute("SELECT f_table_name FROM geometry_columns;")          cursor.execute("SELECT c.relname FROM pg_class c"
167                           " WHERE c.relkind IN ('r', 'v')"
168                                 # Omit the system tables
169                                 " AND c.relname !~ '^pg_'"
170                                 # Omit the special PostGIS tables
171                                 " AND c.relname NOT IN ('geometry_columns',"
172                                                       " 'spatial_ref_sys')"
173                                " AND %d in (SELECT a.atttypid FROM pg_attribute a"
174                                           " WHERE a.attrelid = c.oid)"
175                           " ORDER BY c.relname;", (self.geometry_type,))
176          result = [row[0] for row in cursor.fetchall()]          result = [row[0] for row in cursor.fetchall()]
177          self.connection.commit()          self.connection.commit()
178          return result          return result
179    
180        def table_columns(self, tablename):
181            """Experimental: return information about the columns of a table
182    
183            Return value is a list of (name, type) pairs where name is the
184            name of the column and type either one of the field type columns
185            or the string 'geometry' indicating a geometry column.
186    
187            The intended use of this method is for table selection dialogs
188            which need to determine which columns are usable as id or
189            geometry columns respectively.  Suitable id columns will have
190            type FIELDTYPE_INT and geometry columns will have 'geometry'.
191            """
192            result = []
193            cursor = self.connection.cursor()
194    
195            # This query is taken basically from the \d command of psql
196            # 7.2.1
197            cursor.execute("SELECT a.attname, a.atttypid, a.attnum"
198                           " FROM pg_class c, pg_attribute a"
199                                " WHERE c.relname = %s AND a.attrelid = c.oid"
200                           " ORDER BY a.attnum;", (tablename,))
201    
202            for row in cursor.fetchall():
203                col_name, col_type, col_attnum = row
204                col = None
205                if col_attnum < 1:
206                    # It's a system column.  Only the OID is interesting
207                    # here
208                    if col_name == "oid":
209                        col = (col_name, _raw_type_map[col_type])
210                else:
211                    # If it's an integer
212                    thuban_type = _raw_type_map.get(col_type)
213                    if thuban_type is not None:
214                        col = (col_name, thuban_type)
215                    elif row[1] == self.geometry_type:
216                        col = (col_name, "geometry")
217                if col is not None:
218                    result.append(col)
219    
220            return result
221    
222      def cursor(self):      def cursor(self):
223          """Return a DB API 2.0 cursor for the database"""          """Return a DB API 2.0 cursor for the database"""
224          return self.connection.cursor()          return self.connection.cursor()
# Line 100  class PostGISConnection: Line 227  class PostGISConnection:
227    
228  class PostGISColumn:  class PostGISColumn:
229    
230      """Column description for a PostGISTable"""      """Column description for a PostGISTable
231    
232        In addition to the normal column object attributes name, type and
233        index, PostGISColumn objects have a quoted_name attribute which
234        contains a quoted version of name for use in SQL statements. The
235        quoted_name attribute is mainly intended for internal use by the
236        PostGISTable class.
237        """
238    
239      def __init__(self, name, type, index):      def __init__(self, name, type, index):
240          self.name = name          self.name = name
241            self.quoted_name = quote_identifier(name)
242          self.type = type          self.type = type
243          self.index = index          self.index = index
244    
245    
246  class PostGISTable:  class PostGISTable:
247    
248      """A Table in a PostGIS database"""      """A Table in a PostGIS database
249    
250        A PostgreSQL table may contain columns with types not (yet)
251        supported by Thuban. Instances of this class ignore those columns
252        and pretend they don't exist, i.e. they won't show up in the column
253        descriptions returned by Columns() and other methods.
254        """
255    
256      def __init__(self, db, tablename):      def __init__(self, db, tablename, id_column = None):
257          """Initialize the PostGISTable.          """Initialize the PostGISTable.
258    
259          The db parameter should be an instance of PostGISConnection and          The db parameter should be an instance of PostGISConnection and
260          tablename the name of a table in the database represented by db.          tablename the name of a table in the database represented by db.
261    
262            The id_column parameter should be the name of a column in the
263            table that can be used to identify rows.  The column must have
264            the type integer and be unique and not null.
265    
266            For backwards compatibility reasons, the id_column parameter is
267            optional.  If not given the table must have a column called
268            'gid' which is used as the id_column.  New code should always
269            provide this parameter.
270          """          """
271          self.db = db          self.db = db
272          self.tablename = tablename          self.tablename = tablename
273            # Tablename quoted for use in SQL statements.
274            self.quoted_tablename = quote_identifier(tablename)
275    
276            if not id_column:
277                id_column = "gid"
278            self.id_column = id_column
279            # id column name quoted for use in SQL statements.
280            self.quoted_id_column = quote_identifier(id_column)
281    
282            # Map column names and indices to column objects.
283          self.column_map = {}          self.column_map = {}
284    
285          self._fetch_table_information()          self._fetch_table_information()
286    
287      def _fetch_table_information(self):      def _fetch_table_information(self):
288          """Internal: Update information about the table"""          """Internal: Update information about the table"""
289          self.columns = []          self.columns = []
290          cursor = self.db.cursor()          cursor = self.db.cursor()
291          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
292          description = cursor.description          description = cursor.description
293    
294          for i in range(len(description)):          for i in range(len(description)):
295              for pgtyp, tabletyp in type_map:              col = self._create_col_from_description(i, description[i])
296                  if pgtyp == description[i][1]:              if col is not None:
297                      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)  
298    
299          for col in self.columns:          for col in self.columns:
300              self.column_map[col.name] = col              self.column_map[col.name] = col
# Line 150  class PostGISTable: Line 302  class PostGISTable:
302    
303          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
304          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
305                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
306                                self.tablename))                                           for col in self.columns]),
307                                  self.quoted_tablename))
308    
309        def _create_col_from_description(self, index, description):
310            """Return the column object for the column described by description
311    
312            The parameter index is the index of the column.  The description
313            is a sequence taken from the cursor's description attribute for
314            the column.  That means description[0] is the name of the column
315            and description[1] the type.
316    
317            Return None if the column can't be represented for some reason,
318            e.g. because its type is not yet supported or needs to be
319            treated in some special way.  Derived classes may extend this
320            method.
321            """
322            for pgtyp, tabletyp in type_map:
323                if pgtyp == description[1]:
324                    return PostGISColumn(description[0], tabletyp,
325                                         len(self.columns))
326            return None
327    
328        def DBConnection(self):
329            """Return the dbconnection used by the table"""
330            return self.db
331    
332        def IDColumn(self):
333            """Return the column description object for the id column.
334    
335            If the oid column was used as the id column, the return value is
336            not one of the regular column objects that would be returned by
337            e.g. the Column() method, but it still has meaningful name
338            attribute.
339            """
340            if self.id_column == "oid":
341                return PostGISColumn(self.id_column, table.FIELDTYPE_INT, None)
342            return self.column_map[self.id_column]
343    
344        def TableName(self):
345            """Return the name of the table in the database"""
346            return self.tablename
347    
348        def Title(self):
349            """Return the title of the table.
350    
351            The title is currently fixed and equal to the tablename
352            """
353            return self.tablename
354    
355      def Dependencies(self):      def Dependencies(self):
356          """Return an empty tuple because a PostGISTable depends on nothing else          """Return an empty tuple because a PostGISTable depends on nothing else
# Line 172  class PostGISTable: Line 371  class PostGISTable:
371    
372      def NumRows(self):      def NumRows(self):
373          cursor = self.db.cursor()          cursor = self.db.cursor()
374          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
375          return cursor.fetchone()[0]          return cursor.fetchone()[0]
376    
377      def ReadRowAsDict(self, row):      def RowIdToOrdinal(self, gid):
378            """Return the row ordinal given its id"""
379          cursor = self.db.cursor()          cursor = self.db.cursor()
380          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          cursor.execute("SELECT count(*) FROM %s WHERE %s < %d;"
381                           % (self.quoted_tablename, self.quoted_id_column, gid))
382            return cursor.fetchone()[0]
383    
384        def RowOrdinalToId(self, num):
385            """Return the rowid for given its ordinal"""
386            cursor = self.db.cursor()
387            cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
388                           % (self.quoted_id_column, self.quoted_tablename, num))
389            return cursor.fetchone()[0]
390    
391        def ReadRowAsDict(self, row, row_is_ordinal = 0):
392            cursor = self.db.cursor()
393            if row_is_ordinal:
394                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
395            else:
396                stmt = self.query_stmt + " WHERE %s = %d" % (self.quoted_id_column,
397                                                             row)
398            cursor.execute(stmt)
399          result = {}          result = {}
400          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
401              result[col.name] = value              result[col.name] = value
402          return result          return result
403    
404      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
405          cursor = self.db.cursor()          cursor = self.db.cursor()
406          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
407                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
408                        (self.column_map[col].quoted_name, self.quoted_tablename,
409                         row))
410            else:
411                stmt = ("SELECT %s FROM %s WHERE %s = %d" %
412                        (self.column_map[col].quoted_name, self.quoted_tablename,
413                         self.quoted_id_column, row))
414            cursor.execute(stmt)
415          return cursor.fetchone()[0]          return cursor.fetchone()[0]
416    
417      def ValueRange(self, col):      def ValueRange(self, col):
418          cursor = self.db.cursor()          cursor = self.db.cursor()
419          name = self.column_map[col].name          name = self.column_map[col].quoted_name
420          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
421                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
422          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
423    
424      def UniqueValues(self, col):      def UniqueValues(self, col):
425          cursor = self.db.cursor()          cursor = self.db.cursor()
426          name = self.column_map[col].name          name = self.column_map[col].quoted_name
427          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
428                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
429          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
430    
431      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 211  class PostGISTable: Line 436  class PostGISTable:
436              comparison = "="              comparison = "="
437    
438          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
439              right_template = right.name              right_template = right.quoted_name
440              params = ()              params = ()
441          else:          else:
442              right_template = "%s"              right_template = "%s"
443              params = (right,)              params = (right,)
444    
445          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT %s FROM %s WHERE %s %s %s ORDER BY %s;" \
446                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_id_column, self.quoted_tablename,
447                       left.quoted_name, comparison, right_template,
448                       self.quoted_id_column)
449    
450          cursor = self.db.cursor()          cursor = self.db.cursor()
451          cursor.execute(query, params)          cursor.execute(query, params)
# Line 260  class PostGISShape: Line 487  class PostGISShape:
487    
488    
489  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
490                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
491                     "LINESTRING": SHAPETYPE_ARC,
492                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
493                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
494    
# Line 268  class PostGISShapeStore(PostGISTable): Line 497  class PostGISShapeStore(PostGISTable):
497    
498      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
499    
500        def __init__(self, db, tablename, id_column = "gid",
501                     geometry_column = None):
502            """Initialize the PostGISShapeStore.
503    
504            The db parameter should be an instance of PostGISConnection and
505            tablename the name of a table in the database represented by db.
506    
507            The id_column parameter should be the name of a column in the
508            table that can be used to identify rows.  The column must have
509            the type integer and be unique and not null.
510    
511            The geometry_column paramter, if given, should be the name of
512            the geometry column to use.  If the name given is not a geometry
513            column, raise a ValueError.
514    
515            If no geometry_column is given, the table must have exactly one
516            geometry column.  If it has more than one and the
517            geometry_column is not given, a ValueError will be raised.
518            """
519            self.geometry_column = geometry_column
520            self.geometry_column_was_given = geometry_column is not None
521            PostGISTable.__init__(self, db, tablename, id_column)
522    
523            # For convenience, we have a quoted version of the geometry
524            # column in self.quoted_geo_col
525            self.quoted_geo_col = quote_identifier(self.geometry_column)
526    
527        def _fetch_table_information(self):
528            """Extend inherited method to retrieve the SRID and shape type"""
529            PostGISTable._fetch_table_information(self)
530    
531            # First, try to get it from the geometry_columns table.
532            cursor = self.db.cursor()
533            cursor.execute("SELECT srid, type FROM geometry_columns"
534                           " WHERE f_table_name = %s AND f_geometry_column=%s",
535                           (self.tablename, self.geometry_column))
536            row = cursor.fetchone()
537            if row is not None:
538                self.srid = row[0]
539                self.shape_type = shapetype_map.get(row[1])
540                return
541    
542            # The table is probably really a view and thus not in
543            # geometry_columns.  Use a different approach
544            cursor = self.db.cursor()
545            cursor.execute("SELECT DISTINCT SRID(%s) FROM %s;" %
546                           (quote_identifier(self.geometry_column),
547                            self.tablename))
548            row = cursor.fetchone()
549            if row is not None:
550                self.srid = row[0]
551                # Try to see whether there's another one
552                row = cursor.fetchone()
553                if row is not None:
554                    # There are at least two different srids.  We don't
555                    # support that
556                    self.srid = None
557    
558            cursor = self.db.cursor()
559            cursor.execute("SELECT DISTINCT GeometryType(%s) FROM %s;"
560                           % (quote_identifier(self.geometry_column),
561                              self.tablename))
562            row = cursor.fetchone()
563            if row is not None:
564                self.shape_type = shapetype_map.get(row[0])
565                # Try to see whether there's another one
566                row = cursor.fetchone()
567                if row is not None:
568                    # There are at least two different srids.  We don't
569                    # support that
570                    self.shape_type = None
571    
572        def _create_col_from_description(self, index, description):
573            """Extend the inherited method to find geometry columns
574    
575            If the column indicated by the parameters is a geometry column,
576            record its name in self.geometry_column and a quoted version in
577            self.quoted_geo_col.  In any case return the return value of the
578            inherited method.
579            """
580            col = PostGISTable._create_col_from_description(self, index,
581                                                            description)
582            col_name, col_type = description[:2]
583            if self.geometry_column_was_given:
584                if (col_name == self.geometry_column
585                    and col_type != self.db.geometry_type):
586                    raise TypeError("Column %s in %s is not a geometry column"
587                                    % (self.geometry_column, self.tablename))
588            else:
589                if col is None:
590                    if description[1] == self.db.geometry_type:
591                        # The column is a geometry column.  If the name of
592                        # the geometry column was not given to the
593                        # constructor, and we encounter two geometry
594                        # columns, raise a value error
595                        if self.geometry_column is None:
596                            self.geometry_column = description[0]
597                        else:
598                            raise TypeError("Table %s has two geometry columns"
599                                            " and no column name was given"
600                                            % (self.tablename,))
601            return col
602    
603      def Table(self):      def Table(self):
604          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
605          return self          return self
# Line 277  class PostGISShapeStore(PostGISTable): Line 609  class PostGISShapeStore(PostGISTable):
609          """          """
610          return None          return None
611    
612        def GeometryColumn(self):
613            """Return the column description object for the geometry column
614    
615            There's currently no FIELDTYPE constant for this column, so the
616            return value is not a regular column object that could also be
617            returned from e.g. the Column() method.  Only the name attribute
618            of the return value is meaningful at the moment.
619            """
620            return PostGISColumn(self.geometry_column, None, None)
621    
622      def ShapeType(self):      def ShapeType(self):
623          """Return the type of the shapes in the shapestore."""          """Return the type of the shapes in the shapestore."""
624          cursor = self.db.cursor()          return self.shape_type
         cursor.execute("SELECT type FROM geometry_columns WHERE"  
                        " f_table_name=%s", (self.tablename,))  
         result = cursor.fetchone()[0]  
         cursor.close()  
         return shapetype_map[result]  
625    
626      def RawShapeFormat(self):      def RawShapeFormat(self):
627          """Return the raw data format of the shape data.          """Return the raw data format of the shape data.
# Line 305  class PostGISShapeStore(PostGISTable): Line 642  class PostGISShapeStore(PostGISTable):
642          y=[]          y=[]
643          cursor = self.db.cursor()          cursor = self.db.cursor()
644          try:          try:
645              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
646                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
647              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
648                # calculate the bounding box by hand from that
649                cursor.execute("SELECT extent(%s) FROM %s;"
650                               % (self.quoted_geo_col, self.quoted_tablename))
651              result = cursor.fetchone()              result = cursor.fetchone()
652              while result:              if result[0]:
653                  result = result[0]                  (minx, miny), (maxx, maxy) \
654                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
655                  # 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()  
656          finally:          finally:
657              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
658    
659      def Shape(self, shapeid):      def Shape(self, shapeid):
660          cursor = self.db.cursor()          cursor = self.db.cursor()
661          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE %s=%d"
662                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename,
663                              self.quoted_id_column, shapeid))
664          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
665          cursor.close()          cursor.close()
666          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
667    
668        def AllShapes(self):
669            cursor = self.db.cursor()
670            cursor.execute("SELECT %s, AsText(%s) FROM %s ORDER BY %s"
671                           % (self.quoted_id_column, self.quoted_geo_col,
672                              self.quoted_tablename, self.quoted_id_column))
673            while 1:
674                result = cursor.fetchone()
675                if result is None:
676                    return
677                yield PostGISShape(result[0], result[1])
678    
679    
680      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
681          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
682          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 347  class PostGISShapeStore(PostGISTable): Line 685  class PostGISShapeStore(PostGISTable):
685                  % (left, bottom, left, top, right, top, right, bottom,                  % (left, bottom, left, top, right, top, right, bottom,
686                     left, bottom))                     left, bottom))
687          cursor = self.db.cursor()          cursor = self.db.cursor()
688          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT %(gid)s, AsText(%(geom)s) FROM %(table)s"
689                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %(geom)s && GeometryFromText('%(box)s', %(srid)d)"
690                         % (self.geometry_column, self.tablename,                         " ORDER BY %(gid)s"
691                            self.geometry_column, geom))                         % {"table": self.quoted_tablename,
692                              "geom": self.quoted_geo_col,
693                              "gid": self.quoted_id_column,
694                              "box": geom,
695                              "srid": self.srid})
696          while 1:          while 1:
697              result = cursor.fetchone()              result = cursor.fetchone()
698              if result is None:              if result is None:
699                  raise StopIteration                  return
700              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1620  
changed lines
  Added in v.2734
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26