postgresql ST_Dinstince 计算两个经纬度的距离
时间: 2024-05-01 12:22:11 浏览: 16
在 PostgreSQL 中可以使用 PostGIS 扩展来计算两个经纬度之间的距离。具体可以使用 ST_Distance 函数,示例如下:
```sql
SELECT ST_Distance(
'POINT(116.404, 39.915)'::geography,
'POINT(121.4737, 31.2304)'::geography
) / 1000 as distance_in_km;
```
其中 `POINT(lon, lat)` 是一个地理位置坐标,使用 `::geography` 可以将其转换为地理坐标。ST_Distance 函数返回的结果单位是米,除以 1000 可以得到千米单位的距离。
需要注意的是,使用 PostGIS 扩展前需要先安装和启用该扩展。可以使用以下命令进行安装和启用:
```sql
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
```
执行以上命令后,PostGIS 扩展就会被安装并启用。
相关问题
postgresql ST_Dinstince
ST_Distance is a function in PostgreSQL's PostGIS extension that calculates the distance between two geometries. The distance is returned in the units of the spatial reference system of the geometries.
For example, if you have two points in a geography column, you can use ST_Distance to calculate the distance between them:
```
SELECT ST_Distance(
geography(point1),
geography(point2)
) as distance
FROM mytable;
```
This would return the distance between point1 and point2 in meters (assuming the spatial reference system is in meters).
postgresql st_contains
`ST_Contains` is a PostGIS function that checks if a geometry (such as a point, line or polygon) is completely inside another geometry. It returns true if the first geometry is completely inside the second geometry, otherwise it returns false.
Here's an example usage of `ST_Contains` in PostgreSQL:
```
SELECT *
FROM table1
WHERE ST_Contains(table1.geom, table2.geom);
```
This query selects all records from `table1` where the geometry in the `geom` column of `table1` is completely inside the geometry in the `geom` column of `table2`.
Note that `ST_Contains` only works with two-dimensional geometries. If you are working with three-dimensional geometries, you should use `ST_3DContains` instead.