/[thuban]/branches/WIP-pyshapelib-bramz/test/postgissupport.py
ViewVC logotype

Diff of /branches/WIP-pyshapelib-bramz/test/postgissupport.py

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1679 by bh, Thu Aug 28 14:23:08 2003 UTC revision 2106 by bh, Fri Mar 12 12:59:33 2004 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003 by Intevation GmbH  # Copyright (C) 2003, 2004 by Intevation GmbH
2  # Authors:  # Authors:
3  # Bernhard Herzog <[email protected]>  # Bernhard Herzog <[email protected]>
4  #  #
# Line 198  class PostgreSQLServer: Line 198  class PostgreSQLServer:
198          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],
199                      os.path.join(self.dbdir, "pg_ctl-stop.log"))                      os.path.join(self.dbdir, "pg_ctl-stop.log"))
200    
201      def new_postgis_db(self, dbname, tables = None):      def new_postgis_db(self, dbname, tables = None, reference_systems = None,
202                           views = None):
203          """Create and return a new PostGISDatabase object using self as server          """Create and return a new PostGISDatabase object using self as server
204          """          """
205          db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables)          db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables,
206                                 reference_systems = reference_systems,
207                                 views = views)
208          db.initdb()          db.initdb()
209          self.known_dbs[dbname] = db          self.known_dbs[dbname] = db
210          return db          return db
211    
212      def get_static_data_db(self, dbname, tables):      def get_static_data_db(self, dbname, tables, reference_systems, views):
213          """Return a PostGISDatabase for a database with the given static data          """Return a PostGISDatabase for a database with the given static data
214    
215          If no databasse of the name dbname exists, create a new one via          If no databasse of the name dbname exists, create a new one via
# Line 216  class PostgreSQLServer: Line 219  class PostgreSQLServer:
219          indicated data, return that. If the already existing db uses          indicated data, return that. If the already existing db uses
220          different data raise a value error.          different data raise a value error.
221    
222          The tables argument should be a sequence of table specifications          If the database doesn't exist, create a new one via
223          where each specifications is a (tablename, shapefilename) pair.          self.new_postgis_db.
224    
225            The parameters tables and reference_systems have the same
226            meaning as for new_postgis_db.
227          """          """
228          db = self.known_dbs.get(dbname)          db = self.known_dbs.get(dbname)
229          if db is not None:          if db is not None:
230              if db.has_data(tables):              if db.has_data(tables, reference_systems, views):
231                  return db                  return db
232              raise ValueError("PostGISDatabase named %r doesn't have tables %r"              raise ValueError("PostGISDatabase named %r doesn't have tables %r"
233                               % (dbname, tables))                               % (dbname, tables))
234          return self.new_postgis_db(dbname, tables)          return self.new_postgis_db(dbname, tables, reference_systems, views)
235    
236      def get_default_static_data_db(self):      def get_default_static_data_db(self):
237          dbname = "PostGISStaticTests"          dbname = "PostGISStaticTests"
238            srids = [(1, "proj=longlat datum=WGS84")]
239          tables = [          tables = [
240              # Direct copies of the shapefiles. The shapeids are exactly              # Direct copies of the shapefiles. The shapeids are exactly
241              # the same.              # the same, except where changed with "gid_offset", of
242                # course.  Note that the test implementation requires that
243                # all the landmard tables use an gid_offset of 1000.
244              ("landmarks", os.path.join("..", "Data", "iceland",              ("landmarks", os.path.join("..", "Data", "iceland",
245                                         "cultural_landmark-point.shp"),                                         "cultural_landmark-point.shp"),
246               [("gid_offset", 1000)]),               [("gid_offset", 1000)]),
# Line 244  class PostgreSQLServer: Line 253  class PostgreSQLServer:
253              ("political_multi", os.path.join("..", "Data", "iceland",              ("political_multi", os.path.join("..", "Data", "iceland",
254                                               "political.shp"),                                               "political.shp"),
255               [("force_wkt_type", "MULTIPOLYGON")]),               [("force_wkt_type", "MULTIPOLYGON")]),
256    
257                # Copy of landmarks but using an srid != -1
258                ("landmarks_srid", os.path.join("..", "Data", "iceland",
259                                           "cultural_landmark-point.shp"),
260                 [("gid_offset", 1000),
261                  ("srid", 1)]),
262    
263                # Copy of landmarks with a gid column called "point_id" instead
264                # of "gid" and using an srid != -1.
265                ("landmarks_point_id", os.path.join("..", "Data", "iceland",
266                                                    "cultural_landmark-point.shp"),
267                 [("gid_offset", 1000),
268                  ("srid", 1),
269                  ("gid_column", "point_id")]),
270              ]              ]
271          return self.get_static_data_db(dbname, tables)          views = [("v_landmarks", "SELECT * FROM landmarks_point_id")]
272            return self.get_static_data_db(dbname, tables, srids, views)
273    
274      def connection_params(self, user):      def connection_params(self, user):
275          """Return the connection parameters for the given user          """Return the connection parameters for the given user
# Line 323  class PostGISDatabase: Line 347  class PostGISDatabase:
347    
348      """A PostGIS database in a PostgreSQLServer"""      """A PostGIS database in a PostgreSQLServer"""
349    
350      def __init__(self, server, postgis_sql, dbname, tables = None):      def __init__(self, server, postgis_sql, dbname, tables = None,
351                     reference_systems = (), views = None):
352          """Initialize the PostGISDatabase          """Initialize the PostGISDatabase
353    
354          Parameters:          Parameters:
# Line 343  class PostGISDatabase: Line 368  class PostGISDatabase:
368                  shapefile or (tablename, shapefilename, extraargs)                  shapefile or (tablename, shapefilename, extraargs)
369                  triples. The extraargs should be a list of key, value                  triples. The extraargs should be a list of key, value
370                  pairs to use as keyword arguments to upload_shapefile.                  pairs to use as keyword arguments to upload_shapefile.
371    
372                reference_systems -- Optional description of spatial
373                    reference systems.  If given, it should be a sequence of
374                    (srid, params) pairs where srid is the srid defined by
375                    the proj4 paramter string params.  The srid can be given
376                    as an extra parameter in the tables list.
377    
378                views -- Optional description of views.  If given it should
379                    be a list of (viewname, select_stmt) pairs where
380                    viewname is the name of the view to be created and
381                    select_stmt is the select statement to use as the basis.
382                    The views will be created after the tables and may refer
383                    to them in the select_stmt.
384          """          """
385          self.server = server          self.server = server
386          self.postgis_sql = postgis_sql          self.postgis_sql = postgis_sql
387          self.dbname = dbname          self.dbname = dbname
388          self.tables = tables          self.tables = tables
389            self.views = views
390            if reference_systems:
391                self.reference_systems = reference_systems
392            else:
393                # Make sure that it's a sequence we can iterate over even if
394                # the parameter's None
395                self.reference_systems = ()
396    
397      def initdb(self):      def initdb(self):
398          """Remove the old db directory and create and initialize a new database          """Remove the old db directory and create and initialize a new database
# Line 373  class PostGISDatabase: Line 418  class PostGISDatabase:
418    
419          self.server.execute_sql(self.dbname, "admin",          self.server.execute_sql(self.dbname, "admin",
420                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")
421            self.server.execute_sql(self.dbname, "admin",
422                                    "GRANT SELECT ON spatial_ref_sys TO PUBLIC;")
423    
424            for srid, params in self.reference_systems:
425                self.server.execute_sql(self.dbname, "admin",
426                                        "INSERT INTO spatial_ref_sys VALUES"
427                                        " (%d, '', %d, '', '%s');"
428                                        % (srid, srid, params))
429          if self.tables is not None:          if self.tables is not None:
430              def unpack(item):              def unpack(item):
431                  extra = {"force_wkt_type": None, "gid_offset": 0}                  extra = {"force_wkt_type": None, "gid_offset": 0,
432                             "srid": -1}
433                  if len(info) == 2:                  if len(info) == 2:
434                      tablename, shapefile = info                      tablename, shapefile = info
435                  else:                  else:
# Line 389  class PostGISDatabase: Line 442  class PostGISDatabase:
442                  tablename, shapefile, kw = unpack(info)                  tablename, shapefile, kw = unpack(info)
443                  upload_shapefile(shapefile, self, tablename, **kw)                  upload_shapefile(shapefile, self, tablename, **kw)
444    
445      def has_data(self, tables):          if self.views is not None:
446          return self.tables == tables              for viewname, select_stmt in self.views:
447                    self.server.execute_sql(self.dbname, "admin",
448                                            "CREATE VIEW %s AS %s" % (viewname,
449                                                                      select_stmt))
450                    self.server.execute_sql(self.dbname, "admin",
451                                            "GRANT SELECT ON %s TO PUBLIC;"
452                                            % viewname)
453    
454        def has_data(self, tables, reference_systems, views):
455            return (self.tables == tables
456                    and self.reference_systems == reference_systems
457                    and self.views == views)
458    
459    
460  def find_postgis_sql():  def find_postgis_sql():
# Line 486  def skip_if_no_postgis(): Line 550  def skip_if_no_postgis():
550      if _cannot_run_postgis_tests:      if _cannot_run_postgis_tests:
551          raise support.SkipTest(_cannot_run_postgis_tests)          raise support.SkipTest(_cannot_run_postgis_tests)
552    
553    def skip_if_addgeometrycolumn_does_not_use_quote_ident():
554        """Skip a test if the AddGeometryColumn function doesn't use quote_ident
555    
556        If the AddGeometryColumn function doesn't use quote_ident it doesn't
557        support unusual table or column names properly, that is, it will
558        fail with errors for names that contain spaces or double quotes.
559    
560        The test performed by this function is a bit simplistic because it
561        only tests whether the string 'quote_ident' occurs anywhere in the
562        postgis.sql file. This will hopefully work because when this was
563        fixed in postgis CVS AddGeometryColumn was the first function to use
564        quote_ident.
565        """
566        f = file(find_postgis_sql())
567        content = f.read()
568        f.close()
569        if content.find("quote_ident") < 0:
570            raise support.SkipTest("AddGeometryColumn doesn't use quote_ident")
571    
572  def coords_to_point(coords):  def coords_to_point(coords):
573      """Return string with a WKT representation of the point in coords"""      """Return string with a WKT representation of the point in coords"""
574      x, y = coords[0]      x, y = coords[0]
# Line 520  wkt_converter = { Line 603  wkt_converter = {
603      }      }
604    
605  def upload_shapefile(filename, db, tablename, force_wkt_type = None,  def upload_shapefile(filename, db, tablename, force_wkt_type = None,
606                       gid_offset = 0):                       gid_offset = 0, gid_column = "gid", srid = -1):
607      """Upload a shapefile into a new database table      """Upload a shapefile into a new database table
608    
609      Parameters:      Parameters:
# Line 538  def upload_shapefile(filename, db, table Line 621  def upload_shapefile(filename, db, table
621    
622      gid_offset -- A number to add to the shapeid to get the value for      gid_offset -- A number to add to the shapeid to get the value for
623                  the gid column (default 0)                  the gid column (default 0)
624    
625        gid_column -- The name of the column with the shape ids.  Default
626                      'gid'.  If None, no gid column will be created.  The
627                      name is directly used in SQL statements, so if it
628                      contains unusualy characters the caller should provide
629                      a suitable quoted string.
630    
631        srid -- The srid of the spatial references system used by the table
632                and the data
633      """      """
634      import dbflib, shapelib      import dbflib, shapelib
635    
# Line 562  def upload_shapefile(filename, db, table Line 654  def upload_shapefile(filename, db, table
654                 dbflib.FTInteger: "INTEGER",                 dbflib.FTInteger: "INTEGER",
655                 dbflib.FTDouble: "DOUBLE PRECISION"}                 dbflib.FTDouble: "DOUBLE PRECISION"}
656    
657      insert_formats = ["%(gid)s"]      insert_formats = []
658      fields = ["gid INT"]      if gid_column:
659            insert_formats.append("%(gid)s")
660    
661        fields = []
662        fields_decl = []
663        if gid_column:
664            fields.append(gid_column)
665            fields_decl.append("%s INT" % gid_column)
666      for i in range(dbf.field_count()):      for i in range(dbf.field_count()):
667          ftype, name, width, prec = dbf.field_info(i)          ftype, name, width, prec = dbf.field_info(i)
668          fields.append("%s %s" % (name, typemap[ftype]))          fields.append(name)
669            fields_decl.append("%s %s" % (name, typemap[ftype]))
670          insert_formats.append("%%(%s)s" % name)          insert_formats.append("%%(%s)s" % name)
671      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,
672                                                ",\n    ".join(fields))                                                ",\n    ".join(fields_decl))
673      cursor.execute(stmt)      cursor.execute(stmt)
674      #print stmt      #print stmt
675    
# Line 580  def upload_shapefile(filename, db, table Line 680  def upload_shapefile(filename, db, table
680      convert = wkt_converter[wkttype]      convert = wkt_converter[wkttype]
681    
682      cursor.execute("select AddGeometryColumn('%(dbname)s',"      cursor.execute("select AddGeometryColumn('%(dbname)s',"
683                     "'%(tablename)s', 'the_geom', '-1', '%(wkttype)s', 2);"                     "'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);"
684                     % locals())                     % locals())
685        fields.append("the_geom")
686        insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)")
687    
688      insert_formats.append("GeometryFromText(%(the_geom)s, -1)")      insert = ("INSERT INTO %s (%s) VALUES (%s)"
689                  % (tablename, ", ".join(fields), ", ".join(insert_formats)))
     insert = ("INSERT INTO %s VALUES (%s)"  
               % (tablename, ", ".join(insert_formats)))  
690    
691      for i in range(numshapes):      for i in range(numshapes):
692          data = dbf.read_record(i)          data = dbf.read_record(i)
693          data["tablename"] = tablename          data["tablename"] = tablename
694          data["gid"] = i + gid_offset          if gid_column:
695                data["gid"] = i + gid_offset
696            data["srid"] = srid
697          data["the_geom"] = convert(shp.read_object(i).vertices())          data["the_geom"] = convert(shp.read_object(i).vertices())
698          #print insert % data          #print insert % data
699          cursor.execute(insert, data)          cursor.execute(insert, data)
Legend:
Removed from v.1679  
changed lines
  Added in v.2106
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26