Databases
StackOverflow2013
Postsdbo
POMissing results due to geo proximity formula (store locator)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMissing results due to geo proximity formula (store locator)
    primarykey
    data
    text
    <p>OK - I've been wrestling with this for about 3 months on and off and since I've exhausted every geo proximity formula out there that I've come across and I'm no closer to getting the right results I figured it time to ask for some help.</p> <p><strong>THE AIM</strong></p> <p>I'm setting up a fairly basic implementation of a store locator. The user enters their postcode and selects from a predefined list of search radii. The gmaps API generates lat/long coordinates for this address and passes them to a php script. In this script the user coords are queried against a mysql database table (structure below)</p> <pre><code>post_id int(11) post_type varchar(20) lat float(10,6) lng float(10,6) </code></pre> <p>The results of this query (post ids) are entered into a wordpress query which generates the XML that contains the map marker data. (the wordpress query uses post__in and posts_per_page -1 to display info for all ID generated by the query</p> <p><strong>THE PROBLEM</strong></p> <p>In a nutshell, every implementation of the Haversine formula I've come across seems to result in missing markers - specifically any markers that are very close to the users entered coordinates (don't know precisely but I think it's within about 500m). This is a big problem as if the user enters their postcode and there is a store very close to their location it won't show up.</p> <p>I've tried about 8 different permutations of the forumla that I've dug up from various tutorials with the same results. Below is the formula that I'm currently using on the site which provides all markers except for the those very close to the users entered position:</p> <pre><code>$center_lat = $_GET["lat"]; $center_lng = $_GET["lng"]; $radius = $_GET["radius"]; // Calculate square radius search $lat1 = (float) $center_lat - ( (int) $radius / 69 ); $lat2 = (float) $center_lat + ( (int) $radius / 69 ); $lng1 = (float) $center_lng - (int) $radius / abs( cos( deg2rad( (float) $center_lat ) ) * 69 ); $lng2 = (float) $center_lng + (int) $radius / abs( cos( deg2rad( (float) $center_lat ) ) * 69 ); $sqlsquareradius = " SELECT post_id, lat, lng FROM wp_geodatastore WHERE lat BETWEEN ".$lat1." AND ".$lat2." AND lng BETWEEN ".$lng1." AND ".$lng2." "; // End $sqlsquareradius // Create sql for circle radius check $sqlcircleradius = " SELECT t.post_id, 3956 * 2 * ASIN( SQRT( POWER( SIN( ( ".(float) $center_lat." - abs(t.lat) ) * pi() / 180 / 2 ), 2 ) + COS( ".(float) $center_lat." * pi() / 180 ) * COS( abs(t.lat) * pi() / 180 ) * POWER( SIN( ( ".(float) $center_lng." - t.lng ) * pi() / 180 / 2 ), 2 ) ) ) AS distance FROM (".$sqlsquareradius.") AS t HAVING distance &lt;= ".(int) $radius." ORDER BY distance "; // End $sqlcircleradius $result = mysql_query($sqlcircleradius); $row = mysql_fetch_array( $result ); while($row = mysql_fetch_array( $result )) { // the contents of each row $post_ids[] = $row['post_id']; } </code></pre> <p>There was 1 formula that I tried that was suggested by Mike Pelley here: <a href="https://stackoverflow.com/questions/2695365/geolocation-sql-query-not-finding-exact-location">Geolocation SQL query not finding exact location</a></p> <p>This formula seemed to show markers that were very close to the users entered location but missed out others that should have been displayed within the given radius. To clear up any confusion this is the code I used:</p> <pre><code>$center_lat = $_GET["lat"]; $center_lng = $_GET["lng"]; $radius = $_GET["radius"]; $sql = " SELECT post_id, lat, lng, truncate((degrees(acos( sin(radians(lat)) * sin(radians(".$center_lat.")) + cos(radians(lat)) * cos(radians(".$center_lat.")) * cos(radians(".$center_lng." - lng) ) ) ) * 69.09*1.6),1) as distance FROM wp_geodatastore HAVING distance &lt;= ".$radius." ORDER BY distance desc "; // End $sqlcircleradius $result = mysql_query($sql); $row = mysql_fetch_array( $result ); while($row = mysql_fetch_array( $result )) { // Print out the contents of each row $post_ids[] = $row['post_id']; } </code></pre> <p><strong>THE REQUEST</strong></p> <p>Basically I would like to know why neither of these blocks of code are displaying the correct markers. If anyone can suggest any improvements to the code or can point me towards some resource that I might have missed that would be great</p> <p><strong><em>EDIT</em></strong></p> <p>Thought my psudeo answer was working but as it turns out that was still having problems. I've ended up going for a very different tack now and I'm using a very good jquery store locator which can be found here: <a href="http://www.bjornblog.com/web/jquery-store-locator-plugin" rel="nofollow noreferrer">http://www.bjornblog.com/web/jquery-store-locator-plugin</a></p> <p>Won't work for every project out there but for my needs it's perfect (and works!)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USFourStacks
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMissing results due to geo proximity formula (store locator)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMissing results due to geo proximity formula (store locator)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMissing results due to geo proximity formula (store locator)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs there a reason you're not using MySQL's built in geospacial capabilities? http://dev.mysql.com/doc/refman/5.0/en/creating-a-spatially-enabled-mysql-database.html
      singulars
      1. POMissing results due to geo proximity formula (store locator)
      1. USKenneth
    2. COI'm struggling to understand some stuff in your code. Why `HAVING` instead of `WHERE`? What's going on with the `int` stuff in `(float) $center_lat - ( (int) $radius / 69 );` and the `truncate` in your other query? Keep this fact in mind as you apply the formulae: a minute of longitude at the equator is defined as a nautical mile. An integer degree is 60 nautical miles. Finally, try getting rid of `BETWEEN` and using 'WHERE a>=lat1 AND a <=lat2` instead. It should give the same complexity of query and it spells out the inclusivity /exclusivity of the range you're searching.
      singulars
      1. POMissing results due to geo proximity formula (store locator)
      1. USO. Jones
    3. CO@Kenneth - I'm not using the geospatial queries for a couple of reasons. First, I'm using the [geo data store plugin](http://wordpress.org/extend/plugins/geo-data-store) to create and maintain my table of marker data. This plugin generates the table structure I showed above. Second, the vast majority of the tutorials I've come across for creating store locator type maps seem to recommend a table structure similar to the one listed. Perhaps these aren't the best reasons but I've come this far with the current setup and I'm pretty sure that what I'm trying to do should be possible.
      singulars
      1. POMissing results due to geo proximity formula (store locator)
      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