Athena/Pestro Queries

Lambda Expression

Powerful Prestro Lamda expressions are also supported in Athena.

Transform : transform(array<T>, function<T, U>) → ARRAY<U>

Returns an array that applies function to each element of array.

Example – Process array and extract only name from structure of array like Array[ Row(name ,language ) ]

Input array -> [{name=Londt Park – Bowls, language=ENG}, {name=Londt Park – Tennis, language=ENG}]

Expected result array -> [Londt Park – Bowls,Londt Park – Tennis]

SELECT transform (names, x->x.name)
FROM place
where cardinality (names ) >1
-- Result - [Londt Park - Bowls, Londt Park - Tennis]

Athena / Pestro Geospatial Queries

Geospatial Athena queries

Query : Find the distance between two geo-location (points) in meters.

to_spherical_geography() is used to get distance in meter. Function is now supported in Athena in “Preview_functionality_release”. To use preview functionality release create a workspace with name “AmazonAthenaPreviewFunctionality”.

select ST_Distance(
  to_spherical_geography(ST_Point(-71.088211, 42.661712)), 
  to_spherical_geography(ST_Point(-71.088211, 42.670712))
) ;                                                                                  

---

Result – > 1000.755910597454 (meters)

---

cast() – String values to decimal to use spatial functions

SELECT cast (
ST_Distance(
   to_spherical_geography( 
        ST_Point( cast('-71.0882110' as decimal(10,7)),
                         cast('42.6617120' as decimal(10,7)))),
   to_spherical_geography(
       ST_Point(-71.0882110, 42.6707120))) as int);
          

---

Result -> 1001 (meters)

Geospatial with athena

Tweet

• WordPress
• Twitter
• LinkedIn

Protected: Image annotation

This content is password protected. To view it please enter your password below:

Password:

Restart tomcat using java code , check for session count ( no one using application ).

Refer to previous Blog how to get session count for application .: https://techspread.wordpress.com/2014/08/12/get-application-session-count-on-tomcat-using-java-code/

Java code to get session count for application and restart tomcat if session count is  zero for application.

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import sun.misc.BASE64Encoder;
public class RestartTomcat {
    public static void main(final String[] args) {
        try {
            final URL url = new URL("http://localhost/manager/text/sessions?path=/fcm");
            final BASE64Encoder e = new BASE64Encoder();
            final String encoding = e.encode("admin:admin".getBytes());
            final HttpURLConnection connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("GET");
            connection.setDoOutput(true);
            connection.setRequestProperty("Authorization", "Basic " + encoding);
            final InputStream content = connection.getInputStream();
            final BufferedReader in = new BufferedReader(new InputStreamReader(content));
            String line;
            String lastLine = "";
            System.out.println("Tomcat response: ");
            while ((line = in .readLine()) != null) {
                lastLine = line;
                System.out.println(line);
            }
            final String[] parts = lastLine.split("minutes: ");
            if (parts.length >= 2) {
                System.out.println("\nSessions count is " + parts[1]);
            } else {
                System.out.println("zero session");
                final String serviceName = "gusvc"; // Insert your service name 
                try {
                    stopStartService(serviceName, "stop");
                    stopStartService(serviceName, "start");
                } catch (final IOException ioe) {
                    ioe.printStackTrace();
                } catch (final InterruptedException ire) {
                    ire.printStackTrace();
                }
            }
        } catch (final Exception e) {
            e.printStackTrace();
        }
    }
    public static boolean stopStartService(final String serviceName, final String command) throws IOException, InterruptedException {
        final String executeCmd = "cmd /c net " + command + " \"" + serviceName + "\"";
        final Process runtimeProcess = Runtime.getRuntime().exec(executeCmd);
        final int processComplete = runtimeProcess.waitFor();
        System.out.println("processComplete: " + processComplete);
        System.out.print(" Service " + serviceName + " " + command + " ");
        if (processComplete == 1) {
            // if values equal 1 process failed
            System.out.println("failed");
        } else if (processComplete == 0) {
            System.out.println("Success");
            return true;
        } else if (processComplete == 2) {
            System.out.println("Error");
        }
        return false;
    }
}

---

output : Tomcat response: OK – Session information for application at context path /fcm Default maximum session inactive interval 30 minutes zero session processComplete: 2  Service  gusvc stop Error processComplete: 0  Service  gusvc start Success

---

Get application session count on Tomcat using java code

Apache Tomcat have different types of Users Role.

Role names in the web.xml file of the Manager web application. The available roles are:
manager-gui — Access to the HTML interface.
manager-status — Access to the “Server Status” page only.
manager-script — Access to the tools-friendly plain text interface .
manager-jmx — Access to JMX proxy interface and to the “Server Status” page.

To manage application like application reload , stop or start we need user with role manage-script.
Give role manage-script to user in tomcat-users.xml
e.g

<role rolename=”tomcat”/>
<role rolename=”admin-script”/>
<role rolename=”manager-gui”/>
<role rolename=”manager-script”/>
<user name=”admin” password=”admin” roles=”manager-gui,admin-script,tomcat,manager-script” />

Simple java code to get connection count of your application using java code.

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import sun.misc.BASE64Encoder;

public class RestartTomcat {

    public static void main(final String[] args) {
        try {
            final URL url = new URL("http://localhost/manager/text/sessions?path=/manager");
            final BASE64Encoder e = new BASE64Encoder();
            final String encoding = e.encode("admin:admin".getBytes());

            final HttpURLConnection connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("GET");
            connection.setDoOutput(true);
            connection.setRequestProperty("Authorization", "Basic " + encoding);
            final InputStream content = connection.getInputStream();
            final BufferedReader in = new BufferedReader(new InputStreamReader(content));

            String line;
            String lastLine = "";
            System.out.println("Tomcat response: ");
            while ((line = in .readLine()) != null) {
                lastLine = line;
                System.out.println(line);
            }
            final String[] parts = lastLine.split("minutes: ");
            if (parts.length >= 2) {
                System.out.println("\nSessions count is " + parts[1]);

            } else {
                System.out.println("zero session");
            }
        } catch (final Exception e) {
            e.printStackTrace();
        }

           
    }
}

Output will be like this :

Tomcat response:
OK – Session information for application at context path /manager
Default maximum session inactive interval 30 minutes
1 – <2 minutes: 2 sessions

Sessions count is 2 sessions

Write / Read Geotag JPEGs (EXIF data) in Android (gps)

Simple android code to write / read location information to image

First we need to convert Location information from Degrees to DMS unit.  [ code is provided at bottom ]

Android provide ExifInterface class , This is a class for reading and writing Exif tags in a JPEG file.

Write Location to Image

• Using class [  void setAttribute(String tag, String value) ] method we can set Exif TAG a value.

e.g  Import TAGS are : TAG_GPS_LATITUDE,TAG_GPS_LATITUDE_REF,TAG_GPS_LONGITUDE,     TAG_GPS_LONGITUDE_REF,TAG_DATETIME

• Use [void saveAttributes() ]  method to save the tag data into the JPEG file.

Read Location from image

• Use [String getAttribute(String tag)] to read TAG from Image.
• To Fetch location information from image use getLatLong(float[] output) Stores the latitude and longitude value in a float array.




import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;import android.annotation.SuppressLint;
import android.location.Location;
import android.media.ExifInterface;

@SuppressLint("SimpleDateFormat")
public class GeoTagImage {

/**
*
* Write Location information to image.
* @param imagePath : image absolute path
* @return : location information
*/
public void MarkGeoTagImage(String imagePath,Location location)
{
try {
ExifInterface exif = new ExifInterface(imagePath);
exif.setAttribute(ExifInterface.TAG_GPS_LATITUDE, GPS.convert(location.getLatitude()));
exif.setAttribute(ExifInterface.TAG_GPS_LATITUDE_REF, GPS.latitudeRef(location.getLatitude()));
exif.setAttribute(ExifInterface.TAG_GPS_LONGITUDE, GPS.convert(location.getLongitude()));
exif.setAttribute(ExifInterface.TAG_GPS_LONGITUDE_REF, GPS.longitudeRef(location.getLongitude()));
SimpleDateFormat fmt_Exif = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");
exif.setAttribute(ExifInterface.TAG_DATETIME,fmt_Exif.format(new Date(location.getTime())));
exif.saveAttributes();
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* Read location information from image.
* @param imagePath : image absolute path
* @return : loation information
*/
public Location readGeoTagImage(String imagePath)
{
Location loc = new Location("");
try {
ExifInterface exif = new ExifInterface(imagePath);
float [] latlong = new float[2] ;
if(exif.getLatLong(latlong)){
loc.setLatitude(latlong[0]);
loc.setLongitude(latlong[1]);
}
String date = exif.getAttribute(ExifInterface.TAG_DATETIME);
SimpleDateFormat fmt_Exif = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");
loc.setTime(fmt_Exif.parse(date).getTime());

} catch (IOException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}
return loc;
}
}


//Code to convert  Degrees to DMS unit

//Code to convert  <strong>Degrees to DMS unit</strong>

class GPS {
private static StringBuilder sb = new StringBuilder(20);
/**
* returns ref for latitude which is S or N.
*
* @param latitude
* @return S or N
*/
public static String latitudeRef(final double latitude) {
return latitude < 0.0d ? "S" : "N";
}

/**
* returns ref for latitude which is S or N.
*
* @param latitude
* @return S or N
*/
public static String longitudeRef(final double longitude) {
return longitude < 0.0d ? "W" : "E";
}
/**
* convert latitude into DMS (degree minute second) format. For instance<br/>
* -79.948862 becomes<br/>
* 79/1,56/1,55903/1000<br/>
* It works for latitude and longitude<br/>
*
* @param latitude could be longitude.
* @return
*/
public static final String convert(double latitude) {
latitude = Math.abs(latitude);
final int degree = (int)latitude;
latitude *= 60;
latitude -= degree * 60.0d;
final int minute = (int)latitude;
latitude *= 60;
latitude -= minute * 60.0d;
final int second = (int)(latitude * 1000.0d);

sb.setLength(0);
sb.append(degree);
sb.append("/1,");
sb.append(minute);
sb.append("/1,");
sb.append(second);
sb.append("/1000,");
return sb.toString();
}
}

Shell scrip to kill Old ArcSDE connection

Goal : The script employs the ‘sdemon’ command to retrieve comprehensive connection details, subsequently comparing the current date with the connection’s creation date. If the connection has been active for more than 24 hours, it becomes eligible for termination.

Script : killconnection.sh

sdemon -o info -I users| tail -n +6 | head -n -1 > temp.csv
filename=temp.csv;TODAY=$(date +%s);
date=$(date);
echo “Killing old connections date : $date”;
while read -r line;
 do
 # echo “$line”;
  d=$(echo $line | awk ‘{print $6 ,$7 ,$8 ,$9 ,$10, $11}’);

  #echo “d: $d”;
  dt=$(date -d “$d” +%s) ;
  #echo “dt: $dt”;

 # echo “diff : $(( $(( TODAY – dt ))/86400))”;
  dtdiff=$(($(( TODAY – dt ))/86400));

  pid=$(echo $line | awk ‘{print $2}’);
  if [ "$dtdiff" -gt 0 ]; then
    echo “killing old date $d connection : $pid”
    kill -9 $pid;
   else
    echo “same date : $d pid : $pid”;
   fi
 done < $filename

---

Compare Two Excel sheets on column and write unique result in other worsheet

problems is too compare two excel sheets based on column basic and write unique values of sheet 2 in result sheet .

Sheet 1	Sheet 2	Sheet 3 /result
A	a	A
a	p	p
b	q	q
c	a	r
d	b	q
e	r	33
f	q	44
1	c
2	2
3	33
	3
	44

Sample code is here

Option Explicit

Sub Compare()
    
    Dim Row1Crnt    As Long
    
    Dim Row2Crnt    As Long
    
    Dim Row3Crnt    As Long
    
    Dim Row1Last    As Long
    
    Dim Row2Last    As Long
    
    Dim ValueSheet1
    
    Dim ValueSheet2
    
    Dim duplicate   As Boolean
    
    Dim maxColmn    As Long
    
    Dim i
    
    maxColmn = 10        ' number of column to compare
    
    For i = 1 To maxColmn
        
        With Sheets("Sheet1")
            
            Row1Last = .Cells(Rows.Count, i).End(xlUp).Row
            
        End With
        
        With Sheets("Sheet2")
            
            Row2Last = .Cells(Rows.Count, i).End(xlUp).Row
            
        End With
        
        Row1Crnt = 2
        
        Row2Crnt = 2
        
        Row3Crnt = 2
        
        maxColmn = 10
        
        Do While Row2Crnt <= Row2Last
            
            duplicate = FALSE
            
            Row1Crnt = 2
            
            With Sheets("Sheet2")
                
                ValueSheet2 = .Cells(Row2Crnt, i).Value
                
            End With
            
            Do While Row1Crnt <= Row1Last
                
                With Sheets("Sheet1")
                    
                    ValueSheet1 = .Cells(Row1Crnt, i).Value
                    
                End With
                
                If ValueSheet1 = ValueSheet2 Then
                    
                    duplicate = TRUE
                    
                    Exit Do
                    
                End If
                
                Row1Crnt = Row1Crnt + 1
                
            Loop
            
            If duplicate = FALSE Then
                
                With Sheets("Sheet3")
                    
                    .Cells(Row3Crnt, i).Value = ValueSheet2
                    
                    Row3Crnt = Row3Crnt + 1
                    
                End With
                
            End If
            
            Row2Crnt = Row2Crnt + 1
            
        Loop
        
    Next
    
End Sub

export excel to csv in unicode (utf-8) using macro ( vba )

• Use ADODB.Stream to write content to csv.
• Specify character set to “UTF-8” for Unicode character support in exported csv.
• macro code .

Public Sub WriteCSV()
    Set wkb = ActiveSheet
    Dim fileName    As String
    Dim MaxCols     As Integer
    fileName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
    
    If fileName = "False" Then
        End
    End If
    
    On Error GoTo eh
    Const adTypeText = 2
    Const adSaveCreateOverWrite = 2
    
    Dim BinaryStream
    Set BinaryStream = CreateObject("ADODB.Stream")
    BinaryStream.Charset = "UTF-8"
    BinaryStream.Type = adTypeText
    BinaryStream.Open
    
    For r = 1 To 10 ' define the max count 
        s = ""
        c = 1
        While Not IsEmpty(wkb.Cells(r, c).Value)
            s = s & wkb.Cells(r, c).Value & ","
            c = c + 1
        Wend
        BinaryStream.WriteText s, 1
    Next r
    
    BinaryStream.SaveToFile fileName, adSaveCreateOverWrite
    BinaryStream.Close
    
    MsgBox "CSV generated successfully"
    
    eh:
    
End Sub

pymager – image storage.

pymager – image storage  :  python image storage and resizing service accessible through a RESTful interface.

Find demo application developed using dojo , php and pymager as image store .
Demo

Pymager supports all databases supported by “SQLAlchemy”(Hibernate like framework for python).
Access server using RESTful interface.