/[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 1634 by bh, Fri Aug 22 16:55:19 2003 UTC revision 2459 by bh, Wed Dec 15 11:12:11 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 184  class PostgreSQLServer: Line 184  class PostgreSQLServer:
184              raise RuntimeError("postmaster didn't start")              raise RuntimeError("postmaster didn't start")
185    
186      def is_running(self):      def is_running(self):
187          """Return true a postmaster process is running on self.dbdir          """Return whether a postmaster process is running on self.dbdir
188    
189          This method runs pg_ctl status on the dbdir so even if the          This method runs pg_ctl status on the dbdir and returns True if
190          object has just been created it is possible that this method          that command succeeds and False otherwise.
191          returns true if there's still a postmaster process running for  
192          self.dbdir.          Note that it is possible that this method returns true even if
193            the PostgreSQLServer instance has just been created and
194            createdb() has not been called yet.  This can happen, for
195            instance, if the server has been started manually for debugging
196            purposes after a test suite run.
197          """          """
198          return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"])          return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"])
199    
# Line 198  class PostgreSQLServer: Line 202  class PostgreSQLServer:
202          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],
203                      os.path.join(self.dbdir, "pg_ctl-stop.log"))                      os.path.join(self.dbdir, "pg_ctl-stop.log"))
204    
205      def new_postgis_db(self, dbname, tables = None):      def new_postgis_db(self, dbname, tables = None, reference_systems = None,
206                           views = None):
207          """Create and return a new PostGISDatabase object using self as server          """Create and return a new PostGISDatabase object using self as server
208          """          """
209          db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables)          db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables,
210                                 reference_systems = reference_systems,
211                                 views = views)
212          db.initdb()          db.initdb()
213          self.known_dbs[dbname] = db          self.known_dbs[dbname] = db
214          return db          return db
215    
216      def get_static_data_db(self, dbname, tables):      def get_static_data_db(self, dbname, tables, reference_systems, views):
217          """Return a PostGISDatabase for a database with the given static data          """Return a PostGISDatabase for a database with the given static data
218    
219          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 223  class PostgreSQLServer:
223          indicated data, return that. If the already existing db uses          indicated data, return that. If the already existing db uses
224          different data raise a value error.          different data raise a value error.
225    
226          The tables argument should be a sequence of table specifications          If the database doesn't exist, create a new one via
227          where each specifications is a (tablename, shapefilename) pair.          self.new_postgis_db.
228    
229            The parameters tables and reference_systems have the same
230            meaning as for new_postgis_db.
231          """          """
232          db = self.known_dbs.get(dbname)          db = self.known_dbs.get(dbname)
233          if db is not None:          if db is not None:
234              if db.has_data(tables):              if db.has_data(tables, reference_systems, views):
235                  return db                  return db
236              raise ValueError("PostGISDatabase named %r doesn't have tables %r"              raise ValueError("PostGISDatabase named %r doesn't have tables %r"
237                               % (dbname, tables))                               % (dbname, tables))
238          return self.new_postgis_db(dbname, tables)          return self.new_postgis_db(dbname, tables, reference_systems, views)
239    
240      def get_default_static_data_db(self):      def get_default_static_data_db(self):
241          dbname = "PostGISStaticTests"          dbname = "PostGISStaticTests"
242          tables = [("landmarks", os.path.join("..", "Data", "iceland",          srids = [(1, "proj=longlat datum=WGS84")]
243                                               "cultural_landmark-point.shp")),          tables = [
244                    ("political", os.path.join("..", "Data", "iceland",              # Direct copies of the shapefiles. The shapeids are exactly
245                # the same, except where changed with "gid_offset", of
246                # course.  Note that the test implementation requires that
247                # all the landmard tables use an gid_offset of 1000.
248                ("landmarks", os.path.join("..", "Data", "iceland",
249                                           "cultural_landmark-point.shp"),
250                 [("gid_offset", 1000)]),
251                ("political", os.path.join("..", "Data", "iceland",
252                                               "political.shp")),                                               "political.shp")),
253                    ("roads", os.path.join("..", "Data", "iceland",              ("roads", os.path.join("..", "Data", "iceland",
254                                           "roads-line.shp"))]                                           "roads-line.shp")),
255          return self.get_static_data_db(dbname, tables)  
256                # The polygon data as a MULTIPOLYGON geometry type
257                ("political_multi", os.path.join("..", "Data", "iceland",
258                                                 "political.shp"),
259                 [("force_wkt_type", "MULTIPOLYGON")]),
260    
261                # Copy of landmarks but using an srid != -1
262                ("landmarks_srid", os.path.join("..", "Data", "iceland",
263                                           "cultural_landmark-point.shp"),
264                 [("gid_offset", 1000),
265                  ("srid", 1)]),
266    
267                # Copy of landmarks with a gid column called "point_id" instead
268                # of "gid" and using an srid != -1.
269                ("landmarks_point_id", os.path.join("..", "Data", "iceland",
270                                                    "cultural_landmark-point.shp"),
271                 [("gid_offset", 1000),
272                  ("srid", 1),
273                  ("gid_column", "point_id")]),
274                ]
275            views = [("v_landmarks", "SELECT * FROM landmarks_point_id")]
276            return self.get_static_data_db(dbname, tables, srids, views)
277    
278      def connection_params(self, user):      def connection_params(self, user):
279          """Return the connection parameters for the given user          """Return the connection parameters for the given user
# Line 313  class PostGISDatabase: Line 351  class PostGISDatabase:
351    
352      """A PostGIS database in a PostgreSQLServer"""      """A PostGIS database in a PostgreSQLServer"""
353    
354      def __init__(self, server, postgis_sql, dbname, tables = None):      def __init__(self, server, postgis_sql, dbname, tables = None,
355                     reference_systems = (), views = None):
356            """Initialize the PostGISDatabase
357    
358            Parameters:
359    
360                server -- The PostgreSQLServer instance containing the
361                    database
362    
363                postgis_sql -- Filename of the postgis.sql file with the
364                    postgis initialization code
365    
366                dbname -- The name of the database
367    
368                tables -- Optional description of tables to create in the
369                    new database. If given it should be a list of
370                    (tablename, shapefilename) pairs meaning that a table
371                    tablename will be created with the contents of the given
372                    shapefile or (tablename, shapefilename, extraargs)
373                    triples. The extraargs should be a list of key, value
374                    pairs to use as keyword arguments to upload_shapefile.
375    
376                reference_systems -- Optional description of spatial
377                    reference systems.  If given, it should be a sequence of
378                    (srid, params) pairs where srid is the srid defined by
379                    the proj4 paramter string params.  The srid can be given
380                    as an extra parameter in the tables list.
381    
382                views -- Optional description of views.  If given it should
383                    be a list of (viewname, select_stmt) pairs where
384                    viewname is the name of the view to be created and
385                    select_stmt is the select statement to use as the basis.
386                    The views will be created after the tables and may refer
387                    to them in the select_stmt.
388            """
389          self.server = server          self.server = server
390          self.postgis_sql = postgis_sql          self.postgis_sql = postgis_sql
391          self.dbname = dbname          self.dbname = dbname
392          self.tables = tables          self.tables = tables
393            self.views = views
394            if reference_systems:
395                self.reference_systems = reference_systems
396            else:
397                # Make sure that it's a sequence we can iterate over even if
398                # the parameter's None
399                self.reference_systems = ()
400    
401      def initdb(self):      def initdb(self):
402          """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 343  class PostGISDatabase: Line 422  class PostGISDatabase:
422    
423          self.server.execute_sql(self.dbname, "admin",          self.server.execute_sql(self.dbname, "admin",
424                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")
425            self.server.execute_sql(self.dbname, "admin",
426                                    "GRANT SELECT ON spatial_ref_sys TO PUBLIC;")
427    
428            for srid, params in self.reference_systems:
429                self.server.execute_sql(self.dbname, "admin",
430                                        "INSERT INTO spatial_ref_sys VALUES"
431                                        " (%d, '', %d, '', '%s');"
432                                        % (srid, srid, params))
433          if self.tables is not None:          if self.tables is not None:
434              for tablename, shapefile in self.tables:              def unpack(item):
435                  upload_shapefile(shapefile, self, tablename)                  extra = {"force_wkt_type": None, "gid_offset": 0,
436                             "srid": -1}
437      def has_data(self, tables):                  if len(info) == 2:
438          return self.tables == tables                      tablename, shapefile = info
439                    else:
440                        tablename, shapefile, kw = info
441                        for key, val in kw:
442                            extra[key] = val
443                    return tablename, shapefile, extra
444    
445                for info in self.tables:
446                    tablename, shapefile, kw = unpack(info)
447                    upload_shapefile(shapefile, self, tablename, **kw)
448    
449            if self.views is not None:
450                for viewname, select_stmt in self.views:
451                    self.server.execute_sql(self.dbname, "admin",
452                                            "CREATE VIEW %s AS %s" % (viewname,
453                                                                      select_stmt))
454                    self.server.execute_sql(self.dbname, "admin",
455                                            "GRANT SELECT ON %s TO PUBLIC;"
456                                            % viewname)
457    
458        def has_data(self, tables, reference_systems, views):
459            return (self.tables == tables
460                    and self.reference_systems == reference_systems
461                    and self.views == views)
462    
463    
464  def find_postgis_sql():  def find_postgis_sql():
# Line 408  def reason_for_not_running_tests(): Line 517  def reason_for_not_running_tests():
517         The name of the postgis_sql file is determined by find_postgis_sql()         The name of the postgis_sql file is determined by find_postgis_sql()
518       - psycopg can be imported successfully.       - psycopg can be imported successfully.
519      """      """
520        # run_command currently uses Popen4 which is not available under
521        # Windows, for example.
522        if not hasattr(popen2, "Popen4"):
523            return "Can't run PostGIS test because popen2.Popen4 does not exist"
524    
525      try:      try:
526          run_command(["pg_ctl", "--help"], None)          run_command(["pg_ctl", "--help"], None)
527      except RuntimeError:      except RuntimeError:
# Line 440  def skip_if_no_postgis(): Line 554  def skip_if_no_postgis():
554      if _cannot_run_postgis_tests:      if _cannot_run_postgis_tests:
555          raise support.SkipTest(_cannot_run_postgis_tests)          raise support.SkipTest(_cannot_run_postgis_tests)
556    
557  def point_to_wkt(coords):  def skip_if_addgeometrycolumn_does_not_use_quote_ident():
558        """Skip a test if the AddGeometryColumn function doesn't use quote_ident
559    
560        If the AddGeometryColumn function doesn't use quote_ident it doesn't
561        support unusual table or column names properly, that is, it will
562        fail with errors for names that contain spaces or double quotes.
563    
564        The test performed by this function is a bit simplistic because it
565        only tests whether the string 'quote_ident' occurs anywhere in the
566        postgis.sql file. This will hopefully work because when this was
567        fixed in postgis CVS AddGeometryColumn was the first function to use
568        quote_ident.
569        """
570        f = file(find_postgis_sql())
571        content = f.read()
572        f.close()
573        if content.find("quote_ident") < 0:
574            raise support.SkipTest("AddGeometryColumn doesn't use quote_ident")
575    
576    def coords_to_point(coords):
577      """Return string with a WKT representation of the point in coords"""      """Return string with a WKT representation of the point in coords"""
578      x, y = coords[0]      x, y = coords[0]
579      return "POINT(%r %r)" % (x, y)      return "POINT(%r %r)" % (x, y)
580    
581  def polygon_to_wkt(coords):  def coords_to_polygon(coords):
582      """Return string with a WKT representation of the polygon in coords"""      """Return string with a WKT representation of the polygon in coords"""
583      poly = []      poly = []
584      for ring in coords:      for ring in coords:
585          poly.append(", ".join(["%r %r" % p for p in ring]))          poly.append(", ".join(["%r %r" % p for p in ring]))
586      return "POLYGON((%s))" % "), (".join(poly)      return "POLYGON((%s))" % "), (".join(poly)
587    
588  def arc_to_wkt(coords):  def coords_to_multilinestring(coords):
589      """Return string with a WKT representation of the arc in coords"""      """Return string with a WKT representation of the arc in coords"""
590      poly = []      poly = []
591      for ring in coords:      for ring in coords:
592          poly.append(", ".join(["%r %r" % p for p in ring]))          poly.append(", ".join(["%r %r" % p for p in ring]))
593      return "MULTILINESTRING((%s))" % "), (".join(poly)      return "MULTILINESTRING((%s))" % "), (".join(poly)
594    
595  def upload_shapefile(filename, db, tablename):  def coords_to_multipolygon(coords):
596        """Return string with a WKT representation of the polygon in coords"""
597        poly = []
598        for ring in coords:
599            poly.append(", ".join(["%r %r" % p for p in ring]))
600        return "MULTIPOLYGON(((%s)))" % ")), ((".join(poly)
601    
602    wkt_converter = {
603        "POINT": coords_to_point,
604        "MULTILINESTRING": coords_to_multilinestring,
605        "POLYGON": coords_to_polygon,
606        "MULTIPOLYGON": coords_to_multipolygon,
607        }
608    
609    def upload_shapefile(filename, db, tablename, force_wkt_type = None,
610                         gid_offset = 0, gid_column = "gid", srid = -1):
611        """Upload a shapefile into a new database table
612    
613        Parameters:
614    
615        filename -- The name of the shapefile
616    
617        db -- The PostGISDatabase instance representing the database
618    
619        tablename -- The name of the table to create and into which the data
620                    is to be inserted
621    
622        force_wkt_type -- If given the real WKT geometry type to use instead
623                    of the default that would be chosen based on the type of
624                    the shapefile
625    
626        gid_offset -- A number to add to the shapeid to get the value for
627                    the gid column (default 0)
628    
629        gid_column -- The name of the column with the shape ids.  Default
630                      'gid'.  If None, no gid column will be created.  The
631                      name is directly used in SQL statements, so if it
632                      contains unusualy characters the caller should provide
633                      a suitable quoted string.
634    
635        srid -- The srid of the spatial references system used by the table
636                and the data
637        """
638      import dbflib, shapelib      import dbflib, shapelib
639    
640        # We build this map here because we need shapelib which can only be
641        # imported after support.initthuban has been called which we can't
642        # easily do in this module because it's imported by support.
643        shp_to_wkt = {
644            shapelib.SHPT_POINT: "POINT",
645            shapelib.SHPT_ARC: "MULTILINESTRING",
646            shapelib.SHPT_POLYGON: "POLYGON",
647            }
648    
649      server = db.server      server = db.server
650      dbname = db.dbname      dbname = db.dbname
651      conn = psycopg.connect("dbname=%s " % dbname      conn = psycopg.connect("dbname=%s " % dbname
# Line 474  def upload_shapefile(filename, db, table Line 658  def upload_shapefile(filename, db, table
658                 dbflib.FTInteger: "INTEGER",                 dbflib.FTInteger: "INTEGER",
659                 dbflib.FTDouble: "DOUBLE PRECISION"}                 dbflib.FTDouble: "DOUBLE PRECISION"}
660    
661      insert_formats = ["%(gid)s"]      insert_formats = []
662      fields = ["gid INT"]      if gid_column:
663            insert_formats.append("%(gid)s")
664    
665        fields = []
666        fields_decl = []
667        if gid_column:
668            fields.append(gid_column)
669            fields_decl.append("%s INT" % gid_column)
670      for i in range(dbf.field_count()):      for i in range(dbf.field_count()):
671          ftype, name, width, prec = dbf.field_info(i)          ftype, name, width, prec = dbf.field_info(i)
672          fields.append("%s %s" % (name, typemap[ftype]))          fields.append(name)
673            fields_decl.append("%s %s" % (name, typemap[ftype]))
674          insert_formats.append("%%(%s)s" % name)          insert_formats.append("%%(%s)s" % name)
675      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,
676                                                ",\n    ".join(fields))                                                ",\n    ".join(fields_decl))
677      cursor.execute(stmt)      cursor.execute(stmt)
678      #print stmt      #print stmt
679    
680      numshapes, shapetype, mins, maxs = shp.info()      numshapes, shapetype, mins, maxs = shp.info()
681      if shapetype == shapelib.SHPT_POINT:      wkttype =  shp_to_wkt[shapetype]
682          convert = point_to_wkt      if force_wkt_type:
683          wkttype = "POINT"          wkttype = force_wkt_type
684      elif shapetype == shapelib.SHPT_POLYGON:      convert = wkt_converter[wkttype]
         convert = polygon_to_wkt  
         wkttype = "POLYGON"  
     elif shapetype == shapelib.SHPT_ARC:  
         convert = arc_to_wkt  
         wkttype = "MULTILINESTRING"  
     else:  
         raise ValueError("Unsupported Shapetype %r" % shapetype)  
685    
686      cursor.execute("select AddGeometryColumn('%(dbname)s',"      cursor.execute("select AddGeometryColumn('%(dbname)s',"
687                     "'%(tablename)s', 'the_geom', '-1', '%(wkttype)s', 2);"                     "'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);"
688                     % locals())                     % locals())
689        fields.append("the_geom")
690        insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)")
691    
692      insert_formats.append("GeometryFromText(%(the_geom)s, -1)")      insert = ("INSERT INTO %s (%s) VALUES (%s)"
693                  % (tablename, ", ".join(fields), ", ".join(insert_formats)))
     insert = ("INSERT INTO %s VALUES (%s)"  
               % (tablename, ", ".join(insert_formats)))  
694    
695      for i in range(numshapes):      for i in range(numshapes):
696          data = dbf.read_record(i)          data = dbf.read_record(i)
697          data["tablename"] = tablename          data["tablename"] = tablename
698          data["gid"] = i          if gid_column:
699                data["gid"] = i + gid_offset
700            data["srid"] = srid
701          data["the_geom"] = convert(shp.read_object(i).vertices())          data["the_geom"] = convert(shp.read_object(i).vertices())
702          #print insert % data          #print insert % data
703          cursor.execute(insert, data)          cursor.execute(insert, data)
Legend:
Removed from v.1634  
changed lines
  Added in v.2459
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26