Question
· Dec 8, 2018

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

Discussion (2)0
Log in or sign up to continue