Create a view for a 52N SOS, so that it can be accessed as a WFS from, for example, QGIS or GeoServer

sos_to_wfs.py

"""
Create a view for a 52N SOS, so that it can be accessed as a WFS from, 
for example, QGIS or GeoServer

Version: 0.1
Author: Derek Hohls, Meraka, CSIR
"""

import psycopg2 as psy


def execute_sql(conn, sql):
    try:
        curs.execute(sql)
        curs.execute("COMMIT")
    except psy.IntegrityError, e:
        print 'WARNING: %s' % e
        conn.rollback()
    except psy.ProgrammingError, e:
        print 'WARNING: %s' % e
        conn.rollback()

print "Connecting..."
conn = psy.connect(dbname="my_dbname", user="db_username", host="0.0.0.0") 
curs = conn.cursor()

#Step 0: setup data for the views -> one view for each variable
#   These must obviously match known, named-by-uri variables in the SOS database,
#   omitting the usual 'urn:ogc:def:phenomenon:OGC:1.0.30:' prefix, 
#   which is added in the code.

variables = [
    {'var':'ph', 'view':'my_sos_wfs_ph'},
    {'var':'Temperature', 'view':'my_sos_wfs_temperature'},
]

#Step 1: Create FOI keys table
execute_sql(conn, 
"""CREATE TABLE wfs_gid_lookup (
   gid integer NOT NULL,
   foi_id_fkey character varying(100) NOT NULL
   );"""
)
execute_sql(conn, 
"""CREATE SEQUENCE wfs_gid_lookup_gid_seq
   START WITH 1
   INCREMENT BY 1
   NO MINVALUE
   NO MAXVALUE
   CACHE 1;"""
)
execute_sql(conn, 
"""ALTER SEQUENCE wfs_gid_lookup_gid_seq OWNED BY wfs_gid_lookup.gid;"""
)
execute_sql(conn, 
"""ALTER TABLE ONLY wfs_gid_lookup ALTER COLUMN gid 
   SET DEFAULT nextval('wfs_gid_lookup_gid_seq'::regclass);"""
)
execute_sql(conn, 
"""ALTER TABLE ONLY wfs_gid_lookup
   ADD CONSTRAINT wfs_gis_lookup_pkey PRIMARY KEY (gid);"""
)
execute_sql(conn, 
"""ALTER TABLE ONLY wfs_gid_lookup
   ADD CONSTRAINT wfs_gid_lookup_foi_fkey FOREIGN KEY (foi_id_fkey) 
   REFERENCES feature_of_interest(feature_of_interest_id);"""
)

#Step 2: Populate the FOI keys table

execute_sql(conn, 
"""INSERT INTO  wfs_gid_lookup (foi_id_fkey) 
   (SELECT DISTINCT feature_of_interest_id FROM feature_of_interest);"""
)

for variable in variables:
    #Step 3: Create the views
    #   Each step represents one view for a single variable; note that 
    #   missing data is excluded from the summary values (as these can cause 
    #   calculation failures).

    # remove old
    execute_sql(conn, 
    """DROP VIEW %(view)s;""" % variable
    )
    # add new
    execute_sql(conn, 
    """CREATE OR REPLACE VIEW %(view)s AS
       SELECT lookup.gid, foi.feature_of_interest_id AS id, foi.feature_of_interest_name AS name, 
       'sos/csv?feature=' || foi.feature_of_interest_id as url, 
       foi.geom, phenom.unit, count(obs.*) AS count, max(obs.numeric_value) AS max, 
       min(obs.numeric_value) AS min, 
       round(avg(obs.numeric_value), 2) AS average, max(obs.time_stamp) AS last_date 
       FROM wfs_gid_lookup lookup, feature_of_interest foi, observation obs, phenomenon phenom
       WHERE ((((((lookup.foi_id_fkey)::text = (foi.feature_of_interest_id)::text) 
       AND ((obs.feature_of_interest_id)::text = (foi.feature_of_interest_id)::text)) 
       AND ((phenom.phenomenon_id)::text = (obs.phenomenon_id)::text)) 
       AND ((obs.phenomenon_id)::text = 'urn:ogc:def:phenomenon:OGC:1.0.30:%(var)s'::text)) 
       AND (obs.numeric_value <> 'NaN'::numeric)) 
       GROUP BY lookup.gid, foi.feature_of_interest_id, phenom.phenomenon_id;""" % variable
    )
    #ensure correct ownership
    execute_sql(conn, 
    """ALTER VIEW  %(view)s OWNER TO db_username;""" % variable
    )

    #Register the view in the geometry_columns table
    #    This is needed in versions of PostGIS less than 2.0... 
    #    SQL needs to be created and run for each parameter/view that has been created

    execute_sql(conn, 
    """INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, 
       f_geometry_column, coord_dimension, srid, "type")
       SELECT '', 'public', '%(view)s', 'geom', ST_CoordDim(geom), 
       ST_SRID(geom), GeometryType(geom)
       FROM %(view)s LIMIT 1;""" % variable
    )

conn.close()
print "Done..."