Robin's Blog

Fixing SQL error ‘invalid reference to FROM-clause entry for table’

Here’s a bit of SQL I wrote recently that had an error in it which I struggled to find. The error I got was ERROR: invalid reference to FROM-clause entry for table "roads" and I did some Googling but nothing really seemed to make sense as a cause of this error.

My SQL looked like this:

WITH args AS (
    SELECT
        ST_TileEnvelope(z, x, y) AS bounds,
        ST_Transform(ST_MakeEnvelope(l, b, r, t, 4326), 27700) AS area
),
mvtgeom AS (
    SELECT
        ST_AsMVTGeom(
            ST_Transform(ST_Intersection(roads.geom, args.area), 3857),
            args.bounds
        ) AS geom
    FROM
        roads, args
    JOIN floods ON ST_Intersects(roads.geom, floods.wkb_geometry)
    WHERE
        roads.geom && args.area
        AND roads.geom && args.bounds
)
SELECT
    ST_AsMVT(mvtgeom, 'default') INTO result
FROM
    mvtgeom;

This looks a bit weird, but it’s a slightly re-written version of the SQL inside a function that was written to produce Mapbox Vector Tiles (MVT) for use in an online webmap. The function is picked up by pg_tileserv and called appropriately when tiles are needed.

What I’m doing in the code is setting up a couple of parameters – the tile envelope and the selected area, and then running a fairly simple join with extra WHERE conditions, converting the outputs to MVT geometries and then the whole thing to a MVT itself.

The problem is inside the mvtgeom AS section – let’s simplify the SQL there a bit so it is easier to look at:

SELECT
        ST_Intersection(roads.geom, args.area), 3857) AS geom
FROM
        roads, args
        JOIN floods ON ST_Intersects(roads.geom, floods.wkb_geometry)
WHERE
        roads.geom && args.area
        AND roads.geom && args.bounds

I’ve got rid of all the MVT related stuff, so we have a more standard PostGIS query: we’re looking for the intersection of the roads and the area, where the roads intersect the floods, and the roads are inside the area and inside the tile boundaries (both defined in the args CTE). Possibly the error is a bit clearer now – maybe…

The answer is, it’s all about the placement of the JOIN clause. I’d never really properly thought about this (even though it’s a bit obvious!), but the JOIN statement refers to the table listed immediately before it. Therefore, if we reformat the above slightly we’re selecting:

FROM
roads,
args JOIN floods ON ST_Intersects(roads.geom, floods.wkb_geometry)

That is, we are running the JOIN on the args table, not on the roads table. The slightly confusing error message (ERROR: invalid reference to FROM-clause entry for table "roads" ) means that this JOIN clause makes no sense when run against the args table, as there’s no such thing as roads in that context.

All we need to do to fix this is move the JOIN clause before args:

FROM
roads JOIN floods ON ST_Intersects(roads.geom, floods.wkb_geometry),
args

Remember the comma after the JOIN clause – we’re still in the list of tables/things that we’re selecting FROM.

In general, the format of a FROM clause is:

FROM
<tablename> [JOIN clause],
<tablename>,
…

For context, I was running this SQL in Postgres, with the PostGIS extension. I’m not an expert in SQL, so please let me know if I’ve made any errors in this post.

If you found this post useful, please consider buying me a coffee.
This post originally appeared on Robin's Blog.

Tagged with:

Categorised as: Computing, GIS, How To, Programming