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.

Improve this question
13
  • 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
    Commented Nov 29, 2016 at 21:23
  • 3
    Once again 2/3 people who voted to close this question are ArcGIS users.
    – Inactivated Account
    Commented Nov 29, 2016 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
    Commented Nov 30, 2016 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
    Commented Nov 30, 2016 at 2:47
  • 1
    @JohnBarça ArcGIS for Everything©
    – Inactivated Account
    Commented Nov 30, 2016 at 16:42

2 Answers 2

Reset to default
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';
Improve this answer
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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