MySQL Select rectangular region closest to point
<p>I need to select the region closest to an x/y coordinate from a table of rectangular regions defined by x1/y1, x2/y2 points. The point is outside of any of the rectangular regions.</p>

<p>I have managed to select the regions that are closest in the x axis and the y axis using two separate queries.</p>

<p>The table has columns zoneid(int), mappath(varchar), type(varchar), x1(int), y1(int), x2(int), y2(int)</p>

<p>Nearest region in X-axis</p>

<pre class="lang-sql prettyprint-override"><code>SELECT *
    FROM mapfiles_zones a
    WHERE a.mappath=? AND a.y1 &lt;= $y AND a.y2 &gt;= $y
    GROUP BY a.zoneid
    ORDER BY abs(a.x1 - $x), abs(a.x2 - $x)
    LIMIT 1
</code></pre>

<p>Nearest region in Y-axis</p>

<pre class="lang-sql prettyprint-override"><code>SELECT *
        FROM mapfiles_zones a
        WHERE a.mappath=? AND a.x1 &lt;= $x AND a.x2 &gt;= $x
        GROUP BY a.zoneid
        ORDER BY abs(a.y1 - $y), abs(a.y2 - $y)
        LIMIT 1
</code></pre>

<p>I am having trouble comparing returned region arrays to determin which is closest in their respective axis. </p>

<p>I cannot use min() on the arrays in PHP because there are other numeric values that end up being analysed. I tried combining the arrays using UNION but get the error</p>

<pre><code>number of bound variables does not match number of tokens
</code></pre>

<p>How can I get the closest region from these two results in PHP, or better yet, would it be possible to combine these two queries to return the single closest region?</p>