I have both iOS and desktop apps which read and write geographic coordinates to a SQLite database. When I read the coordinates using:
Var rs as RowSet
sql = "SELECT * FROM Locations"
rs = myDB.SelectSQL(sql)
While Not rs.AfterLastRow
latitude = rs.column("latitude").DoubleValue
longitude = rs.column("longitude").DoubleValue
rs.MoveToNextRow
Wend
latitude and longitude are read correctly to many significant figures. For mapping applications, one needs at least 5 or 6 significant figures to the right of the decimal point: 43.123456, -70.123456
If instead, I read the database using the following construct:
Var rs as RowSet
sql = "SELECT * FROM Locations"
rs = myDB.SelectSQL(sql)
for Each row as DatabaseRow in rs
latitude = row.column("latitude").DoubleValue
longitude = row.column("longitude").DoubleValue
next
the values of latitude and longitude are rounded to three significant figures: 43.123, -70.123. This results in an mislocation error of about 10+ meters. As I am using a 1 m digital elevation model (DEM), this is quite a significant error.
Is this a known issue? I’ve just spent the better part of a day getting rid of all the “for each row as databaseRow…” loops in my apps. I’ve known that this construct is slower than the “while not rs.afterlastrow…” way of reading the database, but this is downright inaccurate. There are many possible applications that require more than six significant figures. Hope no one is designing rocket ships with the “for each row as databaseRow in rs” loop!
db = New SQLiteDatabase
db.Connect
db.ExecuteSQL("CREATE TABLE test (id INT NOT NULL PRIMARY KEY, lat DOUBLE, lon DOUBLE);")
db.ExecuteSQL("INSERT INTO test (id, lat, lon) VALUES (1, 3.145678909, 41.22559977);")
Var Rows As RowSet = db.SelectSQL("SELECT * FROM test;")
For Each Row As DatabaseRow In Rows
Var rowlat As Double = Row.Column("lat").DoubleValue
Var rowslat As Double = Rows.Column("lat").DoubleValue
Var rowlon As Double = Row.Column("lon").DoubleValue
Var rowslon As Double = Rows.Column("lon").DoubleValue
Break
Next Row
Lots of silly and deadly bugs (as this one) have been found in a “DatabaseRow” iterating a “RowSet” since 2020, it seems a never ending story. This one MUST BE fixed in a dot release soon.
Again, tolerable bugs, for a few weeks, are cosmetic bugs, intolerable bugs, needing immediate intervention, are ANYTHING that causes data corruption (as this one). So, no I/O handling errors, Database handling errors, String handling errors, Math errors, or similar cases corrupting user data, many times silently, can exist in a release, and the vendor must issue any number of dot releases to fix them all as soon as discovered.
I agree: data corruption can have deadly consequences and needs an immediate fix. As a scientist and not a professional programmer, I tend to assume that my stupid programmer errors (which are many) are to blame, but I couldn’t make this one go away and a 10 m mislocation error drives me crazy!
Did you provide a sample code (privately) to the case?
Almost always I see requests to supply a sample project to start reviewing the case, so is better if you did provide one. If not, can you add it? Thanks.
