[postgis-users] Unique IDs in Views and Quantum GIS

[pcreso at pcreso.com]

Hi Phil,

If your view is large, you will get sluggish performance with QGIS. One solution is a materialised view, which is not directly supported in Postgres, but can be made to work. This does create a physical table representing the view, which can have appropriate indexes applied.

The issue without direct support for materialised views is maintenance: assuring that the MV is automatically updated to match the underlying tables. This is discussed & examples using triggers shown at: 
http://wiki.postgresql.org/wiki/Materialized_Views

HTH,

  Brent Wood
--- On Fri, 9/16/11, Phil James <borntopedal at yahoo.co.uk> wrote:




--- On Fri, 9/16/11, Phil James <borntopedal at yahoo.co.uk> wrote:

From: Phil James <borntopedal at yahoo.co.uk>
Subject: [postgis-users] Unique IDs in Views and Quantum GIS
To: "postgis-users at postgis.refractions.net" <postgis-users at postgis.refractions.net>
Date: Friday, September 16, 2011, 10:12 PM

I am trying to create a view that can be opened in Quantum using ST_INTERSECTION.  This returns multiple entries for the same road object and therefore Quantum refuses to use the GID field as it is not unique.  As an alternative I thought to use row_number but this is a bigint and Quantum says it wants int4 (integer) so I cast this to integer (which is OK as there are not that many records).  However, Quantum still refuses to recognise this as a potential key field presumably as it has no UNIQUE or PK constraints on it.  I know this is more a question for the Quantum list but anyone any ideas how to get round this?
 I know I can use CREATE TABLE instead of a View and add a PK to the table but this is bugging me now ! I can also do it with SELECT DISTINCT ON (gid) but then we only get one intersection point per geometry when there may be multiples.
DROP VIEW intersection_view;CREATE VIEW intersection_view AS(SELECT DISTINCT 	CAST (row_number() over (order by r.gid)
  AS integer) as id, r.name, r.gid, ST_INTERSECTION(r.the_geom, w.the_geom) AS the_geom,	ASTEXT(ST_INTERSECTION(r.the_geom, w.the_geom)) AS intersectionptFROM	neroads r, newaterways wWHERE	ST_INTERSECTS(r.the_geom, w.the_geom)AND	r.type ILIKE 'prim%'	);
Many thanks
Phil
-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110916/b1aedee6/attachment.html>