title: PostgreSQL complex query to find closest matching values for two fields
body: <p>Hi I have a database with the following structure:</p>
  
  <p>Table 1:</p>
  
  <pre><code>time(type timestamp)        longitude(type string)        latitude(type string)
  2:00                        -110.4365                     38.7463
  2:00                        -110.2743                     38.7983
  2:00                        -102.4434                     36.9438
  3:00                        -112.3254                     39.2222
  etc                         etc                           etc
  </code></pre>
  
  <p>Now I have some code that plots values and when it does so it loses some accuracy.The user can click the points and query the database for further information on the point. However as the plotting function lost some accuracy I need to account for this in the query.</p>
  
  <p>So the idea is that I would like to query for time = x and also (latitude = closest value to y and longitude = closest value to z). You see the closest value is dependent on both latitude and longitude and this is where I'm getting confused as to how to proceed.</p>
  
  <p>Lets say I was looking for:</p>
  
  <pre><code>Time = 2:00
  Longitude = -110.3421
  Latitude = 38.7587
  </code></pre>
  
  <p>Now if I did a query for:</p>
  
  <pre><code>Time = 2:00 and Longitude = closest to -110.3421
  </code></pre>
  
  <p>then the result would be row 2.</p>
  
  <p>However if I did:</p>
  
  <pre><code>Time = 2:00 and Latitude = closest to 38.7587
  </code></pre>
  
  <p>then the result would be row 1.</p>
  
  <p>If I do a query on:</p>
  
  <pre><code>Time = 2:00 and Longitude = closest to -110.3421 and Latitude = closest to 38.7587
  </code></pre>
  
  <p>then the result would be row 1. This is the type of query that I require...</p>
  
  <p>I found the following post useful for a query on the closest value in one field:</p>
  
  <p><a href="https://stackoverflow.com/a/6103352/1800665">https://stackoverflow.com/a/6103352/1800665</a></p>
  
  <p>Thanks,
  James</p>
  
  <p>EDIT: I have the following:</p>
  
  <pre><code>(SELECT * FROM (
  (SELECT * FROM Table1 WHERE cast(latitude as double precision) &gt;= '38.7587' AND healthtime = '2:00' AND cast(longitude as double precision) &gt;= '-110.3421') 
  UNION ALL 
  (SELECT * FROM Table1 WHERE cast(latitude as double precision) &lt; '38.7587' AND healthtime = '2:00' AND cast(longitude as double precision) &lt; '-110.3421')
  ) as nearest ORDER BY ( abs('38.7587'-cast(latitude as double precision)) + abs('-110.3421'-cast(longitude as double precision))) LIMIT 1)
  </code></pre>