我是PostgreSQL和PostGIS的新手。我想在PostgreSQL 9.1.1数据库表中存储纬度和经度值。我将计算两点之间的距离,通过使用这个位置值找到更近的点。
我应该为纬度和经度使用哪种数据类型?
您可以使用数据类型 point
-组合(x,y)
,可以是您的lat/long。占用16字节:2个float8
内部编号
或者设置为两列,类型为 float
(= float8
或double precision
)。每个8字节。
或者 real
(= float4
),如果不需要额外的精度。每个4字节。
或者甚至 numeric
如果需要绝对精度的话。每组4个数字2个字节,加上3 - 8个字节的开销。
阅读关于数字类型和几何类型的精细手册。
geometry
和 geography
数据类型由附加模块PostGIS提供,在表中占用一个列。每个点占用32字节。有一些额外的开销,比如SRID。这些类型存储(long/lat),而不是(lat/long)。
从这里开始阅读PostGIS手册。
在PostGIS中,对于具有经纬度的点有地理数据类型
添加一个列:
alter table your_table add column geog geography;
插入数据
insert into your_table (geog) values ('SRID=4326;POINT(longitude latitude)');
4326是空间参考ID,它表示经度和纬度的数据,与GPS相同。更多信息:http://epsg.io/4326
顺序是经度,纬度-所以如果你把它画成地图,它是(x, y)。
要找到最近的点,你需要首先创建空间索引:
create index on your_table using gist (geog);
,然后请求,比如说,离给定点最近的5个点:
select *
from your_table
order by geog <-> 'SRID=4326;POINT(lon lat)'
limit 5;
我强烈提倡postgi。它是专门为这种类型的数据类型,它有开箱的方法来计算点之间的距离,在其他GIS操作中,你会发现在未来很有用
如果你不需要PostGIS提供的所有功能,Postgres(现在)提供了一个扩展模块叫做earthdistance。它使用point
或cube
数据类型,具体取决于您对距离计算的精度需求。
然后,您可以使用earth_box()
函数来-例如-查询在某个位置的一定距离内的点。
在PostGIS中,几何比地理(圆形地球模型)更受欢迎,因为计算更简单,因此更快。它也有更多可用的功能,但在很长的距离上精度较低。
将CSV的long和late字段导入DECIMAL(10,6)
列。6位数是10厘米的精度,对于大多数用例来说应该足够了。然后将导入的数据转换为正确的SRID
错了!
/* try what seems the obvious solution */
DROP TABLE IF EXISTS public.test_geom_bad;
-- Big Ben, London
SELECT ST_SetSRID(ST_MakePoint(-0.116773, 51.510357),4326) AS geom
INTO public.test_geom_bad;
正确的方法
/* add the necessary CAST to make it work */
DROP TABLE IF EXISTS public.test_geom_correct;
SELECT ST_SetSRID(ST_MakePoint(-0.116773, 51.510357),4326)::geometry(Geometry, 4326) AS geom
INTO public.test_geom_correct;
验证SRID不为零!
/* now observe the incorrect SRID 0 */
SELECT * FROM public.geometry_columns
WHERE f_table_name IN ('test_geom_bad','test_geom_correct');
使用WKT查看器和
验证长参数的顺序。 SELECT ST_AsEWKT(geom) FROM public.test_geom_correct
为最佳性能建立索引
CREATE INDEX idx_target_table_geom_gist
ON target_table USING gist(geom);
使用Point数据类型在单列中存储经度和纬度:
CREATE TABLE table_name (
id integer NOT NULL,
name text NOT NULL,
location point NOT NULL,
created_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT table_name_pkey PRIMARY KEY (id)
)
创建location列的索引:
CREATE INDEX ON table_name USING GIST(location);
GiST索引能够优化"最近邻"搜索:
SELECT * FROM table_name ORDER BY location <-> point '(-74.013, 40.711)' LIMIT 10;
注意:点的第一个元素是经度,第二个元素是纬度。
查询操作符
为完整起见,您可以使用PostgreSQL 11提供的复合类型列。
创建列:
CREATE TYPE geo AS (lat float, long float);
ALTER TABLE your_table ADD COLUMN location geo;
插入数据(选项):
-- with field names
INSERT INTO your_table (location.lat, location.long) VALUES (8.12345, -55.34567);
-- string syntax
INSERT INTO your_table (location) VALUES ('(8.12345, -55.34567)');
-- ROW() keyword
INSERT INTO your_table (location) VALUES (ROW(8.12345, -55.34567));
-- ROW() keyword is optional for multi-column composite types
INSERT INTO your_table (location) VALUES ((8.12345, -55.34567));
组合类型通常是不受欢迎的,因为它们就像一个不必要的"表"中的"表"。但在这种情况下,我认为它们提供了(轻微的)优势:
您可以像
point
一样在单列中编码您的地理位置,但还有一个额外的优点,您可以根据所需的精度(real
,float
,numeric
)选择组件数据类型。可以使用常用的" late, long"坐标的排序;当使用
point
作为地理位置值时,您宁愿使用"long, lat"为了符合PostgreSQL的实践,其中有几个函数期望:"点被当作(经度,纬度),而不是相反,因为经度更接近x轴的直观概念,而纬度更接近y轴。"
(PostgreSQL 15 Manual, on Point-Based Earth distance)
能够在
INSERT
语句中引用字段名,避免格式为(lat, long)或(long, lat)时的任何混淆。不依赖重量级PostGIS,而earthdistance模块,特别是"点基地球距离"的运算符
<@>
,仍然可以用来做接近搜索。(由于我们使用不同的数据类型,并且将组件顺序颠倒为(lat, long),因此您必须动态地创建point
值)