Cannot Query on Field in IRIS

I have a persistent class that represents cities across the United States.  It is below, but basically has a City Id, Name, Lat, Lon and a few other unimportant fields for this issue.  Anytime I attempt to query on the Latitude or Longitude it immediately returns no results.  My first thought was that it was a casting issue so I tried casting both sides to floats, ints, even strings and in all cases it immediately comes back with no results.  I then decided to cast it to a string and attempt a like statement thinking it might be something about how floats are handled, but still no joy.  Any ideas on why I cannot query on these fields?  More details are below.

Property CityId As %Library.Integer;
Property City As %Library.String(MAXLEN=500);
Property State As %Library.String(MAXLEN=2);
Property Country As %Library.String(MAXLEN=500);
Property Longitude As %Library.Float;
Property Latitude As %Library.Float;
Property USCRNName As %Library.String;
Property Status As %Integer;
Property DateRecAdded As %Library.DateTime [InitialExpression = { $ZDT($SYSTEM.Util.UTCtoLocalWithZTIMEZONE($ZTIMESTAMP), 3, 1, 0) }];

Index CityId ON CityId;
Index State ON State;
Index Longitude ON Longitude;
Index Latitude ON Latitude;
Index Status On Status;

Sample Data

SELECT CityId, Latitude, Longitude
FROM MML.Cities

4070245    31.21073    -85.484657
4344544    31.11685    -93.183502
4215307    31.313    -81.55899
5285039    31.386209    -110.288139
4673179    31.40266    -98.081139
4047656    34.037609    -94.107697
5493998    34.58979    -105.28611
5815135    47.500118    -120.501472
5391891    34.106682    -117.806732
4056099    31.41683    -86.000221

Knowing 31.21073 is a valid Latitude I try the following query

SELECT CityId, Latitude, Longitude
FROM MML.Cities
WHERE Latitude = 31.21073

No results

SELECT CityId, Latitude, Longitude
FROM MML.Cities
WHERE CAST(Latitude as Float) = CAST(31.21073 AS Float)

No results

SELECT CityId, Latitude, Longitude
FROM MML.Cities
WHERE SUBSTRING(Latitude, 0, 3) = 31

No Results

SELECT CityId, Latitude, Longitude
FROM MML.Cities
WHERE CAST(SUBSTRING(Latitude, 0, 3) as Integer) = 31

No Results

  • 0
  • 0
  • 113
  • 1
  • 1

Answers

Is it possible that you added index definitions after you filled the table with data?

If that is the case, then those indices are not built ("empty"), and so if the query uses them, then it returns no data. In such case you have to build your indices manually via ##class(MML.Cities).%BuildIndices().

Just tried this and it did in fact fix the issue.  Never even thought about the fact that the indexes were put in place a few days after the table was populated.  Thank you!