有人知道postGIS中的st_line_substring潜在的问题吗? [英] Does anyone know of potential problems with st_line_substring in postGIS?

问题描述

具体来说，我得到的结果是我不明白的.我的理解很可能是错误的，但我不这么认为.因此，我希望有人会说是的，这是一个已知问题"或否，它工作正常，这就是为什么您的理解是错误的."

这是我的例子.

首先，我具有以下经/纬度几何图形.

LINESTRING(-1.32007599 51.06707497，-1.31192207 51.09430508，-1.30926132 51.10206677，-1.30376816 51.11133597，-1.29261017 51.12981493，-1.27510071 51.15906713，-1.27057314 51.16440941，-1.26606703 51.16897072，-1 268751.7615 1.25793457 51.19727033，-1.25669003 51.20141159，-1.25347137 51.20630532，-1.24845028 51.21110444，-1.23325825 51.22457158，-1.2274003 51.22821321，-1.22038364 51.23103494，-1.20326042 51.23596583，-1.1776185 51.24346193.16368273616637827826 ，-1.10906124 51.26728549，-1.09052181 51.26823871，-1.08522177 51.26885628，-1.07013702 51.27070895，-1.03683472 51.27350122，-1.00917578 51.27572955，-0.98243952 51.2779175，-0.9509182 51.28095094，-0.9267354 51.2831.218.1808.918.1808.9158.918 0.7534647 51.29708113，-0.74908733 51.29795323，-0.7400322 51.2988924，-0.71535587 51.30125366，-0.68475723 51.29863749，-0.65746307 5 1.30220618，-0.63246489 51.30380261，-0.60542822 51.30645873，-0.58150291 51.3103219，-0.57603121 51.31150225，-0.57062387 51.31317883，-0.54195642 51.32475227，-0.4855442 51.34771616，-0.4553318 51.36283147)

这是在我的表格中名为"geom"的列中，称为"fibre_lines".当我运行以下查询时，

select st_length(geography(geom), false) as full_length,
  st_length(geography(st_line_substring(geom, 0, 1)), false) as full_length_2,
  st_length(geography(st_line_substring(geom, 0, 0.5)), false) as first_half,
  st_length(geography(st_line_substring(geom, 0.5, 1)), false) as second_half
from fibre_lines
where id = 10;

我得到以下结果...

76399.4939375278 76399.4939375278 41008.9667229201 35390.5272197668

前两个对我来说很有意义，它们只是假设球形地球的直线的长度.第一个只是使用显而易见的功能，而第二个则使用st_line_substring来获取整行的长度.这两个值是一致的.

但是最后两个让我感到困惑.我要的是行的前半部分的长度，然后我要的是后半部分的长度.我的期望是这些将相等或几乎相等.相反，上半场比下半场长约6公里.

如果在地图上绘制几何图形，您会看到直线的前三分之一完全朝北/朝南，而其余三分之二则朝东/向西.在请求球形地球上的长度时，我不会想到这会有所作为，但是很高兴得知我错了(只要也能解释为什么我错了).

作为参考，我正在使用的PostGIS是 1.5.8..如果这是一个错误，则可以升级到较新的版本，但并非无关紧要，所以我宁愿只这样做如果有必要.

有人有想法吗?

解决方案

尽管Arunas的评论没有直接回答我的问题，但确实使我进行了一些研究，我认为可以确定问题所在.我将其发布在这里的部分目的是使它直接进入我的脑海，并在某些情况下以防其他人想知道.

关键似乎是PostGIS在几何"和地理"之间的区别.几何形状是2D平面几何形状，通常在UTM中使用，并与地球在平坦表面上的投影(该投影是可配置的)一起使用.另一方面，地理的目的是专门存储纬度/经度信息，并且可以在球体或椭球上使用.因此，我遇到的基本问题是双重的:

1. 从我的原始帖子中可能看不出来的是，我正在使用几何对象存储纬度/经度信息，而不是UTM.我通常将其投射到某个地理区域，以便获得正确的答案，但是如果我将其实际存储为地理对象，它将更加正确.这样一来，就无需在代码中进行大量强制转换，并允许PostGIS告诉我什么时候做错了事.
2. 虽然ST_Length可与几何或地理一起使用，但ST_Line_Substring仅与几何一起使用.因此，当我要求它的中点时，我是在问它一个平面几何体的中点.这将为我提供关于纬度坐标的正确答案，但是对于经度，它将有一个误差项(对于大多数投影而言)随着我离赤道的距离增加而增加.

我研究了PostGIS的较新版本，它们似乎没有ST_Line_Substring或类似的东西，这些东西会给我50％的地理位置，因此我将不得不硬"地使用ST_Length给出我所有的段长度，然后将它们加起来并进行插值所需的数学运算.

Specifically I'm getting a result that I do not understand. It is possible that my understanding is simply wrong, but I don't think so. So I'm hoping that someone will either say "yes, that's a known problem" or "no, it is working correct and here is why your understanding is wrong".

Here is my example.

To start I have the following geometry of lat/longs.

LINESTRING(-1.32007599 51.06707497,-1.31192207 51.09430508,-1.30926132 51.10206677,-1.30376816 51.11133597,-1.29261017 51.12981493,-1.27510071 51.15906713,-1.27057314 51.16440941,-1.26606703 51.16897072,-1.26235485 51.17439257,-1.26089573 51.17875111,-1.26044512 51.1833917,-1.25793457 51.19727033,-1.25669003 51.20141159,-1.25347137 51.20630532,-1.24845028 51.21110444,-1.23325825 51.22457158,-1.2274003 51.22821321,-1.22038364 51.23103494,-1.20326042 51.23596583,-1.1776185 51.24346193,-1.16356373 51.24968088,-1.13167763 51.26363353,-1.12247229 51.2659966,-1.11629248 51.26682901,-1.10906124 51.26728549,-1.09052181 51.26823871,-1.08522177 51.26885628,-1.07013702 51.27070895,-1.03683472 51.27350122,-1.00917578 51.27572955,-0.98243952 51.2779175,-0.9509182 51.28095094,-0.9267354 51.28305811,-0.90499878 51.28511151,-0.86051702 51.2883055,-0.83661318 51.29023789,-0.7534647 51.29708113,-0.74908733 51.29795323,-0.7400322 51.2988924,-0.71535587 51.30125366,-0.68475723 51.29863749,-0.65746307 51.30220618,-0.63246489 51.30380261,-0.60542822 51.30645873,-0.58150291 51.3103219,-0.57603121 51.31150225,-0.57062387 51.31317883,-0.54195642 51.32475227,-0.4855442 51.34771616,-0.4553318 51.36283147)

This is in a column called "geom" in my table, called "fibre_lines". When I run the following query,

select st_length(geography(geom), false) as full_length,
  st_length(geography(st_line_substring(geom, 0, 1)), false) as full_length_2,
  st_length(geography(st_line_substring(geom, 0, 0.5)), false) as first_half,
  st_length(geography(st_line_substring(geom, 0.5, 1)), false) as second_half
from fibre_lines
where id = 10;

I get the following result...

76399.4939375278 76399.4939375278 41008.9667229201 35390.5272197668

The first two make sense to me, they are simply the length of my line assuming a spherical earth. The first is just using the obvious function while the second is using st_line_substring to get the length of the entire line. These two values agree.

But the last two have me puzzled. I am asking for the length of the first half of the line, then I'm asking for the length of the last half. My expectation was that these would be equal or nearly equal. Instead the first half is about 6km longer than the second half.

If you plot the geometry on the map you will see that the first third of the line is fairly north/south oriented and the remaining two thirds are more east/west. I wouldn't have thought that would make a difference when asking for the length on a spherical earth, but I am happy to be told that I'm wrong (so long as it is also explained why I'm wrong).

For reference the PostGIS I am using is 1.5.8. If this is a bug, upgrading to a newer version is possible, but not trivial, so I would prefer to only do that if it is necessary.

Anyone have ideas?

解决方案

While Arunas' comments didn't directly answer my question, it did lead me to some research that I think identifies the problem. I'm posting it here in part to get it straight in my own mind and in part in case others are wondering.

It seems the key is the PostGIS distinction between a "geometry" and a "geography". A geometry is a 2D planar geometry that is typically in UTMs and used with a projection of the globe onto a flat surface (which projection is configurable). A geography, on the other hand, is designed to store latitude/longitude information specifically and is used to work either on a sphere or a spheroid. So the essential problem I have is twofold:

1. Perhaps not obvious from my original post is that I am using a geometry object to store lat/long information rather than UTMs. I cast that to a geography most of the time so that I get the correct answers, but it would be more correct if I actually stored it as a geography object. That would eliminate the need for a number of the casts in my code as well as allow PostGIS to tell me when I am doing something wrong.
2. While ST_Length will work with either a geometry or a geography, ST_Line_Substring only works with geometries. Hence when I ask it for the halfway point, I am asking it for the halfway point of a flat geometry. This will give me the correct answer for the latitude coordinate, but for the longitude it will have an error term that increases (for most projections) the farther I am from the equator.

I've looked into newer versions of PostGIS and they don't seem to have an ST_Line_Substring or anything similar that will give me the 50% point of a geography, so I will have to do it the "hard" way by using ST_Length to give me all my segment lengths and then adding them up and doing the math needed for my interpolation.

这篇关于有人知道postGIS中的st_line_substring潜在的问题吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！