[postgis-users] PostgreSQL/PostGIS as a bundled database

[Frank Warmerdam]

Folks,

A client of mine is working on greater integration of their product with
spatially enabled databases for multiuser shared work environments (ie.
Oracle Spatial, SDE).  However, having made their applications more friendly
to the multi-user database backed world the question arises whether they can
offer a light version of this for customers without an existing spatial
database installation.

Not surprisingly PostgreSQL/PostGIS comes up as an option.  So my question
is what issues might be encountered trying to bundle PostGIS with an
application.  I imagining it being installed and activivated as an optional
step in an InstallShield style install on Windows.  Some sort of simple
control panel would likely be needed from within the application to administer
the database but it would desirable for it to be kept simple.  The user should
be able to install the database on one machine and reach it from others.

What issues/concerns would people have about this?  Some things that come
to mind for me are:

  o Currently on Windows a Postgres installation has to be built in the Cygwin
    environment, right?  How much of that is required at run-time?  I am hoping
    all we would need to include out of Cygwin in the install is one or two
    runtime DLLs.  Are there required components of Postgres that would depend
    on Cygwin shell scripts or external commands?

  o To allow multiple machines on the LAN to access it the db instance would
    need to be started in the mode where you can connect to it by internet
    socket.  In this case it would be important to install with user supplied
    passwords, right?

  o I am assuming that the client application would use the Win32 builds of
    libpq and so would have no direct dependency on Cygwin.  Anyone using this
    method now?  (I would really like to get this working in OGR but haven't
    had the time to try it).

  o How big (roughly) is a Postgres install with a database initialized but
    no spatial data loaded?  An Oracle 8.1.7 installation on Windows seems to
    be on the order of 1GB and apparently an Oracle 9 base install is
    substantially larger.  I am hoping the Postgres installed with an empty
    db initialized would be less than 100MB.

  o How easy is it to ensure that postgres is started at boot up?  Can it be
    made an NT service?  Is this the best approach?  On unix I would ensure
    that "pg_ctl start -o -i " was called from a boot script.  What is the
    equivelent to that on NT?

  o Assuming we want to keep things simple for a user what are the minimum
    database administration requirements that would be need to be exposed?
    I can think of:
      o Initial installation.  The user might be given an option to place
        the database instance somewhere other than within the installed
        software tree.
      o An option to start/stop/restart the database (ie. pg_ctl) and
        control whether it is remotely accessable or not.
      o An option to backup and restore the database.
      o An option to create/delete users and set/reset their passwords.

    I am assuming that creating layers, altering field definitions and
    so forth would be done through the normal GUI interface for the
    application rather than in a Postgres specific manner.

  o What does a client install need?  Just the primary application and
    libpq.dll, right?   The client install should only need to know the
    machine name to connect to, right?  Is there a default port for postgres?
    would there be a reason to change it?

  o What issues of conflicts with other software might we encounter?  Should
    we run postgres on a different port to ensure we don't conflict with any
    other postgres instance that might exist on the clients systems?  Are
    there issues of picking up the wrong cygwin DLLs at runtime if the user
    has another cygwin version installed?

  o What would be necessary if we also wanted the database to be ODBC
    accessable for access from other windows applications?  Are there good
    ODBC drivers for postgres on windows?  Are there other complicating
    issues to using them?

I think the open source, and free nature of PostgreSQL/PostGIS could make
it an attractive option as a bundled free spatial database for a variety of
GIS software packages.  Furthermore I think it's use in this configuration
could result in commercial support for further development of PostGIS.

PS. I am working on Oracle Spatial support for OGR right now.  I haven't
     reviewed the Oracle 9 Spatial docs yet, but in Oracle 8 there seems to
     be very little OGC SFSQL support.  There is no apparent way to get
     geometries in WKT or WKB formats for instance.  However, the coordinate
     systems are in WKT format.  All in all I am disappointed so far with
     the OGC standards compliance in Oracle Spatial.

Best regards,

-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent