/[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 2472 by bh, Thu Dec 16 15:18: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 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 48  class PostGISConnection: Line 93  class PostGISConnection:
93          self.user = user          self.user = user
94          self.password = password          self.password = password
95          self.dbtype = dbtype          self.dbtype = dbtype
96            self.connect()
97    
98        def connect(self):
99            """Internal: Establish the database connection"""
100          params = []          params = []
101          for name in ("host", "port", "dbname", "user", "password"):          for name in ("host", "port", "dbname", "user", "password"):
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          # determine the OID for the geometry type. This is PostGIS          # determine the OID for the geometry type. This is PostGIS
111          # specific.          # specific.
# Line 67  class PostGISConnection: Line 119  class PostGISConnection:
119          else:          else:
120              raise ValueError("Can't determine postgres type of geometries")              raise ValueError("Can't determine postgres type of geometries")
121    
122        def BriefDescription(self):
123            """Return a brief, one-line description of the connection
124    
125            The return value is suitable for a list box of all database
126            connections.
127            """
128            return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
129                    % self.__dict__)
130    
131        def MatchesParameters(self, parameters):
132            """Return whether the connection matches the dictionary of parameters
133    
134            Return whether instatiating the connection with the given
135            parameters would establish essentially the same connection as
136            self. The connection is essentially the same if the same
137            database (identified by host, port and databasename) is accessed
138            as the same user.
139            """
140            return (parameters["host"] == self.host
141                    and parameters["port"] == self.port
142                    and parameters["dbname"] == self.dbname
143                    and parameters["user"] == self.user)
144    
145      def Close(self):      def Close(self):
146          """Close the database connection"""          """Close the database connection"""
147          self.connection.close()          self.connection.close()
148    
149      def GeometryTables(self):      def GeometryTables(self):
150          """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"""
151    
152            # The query is basically taken from the psql v. 7.2.1.  When
153            # started with -E it prints the queries used for internal
154            # commands such as \d, which does mostly what we need here.
155          cursor = self.connection.cursor()          cursor = self.connection.cursor()
156          cursor.execute("SELECT f_table_name FROM geometry_columns;")          cursor.execute("SELECT c.relname FROM pg_class c"
157                           " WHERE c.relkind IN ('r', 'v')"
158                                 # Omit the system tables
159                                 " AND c.relname !~ '^pg_'"
160                                 # Omit the special PostGIS tables
161                                 " AND c.relname NOT IN ('geometry_columns',"
162                                                       " 'spatial_ref_sys')"
163                                " AND %d in (SELECT a.atttypid FROM pg_attribute a"
164                                           " WHERE a.attrelid = c.oid)"
165                           " ORDER BY c.relname;", (self.geometry_type,))
166          result = [row[0] for row in cursor.fetchall()]          result = [row[0] for row in cursor.fetchall()]
167          self.connection.commit()          self.connection.commit()
168          return result          return result
169    
170        def table_columns(self, tablename):
171            """Experimental: return information about the columns of a table
172    
173            Return value is a list of (name, type) pairs where name is the
174            name of the column and type either one of the field type columns
175            or the string 'geometry' indicating a geometry column.
176    
177            The intended use of this method is for table selection dialogs
178            which need to determine which columns are usable as id or
179            geometry columns respectively.  Suitable id columns will have
180            type FIELDTYPE_INT and geometry columns will have 'geometry'.
181            """
182            result = []
183            cursor = self.connection.cursor()
184    
185            # This query is taken basically from the \d command of psql
186            # 7.2.1
187            cursor.execute("SELECT a.attname, a.atttypid, a.attnum"
188                           " FROM pg_class c, pg_attribute a"
189                                " WHERE c.relname = %s AND a.attrelid = c.oid"
190                           " ORDER BY a.attnum;", (tablename,))
191    
192            for row in cursor.fetchall():
193                col_name, col_type, col_attnum = row
194                col = None
195                if col_attnum < 1:
196                    # It's a system column.  Only the OID is interesting
197                    # here
198                    if col_name == "oid":
199                        col = (col_name, _raw_type_map[col_type])
200                else:
201                    # If it's an integer
202                    thuban_type = _raw_type_map.get(col_type)
203                    if thuban_type is not None:
204                        col = (col_name, thuban_type)
205                    elif row[1] == self.geometry_type:
206                        col = (col_name, "geometry")
207                if col is not None:
208                    result.append(col)
209    
210            return result
211    
212      def cursor(self):      def cursor(self):
213          """Return a DB API 2.0 cursor for the database"""          """Return a DB API 2.0 cursor for the database"""
214          return self.connection.cursor()          return self.connection.cursor()
# Line 87  class PostGISConnection: Line 217  class PostGISConnection:
217    
218  class PostGISColumn:  class PostGISColumn:
219    
220      """Column description for a PostGISTable"""      """Column description for a PostGISTable
221    
222        In addition to the normal column object attributes name, type and
223        index, PostGISColumn objects have a quoted_name attribute which
224        contains a quoted version of name for use in SQL statements. The
225        quoted_name attribute is mainly intended for internal use by the
226        PostGISTable class.
227        """
228    
229      def __init__(self, name, type, index):      def __init__(self, name, type, index):
230          self.name = name          self.name = name
231            self.quoted_name = quote_identifier(name)
232          self.type = type          self.type = type
233          self.index = index          self.index = index
234    
235    
236  class PostGISTable:  class PostGISTable:
237    
238      """A Table in a PostGIS database"""      """A Table in a PostGIS database
239    
240        A PostgreSQL table may contain columns with types not (yet)
241        supported by Thuban. Instances of this class ignore those columns
242        and pretend they don't exist, i.e. they won't show up in the column
243        descriptions returned by Columns() and other methods.
244        """
245    
246      def __init__(self, db, tablename):      def __init__(self, db, tablename, id_column = None):
247          """Initialize the PostGISTable.          """Initialize the PostGISTable.
248    
249          The db parameter should be an instance of PostGISConnection and          The db parameter should be an instance of PostGISConnection and
250          tablename the name of a table in the database represented by db.          tablename the name of a table in the database represented by db.
251    
252            The id_column parameter should be the name of a column in the
253            table that can be used to identify rows.  The column must have
254            the type integer and be unique and not null.
255    
256            For backwards compatibility reasons, the id_column parameter is
257            optional.  If not given the table must have a column called
258            'gid' which is used as the id_column.  New code should always
259            provide this parameter.
260          """          """
261          self.db = db          self.db = db
262          self.tablename = tablename          self.tablename = tablename
263            # Tablename quoted for use in SQL statements.
264            self.quoted_tablename = quote_identifier(tablename)
265    
266            if not id_column:
267                id_column = "gid"
268            self.id_column = id_column
269            # id column name quoted for use in SQL statements.
270            self.quoted_id_column = quote_identifier(id_column)
271    
272            # Map column names and indices to column objects.
273          self.column_map = {}          self.column_map = {}
274    
275          self._fetch_table_information()          self._fetch_table_information()
276    
277      def _fetch_table_information(self):      def _fetch_table_information(self):
278          """Internal: Update information about the table"""          """Internal: Update information about the table"""
279          self.columns = []          self.columns = []
280          cursor = self.db.cursor()          cursor = self.db.cursor()
281          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
282          description = cursor.description          description = cursor.description
283    
284          for i in range(len(description)):          for i in range(len(description)):
285              for pgtyp, tabletyp in type_map:              col = self._create_col_from_description(i, description[i])
286                  if pgtyp == description[i][1]:              if col is not None:
287                      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)  
288    
289          for col in self.columns:          for col in self.columns:
290              self.column_map[col.name] = col              self.column_map[col.name] = col
# Line 137  class PostGISTable: Line 292  class PostGISTable:
292    
293          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
294          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
295                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
296                                self.tablename))                                           for col in self.columns]),
297                                  self.quoted_tablename))
298    
299        def _create_col_from_description(self, index, description):
300            """Return the column object for the column described by description
301    
302            The parameter index is the index of the column.  The description
303            is a sequence taken from the cursor's description attribute for
304            the column.  That means description[0] is the name of the column
305            and description[1] the type.
306    
307            Return None if the column can't be represented for some reason,
308            e.g. because its type is not yet supported or needs to be
309            treated in some special way.  Derived classes may extend this
310            method.
311            """
312            for pgtyp, tabletyp in type_map:
313                if pgtyp == description[1]:
314                    return PostGISColumn(description[0], tabletyp,
315                                         len(self.columns))
316            return None
317    
318        def DBConnection(self):
319            """Return the dbconnection used by the table"""
320            return self.db
321    
322        def IDColumn(self):
323            """Return the column description object for the id column.
324    
325            If the oid column was used as the id column, the return value is
326            not one of the regular column objects that would be returned by
327            e.g. the Column() method, but it still has meaningful name
328            attribute.
329            """
330            if self.id_column == "oid":
331                return PostGISColumn(self.id_column, table.FIELDTYPE_INT, None)
332            return self.column_map[self.id_column]
333    
334        def TableName(self):
335            """Return the name of the table in the database"""
336            return self.tablename
337    
338        def Title(self):
339            """Return the title of the table.
340    
341            The title is currently fixed and equal to the tablename
342            """
343            return self.tablename
344    
345      def Dependencies(self):      def Dependencies(self):
346          """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 361  class PostGISTable:
361    
362      def NumRows(self):      def NumRows(self):
363          cursor = self.db.cursor()          cursor = self.db.cursor()
364          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
365          return cursor.fetchone()[0]          return cursor.fetchone()[0]
366    
367      def ReadRowAsDict(self, row):      def RowIdToOrdinal(self, gid):
368            """Return the row ordinal given its id"""
369          cursor = self.db.cursor()          cursor = self.db.cursor()
370          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          cursor.execute("SELECT count(*) FROM %s WHERE %s < %d;"
371                           % (self.quoted_tablename, self.quoted_id_column, gid))
372            return cursor.fetchone()[0]
373    
374        def RowOrdinalToId(self, num):
375            """Return the rowid for given its ordinal"""
376            cursor = self.db.cursor()
377            cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d;"
378                           % (self.quoted_id_column, self.quoted_tablename, num))
379            return cursor.fetchone()[0]
380    
381        def ReadRowAsDict(self, row, row_is_ordinal = 0):
382            cursor = self.db.cursor()
383            if row_is_ordinal:
384                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
385            else:
386                stmt = self.query_stmt + " WHERE %s = %d" % (self.quoted_id_column,
387                                                             row)
388            cursor.execute(stmt)
389          result = {}          result = {}
390          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
391              result[col.name] = value              result[col.name] = value
392          return result          return result
393    
394      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
395          cursor = self.db.cursor()          cursor = self.db.cursor()
396          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
397                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
398                        (self.column_map[col].quoted_name, self.quoted_tablename,
399                         row))
400            else:
401                stmt = ("SELECT %s FROM %s WHERE %s = %d" %
402                        (self.column_map[col].quoted_name, self.quoted_tablename,
403                         self.quoted_id_column, row))
404            cursor.execute(stmt)
405          return cursor.fetchone()[0]          return cursor.fetchone()[0]
406    
407      def ValueRange(self, col):      def ValueRange(self, col):
408          cursor = self.db.cursor()          cursor = self.db.cursor()
409          name = self.column_map[col].name          name = self.column_map[col].quoted_name
410          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
411                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
412          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
413    
414      def UniqueValues(self, col):      def UniqueValues(self, col):
415          cursor = self.db.cursor()          cursor = self.db.cursor()
416          name = self.column_map[col].name          name = self.column_map[col].quoted_name
417          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
418                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
419          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
420    
421      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 198  class PostGISTable: Line 426  class PostGISTable:
426              comparison = "="              comparison = "="
427    
428          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
429              right_template = right.name              right_template = right.quoted_name
430              params = ()              params = ()
431          else:          else:
432              right_template = "%s"              right_template = "%s"
433              params = (right,)              params = (right,)
434    
435          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT %s FROM %s WHERE %s %s %s ORDER BY %s;" \
436                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_id_column, self.quoted_tablename,
437                       left.quoted_name, comparison, right_template,
438                       self.quoted_id_column)
439    
440          cursor = self.db.cursor()          cursor = self.db.cursor()
441          cursor.execute(query, params)          cursor.execute(query, params)
# Line 247  class PostGISShape: Line 477  class PostGISShape:
477    
478    
479  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
480                     "MULTIPOLYGON": SHAPETYPE_POLYGON,
481                     "LINESTRING": SHAPETYPE_ARC,
482                   "MULTILINESTRING": SHAPETYPE_ARC,                   "MULTILINESTRING": SHAPETYPE_ARC,
483                   "POINT": SHAPETYPE_POINT}                   "POINT": SHAPETYPE_POINT}
484    
# Line 255  class PostGISShapeStore(PostGISTable): Line 487  class PostGISShapeStore(PostGISTable):
487    
488      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
489    
490        def __init__(self, db, tablename, id_column = "gid",
491                     geometry_column = None):
492            """Initialize the PostGISShapeStore.
493    
494            The db parameter should be an instance of PostGISConnection and
495            tablename the name of a table in the database represented by db.
496    
497            The id_column parameter should be the name of a column in the
498            table that can be used to identify rows.  The column must have
499            the type integer and be unique and not null.
500    
501            The geometry_column paramter, if given, should be the name of
502            the geometry column to use.  If the name given is not a geometry
503            column, raise a ValueError.
504    
505            If no geometry_column is given, the table must have exactly one
506            geometry column.  If it has more than one and the
507            geometry_column is not given, a ValueError will be raised.
508            """
509            self.geometry_column = geometry_column
510            self.geometry_column_was_given = geometry_column is not None
511            PostGISTable.__init__(self, db, tablename, id_column)
512    
513            # For convenience, we have a quoted version of the geometry
514            # column in self.quoted_geo_col
515            self.quoted_geo_col = quote_identifier(self.geometry_column)
516    
517        def _fetch_table_information(self):
518            """Extend inherited method to retrieve the SRID and shape type"""
519            PostGISTable._fetch_table_information(self)
520    
521            # First, try to get it from the geometry_columns table.
522            cursor = self.db.cursor()
523            cursor.execute("SELECT srid, type FROM geometry_columns"
524                           " WHERE f_table_name = %s AND f_geometry_column=%s",
525                           (self.tablename, self.geometry_column))
526            row = cursor.fetchone()
527            if row is not None:
528                self.srid = row[0]
529                self.shape_type = shapetype_map.get(row[1])
530                return
531    
532            # The table is probably really a view and thus not in
533            # geometry_columns.  Use a different approach
534            cursor = self.db.cursor()
535            cursor.execute("SELECT DISTINCT SRID(%s) FROM %s;" %
536                           (quote_identifier(self.geometry_column),
537                            self.tablename))
538            row = cursor.fetchone()
539            if row is not None:
540                self.srid = row[0]
541                # Try to see whether there's another one
542                row = cursor.fetchone()
543                if row is not None:
544                    # There are at least two different srids.  We don't
545                    # support that
546                    self.srid = None
547    
548            cursor = self.db.cursor()
549            cursor.execute("SELECT DISTINCT GeometryType(%s) FROM %s;"
550                           % (quote_identifier(self.geometry_column),
551                              self.tablename))
552            row = cursor.fetchone()
553            if row is not None:
554                self.shape_type = shapetype_map.get(row[0])
555                # Try to see whether there's another one
556                row = cursor.fetchone()
557                if row is not None:
558                    # There are at least two different srids.  We don't
559                    # support that
560                    self.shape_type = None
561    
562        def _create_col_from_description(self, index, description):
563            """Extend the inherited method to find geometry columns
564    
565            If the column indicated by the parameters is a geometry column,
566            record its name in self.geometry_column and a quoted version in
567            self.quoted_geo_col.  In any case return the return value of the
568            inherited method.
569            """
570            col = PostGISTable._create_col_from_description(self, index,
571                                                            description)
572            col_name, col_type = description[:2]
573            if self.geometry_column_was_given:
574                if (col_name == self.geometry_column
575                    and col_type != self.db.geometry_type):
576                    raise TypeError("Column %s in %s is not a geometry column"
577                                    % (self.geometry_column, self.tablename))
578            else:
579                if col is None:
580                    if description[1] == self.db.geometry_type:
581                        # The column is a geometry column.  If the name of
582                        # the geometry column was not given to the
583                        # constructor, and we encounter two geometry
584                        # columns, raise a value error
585                        if self.geometry_column is None:
586                            self.geometry_column = description[0]
587                        else:
588                            raise TypeError("Table %s has two geometry columns"
589                                            " and no column name was given"
590                                            % (self.tablename,))
591            return col
592    
593      def Table(self):      def Table(self):
594          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
595          return self          return self
# Line 264  class PostGISShapeStore(PostGISTable): Line 599  class PostGISShapeStore(PostGISTable):
599          """          """
600          return None          return None
601    
602        def GeometryColumn(self):
603            """Return the column description object for the geometry column
604    
605            There's currently no FIELDTYPE constant for this column, so the
606            return value is not a regular column object that could also be
607            returned from e.g. the Column() method.  Only the name attribute
608            of the return value is meaningful at the moment.
609            """
610            return PostGISColumn(self.geometry_column, None, None)
611    
612      def ShapeType(self):      def ShapeType(self):
613          """Return the type of the shapes in the shapestore."""          """Return the type of the shapes in the shapestore."""
614          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]  
615    
616      def RawShapeFormat(self):      def RawShapeFormat(self):
617          """Return the raw data format of the shape data.          """Return the raw data format of the shape data.
# Line 292  class PostGISShapeStore(PostGISTable): Line 632  class PostGISShapeStore(PostGISTable):
632          y=[]          y=[]
633          cursor = self.db.cursor()          cursor = self.db.cursor()
634          try:          try:
635              stmt = ("SELECT AsText(Envelope(%s)) FROM %s;"              # Using the extent function is postgis specific. An OGC
636                      % (self.geometry_column, self.tablename))              # Simple Features compliant solution would be to use a query
637              cursor.execute(stmt)              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
638                # calculate the bounding box by hand from that
639                cursor.execute("SELECT extent(%s) FROM %s;"
640                               % (self.quoted_geo_col, self.quoted_tablename))
641              result = cursor.fetchone()              result = cursor.fetchone()
642              while result:              if result[0]:
643                  result = result[0]                  (minx, miny), (maxx, maxy) \
644                  # Here we must do some parsing through the result string                        = wellknowntext.parse_wkt_thuban(result[0])[0]
645                  # 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()  
646          finally:          finally:
647              cursor.close()              cursor.close()
         if not x:  
             # Empty table  
             return None  
         return (min(x), min(y), max(x), max(y))  
648    
649      def Shape(self, shapeid):      def Shape(self, shapeid):
650          cursor = self.db.cursor()          cursor = self.db.cursor()
651          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE %s=%d"
652                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename,
653                              self.quoted_id_column, shapeid))
654          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
655          cursor.close()          cursor.close()
656          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
657    
658        def AllShapes(self):
659            cursor = self.db.cursor()
660            cursor.execute("SELECT %s, AsText(%s) FROM %s ORDER BY %s"
661                           % (self.quoted_id_column, self.quoted_geo_col,
662                              self.quoted_tablename, self.quoted_id_column))
663            while 1:
664                result = cursor.fetchone()
665                if result is None:
666                    return
667                yield PostGISShape(result[0], result[1])
668    
669    
670      def ShapesInRegion(self, bbox):      def ShapesInRegion(self, bbox):
671          """Generate all shapes overlapping the region given by bbox."""          """Generate all shapes overlapping the region given by bbox."""
672          # IMPORTANT:This will work for PostGIS < 0.8          # IMPORTANT:This will work for PostGIS < 0.8
# Line 334  class PostGISShapeStore(PostGISTable): Line 675  class PostGISShapeStore(PostGISTable):
675                  % (left, bottom, left, top, right, top, right, bottom,                  % (left, bottom, left, top, right, top, right, bottom,
676                     left, bottom))                     left, bottom))
677          cursor = self.db.cursor()          cursor = self.db.cursor()
678          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT %(gid)s, AsText(%(geom)s) FROM %(table)s"
679                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %(geom)s && GeometryFromText('%(box)s', %(srid)d)"
680                         % (self.geometry_column, self.tablename,                         " ORDER BY %(gid)s"
681                            self.geometry_column, geom))                         % {"table": self.quoted_tablename,
682                              "geom": self.quoted_geo_col,
683                              "gid": self.quoted_id_column,
684                              "box": geom,
685                              "srid": self.srid})
686          while 1:          while 1:
687              result = cursor.fetchone()              result = cursor.fetchone()
688              if result is None:              if result is None:
689                  raise StopIteration                  return
690              yield PostGISShape(result[0], result[1])              yield PostGISShape(result[0], result[1])
Legend:
Removed from v.1605  
changed lines
  Added in v.2472
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26