CODEWITHSUNDEEP
sqlpostgresqlpostgis
Philipp_Kats
Philipp_Kats

Reputation: 4214

POSTGRESQL, No Subquery: subquery in FROM must have an alias

I am querying the database with a calculated field and a Spatial Query, but without any subquery, to my understanding. However, POSTGRESQL keeps asking me for an alias....

 SELECT geoid as destination, 
    lehd_graph.h_geocode asorigin,lehd_graph.S000 as population,
    (ST_buffer(ST_Transform(ST_SetSRID(ST_Point(-74.01128768920898, 40.739843698929995), 4326), 3857), 500)))  /
ST_area(lehd_map.the_geom_webmercator)) as fraction 
FROM lehd LEFT JOIN lehd_graph 
ON lehd.w_geocode = lehd_graph.w_geocode 
WHERE ST_intersects( lehd_map.the_geom_webmercator, 
ST_buffer(ST_Transform( ST_SetSRID(ST_Point(-74.01128768920898, 40.739843698929995), 4326), 3857), 500))

Syntax error: subquery in FROM must have an alias

Upvotes: 0

Views: 955

Answers (1)

Patrick
Patrick

Reputation: 32286

You have a couple of issues with your query. The immediate error is caused by a mismatched number of parentheses in the first call to ST_Buffer() but even with that corrected your query won't execute; in fact, that first call is a very expensive way of imprecisely calculating the area of a circle with a radius of 500 meter - which is 785398.163 square meter exactly - so you can just get rid of that call and plug in the area. (An additional problem, which is now no longer relevant, is that you were trying to divide a geometry by a scalar value from ST_Area() which is obviously impossible.)

A further issue is that you did not include table lehd_map in your FROM clause; I added it here through a JOIN.

SELECT geoid AS destination, 
       lehd_graph.h_geocode AS origin,
       lehd_graph.S000 AS population,
       785398.163 / ST_Area(lehd_map.the_geom_webmercator)) AS fraction 
FROM lehd
LEFT JOIN lehd_graph USING (w_geocode)
JOIN lehd_map ON ST_Intersects(lehd_map.the_geom_webmercator, 
                   ST_Buffer(
                     ST_Transform(
                       ST_SetSRID(
                         ST_Point(-74.01128768920898, 40.739843698929995),
                         4326),
                       3857),
                     500)
                   );

If you feel uneasy about separating the buffer of the point and the area of the buffer, then you could combine them in a CTE to emphasize how the area is related to the buffered point:

WITH pt(geom, area) AS (
    VALUES (ST_Buffer(
              ST_Transform(
                ST_SetSRID(
                  ST_Point(-74.01128768920898, 40.739843698929995), 4326), 3857), 500),
            785398.163) -- 500 x 500 x pi: area of the buffer around the point
)
SELECT geoid AS destination, 
       lehd_graph.h_geocode AS origin,
       lehd_graph.S000 AS population,
       pt.area / ST_Area(lehd_map.the_geom_webmercator) AS fraction 
FROM pt
JOIN lehd ON true
LEFT JOIN lehd_graph USING (w_geocode)
JOIN lehd_map ON ST_Intersects(lehd_map.the_geom_webmercator, pt.geom);

Upvotes: 3

Related Questions