Storing GPS locations in a database varchar field

Question

I'd be grateful for any advice that anyone has regarding:

How you you effectively store a gps (or any floating point number) in a varchar field that can be indexed.

---

Background:

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:

file_table              metadata_table
----------              --------------
file_id         ->      file_id (number)
file_name               metadata_id (number)
file_location           metadata_value (varchar)
...etc

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.

Now as far as I see I have a few options:

1) Store latitude and longitude within the same metadata_value varchar (eg. '52.4343242,-1.32324').

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?

2) Store latitude and longitude as separate rows in the metadata_table.

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.

I can convert the varchars to floating point when querying, however I'm not sure whether this will ignore the index I have on metadata_table.metadata_value and perform table-scans instead.

3) Create dedicated floating point fields to store gps data.

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.

Any help or advise appreciated.

Answer 1

You can use Oracle locator. The free subset of Oracle Spatial to do all kind of different geographical manipulations and indexing of spatial data: http://www.oracle.com/technology/products/spatial/index.html

With the use of column type mdsys.sdo_geometry you can store points, clouds of points, lines, polygons and 3D things in the database.