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

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

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'))

Java 8 is available

JDK™ 8 Early Access Releases 8u20 Build b05 

https://jdk8.java.net/download.html

My Mac crashes Wireless Network every time

I work remotely most of the time, but when coming to the office the wireless network would crash every time. I thought it was bad router, but my co-workers started to notice it happens only when I connect. The network would slow down to a craw and crash.

I was eventually BANNED FROM NETWORK! :) 

I though I had some apps like Dropbox hugging the network, but it did not seem like it.

I have read that connecting to external DNS, not your Internet Provider's DNS, servers might speed up the lookup.

I got this list from support.hidemyass.com

• OpenDNS: 208.67.222.222 + 208.67.220.220    
• GoogleDNS: 8.8.4.4 + 8.8.8.8
• ComodoDNS: 156.154.70.22 + 156.154.71.22    
• UltraDNS: 156.154.70.1 + 156.154.71.1    
• NortonDNS: 198.153.192.1 + 198.153.194.1

To do so I got to:

Apple > System Preferences.. > Network > Wi-Fi (Connected) > Advanced .. > DNS

Add caption

Android Keystore and certificates for Facebook and Google Map APIs

In the Terminal:

1) change directory to place where you want to save your keystore

@ Android_Keystore $ which keytool

/usr/bin/keytool
   

2) change command to something like this, execute it

keytool -genkey -v -keystore MyCompanyName-release-key.keystore -alias MyApp_name -keyalg RSA -keysize 2048 -validity 10000

3) you will be asked for two sets of passwords, if you have multiple developer groups working on different apps then provide different. If you are single developer for single app then you can keep them same.

The process will look like this:

What is your first and last name?

  [Uki]:  Uki Lucas

What is the name of your organizational unit?

  [CyberWalkAbout]:  CyberWalkAbout

What is the name of your organization?

  [CyberWalkAbout.com]:  CyberWalkAbout.com

What is the name of your City or Locality?

  [Chicago, IL]:  Chicago

What is the name of your State or Province?

  [IL]:  IL

What is the two-letter country code for this unit?

  [US]:  US

Is CN=Uki Lucas, OU=CyberWalkAbout, O=CyberWalkAbout.com, L=Chicago, ST=IL, C=US correct?

  [no]:  yes

Generating 2,048 bit RSA key pair and self-signed certificate (SHA1withRSA) with a validity of 10,000 days

 for: CN=Uki Lucas, OU=CyberWalkAbout, O=CyberWalkAbout.com, L=Chicago, ST=IL, C=US

Enter key password for <CyberFit_alias>

 (RETURN if same as keystore password):  

Re-enter new password: 


[Storing CyberFit-release-key.keystore]



---------------



You will need this for Facebook:


@ Android_Keystore $ keytool -exportcert -alias CyberFit_alias -keystore CyberFit-release-key.keystore | openssl sha1 -binary | openssl base64
Enter keystore password:  




---------------

You will need this for Google APIs (maps)



@ Android_Keystore $ keytool -exportcert -alias CyberFit_alias -keystore CyberFit-release-key.keystore -list -v
Enter keystore password:  









   

Writing Java Mac OS X apps with SWT

I have been playing with idea of Mac desktop apps, I don't have a tutorial yet, but here are few links:

SWT: The Standard Widget Toolkit

http://www.eclipse.org/swt/

Deploying SWT Applications on Mac OS X
http://www.eclipse.org/swt/macosx/


Mac .app 
The .app is just a directory with a specific structure and a few special files.
AppName.app/Contents/Resources/Java/AppName.jar.

Ant tool on Mac Maverick 10.9.2

Apple as usually is playing a game with developers:

$ ant clean

-bash: ant: command not found

$ xcode-select --install

xcode-select: note: install requested for command line developer tools

- Agree

- Finding Software

- Server not available 

Another way:

$ which brew

- nothing

$ ruby -e "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

$ brew install ant

==> Downloading http://www.apache.org/dyn/closer.cgi?path=ant/binaries/apache-ant-1.9.3-bin.tar.gz

Error: Failed to download resource "ant"

Couldn't determine mirror, try again later.

Trying another approach:

@ ~ $ brew install homebrew/dupes/ant

Cloning into '/usr/local/Library/Taps/homebrew-dupes'...

error: RPC failed; result=7, HTTP code = 0

fatal: The remote end hung up unexpectedly

@ ~ $ ant

-bash: ant: command not found

Another approach:
got to http://ant.apache.org/bindownload.cgi
for half a day I was getting server error, but finally in the evening I was able to download:

/usr/local/apache-ant-1.9.3-bin.zip

upzip it

$ cd /usr/local/apache-ant-1.9.3/bin

$ ./ant

Buildfile: build.xml does not exist!

WORKS!

$ edit ~/.profile

Add this to your .profile file

# ANT

export ANT_HOME=/usr/local/apache-ant-1.9.3

export PATH=${PATH}:${ANT_HOME}/bin

Save and Restart terminal 

Now ant command works from any directory.

ADB via USB on Samsung Galaxy Note 12.1 running KitKat

Many of you already know that, but since it is easy to forget here is a reminder how to turn ON the

"{}Developer options"

on devices running Android 4.4.x KitKat

Go to: Settings > General (tab) > About device

tap few times on "Build number" until the "{}Developer options" show in the left menu.

In "{}Developer options" 

- select USB debugging

I also often keep these 2 options selected when I share the device screen in demonstrations:

- Show touches

- Show pointer location

You can see the infinite sign I drew with my finger

For the screen sharing and capture I use command line software called "droid@screen"

Screenshot with Android Cyanogenmod

I started to experiment  with my old Samsung Galaxy Tab 10.1 (Google IO edition) running CyanogenMod Android 4.2.2 by using it exclusively as a replacement for a laptop.

I have been creating tutorials, so I have a need to take a lot of screenshots, since it is tricky, or rather not intuitive, I thought I need to share the shortcut:

Hold Power + Volume Down (side closer to Power) for 1 second.

The screenshot on this tablet is saved under, again not very intuitive:

/storage/emulated/0/Pictures/Screenshots

I wish I kneew to to change that location to save to Google Drive, if you know please post a comment.