title: Storing GPS locations in a database varchar field
body: <p>I'd be grateful for any advice that anyone has regarding:</p>
  
  <p><strong>How you you effectively store a gps (or any floating point number) in a varchar field that can be <em>indexed</em>.</strong></p>
  
  <hr>
  
  <p>Background:</p>
  
  <p>We develop a content management system that can effectively store files of any type together with a collection of metadata.  This file/metadata is stored as follows:</p>
  
  <pre><code>file_table              metadata_table
  ----------              --------------
  file_id         -&gt;      file_id (number)
  file_name               metadata_id (number)
  file_location           metadata_value (varchar)
  ...etc
  </code></pre>
  
  <p>I've been asked to provide support for geo-tagging files (ie. storing gps coordinates as metadata).  Additionally, we'd also like to support files that have multiple geo-tags.</p>
  
  <p>Now as far as I see I have a few options:</p>
  
  <p><strong>1) Store latitude and longitude within the same metadata_value varchar (eg. '52.4343242,-1.32324').</strong></p>
  
  <p>How would I query against this string?  Is there anything clever I can do with sql that will allow me to query against "components" of a string?  Could I store the coordinate as an xml string - would this help?  How can this be effectively indexed?</p>
  
  <p><strong>2) Store latitude and longitude as separate rows in the <em>metadata_table</em>.</strong></p>
  
  <p>This solution fixes the problem of supporting easier querying (at the expense of complexity and unwieldiness, especially when I'll be storing multiple geo-tags per file), however I'm still faced with the problem of indexing.</p>
  
  <p>I can convert the varchars to floating point when querying, however I'm not sure whether this will ignore the index I have on <em>metadata_table.metadata_value</em> and perform table-scans instead.</p>
  
  <p><strong>3) Create dedicated floating point fields to store gps data.</strong></p>
  
  <p>This is the least desirable option since it goes against the grain of the design to add database fields for a specific metadata.  Not all files will store gps data.</p>
  
  <p>Any help or advise appreciated.</p>