2gis中的Postgis中2个POINT之间的距离,以米为单位 [英] Distance between 2 POINTs in Postgis in srid 4326 in metres
问题描述
这可能是一个简单的问题,但是我对PostGIS的掌握不是很好,也不能完全理解所有这些内容.
This is probably a simple question, but I'm not very good at PostGIS and don't fully grok all of this.
基本上,我有一个带有POINT列(point
)的表(nodes
).我已经在此列上创建了索引
Basically I have a table (nodes
) with a POINT column (point
). I have created an index on this column
create index nodes__points on nodes using gist (point)
该列是使用
select addgeometrycolumn('nodes', 'points', 'POINT', 4326, 2)
我正在使用srid 4326,因为我要添加格式(纬度,经度)的数据. (即在爱尔兰都柏林的位置为lat = 53.353 lon = -6.264(我已在GeomFromText('POINT(-6.264 53.535)')
中添加)的坐标系).
I am using srid 4326 because I'm adding data that's in the form (latitude, longitude). (i.e. the co-ordinate system where the position of Dublin, Ireland is lat=53.353 lon=-6.264 (which I've added with GeomFromText('POINT(-6.264 53.535)')
)).
对于每个点,我想找到以该点为中心大约在1km框内的所有点(因此selcet a.id, count(*) from nodes as a, nodes as b where SOME_DISTANCE_FUNCTION_HERE(a.point, b.point, 1000) group by a.id;
.不必精确,只需一个粗略的幽默感即可.1kmbbox可以,则以1公里为圈是可以的.不一定要精确到1公里,只要一个数量级即可.
For each point, I want to find all points that are roughly within a 1km box centred on that point (so selcet a.id, count(*) from nodes as a, nodes as b where SOME_DISTANCE_FUNCTION_HERE(a.point, b.point, 1000) group by a.id;
. It doesn't have to be exact, just a rough hueristic figure. a 1km bbox is fine, a 1km circle is fine. It doesn't have to be exactly 1km, just that order of magnitude.
ST_Distance
/ST_DWithin
/etc.所有都使用SRID的单位,对于4326/WGS64,该单位是度(因此1 = 1纬度/经度).但是我想用米.
The ST_Distance
/ST_DWithin
/etc. all use the units of the SRID, which for 4326/WGS64 is degrees (so 1 = 1 degree of lattitude/longitude). But I want to use metres.
我尝试了可以使用米的ST_distance_sphere
和st_dwithin
,但是如果执行此操作,则explain
表示未使用索引.
I tried ST_distance_sphere
and st_dwithin
which can use metres, but if I do that, the explain
shows that the index isn't being used.
我如何大致得到我想要的东西,并使用地理索引?
How can I get roughly what I want, and use the geographical index?
更新:这是在PostgreSQL 9.1和PostGIS 2.0 svn构建上.
UPDATE: This is on PostgreSQL 9.1 and PostGIS 2.0 svn build.
推荐答案
自编写此书以来,我发现PostGIS中的"geographic"类型与"geometry"类型相对,
Since writing this, I have discovered the "geographic" as opposed to the "geometry" type in PostGIS, which might do exactly what I want.
这篇关于2gis中的Postgis中2个POINT之间的距离,以米为单位的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!