Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Using database functions like <a href="http://msdn.microsoft.com/en-us/library/bb933808.aspx" rel="nofollow noreferrer">STDistance</a> for all n records is suboptimal. Your CPU overhead will increase exponentially.</p> <p>What you should do is check for the amount of points within a rectangle around the current epicenter you are searching. Here's an example (in MySQL):</p> <pre><code>SELECT * FROM `points` WHERE `latitude` &gt;= X1 AND `latitude` &lt;= X2 AND `longitude` &gt;= Y1 AND `longitude` &lt;= Y2 </code></pre> <p>This provides a reduced <code>superset</code> of points that should then be further reduced by calculating the orthodromic distance (with respect to the curvature of the Earth) using the <a href="http://en.wikipedia.org/wiki/Haversine_formula" rel="nofollow noreferrer">Haversine formula</a>.</p> <p><strong>Don't forget</strong> to set up a <a href="http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html" rel="nofollow noreferrer">composite index</a> on <code>latitude</code> and <code>longitude</code>.</p> <p><img src="https://i.stack.imgur.com/d78lX.jpg" alt="Orthodromic distance"></p> <p>Here it is in PHP:</p> <pre><code>&lt;?php function haversine($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') { $theta = $longitude1 - $longitude2; $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta))); $distance = acos($distance); $distance = rad2deg($distance); $distance = $distance * 60 * 1.1515; switch ($unit) { case 'Mi': break; case 'Km': $distance = $distance * 1.609344; } return (round($distance, 2)); } ?&gt; </code></pre> <p><strong>To recap</strong>:</p> <p>Here's an example image illustrating what to do:</p> <p><img src="https://i.stack.imgur.com/gg7Zp.png" alt="Example with CN Tower"></p> <p>The first search would involve a bounding box collision search (MySQL example) to determine the <code>superset</code>, excluding the red points. The second verification process would involve calculating if the points are within an appropriate orthodromic distance with the Haversine formula (PHP example) and taking a <code>subset</code> (composed of the black points).</p>
    singulars
    1. This table or related slice is empty.
    1. POMost efficient way to save way points and do comparisons?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDaniel Li
    1. USDaniel Li
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMost efficient way to save way points and do comparisons?
      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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for your answer, i'm going to think about a solution implementing this idea! I have a concern with this solution, i think it works fine for points that are very close, when we are talking about bigger distance, the points are spread in a bigger area, it gets complicated because it doesn't work for me to do a very big rectangle, the match criteria between routes must be more precise. Thank you!
      singulars
      1. PO
      1. USMario Alberto Barrantes Quesad
    2. CONo problem, good luck with your algorithm.
      singulars
      1. PO
      1. USDaniel Li
 

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