Skip navigation
All Places > AppStudio for ArcGIS > Blog > 2018 > November
2018

Introduction

 

I have some strings in my database. I would like to query them. Also, I want the results quick. Simple, use SQL LIKE and put an index on it.

 

Oh wait, that's weird, it didn't work like how I want... this blog covers the common traps with searching text strings in SQLite.

 

Scenario

 

Let's look at a sample database table. This one is representing property parcels and their very famous owners.

 

CREATE TABLE parcel
(
  owner TEXT
);

INSERT INTO parcel (owner) values ('Bill Gates');
INSERT INTO parcel (owner) values ('Steve Jobs');
INSERT INTO parcel (owner) values ('Jack Dangermond');
INSERT INTO parcel (owner) values ('Steve Wozniak');
INSERT INTO parcel (owner) values ('Tim Cook');
INSERT INTO parcel (owner) values ('Mark Zuckerberg');

 

We will be running queries on the above data, similar to:

 

SELECT * FROM parcel;

 

and, at the same time, we will be studying the query's execution plan with something similar to:

 

EXPLAIN QUERY PLAN SELECT * FROM parcel;

 

Using and optimizing LIKE

 

Once you dabble in a bit of SQL you'll quickly realize that you need to use LIKE for your string searches. For example, if we want to find all owners with names beginning with the letter J we do this:

 

SELECT *
FROM parcel
WHERE owner LIKE 'j%';
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}

 

Here, we observe that it found the right result, i.e. Jack Dangermond, however, the query needed to do a full table scan (i.e. SCAN TABLE parcel) to find that this was the only result.

 

Let's attempt to speed this up with an index and try again:

 

CREATE INDEX ix_parcel_owner ON parcel (owner);
SELECT *
FROM parcel
WHERE owner LIKE 'j%'
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}

 

Well, we got the right result, i.e. Jack Dangermond, but, why is it still doing a full table scan (SCAN TABLE parcel)?

 

Why didn't it use my index ix_parcel_owner?

 

The answer is SQLite, unlike other databases, implements LIKE as a case insensitive search so we got Jack Dangermond which begins with a capital J even though our search pattern was "j%" which begins with a lowercase J. The index that we created was a case sensitive index. LIKE ignored the index because it needed a case insensitive index.

 

To fix this, we try again. We create an index specific for case insensitive searches.

 

CREATE INDEX ix_parcel_owner_collate ON parcel (owner COLLATE NOCASE);
SELECT *
FROM   parcel
WHERE  owner LIKE 'j%';
-- EXPLAIN QUERY PLAN: SEARCH TABLE parcel USING COVERING INDEX ix_parcel_owner_collate (owner>? AND owner<?)
-- OUTPUT: {"owner":"Jack Dangermond"}

 

There, we did it. The result is still Jack Dangermond, this time the index was used (SEARCH TABLE parcel USING COVERING INDEX ix_parcel_owner_collate). Take home message, COLLATE NOCASE is your friend.

 

Alternatives to LIKE for string contains searches

 

Now that we've got some success with LIKE, let's use it to find more things. Let's see if we can find all owners with the letter C anywhere in their name:

 

SELECT *
FROM parcel
WHERE owner LIKE '%c%';
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}
-- OUTPUT: {"owner":"Tim Cook"}
-- OUTPUT: {"owner":"Mark Zuckerberg"}

 

Okay, we got the results we wanted. Jack Dangermond, Tim Cook and Mark Zuckerberg all have the letter C is their names. However, why are we back at full table scans (SCAN TABLE parcel)? Why isn't the index (ix_parcel_owner_collate) being used any more? That's because the B-Tree index being used works like how you look up names in a phone book. It works great if you have the starting letter(s) (i.e. divide the book in half, choose the half your letter is in, divide the book in half again).

 

However, because we don't have a starting letter, we can no longer use the phone book trick. We're back scanning every record in the table. The index we created is useless for this type of query.

 

So, what can we do? Well, there's a technique you can use but it requires rewriting the query, have a look at the following:

 

SELECT *, INSTR(LOWER(owner), LOWER('c')) idx
FROM parcel;
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"idx":0,"owner":"Bill Gates"}
-- OUTPUT: {"idx":0,"owner":"Steve Jobs"}
-- OUTPUT: {"idx":3,"owner":"Jack Dangermond"}
-- OUTPUT: {"idx":0,"owner":"Steve Wozniak"}
-- OUTPUT: {"idx":5,"owner":"Tim Cook"}
-- OUTPUT: {"idx":8,"owner":"Mark Zuckerberg"}

SELECT *
FROM parcel
WHERE INSTR(LOWER(owner), LOWER('c')) > 0;
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}
-- OUTPUT: {"owner":"Tim Cook"}
-- OUTPUT: {"owner":"Mark Zuckerberg"}

 

What is happening is we're using a function INSTR which returns the position of a substring (i.e. the letter C) in a string (i.e. owner). If the substring doesn't exist, you will simply get 0. We use the LOWER function on both the letter C and the owner to make it a case insensitive search.

 

The result is still the same as the previous version involving LIKE, i.e. we are getting all owners with the letter C in their names. However, the query plan is still doing a full table scan (SCAN TABLE parcel).

 

So, what's the point?

 

Well, SQLite allows you to index expressions! Think of it like a pre-calculated column.

 

CREATE INDEX ix_parcel_owner_instr_c ON parcel (INSTR(LOWER(owner), LOWER('c')));

 

The above statement will create an index. It may take some time. For example, if you had over 200000 records, that index may take several seconds to create. Consider that to be a good thing. Time spent here means the INSTR expression is being calculated for all records in the database once and only once. Every time we add a new record or modify an existing record, it will be the only time when that INSTR expression is calculated / recalculated.

 

That expression will never be recalculated at the time of the query. The query would just reuse the pre-calculated value that was stored in the index:

 

 

SELECT *
FROM parcel
WHERE INSTR(LOWER(owner), LOWER('c')) > 0;
-- EXPLAIN QUERY PLAN: SEARCH TABLE parcel USING INDEX ix_parcel_owner_instr_c (<expr>>?)
-- OUTPUT: {"owner":"Jack Dangermond"}
-- OUTPUT: {"owner":"Tim Cook"}
-- OUTPUT: {"owner":"Mark Zuckerberg"}

 

The above confirms we are now using the index (SEARCH TABLE parcel USING INDEX ix_parcel_owner_instr_c).

 

Code Sample

 

This AppStudio code sample was used to generate all the SQL content in this blog:

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

App {
    id: app
    width: 800 * AppFramework.displayScaleFactor
    height: 640 * AppFramework.displayScaleFactor

    property string logText: ""

    Flickable {
        id: flickable

        anchors.fill: parent
        anchors.margins: 10

        contentWidth: textArea.width
        contentHeight: textArea.height
        clip: true

        TextArea {
            id: textArea

            width: flickable.width
            wrapMode: Text.WrapAtWordBoundaryOrAnywhere
            selectByMouse: true
            text: logText
        }
    }

    SqlDatabase {
        id: db
        databaseName: ":memory:"
    }

    Component.onCompleted: {
        db.open();
        exec( [
            "CREATE TABLE parcel",
            "(",
            "    owner TEXT",
            ");"
            ].join("\n") );
        exec("INSERT INTO parcel (owner) values ('Bill Gates');");
        exec("INSERT INTO parcel (owner) values ('Steve Jobs');");
        exec("INSERT INTO parcel (owner) values ('Jack Dangermond');");
        exec("INSERT INTO parcel (owner) values ('Steve Wozniak');");
        exec("INSERT INTO parcel (owner) values ('Tim Cook');");
        exec("INSERT INTO parcel (owner) values ('Mark Zuckerberg');");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE 'j%';"
            ].join("\n") );
        exec("CREATE INDEX ix_parcel_owner ON parcel (owner);");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE 'j%';"
            ].join("\n") );
        exec("CREATE INDEX ix_parcel_owner_collate ON parcel (owner COLLATE NOCASE);");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE 'j%';"
            ].join("\n") );
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE '%c%';"
            ].join("\n") );
        exec( [
            "SELECT *, INSTR(LOWER(owner), LOWER('c')) idx",
            "FROM parcel;",
            ].join("\n") );
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE INSTR(LOWER(owner), LOWER('c')) > 0;"
            ].join("\n") );
        exec("CREATE INDEX ix_parcel_owner_instr_c ON parcel (INSTR(LOWER(owner), LOWER('c')));");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE INSTR(LOWER(owner), LOWER('c')) > 0;"
            ].join("\n") );
    }

    function exec(sql) {
        logText += "\n";
        logText += sql + "\n";

        var explain = db.query("EXPLAIN QUERY PLAN " + sql);
        if (!explain.error && explain.first()) {
            logText += "-- EXPLAIN QUERY PLAN: %1\n".arg(explain.values.detail);
            explain.finish();
        }

        var query = db.query(sql);
        if (query.error) {
            logText += query.error.databaseText;
            logText += query.error.driverText;
            logText += qsTr("NativeErrorCode: %1").arg(query.error.nativeErrorCode);
            logText += qsTr("ErrorType: %1").arg(query.error.type);
            return;
        }

        var ok = query.first();
        while (ok) {
            logText += "-- OUTPUT: %1\n".arg(JSON.stringify(query.values));
            ok = query.next();
        }
        query.finish();
    }
}

 

 

Summary

 

If speed matters to you, you cannot just create an index and just leave it there.

 

You need to check whether your queries use the index with EXPLAIN QUERY PLAN. If your index isn't being used, look at your query. Look at your WHERE clause. Think of what is happening there. Rewrite your WHERE clause if necessary. Create indexes that matches your WHERE clause.

 

If necessary, be prepared to index on expressions. Don't go overboard, we didn't create an index for every letter of the alphabet. That wasn't in our requirements today. We only wanted to search for the letter C and do that better. Over time, requirements changes. We probably will become disinterested in search for the letter C, then, feel free to drop that index. However, if a new criteria becomes more important, e.g. we want to search for all owners with Jack in the first, middle or last name, then, we will create an index to help with that purpose.

Building native apps using AppStudio for ArcGIS allows you to integrate all sorts of cool hardware with your app. The hardest part is knowing how to communicate with the hardware, and what to do with the information that you can get from (or send to) it. The following intends to be an overview of the three B's - Bluetooth, Bluetooth LE and beacons, which may help you decide which of these you might need to learn more about, to solve your hardware communication requirements.

 

Bluetooth

 

In AppStudio 3.0, we introduced support for classic Bluetooth connectivity. The most common use of classic Bluetooth for many years has been by GNSS vendors to connect their receivers to devices. You can also communicate with other sensors using Bluetooth, such as laser rangefinders and environmental sensors. The GNSS Info sample in AppStudio demonstrates how you can connect to a device with Bluetooth. To learn more about this sample see this blog.

 

Bluetooth LE

 

In AppStudio 3.1, we added beta support for Bluetooth low energy (LE). A subset of Bluetooth, as the name suggests, Bluetooth LE uses less power and is ideal for more frequent transmission of smaller amounts of data. Code samples of these are a little harder for us to offer you, as typically the devices that transmit data via Bluetooth LE, do so with proprietary information. The most prolific Bluetooth LE devices in the community are fitness devices. To use your Garmin or Fitbit device you need to connect to a proprietary app to see the information: for example, steps, distance, and calories burned. You will be able to use AppStudio's Bluetooth LE components to detect Bluetooth LE devices, but typically you wont be able to interpret the data that is transmitted. Here is a sample that demonstrates connecting to a fitness device. You may not be able to use this exactly, but it shows how to get started. Once a known device is found: a known service, characteristic and descriptor is also detected by the sample.

 

Services, characteristics and descriptors are how devices package information that they want to share via Bluetooth LE. For more information (including a diagram of how these are related) see the BluetoothLEDevice page in our API Reference. Note:  We are working to add more description and samples here!

 

One of our friends, GPS-IT, have already used AppStudio's Bluetooth LE support to create an app that communicates with their very own hardware! Their platemeter device is used to measure pasture volumes and the accompanying iOS and Android apps built with AppStudio, receive the measurement values from the hardware via Bluetooth LE.

 

Beacons

 

In coming releases of AppStudio we will be adding specific support for beacons. You can consider beacons as a subset of Bluetooth LE devices. They use the same LE protocol, but make identifier information more readily available, suitable for create alerts and triggers when interacting with the beacons.

 

And because this is not complex enough, when reading about beacons, you will see two terms: iBeacon and Eddystone. In short, these are the Apple (iBeacon) and Google (Eddystone) ways of communicating with beacons, but they don't limit which device you use to communicate with the beacon. One is not necessarily better than the other, they are just different. In AppStudio we've made progress with iBeacon properties first, and will share more about our beacon support in coming blogs and releases.

 

You might be thinking: why beacons? The most common beacon use case is that of an interactive shop or gallery. Imagine entering a museum, and when you buy your ticket you are directed to launch the museum tour guide app. In the foyer area the app will show you general info about the museum. As you enter a gallery space, information about the room and the collection you are about to see, is shown (or dictated) to you. As you approach an individual object, information about that specific object is shown to you. This guided navigation can be achieved using beacons located near each object, or entrance to a space.  This is a great way for keeping propriety information on site. The user can only see or interact with the information, whilst they at your venue. Retailers also use a similar pattern to engage with a customer: as you approach an item or location in the store, targeted advertising or specials can be shared with the user.

 

Summary

 

Knowing whether to use Bluetooth or Bluetooth LE is very much dependent on the hardware you want to integrate with your app, and the hardware itself may restrict what type or amount of information you can receive from (or send to) it. We are keen to hear what Bluetooth hardware you have or would like to integrate with your AppStudio app, and look forward to expanding more on these concepts in more blogs and in our AppStudio help.

The AppStudio Framework (AppFramework) provides QML components used when creating apps in AppStudio for ArcGIS. AppFramework library of components is unique in that it has features and capabilities that are neither natively provided by the underlying Qt framework nor the ArcGIS Runtime but are necessary and commonly used for developing apps. With every release of the AppStudio, we enhance AppFramework to exploit new underlying software and hardware device features, utility functions to improve developer productivity. In this blog, we are highlighting the beta features added to the AppFramework at the AppStudio 3.1 release (September 2018). 

 

Note: These capabilities are currently in Beta. This means we are looking forward to your feedback and functionality may undergo minor changes. Please be cautious in using these beta features in your store apps. 

 

Support for Bluetooth Low Energy (BLE) devices

We have added the following new components into AppFramework Devices plugin to support communication with Bluetooth LE devices. You can now create AppStudio apps that can discover and connect to Bluetooth LE devices, discover services, and read or write characteristics for devices that support it. You can find more information on how to use these new components in this blog post.   

 

 

 

New Email Composer component

With AppStudio 3.1, we have introduced a new Inter-App Communication plugin, which includes an EmailComposer component to launch your system’s mail client with a pre-filled draft email that is ready to send, modify, delete or save as a draft.  The following code snippet demonstrates how to show a pre-filled email by clicking on a button using the EmailComposer component.   

 

import ArcGIS.AppFramework.InterAppCommunication 1.0
EmailComposer {
id: emailcomposer
to: "example@example.com"
cc: ["example2@example.com", "example3@example.com"]
bcc: "example4@example.com"
subject: "Feedback for " + app.info.title
body:
"Device OS:" + Qt.platform.os + AppFramework.osVersion + "<p>" +
"Device Locale:" + Qt.locale().name + "<p>" +
"App Version:" + app.info.version + "<p>" +
"AppStudio Version:" + AppFramework.version
html: true
}

Button {
id: openEmailButton
text: qsTr("Generate Email")
onClicked: {
emailcomposer.show()
}
}

 

An Email Composer sample is also available in AppStudio Desktop or Player. 

 

 

      

                                                                           Email Composer Sample App

 

 

Support for cryptographic hashes

Have you ever try to make a web service call from your native app that request to provide encrypted information in the headers or you just looking to store your information securely using the hash. We have made it easier to add these features and more to your app using the new CryptographicHash and MessageAuthenticationCode components available in the AppFramework at the 3.1 release. These components provide a way to generate cryptographic hashes or hash-based message authentication codes. We have published a Cryptographic Hash sample to show you how to use Cryptographic Hash functions and Message Authentication Codes in your AppStudio apps. You can search for it in the AppStudio Desktop or just play it in AppStudio Player Samples page. 

 

 

        

                                                                         Cryptographic Hash Sample App

 

 

New Browser View component

One of the most requested features for iOS has landed in AppStudio 3.1. We are happy to introduce a new native in-app browser component called BrowserView to bring you a full Safari web browser experience for iOS devices. Under the hood, the BrowserView is powered by SafariViewController for iOS and AppFramework WebView on other platforms.  The following code sample demonstrates how to use the BrowserView component.

 

import ArcGIS.AppFramework.WebView 1.0
Button {
text: "Open BrowserView"
onClicked: {
browserView.show()
}
}

BrowserView {
id: browserView
anchors.fill: parent
url: "https://appstudio.arcgis.com/"
}

 

Note: It is recommended to set that the "enableHighDpi" property under "display" to true in the appinfo.json file so that the BrowserView displays without any UI scaling issues.  

Note: The BrowserView documentation is currently not available in online documentation or bundled in Qt Creator while in beta.  Here is a quick API reference. 

Property:

url: url  

The URL of the web page that you wish to load 

Method

show ()  

Shows Web View  

 

 

A Browser View sample app is also available to you on AppStudio Desktop and Player. 

 

     

                                                                              Browser View Sample App

 

AppFramework component enhancements

   

1. Permissions ... Permissions ... Permissions ...

We have added a new checkCapability() method into the AppFramework in 3.1 to help you check if your device has granted critical permissions such as access to the device’s location, camera, microphone, or external storage. This is extremely useful if the user has denied the permission for the first time, you can prompt a warning pop-up after detecting a disabled permission using checkCapability() method. The following code snippet shows you how to check camera permission.  The rectangle will turn green if the device has access to use the camera. 

 

Rectangle {
width: 25
height: width
color : AppFramework.checkCapability(AppFramework.Camera) ? "green" : "red"
}

 

2. New isAppInstalled() method

Have you ever try to open an app from your app and wondered if the other app is available?

Starting from AppStudio 3.1, you can use the isAppInstalled() method to check if an app is installed on your device.  The input parameter is a URI Scheme (e.g. app-urlscheme://) on iOS or a Package Name (e.g com.app.package.name) on Android.

 

Note: This method is currently only implemented on iOS and Android. On all other platforms, this method returns true regardless.

3. AppFramework.systeminformation property enhancements

 AppFramework.systeminformation now returns three new parameters, “family”, “manufacture”, and “model”, to determine if a device is a phone or a tablet, which vendor creates your device, and the model of your device. You can find how to use this property in the Screen and System Info sample, which is available in AppStudio Desktop and Player. 

 

                          

                                                                      Screen and System Info Sample App

 

 

Secure Storage Enhancements

When using Secure Storage component, previously the maximum value and key size were 214 characters on all platforms even though some platforms would allow you to store more. We have introduced two new properties to the SecureStorage component - maximumKeyLength and maximumValueLength allow you to determine the limit on the length of the key and value. With these newly introduced properties, your apps can now take full advantage of a device’s limits. In addition, secure storage is now supported on the Universal Windows Platform (UWP).

 

                   

                                                                               Secure Storage Sample App

 

 

Sharing enhancement on iOS

The Clipboard.share() method opens a sharing dialog, allowing you to share data across apps.  We have added a new option to open a URL link in Safari through this sharing dialog on iOS. 

 

    

                                                                                  Share Sheet Sample App

 

 

We hope you appreciate and use all the new beta features added in AppFramework in your own apps. Please try them out and provide any suggestions or feedback before we finalize in the next releases. Also, let us know if you are looking for any new feature that might help every AppStudio user create app faster, better, and with ease.   

 

 

 

Become an AppStudio for ArcGIS developer! Watch this video on how to sign up for a free trial.

 

Follow us on twitter @AppStudioArcGIS to keep up to date with the latest information and do not forget to let us know about your latest creations using AppStudio and be featured on the AppStudio Showcase.