SQL + While 循环中的熊猫
我正在运行SQL查询，并将结果保存到Pandas DF。我的条件是至少获得n行号。我正在使用while-loop运行并更新查询，直到满足条件为止。
df = pd.DataFrame({})
rows = df.shape[0]
# search radius - miles to meters
radius = 1.5*1609
# Between 20 to 30 results in comps set
while rows <= 30:
# Run query
query = '''
select * from schema_name.table1
where st_distance_sphere(Point({},{}), coords) <= {};
'''.format(Long, Lat, radius)
df = pd.read_sql(query, con)
rows = df.shape[0]
if rows >= 20:
break
else:
radius = radius * 2
基本上，查询正在寻找point坐标的指定半径内的行。我希望这将返回20-30行，但是，结果是100行。我很可能在这里错过了一些东西，并试图调试发生的事情。
使用一段循环如何将结果限制为20-30行？如果返回的行是＆lt; 20，则想法是扩展半径。
I am running a SQL query and saving the results to a pandas DF. My condition is to obtain at least N number of rows. I am using a while-loop to run and update the query until the condition is met.
df = pd.DataFrame({})
rows = df.shape[0]
# search radius - miles to meters
radius = 1.5*1609
# Between 20 to 30 results in comps set
while rows <= 30:
# Run query
query = '''
select * from schema_name.table1
where st_distance_sphere(Point({},{}), coords) <= {};
'''.format(Long, Lat, radius)
df = pd.read_sql(query, con)
rows = df.shape[0]
if rows >= 20:
break
else:
radius = radius * 2
Basically, the query is looking for rows that are within the specified radius of the POINT coordinates. I'd expect this to return 20-30 rows, however, the result is 100 rows. I am likely missing something here and trying to debug what's going on.
Using a while loop how do I restrict the results to 20-30 rows? The idea is to expand the radius if the rows returned are <20.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以添加一个限制子句以限制查询返回的行数。
您还可以在计算的距离上添加订单，然后获取30个最接近的点。
You can add a LIMIT clause to restrict the number of rows returned by the query.
You can also add an ORDER BY for the distance you are calculating and then get the 30 closest points.