which sqlite statement can produce a result to indicate duplicate entries? Android

Question

For my application, I am trying to add entries without having a duplicate entry, and if there are a duplicate notify the user and have him try again. Using SQLite, which I know very very little about, I have tried these two ways and want to know which one is better, more efficient or a better way to develop it?

First way:

db.execSQL("INSERT INTO " + DatabaseHelper.DATABASE_TABLE + 
"(LATITUDE, LONGITUDE, RATING) SELECT " + latitude + ", " + longitude + ", " + rating +
" WHERE NOT EXISTS (SELECT 1 FROM " + DatabaseHelper.DATABASE_TABLE +
" WHERE LATITUDE = " + latitude + " AND LONGITUDE = " + longitude + ")");

Second way:

 long id = -1;
 try {
  id = db.compileStatement(
    "SELECT COUNT(*) FROM " + DatabaseHelper.DATABASE_TABLE
      + " WHERE LATITUDE = " + latitude
      + " AND LONGITUDE = " + longitude)
    .simpleQueryForLong();
 } catch (NullPointerException e) {
  return -1;
 }
 return id;

The first way, either inserts or ignores the values, I check by doing so, storing the row-count of the rows into a variable before the call, call that function, and then check its results. If the results is not higher than it was before the call, the insert was made ignored, prompt the user with the 'values exist' message. (Very sloppy, I know but desperate times calls for desperate measures)

The second way, returns the actual count of rows that match the numbers I want to store, if the number returned greater than 1, prompt the user with the 'values exist' message.

I have been going back and forth, trying different ways but I do not know how to set up SQLite to have UNIQUE pairs, which I was told would be the easiest. If anyone can correct either of these ways and/or comment on them, that'd be greatly appreciated.

Thanks

Answer 1

Here's what I understand you want: To have a table Database_Table (not the most descriptive name, if I may say so) which will never allow the same latitude and longitude pair to be entered for two lines.

If correct, you want to declare either a PRIMARY or UNIQUE KEY that incorporates the columns latitude and longitude. If you do so then any attempt to INSERT the same pair will throw an exception, which you can catch and thereby notify the user. Using this technique you will not need to use the WHERE NOT EXISTS (...) clause in your query, just do a plain INSERT and let SQLite alert you to a violation of your UNIQUEness constraint.

If your table already exists, the key can most easily be added using the SQL command CREATE INDEX. Since you may only have a single PRIMARY KEY on any table, if you have a PRIMARY KEY on the table already, you will need to use a UNIQUE KEY for this purpose.

The simplest form of the CREATE INDEX statement you would use is:

CREATE UNIQUE INDEX lat_long_unique ON Database_Table(latitude, longitude)

Answer 2

It seems to me that using a raw lat/long is not a great way to check for duplicates. Each possible lat/long combination using Android's GeoPoint 1E6 format covers an area of something less than five square inches. This size is, of course, different depending on where on Earth you are standing, but it makes a pretty good rule-of-thumb. So you should at least round up your lat/long to the nearest ten, hundred or thousand depending on the size of the thing you want to measure.

Before you store it in the database:

lat = (lat/1000)*1000; 
lon = (lon/1000)*1000;

Here's a good tool for calculating distances between lat/long points on Earth:

http://jan.ucc.nau.edu/~cvm/latlongdist.html

Answer 3

I have decided to go with my second approach (see above)

Reason being since creating a UNIQUE INDEX seem to not to work for me and proved to be a difficult task for me to accomplish whether that was result from my or SQLite's end.

It's purpose is not trivial, approach may be elementary but it does what I need it to do.

I hope people encountering a situation like this can be influenced by the answers left here and wish they have better luck with it than I had.