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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
