PostGIS Data Store

SeanGillies (SG) Hannes Kleindienst (HK)

An interface to a PostGIS (Pg) enabled database, implements PCL's IFeatureStore interface.

Cases

There are 3 basic use cases for feature types from a PostGIS datastore

Feature tables

Tables loaded, perhaps using shp2pgsql, and used as-is. According to the OGC Simple Features for SQL spec, each table must have an entry in the geometry_columns table.

Views

Database administrators may create views of feature tables. Views aren't covered by the OGC SFSQL spec. To be used with the MapServer? rendering engine, views will need a unique primary key-like column that holds unique feature ids (FID).

User-defined selections

MapServer? users are accustomed to being able to define their own selections and use them as datasources. I think this is a valid use case and needs to be supported. This is a bit like a view, but sacrifices performance for customizability.

Two classes of feature types

There will be two classes of feature types.

Standard feature type

One class will be feature types defined in the database: tables and views. Calling the typenames() method on a Pg datastore will return all tables and views that contain a geometry column.

User-defined feature type

The second class will be user-defined feature types that are completely described by a unique name, SQL fragment, unique id (FID), and SRID like

ranked-countries

SELECT c.gid, c.the_geom, c.fips_cntry, r.rank
FROM countries c, rankings r
WHERE c.fips_cntry = r.fips_cntry

gid

4326

Some additional comments: In order to implement the schema() method, it might be useful to have not just one parameter for the SQL fragment, but several: geometry field, list of attribute fields, FROM clause, WHERE clause (optional) and ORDER BY clause (optional ... necessary?).

Agreed. (SG)

Also, since it is not so easy to derive the datatype, it might be useful to have the datatype as additional parameter.

I'd like to avoid the use of a datatype parameter if possible (SG)

Discussion: dynamic queries

If GIS-data is used together with access control, it might happen, that the access to certain features is restricted due to user or group specific permissions. In this case, the WHERE clause in the user-defined feature store needs to be defined on the fly, based on the users credentials. Parameters like the username need to be passed to the SQL generator.

Getting the FID and geometry SRID of a view

Not so complex a problem after. The PostgreSQL information_schema has views that explain which tables and columns are used in other views, and this allows us to track down the SRID.