1

Referencing the alias length_road in the WHERE clause does not seem to be allowed. Does this query as written cause the ST_LENGTH operation to be run more than once per record, and if so, how can that be avoided?

SELECT
  roads_nat.*,
  ST_LENGTH(roads_nat.road_geom) AS length_road
FROM
  `bigquery-public-data.geo_us_roads.us_national_roads` AS roads_nat
WHERE
  ST_LENGTH(roads_nat.road_geom) > 1000 -- vs. length_road > 1000
3

This is a question about SQL column aliasing, not just big query ... the alias can only be used on a column name, not on a column in a where clause. If you created a subquery, you would reference the alias of the length_road in the where clause of the subquery instead.

select * from (
    SELECT
      roads_nat.*,
      ST_LENGTH(roads_nat.road_geom) AS length_road
    FROM
      `bigquery-public-data.geo_us_roads.us_national_roads` AS roads_nat

) as main 

WHERE main.length_road > 1000 
1

In PostGIS, since st_length is immutable, it will be called once only per row. This is true regardless of the position of the call (in the select and/or the where)

The doc says

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

When used against a table row, each row is considered constant. So if you call the same function several times on the same row, the function will be evaluated once only.

  • Thanks JGH. I was wondering if something like this was the case independent of aliasing but was not sure of the terminology. This is really helpful. – mapit Nov 15 '19 at 19:46

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