Location catalog by names, description

[mdstanfill]

I thought I'd brought this up before, but it may have only been in email or Teams discussions.

We at Northwestern Division have frequently-used database query tools whose workflow essentially starts with the user asking for locations and/or time series that match a string describing the project or gauge. For example, the user might type in "john day", which would return locations associated with the John Day lock and dam, the John Day river, and the town called John Day. It's very useful, since they don't need to know that the project itself is JDA, that it has 20 spillbays (JDA-SBxx), 16 units (JDA-Uxx), that its tailwater is called JDAW, or that it has a temperature string called JDA_S1, a RAWS station at JDFO, an additional forebay gauge age at JDFW, and a water quality gauge at JDY. And they don't need to know the John Day river gauge names: DAYO, JDMO, JDOO, JHAO, JHAW, JHNO, JPGO, KEEO, MONO, RTRO, SERO, and SFJO. But all of these (with full names, lat/long) will be returned so they can sort out what it is they're actually interested in, data wise.

I don't know of any way to do this sort of query with CDA, short of grabbing the metadata for every location by office and sorting through it locally. It would be seriously useful to be able to do this work on the database side instead.

For context, the SQL used to grab a list of locations using a query string q is:

SELECT DISTINCT base_location_id, location_id, public_name,
                latitude, longitude, bounding_office_id
  FROM cwms_v_loc
 WHERE (    UPPER( base_location_id )    = :q
         OR UPPER( base_location_id ) LIKE :q || '\\_%' ESCAPE '\\'
         OR UPPER( public_name      ) LIKE '%' || :q || '%'
         OR UPPER( long_name        ) LIKE '%' || :q || '%'
         OR UPPER( description      ) LIKE '%' || :q || '%' )
       AND loc_active_flag = 'T'
ORDER BY location_id

sometimes without the AND loc_active_flag = 'T' clause (it can be switched out by the enclosing function).

Are there any plans to implement catalog functions by name and/or description?

[MikeNeilson]

I believe you have, thought in a slightly different context.

It's definitely a good idea, and we've discussed supporting search by field such as public name or description, but reading your post I wonder if instead we should go a slightly different route and implement a more generic full text search. No idea at present how that would work, but seems a superior experience to all parties including the public.

[mdstanfill]

The query above was written to kind of bridge the gap between savvy (or return) users putting in a known (possibly base) location ID (ee.gg. JHAW or jda) and those who are just browsing for data based on name or description. As custom SQL, it of course returns only those things the calling process is interested in, but could be extended to provide information useful to more general applications.

[JeremyDKellett]

+1 to this thread. We use a similar query (minus long_name and the 2nd base_location_id predicate) too.

I remember some discussion about adding a multicolumn text index against the columns as IIRC the existing columns don't have indexes on the public name, map comments, long name, description, etc.

[MikeNeilson]

Can you look into creating that index and if it requires an extra license? Seems like if there's an appropriate index to force a search on should be relatively trivial to add the support into CDA itself.

[mdstanfill]

The second base location predicate is to catch associated locations that aren't sub-locations. We're using an early standard for handling sub-sub-locations. JDA_S1 above is a bad example, because it doesn't currently have any sub-locations or time series, but Cougar Dam on the Willamette has a similar location: CGR_S1, a temperature string in the forebay, with lots of sub-locations associated with it:

CGR_S1-E1504ft
CGR_S1-E1524ft
CGR_S1-E1536ft
CGR_S1-E1548ft
CGR_S1-E1560ft
CGR_S1-E1572ft
CGR_S1-E1578ft
CGR_S1-E1584ft
CGR_S1-E1590ft
CGR_S1-E1596ft
CGR_S1-E1602ft
CGR_S1-E1608ft
CGR_S1-E1614ft
CGR_S1-E1620ft
CGR_S1-E1626ft
CGR_S1-E1632ft
CGR_S1-E1638ft
CGR_S1-E1644ft
CGR_S1-E1650ft
CGR_S1-E1656ft
CGR_S1-E1662ft
CGR_S1-E1668ft
CGR_S1-E1674ft
CGR_S1-E1680ft

We didn't want these to be direct sub-locations of CGR itself, but still wanted it to be clear that CGR_S1 is associated with CGR. CWMS didn't (and may still not) support sub-sub-locations, so we formatted the location ID as if it did, but made the first dash an underscore. This may not apply to your office, but would be really helpful to retain for ours.

Use of long_name was likely to sweep in any variously-named locations that perhaps got abbreviated in the public_name. In practice, it may not make much difference to the query result, but seeks to avoid confusion when, for example, "Abernathy Mountain" is abbreviated "Abernathy Mt." in public_name but not long_name.

[MikeNeilson]

For a district/division who's staff were primary instigators of the standard naming convention you really push the limits of that documents suggestions. (NOTE: I approve.)

For the sub-sub locations, not not at this time. Though Mike P, Jeremy, and I would all prefer to move to a simple self referential table that allows that; at least up to a certain level (I suspect infinity would be cause some nasty issues). There's been talk about popping a new Standard Naming PDT back up, something I would definitely bring up in such talks.

That said, I think the multi-column full text search experiment Jeremy is soon to embark on should be able to cover some of that. Though we'll need to do some further experimentation in the API to see what we can do.

[JeremyDKellett]

I opened a issue in cwms-db (CWDB-311) and implemented the text indexing to HEC's DB for testing.

For DB queries, the predicate syntax is
:

CONTAINS(public_name, :q) > 0

The multi column index is on public_name, and indexes sub_location_id, public_name, full_name, map_label, and nearest_city. Any CONTAINS query on public_name will hit that index. The index updates on changes to the table.

It also does away with the table scans the original query had which should perform better at scale.

[rma-rripken]

If I'm summarizing this issue correctly you'd like to be able to add LIKE expressions on: base_location_id, public_name, long_name, description.
Also t/f predicate on active.

Is part of this issue to implement/expose a query like the one Michael posted above or just to make it possible to query/filter for similar data?

The location catalog and timeseries catalog was changed not to long ago in a way that should make adding additional predicates really easy.
For completeness does it make sense to just add the ability for users to add a predicate for every Location field?

Some of the existing predicates use REGEXP_LIKE. I'm not sure if any users use the power available but it was there. If we're going to be doing large amounts of these searches maybe REGEXP_LIKE should transition to LIKE instead.

I know nothing about whats happening on the full-text search side of things. I'll need pointers on how to adapt the final queries if it requires anything above vanilla sql.

[mdstanfill]

The SQL in the initial post is a query we've been using for some time. I have no objection to being able to search on more location fields, though. If there's a serious computational tradeoff between LIKE and REGEXP_LIKE clauses, perhaps there could be separate predicates depending on what kind of match you're looking for. I, for one, don't mind having the option to go full regexp when appropriate. Our end users aren't likely to be writing regular expressions themselves, but they might be useful for "canned" queries from which users might select.

[rma-rripken]

My first time reading through this issue I didn't appreciate that your SQL query made use of OR clauses. The suggestion for full-text-search now makes more sense. Perhaps a user-specified filter expression as suggested in #620 would be the best solution here.

[mdstanfill]

Do you mean a single filter expression matching against a fixed set of fields? For the above query, that would work well, since we've lumped together the use cases of

• an end-user with no idea what the location_ids might be, and
• someone looking for a very specific location_id.

I can imagine situations, though, where you'd know you want to search the [base] location_id but not the name or description. Or an office where descriptions by convention contain keywords useful for winnowing locations (e.g. "water quality" or "weather"), that might pull in unwanted location names. While there's little overlap in these examples (you might get "Weathertop"), it's hard to rule out a situation where the cross-talk between fields might become burdensome for a particular use case.

Of course, ignore the caveats above if you're proposing filter expressions specific to different fields.

If you're thinking of splitting the difference, perhaps a good delineation might be identifiers (base_location_id, location_id, location_kind_id), names (public_name, long_name, map_label), descriptives (description), and locality (nearest_city, county_name, state_initial, nation_id), though the latter might need to be broken up due to the human propensity to label everything the same at different scales (e.g. "Washington" or Tribe/Native Word).

[MikeNeilson]

I think adding a simple "text=<the item." and we know that means push to the full text search, would probably cover it.

Or at least a name like that that would indicate to most that "I have no idea what I'm looking for except but this tiny piece of information."