72125

PHP To Retrieve PostGIS Geography Types

Question:

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

<strong>EDIT</strong>

$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++; }

<strong>EDIT2</strong> 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

Answer1:

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

<strong>Update</strong>

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

Recommend

  • Can not create connection to SQL server using RJDBC
  • 500 internal server error with POST request - Slim framework
  • PHP redirect not working on server
  • Redirect submit button (Login page)
  • Insert data to ms access from java
  • What does if(isset($_POST['btn-signup'])) do?
  • Import raw bytes as raw bytes in R
  • PHP To Retrieve PostGIS Geography Types
  • How can I insert an image in a MySQL database using PHP?
  • Retrieving data from database and displaying it by using ajax
  • Import and process Multiple My SQL tables separately in R
  • Passing multiple selections from selectizeInput to MySQL query
  • Can this query be altered to perform two functions?
  • Run the following method three times with a 1s break on each attempt before throwing exception
  • Turning a query result to an associative array
  • Mysql query works well at workbench but takes too long in r
  • Android Marshmallow [API 23] : Bluetooth UUID returns NULL
  • Undefined references when compiling gSOAP client
  • How to view images from protected folder with php?
  • Stop Bash Script if Hive Fails
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • why do I get the error when installing the gem 'pg'? [duplicate]
  • Master page gives error
  • Importing jscolor library in angular 2
  • Display Images one by one with next and previous functionality
  • Upload files with Ajax and Jquery
  • Google cloud sdk not working when python points python3
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • A cron job substitute?
  • Hits per day in Google Big Query
  • Trying to get generic when generic is not available
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • embed rChart in Markdown
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app