title: Store and sort by distance using latitude and longitude Symfony2 Doctrine
body: <p>I am working in Symfony2 with Doctrine.</p>
  
  <p>I have a database of entities named <code>Landmarks</code> where each entity has a <code>1:many</code> relationship with a <code>MapMarker</code> entity, and it is the <code>MapMarker</code> entity which has a <code>latitude</code> and <code>longitude</code> field, e.g. "Central Park" might be a <code>Landmark</code> with many "entrances" which would be stored as <code>MapMarkers</code>.</p>
  
  <p>I then have another entity named <code>Property</code>, which has a <code>1:1</code> relationship with a <code>MapMarker</code>.</p>
  
  <p>For any given <code>Property</code>, What is the best way to find all the nearby <code>Landmarks</code> in the database, sorted by the Landmark's closest <code>MapMarker</code>?</p>
  
  <p>In a legacy version of this application, without Doctrine or Symfony2, I was doing it this way:</p>
  
  <ol>
  <li><p>Retrieve all the <code>Landmarks</code> in database</p></li>
  <li><p>For each <code>Landmark</code>, go through each of its <code>MapMarkers</code> in a PHP function and calculate the distance between the P<code>roperty's MapMarker's lat,lng</code> and the <code>Landmarks MapMarker's lat,lng</code>.</p></li>
  <li><p>If any of the calculated distances was less than or equal to some pre-determined constant I had chosen (say 3km) then I made linked the <code>Property</code> to the <code>Landmark</code> in a <code>many:many</code> relatioship.</p></li>
  <li><p>Later, when querying the database for a <code>Property's</code> nearby <code>Landmarks</code>, it would go through only the linked <code>Landmarks</code>' <code>MapMarkers</code> and then calculate, again, the nearest <code>MapMarker</code> and distance for presentation like such: </p></li>
  </ol>
  
  <blockquote>
    <blockquote>
      <p>Nearby Landmarks to your Property: </p>
    </blockquote>
    
    <ul>
    <li><strong>Central Park</strong>, Main Gate - 2.4km</li>
    <li>etc.</li>
    </ul>
  </blockquote>
  
  <p>The benefits of this were that it by creating a permanent link between the <code>Property</code> and the <code>Landmark</code>, I automatically had a "short-list" of nearby <code>Landmarks</code> for which to calculate the expensive distance formula. </p>
  
  <p>However as you likely noticed, problems arise when a new <code>Landmark</code> is added, or the <code>MapMarkers</code> of a <code>Landmark</code> change. Also, it is still quite slow to be calculating the distance every time a <code>Property</code> is searched (or reverse if finding all <code>Properties</code> by <code>Landmark</code>).</p>
  
  <p>Really hoping to come up with a better way now that I am migrating to Symfony2 and Doctrine.</p>
  
  <p>Any ideas? </p>