PHP检索PostGIS地理类型 [英] PHP To Retrieve PostGIS Geography Types

问题描述

我有一个带有地理列的PostGIS数据库.我希望能够使用PHP来选择地理位置，以便随后我可以使用ST_Distance运行另一个查询以获取两点之间的距离.但是，当我运行

I have a PostGIS database with a geography column. I'd like to be able to use PHP to SELECT the geography so then I could run another query using ST_Distance to get the distance between the points. However, when I run

SELECT geog from locations;

我得到的怪异值是6，而不是HEX输出(我期望的是诸如0101000020E6100000C442AD69DEAD5740575BB1BFEC6E3D40).当我在phpPgAdmin中使用相同的查询时，我得到了预期的输出，但是没有从我的PHP脚本中得到:(

I get a weird value of 6 instead of the HEX output (something like 0101000020E6100000C442AD69DEAD5740575BB1BFEC6E3D40 is what I'm expecting). When I use the same query in phpPgAdmin I get the expected output, but not from my PHP script :(

非常感谢您的帮助:)

谢谢

elshae

编辑

$locations_result = @pg_query($DBConnect, "SELECT geog from locations;");

    if (!$locations_result) {
        echo "An error occurred upon getting data from the locations table.\n";
        exit;
    }
    $i = 0;
    while ($locations_arr = pg_fetch_row($locations_result)) {

        $locations['location'][$i] = $locations_arr[0];

                echo $locations['location'][$i];
        $i++;
    }

EDIT2 因此，现在我尝试在ST_Distance函数中选择地理位置，以便它可以直接获取地理位置结果，例如:

EDIT2 So now I am trying to select the geography right into the ST_Distance function so that it can just get the geography result directly, something like:

SELECT ST_Distance(geog_a, geog_b) FROM(SELECT geog AS geog_a FROM location WHERE id=123 UNION SELECT geog AS geog_b FROM location WHERE id=345);

但是我的SQL语法是错误的，我还没有弄清楚如何获得我想要的结果. UNION正在将这些值放在一栏中，这已经是我所拥有的.相反，我需要类似的东西:

But my SQL syntax is wrong and I have yet to figure out how I can get the result I'm aiming for. The UNION is putting these values into one column, which is what I have already. Instead I need something like:

geog_a |geog_b
----------------
hdsklgh|shfksh

推荐答案

是您想要的wkb格式吗?

Is it the wkb-format you want?

select ST_AsWKB('geog') from locations

但是为什么要首先提取数据以进行距离计算?

But why do you want to pull out the data first to do the distance-calculation?

/尼古拉斯

更新

好

我不知道为什么您的查询没有得到正确的结果.我在php中太糟糕了.我可能会因为某种原因将结果推入某种不正确的数据类型中.我的意思是从ST_AsText您应该只得到一个字符串，仅此而已.

I don't know why you don't get a proper result on your queries. I am too bad in php. I would guess that the result by some reason is pushed into some data type that is not the right. I mean from ST_AsText you should just get a string, nothing more strange than that.

但是要保持距离，您不应该浪费时间.您进行自我加入即可做到这一点.这就是使用PostGIS并在一张表中比较不同几何形状时一直要做的事情.

But to get your distance you shouldn't pull out the points. You do a self join to do that. That is what you do all the time when using PostGIS and comparing different geometries in one single table.

比方说，第一个地理区域的ID为1，第二个地理区域的ID为2，该查询可能类似于:

Let's say the first geography has id=1 and the second has id=2 the query could be something like:

SELECT ST_Distance(a.the_geog, b.the_geog) as dist 
from locations a, locations b WHERE a.id=1 and b.id = 2;

如果要从id = 1的点到所有点(或其他任何点)的距离，可以这样写:

If you want the distance to all points (or whatever it is) from point with id=1 you could write:

SELECT  ST_Distance(a.the_geog, b.the_geog) as dist
from locations a inner join locations b on a.id != b.id WHERE a.id=1;

以此类推.

那将更加有效.

/尼古拉斯

这篇关于PHP检索PostGIS地理类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！