在数据库的varchar字段中存储GPS位置

Question

如果您有关于以下问题的任何建议，我将不胜感激：

如何在可索引的varchar字段中有效存储GPS（或任何浮点数）。

---

背景：

我们开发了一个内容管理系统，可以有效地存储任何类型的文件以及一组元数据。该文件/元数据存储如下：

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

我被要求提供对地理标记文件的支持（即将GPS坐标作为元数据存储）。此外，我们还希望支持具有多个地理标记的文件。

就我所看到的情况，有几种选择：

1. 在相同的metadata_value varchar中存储纬度和经度（例如'52.4343242，-1.32324'）。

如何查询这个字符串？是否有任何聪明的sql技巧可以让我对字符串的“组成部分”进行查询？我可以把坐标存储为xml字符串吗？这有助于什么？如何有效地建立索引？

2. 在metadata_table中分别存储纬度和经度的行。

这种解决方案在支持更容易的查询问题上有益处（代价是复杂性和笨重性，特别是当我将存储每个文件的多个地理标记时），但我仍面临着索引问题。

我可以在查询时将varchar转换为浮点数，但我不确定这是否会忽略我在metadata_table.metadata_value上的索引并执行表扫描。

3. 创建专用的浮点字段来存储GPS数据。

这是最不理想的选择，因为它违反了为特定元数据添加数据库字段的设计原则。并非所有文件都会存储GPS数据。

欢迎任何帮助或建议。

- Alan

Answer 1

你可以使用Oracle定位器。 免费的Oracle空间子集可用于执行各种不同的地理操作和空间数据索引：http://www.oracle.com/technology/products/spatial/index.html 通过使用列类型mdsys.sdo_geometry，您可以将点、点云、线、多边形和3D物体存储在数据库中。

- tuinstoel

Answer 2

这个链接可能会有所帮助：http://postgis.refractions.net。

- Ctrl Alt D-1337

Answer 3

尽管您已经将此标记为Oracle，但我认为这对于使用MySQL的任何人都很有用：使用空间扩展来存储位置数据。

- Paul Dixon

Answer 4

使用专用的浮点字段或类型为mdsys.sdo_geometry的列存储这些数据是最好的方式。如果文件没有GPS数据，那么这些字段将为空，但这应该成为问题吗？如果一个文件可能有多个相关联的点，请使用详细表。

选项1和2是“通用”解决方案。通用数据库解决方案较慢，因为更难索引并且收集统计数据变得更加困难，所以对于查询优化器来说生活变得更加困难。

此外，使用像Cognos（商业智能）之类的工具收集管理信息的报告在通用解决方案上更难为您的用户提供。

将日期存储在日期字段中，将数字存储在数字字段中，将地理信息存储在地理字段（mdsys.sdo_geometry）中。

在这里，解释了为什么将日期“20031603”存储在数字字段中会减慢速度：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:77598210939534。

- tuinstoel

Answer 5

编辑：请参见评论以了解此方法的不足之处。

回答您的基本问题，忽略任何背后的原因，您可以使用基于函数的索引。如果您选择选项＃2，则应该很简单。

如果您坚持使用＃1，则只需添加一些instr / substr巫术即可，例如：

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;

所以你可以这样做：

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
      )
    )
);

- Alan Koskelin

---

to_number(instr...将在元数据值中没有存储纬度和经度时产生错误。请记住，该字段以通用方式使用，因此它应该能够存储不同的数据。我认为在通用存储纬度和经度时几乎不可能进行索引。 - tuinstoel

好的观点。这也可能意味着仅执行索引的substr部分是无效的。也许创建一个仅包含lat/long数据并对其进行索引的物化视图是一个选择。 - Alkini

Answer 6

通常来说，如果我正在使用一种通用的表格（我不会争辩它们没有用），我倾向于允许多种数据类型进行存储，并强制执行这些类型。例如：

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'))
)

然后元数据表看起来会像这样：

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))
)

整个重点在于：1）您为每个元数据项存储预期类型，2）您在MetaData表中强制执行该类型。

- Damien_The_Unbeliever

---

忘了说，上述设计允许存储空值。如果您想要防止这种情况，请添加额外的CHECK约束条件以确保至少一个*Value列不为null。 - Damien_The_Unbeliever

Answer 7

对于选项1，我建议使用GPS交换格式（GPX）。这是保存GPS点的标准方式。它有标记航点、轨迹和兴趣点的选项。
然而，查询并不容易。

- guerda

Answer 8

只需将纬度和经度分别赋值给变量，如下所示，并将此变量作为字符串（varchar）存储到数据库中。

例如：

location = position.coords.latitude + "," + position.coords.longitude;

2. 当通过变量 location 获取位置时，请按以下方式进行。

示例：

$location = explode(',',$fatched_row['location']); //in php
OR
in javaScript use Split() Function.

你将会得到 location.latitude 和 location.longitude

将这个 location.latitude 和 location.longitude 应用到你的地图中。

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);

- Ashish Pipaliya