database - 从坐标POSTGIS创建的线串

有人能帮忙吗？
下面是我的代码（SQL）中不起作用的部分：

SELECT ST_LENGTH(geom) into distance FROM
     SELECT ST_GeographyFromText('srid=4326;linestring(lon_bus lat_bus, lon_stop lat_stop)') AS geom)
     AS dis;

lon_bus、lat_bus、lon_stop和lat_stop是我从数据库中获得的坐标。当我尝试这个时，我有一个解析错误。但当我用数字替换这些变量时，它就起作用了。有人能帮我吗？我想在代码中保留这些变量。

最佳答案

它不起作用，因为带变量的wkt无效。记住，WKT is just regular text，所以不要混淆wkt和sql。
可以从两个点几何图形make a LineString，然后将其转换为::geography。

SELECT ST_MakeLine(ST_MakePoint(lon_bus, lat_bus),
                   ST_MakePoint(lon_stop, lat_stop))::geography AS geog
FROM (
  SELECT 1 AS lon_bus, 2 AS lat_bus, 3 AS lon_stop, 4 AS lat_stop
) AS data;

要获得测地线长度，请在地理位置上使用st_length。
根据用法，问题不在于如何生成线串，而在于如何计算两个地理位置之间的距离。有几种方法可以做到这一点：

SELECT
  ST_Distance(bus, stop) AS cartesian_distance,
  ST_Distance_Sphere(bus, stop) AS sphere_distance,
  ST_Distance(bus::geography, stop::geography) AS geography_distance,
  ST_Length(ST_MakeLine(bus, stop)::geography) AS geography_length
FROM (
  SELECT ST_MakePoint(lon_bus, lat_bus) AS bus, ST_MakePoint(lon_stop, lat_stop) AS stop
  FROM (SELECT 1 AS lon_bus, 2 AS lat_bus, 3 AS lon_stop, 4 AS lat_stop) AS data
) AS data;
-[ RECORD 1 ]------+-----------------
cartesian_distance | 2.82842712474619
sphere_distance    | 314283.687770102
geography_distance | 313588.397192902
geography_length   | 313588.397192902

最后两个得到相同的结果。如果不需要线串（例如在地图上绘制），则最简单的方法用于geography_distance。