Spatial Geomety SQL - two exact same queries one works the other doesn't

Question

This is bizzarre I have a database that stores polygons and a query running that checks if a point exists within any polygon and retrieves them. However when the query is created as an sql string in my php code it returns nothing however if I type in manually the query - it runs perfectly!

#This works
SELECT * FROM locations 
WHERE type = 'polygon' AND locationable_type = 'Notification' AND 
ST_CONTAINS(locations.geoshape, GeomFromText('Point(25.276987 55.296249)') ) ;

#This doesnt work
SELECT * FROM locations 
WHERE type = 'polygon' AND locationable_type = 'Notification' AND  
ST_CONTAINS(locations.geoshape, GeomFromText('Point(‎25.276987 55.296249)') );

Heres how the sql i actually being generated:

public function get_by_coords($latitude, $longitude){

// this grabs all the notifications
$sql = sprintf("SELECT * FROM locations WHERE type = 'polygon' AND locationable_type = 'Notification' 
        AND ST_CONTAINS(locations.geoshape, GeomFromText('point(%s, %s)') )", ($latitude), ($longitude));

Where $latitude, $longitude are actually passed as strings from a GET variable. I tried to typecast but the result was:

$latitude = "25.276987";
(float)$latitude; // equals zero

Whats going on here? I'm using Codeigniter here.

UPDATE

I just did a var_dump and found something weird. If I var_dump the created SQL query it shows there are 6 more characters than if I var_dump the query directly typed in a string ie:

string(166) "SELECT * FROM locations  WHERE type = 'polygon' AND locationable_type = 'Notification' AND  ST_CONTAINS(locations.geoshape, Point('‎25.27116987','‎55.292216249'))"
string(160) "SELECT * FROM locations  WHERE type = 'polygon' AND locationable_type = 'Notification' AND  ST_CONTAINS(locations.geoshape, Point('25.27116987','55.292216249'))"

The first string is generated while the second was as is - its shows there are 6 extra characters in the first string - I have a weird feeling those are causing issues.. how do I go further here...

Try printing the query with the parameters and then fire it on it server..check if it is working then or not..as U told u tried manually and it worked. — 5eeker, Nov 29 '16 at 11:40
Also in the second query which u have posted contains the coordinates separated with comma which is not present in ur first query. — 5eeker, Nov 29 '16 at 11:42
DId that infact just changed the comma to exactly what was outputted out. No change - the first query gives me a result the other one gives nothing. Its as though the coordinates are totally ignored. — Ali, Nov 29 '16 at 11:52
Have u tried printing the query and the parameters ? and then run the outputted query in your mysql server. — 5eeker, Nov 29 '16 at 12:08
The above query which doesnt run was outputted using an echo statement - the one that does is typed in :( — Ali, Nov 29 '16 at 12:18
If it is just adding extra spaces and if only that is the issue then add a trim function and check — 5eeker, Nov 30 '16 at 10:42

score 0 · Answer 1 · answered Nov 29 '16 at 13:18

0

As far as I remember in php functions we'll still need the dollar sign in front of the arguments.

as you do ....($latitude), ($longitude)); within the function

thus your function arguments should be

public function get_by_coords($latitude,$longitude){

$sql = sprintf("SELECT * FROM locations WHERE type = 'polygon' AND locationable_type = 'Notification' 
        AND ST_CONTAINS(locations.geoshape, GeomFromText('point(%s, %s)') )", ($latitude), ($longitude));
}

answered Nov 29 '16 at 13:18

Fevly Pallar

3,059
2
15
19

There isn't comma `,` in the `point(...)` that separates *latitute* & *longitude*, but you put it on. Moreover are these *locations.geoshape* & *GeomFromText* actually variables or function coming from outside the function? It's either a scope issue or *'point(%s, %s)')* is intepreted as plain string like *'point(25.276987 55.296249)')*. – Fevly Pallar Nov 29 '16 at 15:04
I think I made some progress - can you check the question – Ali Nov 30 '16 at 08:57

score 0 · Accepted Answer · answered Feb 15 '17 at 09:33

Hey guys sorry for the late response. I managed to find out what the issue was. I did a simple strlen on the sql generated vs the sql manually typed in and found a discrepency in the length. There were some kind of hidden characters - so did a simple remove non printable characters and it worked like a charm.

Spatial Geomety SQL - two exact same queries one works the other doesn't

2 Answers2