title: Storing Lat Lng values in MySQL using Spatial Point Type
body: <p>Tech used: MySQL 5.1 and PHP 5.3</p>
  
  <p>I am just designing a new database for a site I am writing. I am looking at the best way of now storing Lat and Lng values. </p>
  
  <p>In the past I have been using DECIMAL and using a PHP/MySQL select in the form:</p>
  
  <pre><code>SQRT(POW(69.1 * (fld_lat - ( $lat )), 2) + POW(69.1 * (($lon) - fld_lon) * COS(fld_lat / 57.3 ), 2 )) AS distance
  </code></pre>
  
  <p>to find nearest matching places.</p>
  
  <p>Starting to read up more on new technologies I am wondering if I should use Spatial Extensions. <a href="http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html</a></p>
  
  <p>Information is quite thin on the ground though and had a question on how to store the data. Instead of using DECIMAL, would I now use POINT as a Datatype?</p>
  
  <p>Also, once stored as a POINT is it easy just to get the Lat Lng values from it in case I want to plot it on a map or should I additionally store the lat lngs as DECIMALS again as well?</p>
  
  <p>I know I should prob use PostGIS as most posts on here say I just don't want to learn a new DB though!</p>
  
  <p><strong>Follow up</strong></p>
  
  <p>I have been playing with the new POINT type. I have been able to add Lat Lng values using the following:</p>
  
  <pre><code>INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London School of Economics", GeomFromText( 'POINT(51.514 -0.1167)' ));
  </code></pre>
  
  <p>I can then get the Lat and Lng values back from the Db using:</p>
  
  <pre><code>SELECT X(geoPoint), Y(geoPoint) FROM spatialTable;
  </code></pre>
  
  <p>This all looks good, however the calculation for distance is the bit I need to solve. Apparently MySQL has a place-holder for a distance function but won't be released for a while. In a few posts I have found I need to do something like the below, however I think my code is slightly wrong:</p>
  
  <pre><code>SELECT
   placeName,
   ROUND(GLength(
    LineStringFromWKB(
     LineString(
      geoPoint, 
      GeomFromText('POINT(52.5177, -0.0968)')
    )
    )
  ))
  AS distance
  FROM spatialTable
  ORDER BY distance ASC;
  </code></pre>
  
  <p>In this example geoPoint is a POINT entered into the DB using the INSERT above.</p>
  
  <p><code>GeomFromText('POINT(52.5177, -0.0968)'</code> is a Lat Lng value I want to calculate a distance from.</p>
  
  <p><strong>More Follow-up</strong></p>
  
  <p>Rather stupidly I had just put in the ROUND part of the SQL without really thinking. Taking this out gives me:</p>
  
  <pre><code>SELECT
  placeName,
  (GLength(
  LineStringFromWKB(
    LineString(
      geoPoint, 
      GeomFromText('POINT(51.5177 -0.0968)')
    )
   )
  ))
  AS distance
  FROM spatialTable
  ORDER BY distance ASC
  </code></pre>
  
  <p>Which seems to give me the correct distances I need.</p>
  
  <p>I suppose the only thing currently that needs answering is any thoughts on whether I am just making life difficult for myself by using Spatial now or future-proofing myself...</p>