record not found on full title, if title contains 'of' or 'in'

[pvgenuchten]

Description

If the title of a record contains the word 'of', 'for' or 'in' (and probably all stopwords), the record will not be found if you search on full title using q search in ogcapi-records

I found this behaviour at this record (having postgres backend):
https://rwanda.lsc-hubs.org/collections/metadata:main/items?q=Mycorrhizal+colonization+major+banana+genotypes+six+East+African+environments vs
https://rwanda.lsc-hubs.org/collections/metadata:main/items?q=Mycorrhizal+colonization+of+major+banana+genotypes+in+six+East+African+environments

Does it make sense to strip stopwords from q before querying the database?

[pvgenuchten]

actually when testing with postgres, i got this error message: 'column anytext_tsvector does not exist', so apparently the mechanism of full text search seems implemented, but somehow not activated, after creating the columns and trigger, full text search on title works without problems, so i guess i should rename this issue to: admin.py does not create tsvector tables at db-setup...?

[pvgenuchten]

no creation of ts-vector columns/triggers seem related to:

• no postgis
• no plpythonu

when deploying pycsw on a postgis enabled postgres, all seem fine, ts-vector columns created and populated

[pvgenuchten]

Problem is that for each keyword in q the full text search is triggered, and i assume a text search on only a stopword fails

plainto_tsquery('english', '%Aerial%') @@ anytext_tsvector
plainto_tsquery('english', '%the%') @@ anytext_tsvector
plainto_tsquery('english', '%Photos%') @@ anytext_tsvector

in stead should be
to_tsquery('english', 'Aerial:* & the:* & Photos:*') @@ anytext_tsvector

[tomkralidis]

Some further analysis on FTS. Consider a record with anytext field containing the terms auxiliary and https://stac-extensions.github.io/datacube/v2.0.0/schema.json:

pycsw=# select identifier from records where anytext like '%auxiliary%' limit 1;
                  identifier                   
-----------------------------------------------
 ESACCI-LC-L4-LCCS-Map-300m-P1Y-2009-v2.0.7cds
(1 row)

pycsw=# select identifier from records WHERE to_tsquery('english', 'auxiliary') @@ anytext_tsvector;
                  identifier                   
-----------------------------------------------
 ESACCI-LC-L4-LCCS-Map-300m-P1Y-2009-v2.0.7cds
(1 row)

pycsw=# select identifier from records WHERE to_tsquery('english', 'auxil') @@ anytext_tsvector;
 identifier 
------------
(0 rows)

pycsw=# select identifier from records WHERE to_tsquery('english', 'auxil:*') @@ anytext_tsvector;
                  identifier                   
-----------------------------------------------
 ESACCI-LC-L4-LCCS-Map-300m-P1Y-2009-v2.0.7cds
(1 row)

Here, we see that auxil:* generates a match. Looking closer atdatacube:

pycsw=# select identifier from records where anytext like '%datacube%' limit 1;
                  identifier                   
-----------------------------------------------
 ESACCI-LC-L4-LCCS-Map-300m-P1Y-2009-v2.0.7cds
(1 row)

pycsw=# select identifier from records where anytext like ' datacube%' limit 1;
 identifier 
------------
(0 rows)

Here, we see the second query yields empty.

I think this is because datacube is NOT a bare word in the anytext, but it’s part of the STAC datacube extenstion URI. This is why FTS queries do not catch it for fuzzy searches (which are fuzzy at the END of the string only).
So PG FTS queries follow the typeahead pattern.
For good measure, going back to testing with plainto_tsquery:

pycsw=# select identifier from records WHERE plainto_tsquery('english', 'auxil:*') @@ anytext_tsvector;
 identifier 
------------
(0 rows)

pycsw=# select identifier from records WHERE to_tsquery('english', 'auxil:*') @@ anytext_tsvector;
         identifier          
-----------------------------------------------
 ESACCI-LC-L4-LCCS-Map-300m-P1Y-2009-v2.0.7cds
(1 row)

Proposed change, then, in pycsw/core/pygeofilter_evaluate.py:

    @handle(ast.Like)
    def ilike(self, node, lhs):
        LOGGER.debug('Overriding ILIKE filter handling')
        LOGGER.debug(f'Term: {node.pattern}')
        if (str(lhs.prop) == 'dataset.anytext' and
                self._pycsw_dbtype.startswith('postgres')):
            if not node.pattern.startswith('%'):
                LOGGER.debug('Kicking into PostgreSQL FTS mode')
                if node.pattern.endswith('%'):
                    LOGGER.debug('Fuzzy term search')
                    term = node.pattern.replace('%', ':*')
                else:
                    LOGGER.debug('Normal term search')
                    term = node.pattern
                LOGGER.debug(f'FTS term: {term}")
                return text(f"to_tsquery('english', '{term}') @@ anytext_tsvector")  # noqa

        LOGGER.debug('Default ILIKE behaviour')
        return filters.like(
            lhs,
            node.pattern,
            not node.nocase,
            node.not_
        )

now, in https://github.com/geopython/pycsw/blob/master/pycsw/ogc/api/records.py#L1192, for anytext=foo or q=foo type searches, pycsw:

• transforms to an ILIKE statment with wildcards surrounding the string
• pycsw/core/pygeofilter_evaluate.py as a result will ALWAYS set as an ILIKE , so q= and anytext= will NEVER be “search engine style searches”
• I’m not as a worried about anytext= because it really shouldn’t be advertised per se, but q= always being an ILIKE and NEVER being an FTS search concerns me.

Of course, these are different codepaths as compared to CQL filters (which are solved by #901).

I think we need to (for OARec ONLY, not CSW):

• have clear guidance on what a wildcard is in q= . IMHO this should be *, so:
• q=birds -> FTS term search to_tsquery on birds
• q=bird* -> FTS fuzzy search to_tsquery on bird:*
• q=*bird -> drop to SQL ILIKE on %bird
• q=*bird* -> drop to SQL ILIKE on %bird%
• q=*bird* fowl -> process each term per above (drop to SQL ILIKE on %bird%, FTS term search to_tsquery on fowl)

@pvgenuchten @kalxas thoughts?

Aside: To add another dimension, see OARec requirements on q: https://docs.ogc.org/DRAFTS/20-004.html#core-query-parameters-q. So we should actually be supporting splitting a q= by ,? As well, should this be an AND or OR search?

[tomkralidis]

Followup OARec discussion in opengeospatial/ogcapi-records#295 (comment)

[pvgenuchten]

2 additions

• with Ilike; multi word strings are split on space (and anytext ilike q-part), stop words should be removed from q string else no results
• multi string part search Should be ‘and’ concatenation else you get many results , except usually when you select multiple keywords from a single code list