2

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?

5

The error message is a bit misleading because the Syntax Error is in Line 5. You're missing the ) after the first FROM which leads to the error below in the code.

Update:

Also you have a second Error: DISTINCT ON (column) is wrong. Either DISTINCT column or DISTINCT ON (column) column would be correct.

Update 2:

Another error: You named the subqueries buildings and sewery, you cannot use a and b outside of the subquery. Also these aliases are not needed, so I removed them. Additionally, I changed your CROSS JOIN LATERAL to a , because it is not needed here.

SELECT
buildings.mid_st,
buildings.geometry,
ST_Distance(buildings.geometry::geography, sewery.geometry::geography) as distance
FROM
(
  SELECT DISTINCT ON (geometry)
  geometry, mid_st
  FROM public.ks2_st
) as buildings,
(
  SELECT _mid_gji, geometry
  FROM gji.t2100_ill      
) as sewery
ORDER BY buildings.geometry <-> sewery.geometry
LIMIT 1

Additional Hint when using PostgreSQL < 9.5:

<-->: For PostgreSQL below 9.5 only gives centroid distance of bounding boxes and for PostgreSQL 9.5+, does true KNN distance search giving true distance between geometries, and distance sphere for geographies.

You can use LIMIT 10 in the subquery and then ORDER BY ST_Distance(geom1,geom2) LIMIT 1 at the end as a workaround.

  • Still the same error: ERROR: syntax error at or near "FROM" LINE 8: FROM public.ks2_st a – Miha Kl Jul 20 '17 at 13:12
  • this is not the same error, it is a second one. I updated the answer. – RoVo Jul 20 '17 at 13:32
  • I will try tomorrow again. I hope it will work. Thank you! – Miha Kl Jul 20 '17 at 15:52
  • Still get the error: ERROR: missing FROM-clause entry for table "a" LINE 14: ORDER BY a.geometry <-> b.geometry LIMIT 1 – Miha Kl Jul 21 '17 at 6:28
  • This is again a new Error... I did not proof-read your whole query. I'll update once more. – RoVo Jul 21 '17 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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