Postgres 如何查询两个坐标之间的距离?

从网上找的代码是

select ST_Length(Geography(ST_GeomFromText('LINESTRING(120.451737 36.520975,120.455636 36.520885)')));

/*报错语句:  ERROR: invalid reference to FROM-clause entry for table sc*/
select sc.*,dis.distance 
	from bus_service_center sc,(select ST_Length(Geography(ST_GeomFromText('LINESTRING(117.075591 36.689051,'||sc.longitude||' '||sc.latitude||')'))) as distance) dis
	where sc.operation_status = 'ENABLED' order by distance limit 15 offset (0 * 15)

直接查询是没问题的, 但是想把LINESTRING里的坐标换成字段引用的话就报错了, How To 办?

wendal

1楼•2624天前

报什么错

TuWei1992(tuwei1992)

2楼•2624天前

报 ERROR: invalid reference to FROM-clause entry for table sc

TuWei1992(tuwei1992)

3楼•2624天前

最后自己建了个之前 mysql 里面用的方法

/*创建距离搜索函数*/
CREATE OR REPLACE FUNCTION geo_distance(my_longitude float, my_latitude float, n_longitude float, n_latitude float) RETURNS float AS
'SELECT ROUND(
        	6378.138 * 2 * ASIN(
            	SQRT(
                	POW(
                    	SIN(
		                        (
		                            my_latitude * PI() / 180 - n_latitude * PI() / 180
		                        ) / 2
		                    ),
		                    2
		                ) + COS(my_latitude * PI() / 180) * COS(n_latitude * PI() / 180) * POW(
		                    SIN(
		                        (
		                            my_longitude * PI() / 180 -   n_longitude * PI() / 180
		                        ) / 2
		                    ),
		                    2
		                )
		            )
		        ) * 1000
		    );'
LANGUAGE sql;

请先登陆