Reputation: 13731
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.
Upvotes: 7
Views: 13705
Reputation: 123
Example:
location = position.coords.latitude + "," + position.coords.longitude;
location
variable, do this as follow.Example:
$location = explode(',',$fatched_row['location']); //in php
OR
in javaScript use Split() Function.
you will get location.latitude
& location.longitude
Use this location.latitude
& location.longitude
into your map.
NOTE: location.latitude & location.longitude variable have string type,change it into number.
Example:
Number(location.latitude);
Number(location.longitude);
google.maps.LatLng($latitude,$longitude);
Upvotes: 0
Reputation: 1479
EDIT: see comments for where this falls short.
To answer your base question, ignoring any of the reasoning behind it, you could used function-based indexes. If you go with your option #2, this should be straight-forward.
If you stick with #1, you'll just have to add some instr/substr voodoo; for example:
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'd 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
)
)
);
Upvotes: 1
Reputation: 239824
Generally, if I'm having a one-size-fits-all table (and I'm not arguing they're not useful), I tend to allow a range of datatypes for storage, and enfore the types. E.g.
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 meta data table would look like:
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 whole point being that 1) You store for each Meta data item the expected type, and 2) You enforce that in the MetaData table.
Upvotes: 1
Reputation: 7316
Using dedicated floating point fields or columns of type mdsys.sdo_geometry are the best way to store this data. If a file doesn't have GPS data those fields will be empty but why should that be a problem? If a file could have more than one point associated use a detail table.
Options 1 and 2 are a 'generic' solution. Generic database solutions are slow because they are more difficult to index and collecting statistics becomes harder, so life becomes more difficult for the query optimizer.
Also reporting for collecting management information with tools like Cognos (business intelligence) over a generic solution is harder for your users.
Store dates in a date field, numbers in a number field and geographical information in a geographical field (mdsys.sdo_geometry).
Here it is explained why storing a date like '20031603' in a number field slows things down: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:77598210939534 .
Upvotes: 2
Reputation: 7316
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.
Upvotes: 4
Reputation: 301135
Although you've tagged this with Oracle, I figured this would be useful for anyone using MySQL: use the spatial extensions to store location data.
Upvotes: 3
Reputation: 24069
To option 1, I can say: Use Gps eXchange Format (GPX). It is the standard way to save GPS points. There are options to mark waypoints, tracks and point of interests.
Nontheless, it's not easy to query.
Upvotes: 2