Is there any docs or samples how to Integrate a Geo FTS Search and N1QL

Full Text Search
1

I am trying to work my way thru the not so well documented world of Geo Search and how to combine them with N1QL.
I found a few samples that show N1QL/FTS integration using N1QL SEARCH predicate but none shows how to do that with a Geo based search. I am looking for more info on the syntax if i want to specify a location and a radius as well as sort by distance. Also if i do a basic FTS search like this via curl i specify a from and size which is like offset and limit, will that supersede the N!QL limits or offsets ?

Here is my FTS query that works via curl

{
  "from": 0,
  "size": 100,
  "query": {
    "location": {
      "lon": -115.6887945,
      "lat": 37.516891
     },
      "distance": "2mi",
      "field": "geo"
    },
  "sort": [
    {
      "by": "geo_distance",
      "field": "geo",
      "unit": "mi",
      "location": {
      "lon": -115.6887945,
      "lat": 37.516891
      }
    }
  ]
}'

And this is what i tried for N1QL query with no luck

SELECT META(a).id,
       a._type,
       a.Address
FROM rets a
WHERE a._type='Residential'
    AND SEARCH(a, {"conjuncts": [ { "location": { "lon": -115.6887945, "lat": 37.516891 }, "distance": "2mi", "field": "geo" } ] }, {"index":"Geo_Index"})
2

I think Geo search you must use search request vs search query. cc @abhinav
Basically take your curl one put exactly in second argument.

SELECT META(a).id,
       a._type,
       a.Address
FROM rets a
WHERE a._type='Residential'
    AND SEARCH(a, {"query":{"conjuncts": [ { "location": { "lon": -115.6887945, "lat": 37.516891 }, "distance": "2mi", "field": "geo" } ] }}, {"index":"Geo_Index"})
3

A search request within the SEARCH function becomes necessary only if there’s other parameters like sort, size, from etc. alongside the query. A search query for geo should work just fine.

@makeawish sharing your index definition would certainly help us in assisting you better.

4

Here is my Index, and yes i would like to sort at the end based on distance but i cant get this basic query with n1ql and fts to work.

{
 "name": "Geo_Index",
 "type": "fulltext-index",
 "params": {
  "doc_config": {
   "docid_prefix_delim": "",
   "docid_regexp": "",
   "mode": "type_field",
   "type_field": "_type"
  },
  "mapping": {
   "default_analyzer": "standard",
   "default_datetime_parser": "dateTimeOptional",
   "default_field": "_all",
   "default_mapping": {
    "dynamic": false,
    "enabled": true,
    "properties": {
     "_type": {
      "enabled": true,
      "dynamic": false,
      "fields": [
       {
        "docvalues": true,
        "include_in_all": true,
        "include_term_vectors": true,
        "index": true,
        "name": "_type",
        "type": "text"
       }
      ]
     },
     "geo": {
      "enabled": true,
      "dynamic": false,
      "fields": [
       {
        "docvalues": true,
        "include_in_all": true,
        "include_term_vectors": true,
        "index": true,
        "name": "geo",
        "store": true,
        "type": "geopoint"
       }
      ]
     }
    }
   },
   "default_type": "_default",
   "docvalues_dynamic": true,
   "index_dynamic": true,
   "store_dynamic": false,
   "type_field": "_type"
  },
  "store": {
   "indexType": "scorch"
  }
 },
 "sourceType": "couchbase",
 "sourceName": "rets",
 "sourceUUID": "68ba50313fd833d089e0c9704188988f",
 "sourceParams": {},
 "planParams": {
  "maxPartitionsPerPIndex": 171,
  "indexPartitions": 6,
  "numReplicas": 0
 },
 "uuid": "5aaf847f575fea03"
5

Hmm, your query should’ve worked for the index. Would you share the EXPLAIN output you get for your N1QL Search query?

One thing though - since you’re NOT using a custom type mapping in your index definition and are explicitly indexing “_type” within the default mapping along side “geo”, I’d recommend you changing your query to this …

SELECT META(a).id,  a._type, a.Address
FROM rets a
WHERE SEARCH(a, 
{
  "conjuncts": [
    {
      "field": "_type",
      "match": "Residential"
    },
    {
      "location": {
        "lon": -115.6887945,
        "lat": 37.516891
      },
      "distance": "2mi",
      "field": "geo"
    }
  ]
});
6

That produces an output but not what i expect… It only returns this

[
  {
    "_type": "Residential",
    "id": "Residential::1706e3a1-c744-464d-a625-e6a552135a61"
  }
]

I thought it should also return the a._type and a.Address

7

“Address” for the document is produced only if available. Did you take a look at the document?

8

Ok, that was my bad, it’s not a.Address but a.Record.Address and after changing it shows. I also was able to get the sort working at least it doesn’t throw an error. But i am wondering if there is a way to also return the distance in this query,

9

If it’s the distance of each document from the geopoint - afraid we don’t have this capability yet. The search just returns all documents that fall within the distance of the search - a radius search if you will.

10

Thanks, is there any plans to add that at one point ?

The sorting confuses me a bit, now knowing that i cant get the distance back

 "sort": [
    {
      "by": "geo_distance",
      "field": "geo",
      "unit": "m",
      "location": {
      "lon": -115.6887945,
      "lat": 37.516891
      }
    }

I assume the geo_distance is the default name if you want to order by radius distance. I get the field and the location part but what i am unclear of is the unit part. If we order by distance, it shouldn’t matter if the unit is Yard, Meter or miles if we don’t return the value. So what impact has the unit value on the sort order then.

Also how can I change the sort order from ASC to DSC based on this sort ?

11

Yes geo_distance is the name for radius search.

Here’s documentation on units: https://docs.couchbase.com/server/6.6/fts/fts-geospatial-queries.html#specifying-distances

The default sort-order is ascending . If a field-name is prefixed with the - character, that field’s results are sorted in descending order.

I’d recommend reading up documentation, these 2 articles in specific for your use case …

12

Thanks that - makes it clear , i found some other docs when calling the query from api it used an .descending(true) so i wasn’t sure. I get the Units for the actually query to specify the radius , what none of the docs specifies is what the unit does in the sort clause as we are not returning a given distance is the same in relation if you use 1 mile or 1.6 km

13

hey @abhinav, I think the Sort field in each of hits contain the distance value, but that is in a custom prefix encoded format. Not sure whether SDK has any support for reading this.

Else the user has to custom decode it which is painful, but doable.

14

I don’t think that’s true in every case, here is what a result looks like and the sort is 1 if i use feet or miles here is my sort clause

  "sort": [
    {
      "by": "geo_distance",
      "field": "-geo",
      "unit": "miles",
      "location": {
      "lon": -115.6887945,
      "lat": 37.516891
      }
    }

]

image
image780×448 62.5 KB

15

Hey @makeawish ,

I could get the distance back from the results of travel-sample data set.

  "sort": [
      {
        "by": "geo_distance",
        "field": "geo",
        "location": {
          "lat": 37.399285,
          "lon": -122.107799
        },
        "unit": "m"
      }
    ],
    "includeLocations": false,
    "search_after": null,
    "search_before": null
  },
  "hits": [
    {
      "index": "FTS_5ee0431dd7aebe15_aa574717",
      "id": "airport_3783",
      "score": 0.05256212347401773,
      "sort": [
        "5509"
      ]
    },
    {
      "index": "FTS_5ee0431dd7aebe15_aa574717",
      "id": "airport_8864",
      "score": 0.04786788819093661,
      "sort": [
        "6903"
      ]
    },
    {
      "index": "FTS_5ee0431dd7aebe15_aa574717",
      "id": "landmark_9625",
      "score": 0.04786788819093661,
      "sort": [
        "21109"
      ]
    },
    {

One could retrieve/decode the values if they can try similar steps as that of the below Go’ snippet.

                for _, hit := range searchResult.Hits {
				    v, err := Int64([]byte(hit.Sort[0])). /// assuming only a single sort parameter.
				     if err != nil {
					         // do the needful
				     }
				   hit.Sort[0] = strconv.FormatInt(int64(int64ToFloat64(v)), 10)
		          }

//utility funcs
func Int64(p []byte) (int64, error) {
	var sortableBits int64
	for _, inbyte := range p[1:] {
		sortableBits <<= 7
		sortableBits |= int64(inbyte)
	}
	return int64(uint64((sortableBits << 0)) ^ 0x8000000000000000), nil
}

func int64ToFloat64(i int64) float64 {
	if i < 0 {
		i ^= 0x7fffffffffffffff
	}
	return math.Float64frombits(uint64(i))
}
16

But in my case it returns a 1 in the sort field, if i set it to miles but i always get a 1

17

@makeawish , please read my previous comments. The value is not readable directly. Json decoder won’t be able to decode it. It is in a custom encoded format.
The client has to use similar decoding logic like the aforementioned Go code to decipher its value today unfortunately.