1

I have a POSTGIS database with a table "space" that has two columns "id" and "geometry".

I also have a PHP script that accesses the database and returns data. My program is running on mobile devices and delivers the id and a location. I now try to figure out if the table "space" does have an entry with the given id and if so, if the user is within the boundaries of the geometry in "geometry".

This is my code:

$id = $_POST['id'];
$lon = $_POST['lon'];
$lat = $_POST['lat'];

$statementCheck = $pdo->prepare("SELECT * FROM space WHERE id= :id 
    AND ST_Within(ST_MakePoint($long, $lat), geometry)");
$statementCheck->bindParam(':id', $id, PDO::PARAM_STR);
$statementCheck->execute();

My problem is: $statementCheck->execute() returns false. However, I'm sure that there is a matching dataset in the table. Am I using ST_Within in a wrong way? The tutorials I looked at suggest that this is the way to do it. I'm a bit suspecting the ",geometry" part of ST_MakePoint to be not accurate.

edit: Fixed typing error.

  • Could you provide the values for $long, $lat and geometry? – Michal Zimmermann Oct 20 '16 at 13:48
  • $lon is a decimal coordinate (here: 12.40....) and so is $lat (here: 50.56....). I don't know how to display the geometry. The statement is "false" and does not give a geometry output. When I entered it in the database, I used a GeoJSON representation and uploaded it with ST_SetSRID(ST_GeomFromGeoJSON(.... In the database, it's represented as a whole series of numbers... – s4inz Oct 20 '16 at 14:31
  • It should not be a coordinate issue. Everything I handled was in EPSG 4326 from the beginning, and since my device is an Android phone, its location should also have matching coordinates. – s4inz Oct 20 '16 at 14:33
  • 1
    You refer to $long and $lon in different places. Are you referring to the right variables? Try using consistent names. – amball Oct 21 '16 at 7:24
2

You probably need to set the SRID of your ST_Makepoint to the same as your space.geometry:

$statementCheck = $pdo->prepare("SELECT * FROM space WHERE id= :id 
    AND ST_Within(ST_SetSRID(ST_MakePoint($long, $lat),4326), geometry)");
  • Unfortunately, that didn't improve it. :/ But I see the point, since I'm doing the same thing when writing the geometry into the table at another point in the software. – s4inz Oct 20 '16 at 14:25
  • Well I'm sorry. That actually did solve my problem. The one geometry I was working on was in a different EPSG than I assumed it to be :/. Thanks for your help and the ST_SetSRID hint! – s4inz Oct 24 '16 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.