Calculating distance to nearest object using PostGIS?

Question

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?

Answer 1

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.