Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>EDIT</strong> This location-finder comes up often enough that I've written an article on it.</p> <p><a href="http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/" rel="nofollow">http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/</a></p> <p><strong>Original Post</strong></p> <p>Let's start by dealing with the haversine formula once for all, by putting it into a stored function so we can forget about its gnarly details. NOTE: This whole solution is in statute miles. </p> <pre><code>DELIMITER $$ CREATE FUNCTION distance(lat1 FLOAT, long1 FLOAT, lat2 FLOAT, long2 FLOAT) RETURNS FLOAT DETERMINISTIC NO SQL BEGIN RETURN (3959 * ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(long1) - RADIANS(long2)) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) )); END$$ DELIMITER ; </code></pre> <p>Now let's put together a query that searches on the bounding box, and then refines the search with our distance function and orders by distance</p> <p>Based on the PHP code in the your question:</p> <p>Assume <code>$radius</code> is your radius, <code>$center_lat</code>, <code>$center_lng</code> is your reference point.</p> <pre><code>$sqlsquareradius = " SELECT post_id, lat, lng FROM ( SELECT post_id, lat, lng, distance(lat, lng, " . $center_lat . "," . $center_lng . ") AS distance FROM wp_geodatastore WHERE lat &gt;= " . $center_lat . " -(" . $radius . "/69) AND lat &lt;= " . $center_lat . " +(" . $radius . "/69) AND lng &gt;= " . $center_lng . " -(" . $radius . "/69) AND lng &lt;= " . $center_lng . " +(" . $radius . "/69) )a WHERE distance &lt;= " . $radius . " ORDER BY distance "; </code></pre> <p>Notice a few things about this.</p> <p>First, it does the bounding box computation in SQL rather than in PHP. There's no good reason for that, except keeping all the computation in one environment. <code>(radius / 69)</code> is the number of degrees in <code>radius</code> statute miles. </p> <p>Second, it doesn't fiddle with the size of the longitudinal bounding box based on latitude. Instead it uses a simpler, but slightly too large, bounding box. This bounding box catches a few extra records, but the distance measurement gets rid of them. For your typical postcode / store finder app the performance difference is negligible. If you were searching many more records (e.g. a database of all utility poles) it might not be so trivial.</p> <p>Third, it uses a nested query to do the distance elimination, to avoid having to run the distance function more than once for each item.</p> <p>Fourth, it orders by distance ASCENDING. This means your zero-distance results should show up first in the result set. It usually makes sense to list nearest things first.</p> <p>Fifth, it uses <code>FLOAT</code> rather than <code>DOUBLE</code> throughout. There's a good reason for that. The haversine distance formula is not perfect, because it makes the approximation that the earth is a perfect sphere. That approximation happens to break down at roughly the same level of accuracy as the epsilon for <code>FLOAT</code> numbers. So <code>DOUBLE</code> is deceptive numerical overkill for this problem. (Don't use this haversine formula to do civil engineering work like parking lot drainage, or you will get big puddles a couple of epsilon, a few inches, deep, I promise.) It's fine for store-finder applications.</p> <p>Sixth, you are definitely going to want to create an index for your <code>lat</code> column. If your table of locations doesn't change very often, it will help to create an index for your <code>lng</code> column as well. But your <code>lat</code> index will give you most of your query performance gain.</p> <p>Lastly, I tested the stored procedure and the SQL, but not the PHP.</p> <p>Reference: <a href="http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL" rel="nofollow">http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL</a> Also my experience with a bunch of proximity finders for health care facilities.</p> <p>--------------- EDIT --------------------</p> <p>If you don't have a user interface that lets you define a stored procedure, that's a nuisance. At any rate, PHP lets you use numbered parameters in the sprintf call, so you can generate the whole nested statement like this. NOTE: You might need %$1f etc. You'll need to experiment with this. </p> <pre><code>$sql_stmt = sprintf (" SELECT post_id, lat, lng FROM ( SELECT post_id, lat, lng, (3959 * ACOS(COS(RADIANS(lat)) * COS(RADIANS(%$1s)) * COS(RADIANS(lng) - RADIANS(%$2s)) + SIN(RADIANS(lat)) * SIN(RADIANS(%$1s)) )) AS distance FROM wp_geodatastore WHERE lat &gt;= %$1s -(%$3s/69) AND lat &lt;= %$1s +(%$3s/69) AND lng &gt;= %$2s -(%$3s/69) AND lng &lt;= %$2s +(%$3s/69) )a WHERE distance &lt;= %$3s ORDER BY distance ",$center_lat,$center_lng, $radius); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMissing results due to geo proximity formula (store locator)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USO. Jones
    1. USO. Jones
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the very detailed answer Ollie. Having some trouble implementing it to test it though. Sorry to be a pain but I'm really not familiar with MySQL stored procedures (I can work my way around a MySQL database with PhpMyAdmin but that's about where my MySQL knowledge dries up). Had a look around for some tutorials on creating stored functions using this GUI but haven't found anything to guide me through. Is it vital that the haversine formula is in this format or could it be included in with the rest of the php query?
      singulars
      1. PO
      1. USFourStacks
    2. COThanks for the edit Ollie - appreciate you converting it to php and all the help so far. Unfortunately it doesn't seem to produce any ID values at all. Doubled checked the code to make sure I didn't do anything stupid - couldn't see anything. Also tried your suggestion of changing the type specifier in the sprintf but no change. Wasn't sure if the lower case 'a' in your code was intentional or a typo but either way it produced no result.
      singulars
      1. PO
      1. USFourStacks
    3. COI've awarded the bounty to this answer because it was the most complete and fully-explained answer (despite not actually using it in the end - see my own answer below which I'm rolling with for now). That said this question generated some very good answers and I'm sure that the equations listed below would probably work for other peoples projects with a different setup to mine so they are all well worth a look.
      singulars
      1. PO
      1. USFourStacks
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload