title: PostgreSQL/PostGIS: ST_distance query should only return "nearest" result
body: <p>I would like to query the distance for every point (id) in table A to every polgygon (id) in table B. For the distance calculation I am using ST_Distance. However, ST_Distance returns (obviously) the distance to every polygon. But I only need the "closest" result for each point. So far, I tried the following query which returns the correct result but (of course) for exactly one point.</p>
  
  <pre><code>SELECT polygons.id, points.id, ST_Distance(points.geom, polygons.geom) 
  FROM table_A AS points, table_B AS polygons 
  ORDER BY st_distance ASC LIMIT 1
  </code></pre>
  
  <p>The result should be something like this:</p>
  
  <pre><code>polygon_id | point_id | min(distance)
  -------------------------------------
  1234       |   876    |   54.32
  ...        |   ...    |    ...
  -------------------------------------
  </code></pre>
  
  <p>Do you have any hints? Thank you very much.</p>
  
  <p><strong>UPDATE 1</strong></p>
  
  <pre><code>WITH CTE AS
         (SELECT polygons.id as poly_id, points.id as point_id, 
          ST_Distance(points.geom, polygons.geom) as thedistance , 
          row_number() OVER
                (PARTITION BY points.id ORDER BY ST_Distance(points.geom, polygons.geom)) 
      FROM 
          table_A AS points
      INNER JOIN table_B AS polygons
      ON ST_DWithin(points.geom, polygons.geom, 100)) SELECT * FROM CTE WHERE row_number = 1
  </code></pre>
  
  <p>After running the above query (3h 24m) there was empty result returned. However, there should a result. Is it possible that there is a problem with parentheses?</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>The SRID is 4326 (WGS84) and the polygons are OSM building polygons and the points arbitrary points in the same city.</p>