Showing posts with label DataBase. Show all posts

DBPedia

As an Amazon Associate I earn from qualifying purchases.

DBPedia

As an Amazon Associate I earn from qualifying purchases.

java.sql.sqlexception: [sqlite_error] sql error or missing database (no such table:

When trying to open the database you may get the following exception:

java.sql.sqlexception: [sqlite_error] sql error or missing database (no such table:

even if it looks like the TABLE is missing, in reality the path to the database may not be fully defined.

Consider Incorrect:

private static String databaseFilePath = "kml.db";

Successfully opened connection to jdbc:sqlite:kml.db using org.sqlite.Conn
SQLException for kml.db
Connection closed!
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: placemark)

and Correct:

private static String databaseFilePath = "/Users/uki/_REPO/CLC/2015_spring/cit137/UKI_LUCAS/XmlParsing/kml.db";

Successfully opened connection to jdbc:sqlite:/Users/uki/_REPO/CLC/2015_spring/cit137/UKI_LUCAS/XmlParsing/kml.db using org.sqlite.Conn

If you like this post, please give me your 2 cents ($0.02 litterally) to show token of appreciation and encourage me to write more:

Donate Bitcoins

As an Amazon Associate I earn from qualifying purchases.

java.sql.sqlexception: [sqlite_error] sql error or missing database (no such table:

private static String databaseFilePath = "kml.db";

private static String databaseFilePath = "/Users/uki/_REPO/CLC/2015_spring/cit137/UKI_LUCAS/XmlParsing/kml.db";

Successfully opened connection to jdbc:sqlite:/Users/uki/_REPO/CLC/2015_spring/cit137/UKI_LUCAS/XmlParsing/kml.db using org.sqlite.Conn

If you like this post, please give me your 2 cents ($0.02 litterally) to show token of appreciation and encourage me to write more:

Donate Bitcoins

As an Amazon Associate I earn from qualifying purchases.

IntelliJ IDEA: Database plugin

In this tutorial we will learn how to install the Intellij IDEA database plugin.

Start with opening Settings > search for plugins > search for database > click Browse

Install plugin "Database Navigator"
Note it does not support SQLite

As an Amazon Associate I earn from qualifying purchases.

IntelliJ IDEA: Database plugin

In this tutorial we will learn how to install the Intellij IDEA database plugin.

Start with opening Settings > search for plugins > search for database > click Browse

Install plugin "Database Navigator"
Note it does not support SQLite

As an Amazon Associate I earn from qualifying purchases.

6a. Java: Generic Type Interface

In this tutorial we will learn how to create an Interface that serves any type of object using Java Generic Types introduced in Java 1.5.

As an Amazon Associate I earn from qualifying purchases.

6a. Java: Generic Type Interface

In this tutorial we will learn how to create an Interface that serves any type of object using Java Generic Types introduced in Java 1.5.

Step 1: Create interface

Note you can put it in any package, but this Interface is NOT your project specific, it is not even Android specific.

package com.cyberwalkabout.database;
import java.util.List;
/**
* Created by uki on 10/11/14.
* This interface simply assures that we don't forget to implement most important methods.
* We are using Generic TYPE T as we don't know what objects we will be using in the database.
* The TYPE T could stand for any object e.g. Book, Person, Address, etc.
* You could add more methods of your own, or better method parameters.
*/
public interface DatabaseCrud<T> {
/**
* Saves an object to the database.
*/
public void create(T object);
/**
* This methods reads one record by id.
* Please notice it returns Generic Type T.
*/
public T read(int dbRecordId);
/**
* fetches all objects that match the String searchText
*/
public List<T> fetch(String searchText);
/**
* Update given object in the database.
*/
public int update(T object);
/**
* Deletes given object from the database.
*/
public void delete(T object);
}

Step 2: Implement the Interface in your specific database wrapper code

public class BookSqlHelper extends SQLiteOpenHelper implements DatabaseCrud<Book> {

Make sure you auto-copy JavaDocs from the Interface:

Step 3: Implement your generated methods

The IDE automatically generates method stubs like this:

/**
* Deletes given object from the database.
*
* @param object
*/
@Override
public void delete(Book object) {

}

Now, you only have to fill in the blanks, note I changed name of the object from object to book:

/**
* Inserts a Book object to the database.
* Please note that the Interface uses Generic Type T:
* public void create(T object);
*/
@Override
public void create(Book book) {
Log.w(TAG + "save()", book.toString());
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(FIELDTITLE, book.getTitle());
values.put(FIELDAUTHOR, book.getAuthor());
values.put(FIELDISBN, book.getIsbn());
values.put(FIELDLOCATION, book.getLocation());
db.insert(TABLE_BOOKS, null, values);
db.close();
}

As an Amazon Associate I earn from qualifying purchases.

7b. SQLite CRUD - BookShelf app

In this tutorial you will learn basic SQLite CRUD functions (Create, Read, Update, Delete) for database operations. Actually, we will use Save, Fetch, Update and Delete method names.

As an Amazon Associate I earn from qualifying purchases.

7b. SQLite CRUD - BookShelf app

In this tutorial you will learn basic SQLite CRUD functions (Create, Read, Update, Delete) for database operations. Actually, we will use Save, Fetch, Update and Delete method names.

Step 1: Create a new IntelliJ/Android Studio Project

project name: Week7
module name: BookShelf

Step 2: The first version of this app will not have any UI, edit strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="app_name">Book Shelf - SQLite example</string>
<string name="hello_world">See logcat output!</string>
<string name="action_settings">Settings</string>
</resources>

Step 3: Run the app to make sure everything is OK so far

Step 4: Create new package "model" and new Java class "Book"

In this example we will be operating on the OBJECT Book, therefore we need a model for it.

package com.chicagoandroid.cit299.week7.bookshelf.model;
public class Book {
private int id;
private String title;
private String author;
private String isbn;
private String location;
/**
* Constructor with no parameters
*/
public Book() {
}
/**
* Constructor with title and author parameters
* @param title
* @param author
*/
public Book(String title, String author) {
super();
this.title = title;
this.author = author;
}
/**
* Constructor with ISBN parameter
* @param isbn
*/
public Book(String isbn) {
super();
this.isbn = isbn;
}

For our convenience we will override toString() method that will show us the content of the Book.

@Override
public String toString() {
return "Book: id=" + id
+ "\n title = " + title
+ "\n author = " + author
+ "\n isbn = " + isbn;
}

Step 5: Generate getters and Setters methods for Book.java

Step 6: Create new package "database" and new Java Interface "DatabaseCrud"

package com.cyberwalkabout.database;

import java.util.List;

/**
* Created by uki on 10/11/14.
* This interface simply assures that we don't forget to implement most important methods.
* We are using Generic TYPE T as we don't know what objects we will be using in the database.
* The TYPE T could stand for any object e.g. Book, Person, Address, etc.
* You could add more methods of your own, or better method parameters.
*/
public interface DatabaseCrud<T> {

/**
* Saves an object to the database.
*/
public void create(T object);

/**
* This methods reads one record by id.
* This record has to be in the Database to have id.
* Please notice it returns Generic Type T.
*/
public T read(int dbRecordId);

/**
* fetches all objects that match the String searchText
*/
public List<T> fetch(String searchText);

/**
* Update given object in the database.
*/
public int update(T object);

/**
* Deletes given object from the database.
* This method should wrap delete(int objectDbId);
*/
public void delete(T object);
}

Step 7: Create Java class "BookSqlHelper"

package com.chicagoandroid.cit299.week7.bookshelf.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.chicagoandroid.cit299.week7.bookshelf.model.Book;
import com.cyberwalkabout.database.DatabaseCrud;

import java.nio.Buffer;
import java.util.LinkedList;
import java.util.List;

public class BookSqlHelper extends SQLiteOpenHelper implements DatabaseCrud<Book> {
private static final String TAG = BookSqlHelper.class.getSimpleName();

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "DB_BOOK_SHELF";
private static final String TABLE_BOOKS = "books";
private static final String FIELD_ID = "id";
private static final String FIELDTITLE = "title";
private static final String FIELDAUTHOR = "author";
private static final String FIELDISBN = "isbn";
private static final String FIELDLOCATION = "location";

private static final String[] COLUMNS = { //
FIELD_ID, // 0
FIELDTITLE, // 1
FIELDAUTHOR, // 2
FIELDISBN, // 3
FIELDLOCATION // 4
};

public BookSqlHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_BOOK_TABLE = //
"CREATE TABLE " + TABLE_BOOKS + " ( " //
+ FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " //
+ FIELDTITLE + " TEXT, " //
+ FIELDAUTHOR + " TEXT, " //
+ FIELDISBN + " TEXT, " //
+ FIELDLOCATION + " TEXT " //
+ ")";
db.execSQL(CREATE_BOOK_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS books");
this.onCreate(db);
}

Step 8 : Add "create" method of BookSqlHelper.java

/**
* Inserts a Book object to the database.
*/
@Override
public void create(Book book) {
Log.w(TAG + "save()", book.toString());
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(FIELDTITLE, book.getTitle());
values.put(FIELDAUTHOR, book.getAuthor());
values.put(FIELDISBN, book.getIsbn());
values.put(FIELDLOCATION, book.getLocation());
db.insert(TABLE_BOOKS, null, values);
db.close();
}

Step 9: Implement "read" method of BookSqlHelper.java

/**
* This methods reads one record by id.
*
* @param dbBookId
*/
@Override
public Book read(int dbBookId) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query( //
TABLE_BOOKS, // String table
COLUMNS, // String[] columns
" id = ?", // selection
new String[]{String.valueOf(dbBookId)}, // String[] selection arguments
null, // String group by
null, // String having
null, // String order by
null); // String limit
return getBooksFromCursor(cursor).get(0);
}

Step 9 : Implement "update" method of BookSqlHelper.java

/**
* Update given Book object in the database.
*/
@Override
public int update(Book book) {

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("title", book.getTitle());
values.put("author", book.getAuthor());
values.put("isbn", book.getIsbn());
values.put("location", book.getLocation());

int i = db.update( //
TABLE_BOOKS, // String table
values, // ContentValues values - column/value pairs
FIELD_ID + " = ?", // String where clause
new String[]{String.valueOf(book.getId()) // String[] where arguments
});
db.close();
Log.w(TAG + "update(Book book)", book.toString());
return i;
}

Step 10: Implement "delete" method(s)

/**
* Deletes given object from the database.
*/
@Override
public void delete(Book book) {
delete(book.getId());
Log.d(TAG + "delete", book.toString());
}
/**
* Delete database object by it's id.
*
* @param bookDbId - database id of the object to be deleted.
*/
public void delete(int bookDbId) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete( //
TABLE_BOOKS, // String table
FIELD_ID + " = ?", // String where clause
new String[]{String.valueOf(bookDbId) // String[] where arguments
});
db.close();
Log.d(TAG + "delete(int bookDbId)", "ID: " + bookDbId);
}

As an Amazon Associate I earn from qualifying purchases.

Changing password on PostgreSQL

When running my Java app I get...

uki@.. $ java -jar ./build/libs/XYZ-1.0.jar

Vacuum 'gps_points' table...

org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"

FIX:

uki@.. $ psql -d polygons --user=postgres -c "ALTER USER Postgres WITH PASSWORD 'my_secret';"

ALTER ROLE

As an Amazon Associate I earn from qualifying purchases.

Changing password on PostgreSQL

When running my Java app I get...

uki@.. $ java -jar ./build/libs/XYZ-1.0.jar

Vacuum 'gps_points' table...

org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"

FIX:

uki@.. $ psql -d polygons --user=postgres -c "ALTER USER Postgres WITH PASSWORD 'my_secret';"

ALTER ROLE

As an Amazon Associate I earn from qualifying purchases.

PostgreSQL: postGIS functions

Change to Posgres (database user)

pi@raspberrypi /home/uki $ su postgres

Password:

postgres@raspberrypi:/home/uki$

Select correct database

postgres@raspberrypi:/home/uki$ psql -d polygons

psql (9.1.12)

Type "help" for help.

polygons=#

To exit PostgreSQL shell

polygons=# \q

Select number of Points per Polygon in database

polygons=# select ST_NPoints(polygon) from polygon;

107ms on Respberry Pi

st_npoints
------------
132
132
12
6
26
57
15
6
18
26
19
8
6
6
14
13
10
58
10
8
171
171

Select polygons for the GEO point

polygons=# select ST_NPoints(polygon) points from polygon where ST_Contains(polygon, ST_GeomFromText('POINT(-89.2535979 37.9210949)'));

Alternatively you can run query from Linux user prompt

postgres@raspberrypi:/home/pi$ psql -d polygons -c 'select ST_NPoints(polygon) from polygon;'

If you testing timing of your query then you have to turn it on

polygons=# \timing

Timing is on.

polygons=# select ST_NPoints(polygon) from polygon;

Time: 17.256 ms

As an Amazon Associate I earn from qualifying purchases.

PostgreSQL: postGIS functions

Change to Posgres (database user)

pi@raspberrypi /home/uki $ su postgres

Password:

postgres@raspberrypi:/home/uki$

Select correct database

postgres@raspberrypi:/home/uki$ psql -d polygons

psql (9.1.12)

Type "help" for help.

polygons=#

To exit PostgreSQL shell

polygons=# \q

Select number of Points per Polygon in database

polygons=# select ST_NPoints(polygon) from polygon;

107ms on Respberry Pi

st_npoints
------------
132
132
12
6
26
57
15
6
18
26
19
8
6
6
14
13
10
58
10
8
171
171

Select polygons for the GEO point

polygons=# select ST_NPoints(polygon) points from polygon where ST_Contains(polygon, ST_GeomFromText('POINT(-89.2535979 37.9210949)'));

Alternatively you can run query from Linux user prompt

postgres@raspberrypi:/home/pi$ psql -d polygons -c 'select ST_NPoints(polygon) from polygon;'

If you testing timing of your query then you have to turn it on

polygons=# \timing

Timing is on.

polygons=# select ST_NPoints(polygon) from polygon;

Time: 17.256 ms

As an Amazon Associate I earn from qualifying purchases.

Raspberry Pi, PostGre SQL GEO fencing and Java

Logging Remotely to Raspberry

You can do all following in this tutorial from remote location using Secure Shell in command line:

$ ssh 7x.xy.yy.x -l uki
uki@7x.xy.yy.x's password:
Linux raspberrypi 3.10.25+ #622 PREEMPT Fri Jan 3 18:41:00 GMT 2014 armv6l

You have to have permissions to install stuff

$ sudo apt-get install postgis
[sudo] password for uki:
uki is not in the sudoers file. This incident will be reported.
uki@raspberrypi ~ $ su pi
Password:

Install PostGIS for GEO fencing

pi@raspberrypi ~ $ sudo apt-get install postgis
Reading package lists... Done
Building dependency tree
Reading state information... Done
Suggested packages:
postgresql-9.1-postgis
The following NEW packages will be installed:
postgis
0 upgraded, 1 newly installed, 0 to remove and 43 not upgraded.
Need to get 573 kB of archives.
After this operation, 1,930 kB of additional disk space will be used.
Get:1 http://mirrordirector.raspbian.org/raspbian/ wheezy/main postgis armhf 1.5.3-2+b1 [573 kB]
Fetched 573 kB in 8s (64.3 kB/s)
Selecting previously unselected package postgis.
(Reading database ... 70358 files and directories currently installed.)
Unpacking postgis (from .../postgis_1.5.3-2+b1_armhf.deb) ...
Processing triggers for man-db ...
Setting up postgis (1.5.3-2+b1) ...
pi@raspberrypi ~ $

Change password for linux user for postgre

pi@raspberrypi ~ $ sudo passwd postgres
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
pi@raspberrypi ~ $

Install postgis plugin for PostGre

pi@raspberrypi ~ $ sudo apt-get install postgresql-9.1-postgis
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libgeos-3.3.3 libgeos-c1 libproj0 proj-data
Suggested packages:
proj-bin
The following NEW packages will be installed:
libgeos-3.3.3 libgeos-c1 libproj0 postgresql-9.1-postgis proj-data
0 upgraded, 5 newly installed, 0 to remove and 43 not upgraded.
Need to get 4,460 kB of archives.
After this operation, 11.9 MB of additional disk space will be used.
Do you want to continue [Y/n]? yes
Get:1 http://mirrordirector.raspbian.org/raspbian/ wheezy/main libgeos-3.3.3 armhf 3.3.3-1.1 [576 kB]
Get:2 http://mirrordirector.raspbian.org/raspbian/ wheezy/main postgresql-9.1-postgis armhf 1.5.3-2+b1 [665 kB]
Get:3 http://mirrordirector.raspbian.org/raspbian/ wheezy/main libgeos-c1 armhf 3.3.3-1.1 [165 kB]
Get:4 http://mirrordirector.raspbian.org/raspbian/ wheezy/main proj-data armhf 4.7.0-2 [2,940 kB]
41% [4 proj-data 406 kB/2,940 kB 14%] 219 kB/42% [4 proj-data 457 kB/2,940 kB 16%] 43% [4 p44Get:5 http://mirrordirector.raspbian.org/raspbian/ wheezy/main libproj0 armhf 4.7.0-2 [114 kB]
Fetched 4,460 kB in 30s (148 kB/s)
Selecting previously unselected package libgeos-3.3.3.
(Reading database ... 70370 files and directories currently installed.)
Unpacking libgeos-3.3.3 (from .../libgeos-3.3.3_3.3.3-1.1_armhf.deb) ...
Selecting previously unselected package libgeos-c1.
Unpacking libgeos-c1 (from .../libgeos-c1_3.3.3-1.1_armhf.deb) ...
Selecting previously unselected package proj-data.
Unpacking proj-data (from .../proj-data_4.7.0-2_armhf.deb) ...
Selecting previously unselected package libproj0.
Unpacking libproj0 (from .../libproj0_4.7.0-2_armhf.deb) ...
Selecting previously unselected package postgresql-9.1-postgis.
Unpacking postgresql-9.1-postgis (from .../postgresql-9.1-postgis_1.5.3-2+b1_armhf.deb) ...
Setting up libgeos-3.3.3 (3.3.3-1.1) ...
Setting up libgeos-c1 (3.3.3-1.1) ...
Setting up proj-data (4.7.0-2) ...
Setting up libproj0 (4.7.0-2) ...
Setting up postgresql-9.1-postgis (1.5.3-2+b1) ...
pi@raspberrypi ~ $

Install Java 7 on Raspberry Pi

pi@raspberrypi ~ $ sudo apt-get update && sudo apt-get install oracle-java7-jdk
Hit http://repository.wolfram.com stable Release.gpg
Hit http://repository.wolfram.com stable Release
Hit http://raspberrypi.collabora.com wheezy Release.gpg
Hit http://repository.wolfram.com stable/non-free armhf Packages
Hit http://raspberrypi.collabora.com wheezy Release
Hit http://raspberrypi.collabora.com wheezy/rpi armhf Packages
Get:1 http://mirrordirector.raspbian.org wheezy Release.gpg [490 B]
Ign http://repository.wolfram.com stable/non-free Translation-en_GB
Get:2 http://mirrordirector.raspbian.org wheezy Release [14.4 kB]
Hit http://archive.raspberrypi.org wheezy Release.gpg
Ign http://repository.wolfram.com stable/non-free Translation-en
Hit http://archive.raspberrypi.org wheezy Release
Get:3 http://mirrordirector.raspbian.org wheezy/main armhf Packages [7,426 kB]
Hit http://archive.raspberrypi.org wheezy/main armhf Packages
Ign http://raspberrypi.collabora.com wheezy/rpi Translation-en_GB
Ign http://raspberrypi.collabora.com wheezy/rpi Translation-en
Ign http://archive.raspberrypi.org wheezy/main Translation-en_GB
Ign http://archive.raspberrypi.org wheezy/main Translation-en
Hit http://mirrordirector.raspbian.org wheezy/contrib armhf Packages
Hit http://mirrordirector.raspbian.org wheezy/non-free armhf Packages
Hit http://mirrordirector.raspbian.org wheezy/rpi armhf Packages
Ign http://mirrordirector.raspbian.org wheezy/contrib Translation-en_GB
Ign http://mirrordirector.raspbian.org wheezy/contrib Translation-en
Ign http://mirrordirector.raspbian.org wheezy/main Translation-en_GB
Ign http://mirrordirector.raspbian.org wheezy/main Translation-en
Ign http://mirrordirector.raspbian.org wheezy/non-free Translation-en_GB
Ign http://mirrordirector.raspbian.org wheezy/non-free Translation-en
Ign http://mirrordirector.raspbian.org wheezy/rpi Translation-en_GB
Ign http://mirrordirector.raspbian.org wheezy/rpi Translation-en
Fetched 7,441 kB in 1min 35s (77.9 kB/s)
Reading package lists... Done
Reading package lists... Done
Building dependency tree
Reading state information... Done
oracle-java7-jdk is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 44 not upgraded.
pi@raspberrypi ~ $

Verify Version of Java

uki@raspberrypi ~ $ java -version
java version "1.7.0_40"
Java(TM) SE Runtime Environment (build 1.7.0_40-b43)
Java HotSpot(TM) Client VM (build 24.0-b56, mixed mode)
uki@raspberrypi ~ $

Switching to postgres user

uki@raspberrypi ~ $ su postgres
Password:
postgres@raspberrypi:/home/uki$

Creating new PostGre Database

postgres@raspberrypi:/home/uki$ createdb -E UTF8 polygons
postgres@raspberrypi:/home/uki$

Initialize PostGIS

postgres@raspberrypi:/home/uki$ psql -d polygons -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
SET
BEGIN
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
... many more

Initialize Spatial functions

postgres@raspberrypi:/home/uki$ psql -d polygons -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
... many more

Changing database

postgres@raspberrypi:/home/uki$ psql -d polygons
psql (9.1.12)
Type "help" for help.

polygons=#

Check if postgis is OK

polygons=# SELECT PostGIS_full_version();
postgis_full_version
-------------------------------------------------------------------------------------------------------
POSTGIS="1.5.3" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.8.0" USE_STATS
(1 row)

polygons=#

Change database user password

polygons=# ALTER USER postgres PASSWORD 'XYZ_secure';
ALTER ROLE
polygons=#

Secure Copy your Java Jar to Pi

@ libs $ scp XYZ-1.0.jar pi@7x.xy.yy.x:/home/pi
pi@7x.xy.yy.x's password:
XYZ-1.0.jar 100%
9601KB 384.1KB/s 00:25
@ libs $

Run your Java program on Raspberry Pi

pi@raspberrypi ~ $ java -jar XYZ-1.0.jar -params
Connected to jdbc:postgresql://localhost/polygons
DROP TABLE IF EXISTS gps_points [DONE]

CREATE TABLE gps_points (

... up to you what you want to do

As an Amazon Associate I earn from qualifying purchases.

Raspberry Pi, PostGre SQL GEO fencing and Java

Logging Remotely to Raspberry

You have to have permissions to install stuff

$ sudo apt-get install postgis
[sudo] password for uki:
uki is not in the sudoers file. This incident will be reported.
uki@raspberrypi ~ $ su pi
Password:

Install PostGIS for GEO fencing

Change password for linux user for postgre

pi@raspberrypi ~ $ sudo passwd postgres
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
pi@raspberrypi ~ $

Install postgis plugin for PostGre

Install Java 7 on Raspberry Pi

Verify Version of Java

uki@raspberrypi ~ $ java -version
java version "1.7.0_40"
Java(TM) SE Runtime Environment (build 1.7.0_40-b43)
Java HotSpot(TM) Client VM (build 24.0-b56, mixed mode)
uki@raspberrypi ~ $

Switching to postgres user

uki@raspberrypi ~ $ su postgres
Password:
postgres@raspberrypi:/home/uki$

Creating new PostGre Database

postgres@raspberrypi:/home/uki$ createdb -E UTF8 polygons
postgres@raspberrypi:/home/uki$

Initialize PostGIS

Initialize Spatial functions

Changing database

postgres@raspberrypi:/home/uki$ psql -d polygons
psql (9.1.12)
Type "help" for help.

polygons=#

Check if postgis is OK

Change database user password

polygons=# ALTER USER postgres PASSWORD 'XYZ_secure';
ALTER ROLE
polygons=#

Secure Copy your Java Jar to Pi

@ libs $ scp XYZ-1.0.jar pi@7x.xy.yy.x:/home/pi
pi@7x.xy.yy.x's password:
XYZ-1.0.jar 100%
9601KB 384.1KB/s 00:25
@ libs $

Run your Java program on Raspberry Pi

pi@raspberrypi ~ $ java -jar XYZ-1.0.jar -params
Connected to jdbc:postgresql://localhost/polygons
DROP TABLE IF EXISTS gps_points [DONE]

CREATE TABLE gps_points (

... up to you what you want to do

As an Amazon Associate I earn from qualifying purchases.

PostgreSQL getting started

PostGreSQL site

http://www.postgresql.org/

Download and Run

postgresql-9.3.4-1-osx.app

Installation Directory: /Library/PostgreSQL/9.3
Data Directory: /Library/PostgreSQL/9.3/data
Set superuser password
set port e.g. 5432
set locale e.g en_US.UTF-8
Launch Stack Builder

Stack Builder 3.1.1

Database Drivers
- pgJDBC v9.3-1100-1
Spacial Extensions
- PostGIS 2.1
Select download dir e.g. /Applications/DB/PostGre/Extensions
finish installations

Check Database Size

$ cd /Library/PostgreSQL/9.3

$ ls -alt | grep data

drwx------ 22 postgres daemon 748 Apr 3 14:31 data

4) PostgreSQL client (UI)

pgAdmin3

http://www.postgresql.org/ftp/pgadmin3/release/v1.18.1/osx/

Set PATH to PostgreSQL

$ edit ~/.profile

# PostGreSQL
export POSTGRE_HOME=/Library/PostgreSQL/9.3
export PATH=${PATH}:${POSTGRE_HOME}/bin

Sandbox Directory

create yourself a sandbox directory to play in

$ cd /Applications/DB/PostGre/sandbox

Users

Adding and checking users

@ sandbox $ psql --user=postgres template1 -c '\du'

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------+----

postgres | Superuser, Create role, Create DB, Replication | {}

uki | Password valid until infinity | {}

Create Dump

creating postGreSQL database backup

@ sandbox $ pg_dump -Fp --user=postgres --dbname=gps_points --format=plain --clean --create --file=gps_points.sql

@ sandbox $ ls

gps_points.sql

Terminal (Command Line)

@ 9.3 $ sudo su

Password:

@ 9.3 $ su - postgres

uki:~ postgres$

Check Database Size

SELECT pg_size_pretty(pg_database_size('polygons'))
SELECT pg_size_pretty(pg_relation_size('gps_points'))

As an Amazon Associate I earn from qualifying purchases.

PostgreSQL getting started

PostGreSQL site

http://www.postgresql.org/

Download and Run

postgresql-9.3.4-1-osx.app

Installation Directory: /Library/PostgreSQL/9.3
Data Directory: /Library/PostgreSQL/9.3/data
Set superuser password
set port e.g. 5432
set locale e.g en_US.UTF-8
Launch Stack Builder

Stack Builder 3.1.1

Database Drivers
- pgJDBC v9.3-1100-1
Spacial Extensions
- PostGIS 2.1
Select download dir e.g. /Applications/DB/PostGre/Extensions
finish installations

Check Database Size

$ cd /Library/PostgreSQL/9.3

$ ls -alt | grep data

drwx------ 22 postgres daemon 748 Apr 3 14:31 data

4) PostgreSQL client (UI)

pgAdmin3

http://www.postgresql.org/ftp/pgadmin3/release/v1.18.1/osx/

Set PATH to PostgreSQL

$ edit ~/.profile

# PostGreSQL
export POSTGRE_HOME=/Library/PostgreSQL/9.3
export PATH=${PATH}:${POSTGRE_HOME}/bin

Sandbox Directory

create yourself a sandbox directory to play in

$ cd /Applications/DB/PostGre/sandbox

Users

Adding and checking users

@ sandbox $ psql --user=postgres template1 -c '\du'

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------+----

postgres | Superuser, Create role, Create DB, Replication | {}

uki | Password valid until infinity | {}

Create Dump

creating postGreSQL database backup

@ sandbox $ pg_dump -Fp --user=postgres --dbname=gps_points --format=plain --clean --create --file=gps_points.sql

@ sandbox $ ls

gps_points.sql

Terminal (Command Line)

@ 9.3 $ sudo su

Password:

@ 9.3 $ su - postgres

uki:~ postgres$

Check Database Size

SELECT pg_size_pretty(pg_database_size('polygons'))
SELECT pg_size_pretty(pg_relation_size('gps_points'))

As an Amazon Associate I earn from qualifying purchases.

My interests

private static String databaseFilePath = "kml.db";

private static String databaseFilePath = "/Users/uki/_REPO/CLC/2015_spring/cit137/UKI_LUCAS/XmlParsing/kml.db";

private static String databaseFilePath = "kml.db";

private static String databaseFilePath = "/Users/uki/_REPO/CLC/2015_spring/cit137/UKI_LUCAS/XmlParsing/kml.db";

Step 1: Create interface

Step 2: Implement the Interface in your specific database wrapper code

Step 3: Implement your generated methods

/** * Deletes given object from the database. * * @param object */ @Override public void delete(Book object) { }

Step 1: Create a new IntelliJ/Android Studio Project

Step 2: The first version of this app will not have any UI, edit strings.xml

<?xml version="1.0" encoding="utf-8"?><resources> <string name="app_name">Book Shelf - SQLite example</string> <string name="hello_world">See logcat output!</string> <string name="action_settings">Settings</string></resources>

Step 3: Run the app to make sure everything is OK so far

Step 4: Create new package "model" and new Java class "Book"

@Override public String toString() { return "Book: id=" + id + "\n title = " + title + "\n author = " + author + "\n isbn = " + isbn; }

Step 5: Generate getters and Setters methods for Book.java

Step 6: Create new package "database" and new Java Interface "DatabaseCrud"

Step 7: Create Java class "BookSqlHelper"

Step 8 : Add "create" method of BookSqlHelper.java

Step 9: Implement "read" method of BookSqlHelper.java

Step 10: Implement "delete" method(s)

Change to Posgres (database user)

Select correct database

To exit PostgreSQL shell

Select number of Points per Polygon in database

Select polygons for the GEO point

Alternatively you can run query from Linux user prompt

If you testing timing of your query then you have to turn it on

Change to Posgres (database user)

Select correct database

To exit PostgreSQL shell

Select number of Points per Polygon in database

Select polygons for the GEO point

Alternatively you can run query from Linux user prompt

If you testing timing of your query then you have to turn it on

Logging Remotely to Raspberry

You have to have permissions to install stuff

Install PostGIS for GEO fencing

Change password for linux user for postgre

Install postgis plugin for PostGre

Install Java 7 on Raspberry Pi

Verify Version of Java

Switching to postgres user

Creating new PostGre Database

Initialize PostGIS

Initialize Spatial functions

Changing database

Check if postgis is OK

Change database user password

Secure Copy your Java Jar to Pi

Run your Java program on Raspberry Pi

Logging Remotely to Raspberry

You have to have permissions to install stuff

Install PostGIS for GEO fencing

Change password for linux user for postgre

Install postgis plugin for PostGre

Install Java 7 on Raspberry Pi

Verify Version of Java

Switching to postgres user

Creating new PostGre Database

Initialize PostGIS

Initialize Spatial functions

Changing database

Check if postgis is OK

Change database user password

Secure Copy your Java Jar to Pi

Run your Java program on Raspberry Pi

PostGreSQL site

Download and Run

Stack Builder 3.1.1

Check Database Size

Set PATH to PostgreSQL

Sandbox Directory

Users

Create Dump

Terminal (Command Line)

Check Database Size

PostGreSQL site

Download and Run

Stack Builder 3.1.1

/**
* Deletes given object from the database.
*
* @param object
*/
@Override
public void delete(Book object) {

}

<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="app_name">Book Shelf - SQLite example</string>
<string name="hello_world">See logcat output!</string>
<string name="action_settings">Settings</string>
</resources>

@Override
public String toString() {
return "Book: id=" + id
+ "\n title = " + title
+ "\n author = " + author
+ "\n isbn = " + isbn;
}