Views, PostGIS 2, and QGIS
If you wanted to create a view with spatial capabilities with previous versions of PostGIS all you needed to do was manually register the view in the geometry_columns table. This is no longer possible since from PostGIS 2.0 forward geometry_columns is a view, not a table. However, if you use typemods to define the geometry column your view the geometry_columns view will pick it up. For example, I wanted to take some of the data I loaded from my .fit files and convert the metric data to imperial units, and reproject the data from WGS84 to UTM. Here’s the view that does that for me:
CREATE OR REPLACE VIEW rides.ride_utm AS
SELECT id, no
, utility.units_from_to('meter', 'feet', altitude) AS edge_elev
, 32+(temperature*(1.8)) AS temp_f, speed*2.2369 AS mph, cadence
, ride_date, ride_time, elev_dem
, ST_Transform(the_geom, 26916)::geometry(Point, 26916) As the_geom FROM rides.ride;
The units_from_to function is something I picked up from PostGIS in Action. The geometry column uses ST_Transform to reproject the data, and the typemod after defines the column as having point geometry with my UTM projection.
With the geometry column defined in that manner the geometry_columns view is properly populated. When you want to use this view in QGIS it is visible as a loadable layer. However, before you can use it you must tell QGIS which field is the unique row identifier. To the left of the layer’s SRID number there will be a drop box on the PostGIS layer load screen. This dropbox will give you a list of possible fields to use as your ID column. Just select the column that contains unique values and you’re good to go.
Boring Nerdy Stuff 
No trackbacks yet.