Choose minumum result of SELECT query in Postgresql

Question

I have the following query where I want to calculate the closest user to a building of type "store":

WITH y AS (
  SELECT t.userid as us, ST_Distance(t.thegeometry,b.thegeomtry) as dist
  FROM track t,buildings b
  WHERE b.type = 'store'
)
SELECT us 
FROM   y
WHERE  dist = (SELECT MIN(dist) FROM y)

The problem is when I tried to compute the minimum it gives me 0 which is not true. In my data the minimum distance between user with id 112 and store A equals 2441 meters.

`SELECT MIN(dist) FROM y` `y` must be the real table name, isn't ? — David Bélanger, Jul 12 '12 at 17:25
@DavidBélanger: The CTE would be visible even in a subquery. It's not needed to begin with, though. — Erwin Brandstetter, Jul 12 '12 at 17:27

Erwin Brandstetter · Accepted Answer · 2012-07-12T17:24:48.127

Your query should probably look like this:

SELECT t.userid AS us
      ,ST_Distance(t.thegeometry, b.thegeomtry) AS dist
FROM   track t
CROSS  JOIN buildings b
WHERE  b.type = 'store'
ORDER  BY dist 
LIMIT  1;

"Find the user closest to any of the buildings of type store".
No need for CTE or aggregate functions.

You are probably aware that the query is extremely expensive with bigger tables, because you effectively form a limited cross join between the tables track and buildings - which means O(N²).

I used the explicit CROSS JOIN syntax (which is equivalent to a comma-separated list of tables) to make that clear.

score 1 · Answer 2 · answered Jul 12 '12 at 15:23

I assume that ST_Distance is a function that returns an number, then try with this query:

SELECT
 t.userid AS us
,MIN (ST_Distance(t.thegeometry,b.thegeomtry)) AS dist
FROM track t, buildings b
WHERE b.type = 'store'
GROUP BY t.userid
ORDER BY 2
LIMIT 1

I hope you help.
Victor Zurita M.

Choose minumum result of SELECT query in Postgresql

2 Answers2