How to install Spatialite in android

안드로이드에 Spatialite 설치하기 

 

1. 준비사항

1) android studio ( https://developer.android.com/studio/ )

2) spatialite libs ( https://github.com/geopaparazzi/libjsqlite-spatialite-android/raw/master/archive/20190707.libjsqlite.5.0.0.tar.bz2

3) api source http://www.gaia-gis.it/gaia-sins/spatialite-android/spatialite-for-android-3.0.1.zip

4) 튜토리얼 ( https://www.gaia-gis.it/fossil/libspatialite/wiki?name=spatialite-android-tutorial )

SpatiaLite: spatialite-android-tutorial

 

2. 설치하기 

1) 안드로이드 스튜디오 설치 및 spatialite libs, api source 다운로드 후 압축을 푼다

 

2)  안드로이드 스튜디오에서 기본프로젝트(hello world)를 만든다.

    File > New > New Project > Empty Activity 

 

3) build.gradle 에 sourceSets 추가

android {

--- 기존설정

sourceSets {
 main {
  jniLibs.srcDirs = ["src/main/libs"]
 }
}

}

 

 

4) 압축 푼 libs를 main/java 밑에 복사

 

5) 압축 푼 jsqlite Exception 처리

- .class 파일 모두 제거

- Exception.java 제거 

- 패키지 안의 모든 자바소스 중 jsqlite.Exception -> Exception 으로 스트링 replace

 

6) Exception 작업한 jsqlite 패키지 등록(src/main/java/ 밑에)

 

7) 외부 Storage 권한 등록

- AndroidManifest.xml <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

- onCreate 안에 런타임 권한  등록 

        
        if (ContextCompat.checkSelfPermission(MainActivity.this,
                Manifest.permission.WRITE_EXTERNAL_STORAGE)
                != PackageManager.PERMISSION_GRANTED) {

            if (ActivityCompat.shouldShowRequestPermissionRationale(MainActivity.this,
                    Manifest.permission.WRITE_EXTERNAL_STORAGE)) {
            } else {
                ActivityCompat.requestPermissions(MainActivity.this,
                        new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE},
                        101);

            }
        } else {
            // Permission has already been granted
        }

 

8) 튜토리얼 예제소스 실행

package com.example.spatialtest2;

import android.Manifest;
import android.content.pm.PackageManager;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;
import androidx.core.content.ContextCompat;

import java.io.File;

import jsqlite.Database;
import jsqlite.Stmt;

public class MainActivity extends AppCompatActivity {

    Database db;
    String TAG = "MainActivity";
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        if (ContextCompat.checkSelfPermission(MainActivity.this,
                Manifest.permission.WRITE_EXTERNAL_STORAGE)
                != PackageManager.PERMISSION_GRANTED) {

            if (ActivityCompat.shouldShowRequestPermissionRationale(MainActivity.this,
                    Manifest.permission.WRITE_EXTERNAL_STORAGE)) {
            } else {
                ActivityCompat.requestPermissions(MainActivity.this,
                        new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE},
                        101);
            }
        } else {
            // Permission has already been granted
        }

        try {
            File sdcardDir = new File(Environment.getExternalStorageDirectory().getAbsolutePath()); // your sdcard path
            File spatialDbFile = new File(sdcardDir, "italy.sqlite");
            Log.d(TAG, sdcardDir.getAbsolutePath());
            Log.d(TAG, spatialDbFile.getAbsolutePath());
            db = new jsqlite.Database();
            db.open(spatialDbFile.getAbsolutePath(), jsqlite.Constants.SQLITE_OPEN_READWRITE
                    | jsqlite.Constants.SQLITE_OPEN_CREATE);

            Log.d(TAG, queryVersions());
            Log.d(TAG, queryComuni());
            Log.d(TAG, queryComuniWithGeom());
            Log.d(TAG, queryComuniArea());
            Log.d(TAG, doSimpleTransform());
            Log.d(TAG, queryComuniNearby());
            db.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String queryVersions() throws Exception {
        StringBuilder sb = new StringBuilder();
        sb.append("Check versions...\n");

        Stmt stmt01 = db.prepare("SELECT spatialite_version();");
        if (stmt01.step()) {
            sb.append("\t").append("SPATIALITE_VERSION: " + stmt01.column_string(0));
            sb.append("\n");
        }

        stmt01 = db.prepare("SELECT proj4_version();");
        if (stmt01.step()) {
            sb.append("\t").append("PROJ4_VERSION: " + stmt01.column_string(0));
            sb.append("\n");
        }

        stmt01 = db.prepare("SELECT geos_version();");
        if (stmt01.step()) {
            sb.append("\t").append("GEOS_VERSION: " + stmt01.column_string(0));
            sb.append("\n");
        }
        stmt01.close();

        sb.append("Done...\n");
        return sb.toString();
    }

    public String queryComuni() {
        StringBuilder sb = new StringBuilder();
        sb.append("Query Comuni...\n");

        String query = "SELECT NOME" + //
                " from Comuni_11" + //
                " order by NOME;";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            int index = 0;
            while( stmt.step() ) {
                String nomeStr = stmt.column_string(0);
                sb.append("\t").append(nomeStr).append("\n");
                if (index++ > 5) {
                    break;
                }
            }
            sb.append("\t...");
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        sb.append("Done...\n");

        return sb.toString();
    }

    public String queryComuniWithGeom() {
        StringBuilder sb = new StringBuilder();

        sb.append("Query Comuni with AsText(Geometry)...\n");

        String query = "SELECT NOME" + //
                " , AsText(Geometry)" + //
                " as geom from Comuni_11" +  //
                " where geom not null;";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            while( stmt.step() ) {
                String nomeStr = stmt.column_string(0);
                String geomStr = stmt.column_string(1);
                String substring = geomStr;
                if (substring.length() > 40)
                    substring = geomStr.substring(0, 40);
                sb.append("\t").append(nomeStr).append(" - ").append(substring).append("...\n");
                break;
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        sb.append("Done...\n");

        return sb.toString();
    }

    public String queryComuniArea() {
        StringBuilder sb = new StringBuilder();

        sb.append("Query Comuni area sum...\n");

        String query = "SELECT ST_Area(Geometry) / 1000000.0 from Comuni_11" +  //
                ";";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            double totalArea = 0;
            while( stmt.step() ) {
                double area = stmt.column_double(0);
                totalArea = totalArea + area;
            }
            sb.append("\tTotal area by summing each area: ").append(totalArea).append("Km2\n");
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        sb.append("Done...\n");

        return sb.toString();
    }

    double TEST_LON = 37.253453454;
    double TEST_LAT = 38.253453454;

    public String doSimpleTransform() {
        StringBuilder sb = new StringBuilder();

        sb.append("Coordinate transformation...\n");

        String query = "SELECT AsText(Transform(MakePoint(" + TEST_LON + ", " + TEST_LAT + ", 4326), 32632));";
        sb.append("Execute query: ").append(query).append("\n");
        try {
            Stmt stmt = db.prepare(query);
            if (stmt.step()) {
                String pointStr = stmt.column_string(0);
                sb.append("\t").append(TEST_LON + "/" + TEST_LAT + "/EPSG:4326").append(" = ")//
                        .append(pointStr + "/EPSG:32632").append("...\n");
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        sb.append("Done...\n");

        return sb.toString();

    }

    String NOME = "Bolzano";
    String COMUNITABLE = "omuni_11";
    public String queryComuniNearby() {
        StringBuilder sb = new StringBuilder();
        sb.append("Query Comuni nearby...\n");

        String query = "SELECT Hex(ST_AsBinary(ST_Buffer(Geometry, 1.0))), ST_Srid(Geometry), ST_GeometryType(Geometry) from Comuni_11" +
                " where NOME = 'Bolzano';";
        sb.append("Execute query: ").append(query).append("\n");
        String bufferGeom = "";
        String bufferGeomShort = "";
        try {
            Stmt stmt = db.prepare(query);
            if (stmt.step()) {
                bufferGeom = stmt.column_string(0);
                String geomSrid = stmt.column_string(1);
                String geomType = stmt.column_string(2);
                sb.append("\tThe selected geometry is of type: ").append(geomType).append(" and of SRID: ").append(geomSrid)
                        .append("\n");
            }
            bufferGeomShort = bufferGeom;
            if (bufferGeom.length() > 10)
                bufferGeomShort = bufferGeom.substring(0, 10) + "...";
            sb.append("\tBolzano polygon buffer geometry in HEX: ").append(bufferGeomShort).append("\n");
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(e.getCause()).append(e.getLocalizedMessage()).append("\n");
        }

        query = "SELECT " + NOME + ", AsText(ST_centroid(Geometry)) from " + COMUNITABLE + //
                " where ST_Intersects( ST_GeomFromWKB(x'" + bufferGeom + "') , Geometry );";
        // just for print
        String tmpQuery = "SELECT " + NOME + " from " + COMUNITABLE + //
                " where ST_Intersects( ST_GeomFromWKB(x'" + bufferGeomShort + "') , Geometry );";
        sb.append("Execute query: ").append(tmpQuery).append("\n");
        try {
            sb.append("\tComuni nearby Bolzano: \n");
            Stmt stmt = db.prepare(query);
            while( stmt.step() ) {
                String name = stmt.column_string(0);
                String wkt = stmt.column_string(1);
                sb.append("\t\t").append(name).append(" - with centroid in ").append(wkt).append("\n");
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            sb.append(e.getCause()).append(e.getLocalizedMessage()).append("\n");
        }
        sb.append("Done...\n");

        return sb.toString();
    }
}

끝.