performance - 关于在 Postgresql 中存储 Lat/Lng 坐标(列类型)

Question

我对 postgresql 比较陌生，这就是为什么我求助于那些比我更有经验的人。

我将坐标存储在 postgresql 数据库中。

They look like this: 35.21076593772987,11.22855348629825 35.210780222605616,11.22826420209139 35.210777635062875,11.228241328291957 35.210766843596794,11.228219799676775 35.210765045075604,11.228213072050166 35.21076234732945,11.228200962345223 35.21076324691649,11.228186161764323 35.21077314123606,11.228083902231146 35.210863083636866,11.227228492401766

它们的长度范围从大约 800 字符到 7000。

它们总是包括:

• 数字(0-9)
• 空格 ( )
• 标点符号和逗号 (. ,)

但它们还可以包括:

• 竖线(|)

现在我将它们存储为 TEXT，但据我了解，TEXT 存储在外部，这会影响性能。您会建议切换到另一种色谱柱类型吗？如果有，是哪一个？

非常感谢。

Answer 1

为什么不为此使用 PostGIS？

您忽略了这种数据可能是理想的存储方式 - PostGIS 的数据类型，尤其是 geography 类型。

SELECT ST_GeogFromText('POINT(35.21076593772987 11.22855348629825)');

通过使用geography，您可以将数据存储在一个典型的类型中，该类型支持各种强大的操作和对该类型的索引。当然，这只是一个点；我强烈怀疑您的数据实际上是一条线 或形状，在这种情况下您应该使用 the appropriate PostGIS geography constructor和输入格式。

使用 geography 的一大优势在于，它是一种专门用于询问现实世界中有关距离、“范围内”等问题的类型；您可以使用 ST_Distance_Spheroid 之类的东西来获取点之间的真实地球距离。

避免使用 PostGIS？

如果您想避免使用 PostGIS，而只是将其存储为原生类型，我建议您使用一个点数组:

postgres=> SELECT ARRAY[
     point('35.21076593772987','11.22855348629825'), 
     point('35.210780222605616','11.22826420209139'), 
     point('35.210777635062875','11.228241328291957') 
];
                                                       array                                                        
--------------------------------------------------------------------------------------------------------------------
 {"(35.2107659377299,11.2285534862982)","(35.2107802226056,11.2282642020914)","(35.2107776350629,11.228241328292)"}
(1 row)

... 除非您的点实际上代表了一条线或形状，在这种情况下，请使用适当的类型 - path 或 多边形分别。

这仍然是一个有用的紧凑表示 - 实际上比 text 更重要 - 在数据库中仍然很容易使用。

比较存储:

CREATE TABLE points_text AS SELECT '35.21076593772987,11.22855348629825 35.210780222605616,11.22826420209139 35.210777635062875,11.228241328291957 35.210766843596794,11.228219799676775 35.210765045075604,11.228213072050166 35.21076234732945,11.228200962345223 35.21076324691649,11.228186161764323 35.21077314123606,11.228083902231146 35.210863083636866,11.227228492401766'::text AS p

postgres=> SELECT pg_column_size(points_text.p) FROM points_text;
 pg_column_size 
----------------
            339
(1 row)

CREATE TABLE points_array AS
SELECT array_agg(point(px)) AS p from points_text, LATERAL regexp_split_to_table(p, ' ') split(px);

postgres=> SELECT pg_column_size(p) FROM points_array;
 pg_column_size 
----------------
            168
(1 row)

path 更加紧凑，并且可能是一种更真实的方式来模拟数据的真实:

postgres=> SELECT pg_column_size(path('35.21076593772987,11.22855348629825 35.210780222605616,11.22826420209139 35.210777635062875,11.228241328291957 35.210766843596794,11.228219799676775 35.210765045075604,11.228213072050166 35.21076234732945,11.228200962345223 35.21076324691649,11.228186161764323 35.21077314123606,11.228083902231146 35.210863083636866,11.227228492401766'));
 pg_column_size 
----------------
             96
(1 row)

除非它是一个封闭的形状，在这种情况下使用polygon。

不要...

无论如何，请不要将其建模为文本。当您尝试解决诸如“我如何确定该点是否落在该列中路径的 x 距离内”之类的问题时，它会让您稍后哭泣。 PostGIS 使这类事情变得简单，但前提是您首先要明智地存储数据。

参见 this closely related question ，其中讨论了不仅将内容推送到 text 字段中的充分理由。

也不要过分担心内联和外联存储。您对此无能为力，只有在正确理解数据模型的语义后，您才应该处理它。