Saving GPS locations in the varchar field of the database

Question

I would be grateful for any advice anyone has:

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

---

Background:

We are developing a content management system that can efficiently 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 was asked to provide support for survey files (i.e. store gps coordinates as metadata). In addition, we would also like to support files with multiple geotags.

Now, as far as I can see, I have several options:

1) Keep latitude and longitude within the same metadata_value varchar (e.g. '52 .4343242, -1.32324 ').

How can I query this string? Is there anything clever that I can do with sql that allows me to query the "components" of a string? Can I save the coordinate as an xml string - will this help? How can this be effectively indexed?

2) Store latitude and longitude as separate lines in metadata_table.

This solution eliminates the problem of simplifying queries (due to complexity and cumbersomeness, especially when I will store several geo-tags for each file), however, I still encounter the indexing problem.

I can convert varchars to a floating point when prompted, however I'm not sure if this will ignore the index that I have on metadata_table.metadata_value and scan the tables instead.

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

This is the least desirable option because it goes against the grain of the project to add database fields for specific metadata. Not all files will store gps data.

Any help or recommendation appreciated.

Answer 1

You can use the Oracle locator. A free subset of Oracle Spatial performs all kinds of geographic manipulations and indexing of spatial data: http://www.oracle.com/technology/products/spatial/index.html

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

Answer 2

Could this be any help: http://postgis.refractions.net

Answer 3

Although you noted this with Oracle, I decided that it would be useful for anyone using MySQL: use spatial extensions to store location data .

Answer 4

Using highlighted fields or floating point columns of type mdsys.sdo_geometry is the best way to save this data. If the file does not have GPS data, these fields will be blank, but why should this be a problem? If the file may contain more than one point, use the detail table.

Options 1 and 2 are a “general” solution. Generic database solutions are slow because they are more difficult to index and statistics collection is more difficult, so life becomes more difficult for the query optimizer.

It is also more difficult for your users to report the collection of management information using tools such as Cognos (Business Intelligence) in relation to the overall solution.

Save dates in a date field, numbers in a number field, and geographical information in a geographical field (mdsys.sdo_geometry).

This explains why storing a date of type "20031603" in the number field slows down: http://asktom.oracle.com/pls/asktom/f?p=100:11:07::::P11_QUESTION_ID:77598210939534 .

Answer 5

For option 1, I can say: use the GPS eXchange Format (GPX) . This is the standard way to save GPS points. There are options for designating waypoints, tracks, and points of interest.
However, it is not easy to ask.

Answer 6

EDIT: see comments where this does not fit.

To answer your basic question, ignoring any considerations behind it, you could use functional indexes . If you go with option number 2, it should be straightforward.

If you are sticking to # 1, you just need to add a few vstoo instr / substr; eg:

 select to_number( substr( '52.4343242,-1.32324' , 1 , instr( '52.4343242,-1.32324', ',' ) - 1 ) ) as lattitude , to_number( substr( '52.4343242,-1.32324' , instr( '52.4343242,-1.32324', ',' ) + 1 ) ) as longitude from dual; 

So you would do something like:

 create index lat_long_idx on metadata_table ( to_number( substr( metadata_value , 1 , instr( metadata_value, ',' ) - 1 ) ) , to_number( substr( metadata_value , instr( metadata_value, ',' ) + 1 ) ) ); 

Answer 7

As a rule, if I have a table of the same size for everyone (and I do not claim that they are not useful), I tend to allow a set of data types for storing and higher types. For example.

 CREATE TABLE MetaDataType ( MetaDataID int IDENTITY(1,1) not null, MetaDataType varchar(10) not null, constraint PK_MetaDataType PRIMARY KEY (MetaDataID), constraint UQ_MetaDataType_TypeCheck UNIQUE (MetaDataID,MetaDataType), constraint CK_MetaDataType CHECK (MetaDataType in ('INT','CHAR','FLOAT')) ) 

And then the metadata table will look like this:

 CREATE TABLE MetaData ( FileID int not null, MetaDataID int not null, MetaDataType varchar(10) not null, IntValue int null, CharValue varchar(max) null, FloatValue float null, constraint PK_MetaData PRIMARY KEY (FileID,MetaDataID), constraint FK_MetaData_Files FORIEGN KEY (FileID) references /* File table */, constraint FK_MetaData_Types FOREIGN KEY (MetaDataID,MetaDataType) references MetaDataTypes (MetaDataID,MetaDataType), constraint CK_MetaData_ValidTypes ((MetaDataType = 'INT' or IntValue is null) and (MetaDataType = 'CHAR' or CharValue is null) and (MetaDataType = 'FLOAT' or FloatValue is null)) ) 

The thing is that 1) you save the expected type for each metadata element and 2) you apply this in the MetaData table.