Query the Database in Android

Question

I would like a help from you to check what is wrong with this consultation:

        int raioKm = 10;

        String latitude =
                "CONVERT("                                                          +
                    "NUMERIC(10,7), "                                               +
                    "SUBSTRING(localizacao, 0, CHARINDEX(localizacao, ',' - 1))"    +
                ")";
        String longitude =
                "CONVERT("                                                      +
                    "NUMERIC(10,7), "                                           +
                    "SUBSTRING(localizacao, CHARINDEX(localizacao, ',' + 1), "  +
                        "LEN(localizacao))"                                     +
                ")";

        String formulaHaversine = "(6371 * ACOS(" +
            "COS(radians("+ data.getDoubleExtra("latitude", 0.00) +")) * "      +
            "COS(radians(" + latitude + ")) * "                                 +
            "COS(radians("+ data.getDoubleExtra("longitude", 0.00) +") - "      +
            "RADIANS("+ longitude + ")) + "                                     +
            "SIN(radians(-19.83996)) * "                                        +
            "SIN(radians(" + latitude + "))"                                    +
        "))";

        System.out.println(latitude + ", " + longitude);

        String sql =    "SELECT idFoto as _id, localizacao, diretorio, descricao, "   +
            formulaHaversine + " AS distance FROM Foto HAVING distance <= " + raioKm;

INTRODUCTION TO THE SUBJECT

I am trying to bring from the database only the rows of my table that are within 10 kilometers radius from a refocus point given by the expression data.getDoubleExtra() returning the latitude or longitude of this point.

The column location table Photo is formatted as follows: "latitude , longitude" (with spaces before and after the comma) - and this column needs to be reformatted so that its values, of type STRING, can be converted to DOUBLE. For this purpose, I use the function CONVERT, together with other functions, to obtain latitude and longitude and use them in STRING sql.

The Haversine formula is used to determine whether the coordinates are within the given radius (Identify if set of coordinates is within a radius on Android). Then the formula is used to generate the STRING sql "final".

PROBLEM

As I said, this project is in Android. Therefore, this query to the Database is being made within the method onActivityResult and when I try to compile it I get the following error:

java.lang.Runtimeexception: Failure Delivering result Resultinfo{who=null, request=2, result=-1, data=Intent { Cmp=gborniam.bbphoto/.graphic.Mapsactivity (has extras) }} to Activity {gborniam.bbphoto/gborniam.bbphoto.grafica.Main}: android.database.sqlite.Sqliteexception: no such Function: ACOS (code 1): , while compiling: SELECT idFoto as _id, location, directory, Description, (6371 * ACOS(COS(radians(-17.478109860099323)) * COS(radians(CONVERT(NUMERIC(10,7), SUBSTRING(location, 0, CHARINDEX(location, ',' - 1))))) * COS(radians(-49.62723668664694) - RADIANS(CONVERT(NUMERIC(10,7), SUBSTRING(location, CHARINDEX(location, ',' + 1), LEN(location)))) + SIN(radians(-19.83996)) * SIN(radians(CONVERT(NUMERIC(10,7), SUBSTRING(location, 0, CHARINDEX(location, ',' - 1)))) Distance FROM Photo HAVING Distance <= 10

SOS

Can someone please help me?

Answer 1

For the mistake you’re making and for what I just read, it seems that the same is occurring because Sqlite does not support the function ACOS, other than those contained in query.

See the most relevant part of the exception:

android.database.sqlite.Sqliteexception: no such Function: ACOS (code 1):

There’s a small stretch at this link which confirms the existence of other alternatives created by contributing community users, but are options that are not part of the standard Sqlite package, which should be used by "account and developer rich", state that they do not have an official verification.

See some similar problems below:

• Query to get Records based on Radius in Sqlite?
• No such acos Function exists
• acos Function in Sqlite

A suggestion perhaps, I say perhaps, more feasible for this situation would be to use Haversine’s formula in Java, to calculate the distance between the points. See a example on Github.