I create a query in PostGIS using 'where' clause, and with that a table. now I want to use those attributes to create a intersection with other table... but I don't know how to call the attributes, because is always asking for the original table and like that it will skip the query
First query to get Rail features:
CREATE TABLE RAIL AS SELECT rw.gid, rw.name, rw.type, rw.geom FROM railways rw WHERE "type" = 'rail';
Here I have my new table (layer in qgis) with only the type "rails". NOw when I want to do the intersection with rivers table (layer):
CREATE TABLE RW_W_RESULT AS SELECT rw.gid as rwgid, rw.name as rwname, rw.type as rwtype, w.gid as wgid, w.name as wname, w.type as wtype, ST_COLLECTIONEXTRACT(ST_INTERSECTION(w.geom, rw.geom),1) as rw_w_geom, COUNT(DISTINCT rw.gid) FROM waterways w, rail WHERE ST_INTERSECTS(w.geom, rw.geom) AND w.gid < rw.gid GROUP BY rw.gid,w.gid;
I got this error: ERROR: missing FROM-clause entry for table "rw", that is clear to me, but how im suppose to call the attributes of the new created table(layer) to work with this query? If I eliminate rw. from the attributes its says is ambiguous.