4

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.

  • 3
    This is really a fundamental SQL issue, and not anything GIS specific. If you look at the documentation, you'll find plenty of examples. I'd recommend an explicit JOIN rather than an implicit one in the FROM list. – Vince Nov 29 '16 at 21:23
  • 3
    Once again 2/3 people who voted to close this question are ArcGIS users. – DPSSpatial Nov 29 '16 at 22:29
  • 1
    Since this is on hold, you will also need to do some work on your second query once the RAIL table is fixed as per @Micha's answer. There is a couple of ways to do this query, here's an example of one SELECT rw.gid, rw.name, rw.type, w.name, w.type, case when r.geom is null then 0 else 1 end AS intersects_river FROM rail rw LEFT OUTER JOIN rivers r ON ST_INTERSECTION(rw.geom, r.geom) – MickyT Nov 30 '16 at 0:53
  • 1
    No, not ON ST_Intersection(...), but ON ST_Intersects(...). The latter returns a Boolean, while the former returns a geometry (or geography, depending on input). Calculating the intersection is computationally expensive, so it should only be done when necessary. – Vince Nov 30 '16 at 2:47
  • 1
    @JohnBarça ArcGIS for Everything© – DPSSpatial Nov 30 '16 at 16:42
1

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';
0

This is, indeed, a fundamental SQL question.

The "alias" 'rw' created is the CREATE TABLE RAIL AS query is only valid in this specific query.

So you have to declare it again in the second query in the FROM clause

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 rw
WHERE ST_INTERSECTS(w.geom, rw.geom) AND w.gid < rw.gid 
GROUP BY rw.gid,w.gid;

That should solve your problem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.