I would like to calculate the shortest distance between each building and nearest sewage pipe. I followed steps described on CARTO blog but I encounerd an error. I have data in same database but in different schemas:
- table name for buildings is ks2_st and schema is public
- table name for sewage pipes is t2100_ill and schema is gji
The query is:
SELECT a.mid_st, a.geometry, ST_Distance(geography(b.geometry), geography(a.geometry)) as distance FROM SELECT DISTINCT ON (a.geometry) FROM public.ks2_st a) as buildings CROSS JOIN LATERAL (SELECT b._mid_gji, b.geometry FROM gji.t2100_ill b ORDER BY a.geometry <-> b.geometry LIMIT 1) as sewery
When I try to run query I get message:
ERROR: syntax error at or near "FROM"
LINE 7: FROM public.ks2_st a) as buildings
Can you please help me find the error, because I do not know what the error could be?