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 <borntope...@yahoo.co.uk> wrote: --- On Fri, 9/16/11, Phil James <borntope...@yahoo.co.uk> wrote: From: Phil James <borntope...@yahoo.co.uk> Subject: [postgis-users] Unique IDs in Views and Quantum GIS To: "postgis-users@postgis.refractions.net" <postgis-users@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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
