sqlite，地理坐标索引？ [英] sqlite, index for geographical coordinates?

问题描述

我有一个包含地理数据的SQLite数据库。我的表定义如下：

I have a SQLite database that contains geographical data. My table is defined like this:

     CREATE TABLE GEO_OBJECTS (ID VARCHAR(30) PRIMARY KEY     NOT NULL,
                LATITUDE       NUMERIC(6,3)      NOT NULL,  
                LONGITUDE      NUMERIC(6,3)      NOT NULL)  

接下来我的软件（Java）正在使用这样的简单SQL请求来寻找GEO_OBJECTS：

Next my software (Java) is looking for GEO_OBJECTS using a simple SQL request like this:

SELECT * FROM GEO_OBJECTS WHERE纬度< = 123.4 AND latitude> = 26.32 AND经度< = 12.41和经度> = 6.23;

SELECT * FROM GEO_OBJECTS WHERE latitude <= 123.4 AND latitude >= 26.32 AND longitude <= 12.41 AND longitude >= 6.23;

如果我在纬度和经度上创建一个INDEX，它会改善表现吗？

Will it improve the performances if I create an INDEX on latitude and longitude?

接下来，当我得到此查询的结果时，我正在使用半字形公式来查找大圆距离的对象。

Next, when I get the result of this query I'm using the haversine formula to find the objects in great circle distance.

推荐答案

普通索引可能有助于某些一维间隔查询，但在两个或多个维度中，您需要设计一个索引对于这样的查询，例如R树。

A normal index might help for some one-dimensional interval queries, but in two or more dimensions, you need an index designed for such queries, such as an R-tree.

SQLite确实有一个 R-tree模块，但它可能尚未编译到您的数据库驱动程序中。

SQLite does have an R-tree module, but it might not have been compiled into your DB driver.

您可能会考虑 SpatiaLite ，源自SQLite，具有许多有用的功能（例如计算大圆距离）。

You might consider SpatiaLite, which is derived from SQLite and has many useful functions (such as computing the great circle distance).

这篇关于sqlite，地理坐标索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！