Trying to do geo spatial searches

Full Text Search
6

I’m working on using the “bounding box” concept for this instead as that will allow me to search for all points (similarly to what I do on mobile). So the initial N1QL looks like this:

select d.*,p.* from data d unnest points p 
where d.type in ['Lake','Stream'] and 
p.lat between 55.5 and 55.6 and 
p.lon between 11.0 and 11.5

I then need to calculate the distance from the point I start with to the points found, probably using some of the ideas from this article: How to Speed Up Spatial Search in Couchbase N1QL - DZone Performance

So far the query above is Ok in terms of speed and possibly could be improved by adding an index specifically to help it.

Edit:
This query now shows the distance:

select d.type,d.name,d.`key`,p.*,
    (acos(sin( RADIANS(55.55)) * sin (RADIANS(p.lat)) + 
    cos( RADIANS(55.55 ))  * cos(RADIANS(p.lat)) * 
    cos (RADIANS(p.lon) - RADIANS( 11.25))) * 6371) distance
from data d unnest points p 
where d.type in ['Lake','Stream'] and 
p.lat between 55.5 and 55.6 and 
p.lon between 11.0 and 11.5
order by distance asc
limit 5

Result looks like this:

	[
        {
            "distance": 1.1393731463644405,
            "key": "2272",
            "lat": 55.5496163108,
            "lon": 11.268100815,
            "name": "Bøstrup Å",
            "type": "Stream"
        },
        {
            "distance": 1.2050571143258948,
            "key": "2272",
            "lat": 55.5577846145,
            "lon": 11.2633297918,
            "name": "Bøstrup Å",
            "type": "Stream"
        },
        {
            "distance": 1.5156318704030634,
            "key": "2272",
            "lat": 55.5413010688,
            "lon": 11.2685482159,
            "name": "Bøstrup Å",
            "type": "Stream"
        },
        {
            "distance": 1.789016643436557,
            "key": "2272",
            "lat": 55.5344048529,
            "lon": 11.2569913897,
            "name": "Bøstrup Å",
            "type": "Stream"
        },
        {
            "distance": 1.9525790822056994,
            "key": "2276",
            "lat": 55.5667725132,
            "lon": 11.2408064902,
            "name": "Halleby Å",
            "type": "Stream"
        }
    ]

So now I just need to figure out if I can get rid of the “duplicates” (each point from an entity) in the query - or I need to remove that in code afterwards. But hey, this is doable :wink:

1 Like