How to I query data by geo location and string value at the same time?

SQL++
, ,
1

Hi There,

I have a use case to query data by geo location and string value at the same time:

select poi.* where location nearby [point, distance] and poi.category = ‘Restaurant’

Note: I have created a spatial view, but the view map function only accepts multi-dim number value.

Thank you!

2

N1QL itself does not exploit the spatial view for geo predicates.

3

Spatial view does not allow me to put any string value. This means, I have no way to query (either N1QL or view) geo location and other string values in one shot, right?

4

That’s correct. We don’t have an integrated solution now.

5

For now, you can create a GSI index containing lat / lon / and some distances that you are interested in.

6

I can imagine how to query POI in a box (by restrict the lon/lat value in a range). Any idea for a radius/circle query in N1QL?

Appreciate!

7

I don’t remember the details from high school trigonometry. Something about square root of sum of squares.

N1QL provides a SQRT function which you can use in your query (and your GSI index).

You can also use the box in your index, which will filter things considerably, and then apply the SQRT on top that in your query.

8

Appreciate for the reply.

Thanks to Google. I found the formula. It’s a little bit complex but doable. Considering return to high school for trigonometry :slight_smile:

Thanks a lot,

Allen

9

Strongly recommend to add GeoSpatial Function to N1QL. None of the Spatial query condition looks simple. I have handle cross180degree case plus some long trigonometry formula.

10

@geraldss Wow, nice. My home work tonight: How To Measure Any Distance With The Pythagorean Theorem – BetterExplained :slight_smile:

1 Like
11

Hi @zhaoshenhao,

Sounds good. If you have a list of common functions you would like to see, we can take that into account. Thanks for the suggestion.

12

This query does the job of both filtering on strings and distance. Formulae from http://stackoverflow.com/questions/13026675/calculating-distance-between-two-points-latitude-longitude.

select name, geo, city, (ACOS(SIN(PI()* geo.lat/180.0)*SIN(PI()*37.7/180.0)+COS(PI()*geo.lat/180.0)*
 COS(PI()*37.7/180.0)*COS(PI()*-122.33/180.0-PI()*geo.lon/180.0))*6371) as distance
 from `beer-sample` b
WHERE type = 'brewery' AND
(ACOS(SIN(PI()* geo.lat/180.0)*SIN(PI()*37.7/180.0)+COS(PI()*geo.lat/180.0)*
 COS(PI()*37.7/180.0)*COS(PI()*-122.33/180.0-PI()*geo.lon/180.0))*6371) < 10
 and state = 'California'
 order by distance asc;

You can try this on beer-sample yourself. While this exploits the index on type and state, it still can’t use any index for the spatial index.
Spatial indices use bounding boxes to find the nearest qualifying locations.
This case is a post scan filter…

1 Like
13

All supported “keywords” in Spring-couchbase-data library should be enough:

http://docs.spring.io/spring-data/couchbase/docs/current/reference/html/#couchbase.repository.spatial

Thanks a lot,

Allen

14

Thank you. I have a written a similar one. Anyway, do you have a query to find a POI located in a box (top-left and bottom-right)?

15

Hi @zhaoshenhao, Please take a look at this: http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates.
It has examples for both calculating the distance and creating the b-tree indices on lat-long.

I haven’t tried this yet.

1 Like
16

Hi, What is the last solution of this topic, does Couchbase upgraded to support location queries?

1 Like
17

See this: https://dzone.com/articles/speed-up-spatial-search-in-couchbase-n1ql