Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <pre><code>select t1.post_id, t1.meta_value as lat, t2.meta_value as lon from metatdatatable t1, metadatatable t2 where t1.meta_key = "_wp_field1" and t2.post_id = t1.post_id and t2.meta_key = "_wp_field2" </code></pre> <h2>Edit</h2> <p>...to use this as the basis for yr lat/long calc, you can either create a temp table, or use the results inline with a query similar to the (simplified DISTANCE calc) below</p> <pre><code>select d1.post_id, d1.distance from (select r1.post_id, ABS($lat - r1.lat) + ABS($lon - r1.lon) as DISTANCE from (select t1.post_id, t1.meta_value as lat, t2.meta_value as lon from metatdatatable t1, metadatatable t2 where t1.meta_key = "_wp_field1" and t2.post_id = t1.post_id and t2.meta_key = "_wp_field2") as r1 ) as d1 where d1.distance &lt;= 10 order by d1.distance ASC </code></pre> <p>NB. you might want to apply a rough filter to your lat/long results before doing the 'expensive' lat/long calculation on the results, or before storing in a temp table. The idea would be to ignore all of the r1 results clearly outside of a 10 mile radius. </p> <p>If you use a temp table it will be user session specific.</p> <h2>Edit 2</h2> <p>Have a look at <a href="http://calgary.rasc.ca/latlong.htm" rel="noreferrer">Shape of the Earth</a> for details, but essentially 7 minutes lat &amp; longitude is always greater than 10 miles, so if your lat &amp; longs are recorded in degrees this is 0.117 near enough. Any point differing by more than 0.117 from your target cannot be inside your 10 mile radius. This means you can filter the r1 table like :</p> <pre><code>(select t1.post_id, t1.meta_value as lat, t2.meta_value as lon from metatdatatable t1, metadatatable t2 where t1.meta_key = "_wp_field1" and t2.post_id = t1.post_id and t2.meta_key = "_wp_field2" and ABS(t2.meta_value - $lon) &lt; 0.117 and ABS(t1.meta_value - $lat) &lt; 0.117 ) as r1 </code></pre> <p>NB. If your data spans the Greenwich Meridian, International Date Line or Equator, this will not be strictly correct. Assuming all of your lat/longs are for North America it won't be a problem.</p>
    singulars
    1. This table or related slice is empty.
    1. POmysql join table on itself
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSteve De Caux
    1. USSteve De Caux
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POmysql join table on itself
      singulars
      1. PTQuestion
    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. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthanks - worked perfectly first time (with no changes)
      singulars
      1. PO
      1. USMatt Facer
    2. COby the way, do you know how I can then use those return values in another select statement? I basically need to run the one on this page: http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html This is why I was thinking of putting the results of your working query in to a temp table. But what if lots of people are searching at the same time - might this be a problem or is the temp table related to a users session?
      singulars
      1. PO
      1. USMatt Facer
    3. CO@Matt, have updated my answer accordingly
      singulars
      1. PO
      1. USSteve De Caux
 

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