英文:
Optimizing geohash query
问题
我想看看是否可以在多边形中使用geohash。
Geohash是普通字符串，但是SQL查询与通常与geohashes一起使用的查询有所不同。
这个问题与geohashes无关，所以我没有将其标记为"geohash"。
所以：
我们有这样的表：
create table poly(
ghash varchar(12) not null primary key,
place_id int not null
);
insert into poly values("sr", 1);
insert into poly values("sx", 1);
insert into poly values("sxe0h", 1);
insert into poly values("sx7pu", 1);
insert into poly values("sxe0hebm5", 1);
现在，假设我搜索"sxe0heb"，我想返回"sx"、"sxe0h"、"sxe0hebm5"，因为所有这些都与搜索字符串("sx"、"sxe0h")具有相同的前缀，或者搜索字符串具有相同的前缀("sxe0hebm5")。
问题不是如何做到这一点，而是如何高效地做到这一点。
我能想到的最好的方法是：
select * from poly where ghash = 'sxe0heb%' union -- 收集sxe0heb*
select * from poly where ghash = 'sxe0he' union -- 收集子前缀
select * from poly where ghash = 'sxe0h' union
select * from poly where ghash = 'sxe0' union
select * from poly where ghash = 'sxe' union
select * from poly where ghash = 'sx' union
select * from poly where ghash = 's'
但是由于geohash可能是12个字符，您需要联合12个SQL语句。
英文:
I want to see, if I can use geohash with polygons.
Geohash is normal string, however the SQL query is bit different from what usually is using with geohashes.
The question has nothing to do with geohashes, so I did not tagged it with "geohash".
So:
We have table like this:
create table poly(
ghash varchar(12) not null primary key,
place_id int not null
);
insert into poly values("sr", 1);
insert into poly values("sx", 1);
insert into poly values("sxe0h", 1);
insert into poly values("sx7pu", 1);
insert into poly values("sxe0hebm5", 1);
now, suppose I search "sxe0heb", i want to return "sx", "sxe0h", "sxe0hebm5", because all these have same prefix with search string ("sx", "sxe0h"), or search string have same prefix ("sxe0hebm5")
The question is not how to do it, but how to do it efficiently.
Best thing I can imagine is:
select * from poly where ghash = 'sxe0heb%' union -- collects sxe0heb*
select * from poly where ghash = 'sxe0he' union -- collects subprefix
select * from poly where ghash = 'sxe0h' union
select * from poly where ghash = 'sxe0' union
select * from poly where ghash = 'sxe' union
select * from poly where ghash = 'sx' union
select * from poly where ghash = 's'
but since the geohash may be 12 characters, you need union of 12 sql statements.
答案1
得分: 0
你可以通过以下方式高效地完成：
SET @polystring = "sx";
SELECT * FROM poly WHERE ghash LIKE CONCAT(@polystring, "%")
OR ghash IN (
SUBSTR(@polystring, 1, 1), SUBSTR(@polystring, 1, 2),
SUBSTR(@polystring, 1, 3), SUBSTR(@polystring, 1, 4),
SUBSTR(@polystring, 1, 5), SUBSTR(@polystring, 1, 6),
SUBSTR(@polystring, 1, 7), SUBSTR(@polystring, 1, 8),
SUBSTR(@polystring, 1, 9), SUBSTR(@polystring, 1, 10),
SUBSTR(@polystring, 1, 11));
英文:
You could do it efficiently by doing something like...
SET @polystring = "sx";
select * from poly where ghash LIKE CONCAT(@polystring, "%")
OR ghash IN (
SUBSTR(@polystring, 1, 1), SUBSTR(@polystring, 1, 2),
SUBSTR(@polystring, 1, 3), SUBSTR(@polystring, 1, 4),
SUBSTR(@polystring, 1, 5), SUBSTR(@polystring, 1, 6),
SUBSTR(@polystring, 1, 7), SUBSTR(@polystring, 1, 8),
SUBSTR(@polystring, 1, 9), SUBSTR(@polystring, 1, 10),
SUBSTR(@polystring, 1, 11));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论