如何使用Postgis配置PostgreSQL以计算距离 [英] How to configure PostgreSQL with Postgis to calculate distances

问题描述

我知道这可能是愚蠢的问题，但是我正在寻找一段时间，找不到正确的答案. 我已经安装了PostGIS的PostgreSQL数据库.在一个表中，我有带有lon lat的条目(假设列分别是place，lon，lat).

I know that it might be dumb question, but I'm searching for some time and can't find proper answer. I have PostgreSQL database with PostGIS installed. In one table I have entries with lon lat (let's assume that columns are place, lon, lat).

我应该在此表中添加什么内容和/或可以使用什么程序，以便能够以米为单位计算这些位置之间的距离.

What should I add to this table or/and what procedure I can use, to be able to count distance between those places in meters.

我读到有必要知道一个地方的SRID以便能够计算距离.是否有可能不知道/使用它，但仍然只能基于lon来计算以米为单位的距离?

I've read that it is necessary to know SRID of a place to be able to count distance. Is it possible to not know/use it and still be able to count distance in meters basing only on lon lat?

推荐答案

简短答案:

只需使用 ST_MakePoint即时转换您的 x，y 值. (注意开销！)并计算到给定点的距离，默认的SRS为 WGS84 :

Just convert your x,y values on the fly using ST_MakePoint (mind the overhead!) and calculate the distance from a given point, the default SRS will be WGS84:

SELECT ST_Distance(ST_MakePoint(lon,lat)::GEOGRAPHY,
                   ST_MakePoint(23.73,37.99)::GEOGRAPHY) FROM places;

使用GEOGRAPHY您将获得以米为单位的结果，而使用GEOMETRY将获得以度为单位的结果.当然，了解坐标对的SRS对于计算距离是必不可少的，但是，如果您可以控制数据质量并且坐标是一致的(在这种情况下，请忽略SRS)，就无需担心.如果您打算使用外部数据执行操作，这将变得很棘手，您也从中不了解SRS，并且它可能与您的SRS不同.

Using GEOGRAPHY you will get the result in meters, while using GEOMETRY will give it in degrees. Of course, knowing the SRS of coordinate pairs is imperative for calculating distances, but if you have control of the data quality and the coordinates are consistent (in this case, omitting the SRS), there is not much to worry about. It will start getting tricky if you're planing to perform operations using external data, from which you're also unaware of the SRS and it might differ from yours.

详细答案:

好吧，如果您使用的是PostGIS，则首先不应该在单独的列中使用 x，y .您可以轻松地添加几何/地理位置"列，例如执行以下操作.

Well, if you're using PostGIS you shouldn't be using x,y in separated columns in the first place. You can easily add a geometry / geography column doing something like this.

这是你的桌子...

CREATE TABLE places (place TEXT, lon NUMERIC, lat NUMERIC);

包含以下数据..

INSERT INTO places VALUES ('Budva',18.84,42.92),
                          ('Ohrid',20.80,41.14);

以下是您添加地理位置类型列的方法:

Here is how you add a geography type column:

ALTER TABLE places ADD COLUMN geo GEOGRAPHY;

添加列之后，这就是将坐标转换为地理/几何并更新表格的方式:

Once your column is added, this is how you convert your coordinates to geography / geometry and update your table:

UPDATE places SET geo = ST_MakePoint(lon,lat);

要计算距离，您只需要使用函数 ST_Distance ，如下所示(以米为单位的距离):

To compute the distance you just need to use the function ST_Distance, as follows (distance in meters):

SELECT ST_Distance(geo,ST_MakePoint(23.73,37.99)) FROM places;

   st_distance   
-----------------
 686560.16822422
 430876.07368955
(2 Zeilen)

如果您在 WKT 中具有位置参数，则也可以使用:

If you have your location parameter in WKT, you can also use:

SELECT ST_Distance(geo,'POINT(23.73 37.99)') FROM places;
   st_distance   
-----------------
 686560.16822422
 430876.07368955
(2 Zeilen)

这篇关于如何使用Postgis配置PostgreSQL以计算距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！