doudang8824
2016-11-29 11:36
浏览 73
已采纳

空间几何SQL - 两个完全相同的查询,另一个不工作

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

图片转代码服务由CSDN问答提供 功能建议

这是bizzarre我有一个存储多边形的数据库和一个运行查询,检查任何多边形中是否存在一个点 检索它们。 但是,当我的PHP代码中的查询被创建为sql字符串时,如果我手动输入查询,它将返回任何内容 - 它运行完美!

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

以下是实际生成sql的方法:

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

Where < 代码> $ latitude,$ longitude 实际上是作为GET变量的字符串传递的。 我试图进行类型转换,但结果是:

  $ latitude =“25.276987”; 
(float)$ latitude;  //等于零
   
 
 

这是怎么回事? 我在这里使用Codeigniter。 UPDATE

我刚做了一个var_dump并发现了一些奇怪的东西。 如果我var_dump创建的SQL查询,则表明还有6个字符,如果我将var_dump查询直接输入字符串,即:

  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 ='通知'和ST_CONTAINS(locations.geoshape,Point('25 .27116987','55.292216249'))“
   
 
 

生成第一个字符串,第二个字符串生成 原样 - 它显示第一个字符串中有6个额外的字符 - 我有一种奇怪的感觉,这些是导致问题的......我怎么在这里更进一步......

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • dso15221 2017-02-15 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.

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题