[GIS] Create table from results of ST_Intersection queries in PostGIS

attribute-tablepostgiswhere-clause

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.

Best Answer

You'll need a geometry column in the "rail" table in order to use any spatial functions.

CREATE TABLE RAIL AS
SELECT rw.gid, rw.name, rw.type, rw.geom
FROM railways rw
WHERE "type" = 'rail';
Related Question