Search code examples
phpcodeignitergismysql-spatial

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

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...

Solution

  • 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.