PHP To Retrieve PostGIS Geography Types

问题:

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;

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 :(

I'd really appreciate any help :)

Thank you,

elshae

EDIT

$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 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);

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

回答1:

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?

/Nicklas

Update

Ok

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.

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.

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;

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;

and so on.

That will be very much more effective.

/Nicklas