<html><body><div style="color:#000; background-color:#fff; font-family:arial, helvetica, sans-serif;font-size:12pt"><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; ">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?</div><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; "><br></div><div style="font-family: arial, helvetica,
sans-serif; font-size: 12pt; "> I know I can use CREATE TABLE instead of a View and add a PK to the table but this is bugging me now !</div><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; "> 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.</div><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; "><br></div><div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">DROP VIEW intersection_view;</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">CREATE VIEW intersection_view AS(</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">SELECT DISTINCT </font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><span class="Apple-tab-span" style="white-space:pre"> </span>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,</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><span class="Apple-tab-span" style="white-space:pre"> </span>ASTEXT(ST_INTERSECTION(r.the_geom, w.the_geom)) AS intersectionpt</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">FROM</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><span class="Apple-tab-span" style="white-space:pre"> </span>neroads r, newaterways w</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">WHERE</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><span class="Apple-tab-span" style="white-space:pre"> </span>ST_INTERSECTS(r.the_geom, w.the_geom)</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">AND</font></div><div><font
class="Apple-style-span" face="arial, helvetica, sans-serif"><span class="Apple-tab-span" style="white-space:pre"> </span>r.type ILIKE 'prim%'</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><span class="Apple-tab-span" style="white-space: pre; "> </span>);</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><br></font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">Many thanks</font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif"><br></font></div><div><font class="Apple-style-span" face="arial, helvetica, sans-serif">Phil</font></div></div></div></body></html>