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

[Phil James]

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 intersectionpt
FROM
neroads r, newaterways w
WHERE
ST_INTERSECTS(r.the_geom, w.the_geom)
AND
r.type ILIKE 'prim%'
);

Many thanks

Phil
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110916/b7d39fc7/attachment.html>