{Complexity}

The great circle distance formula:
d = acos(sin(x1)*sin(x2)+cos(x1)*cos(x2)*cos(y2-y1)) * r

CREATE TABLE ZCTA (
zcta CHAR(6) NOT NULL
, lat_degrees DECIMAL(9,6) NOT NULL
, long_degrees DECIMAL(9,6) NOT NULL
, PRIMARY KEY(zcta));

ALTER TABLE ZCTA
ADD COLUMN lat_radians DECIMAL(9,6) NOT NULL
, ADD COLUMN long_radians DECIMAL(9,6) NOT NULL;

UPDATE ZCTA
SET lat_radians = lat_degrees * (PI() / 180)
, long_radians = long_degrees * (PI() / 180);

/* calculating the distance between two points */
SELECT
ROUND(ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956, 2) AS “Distance”
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’1001′
AND x2.zcta = ’21236′;

/* zip codes within a given radius */
SELECT
x2.zcta AS zip
, ROUND(ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956, 2) AS “Distance”
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
ORDER BY Distance;

/* StoreLocation table definition */
CREATE TABLE StoreLocation (
store_id INT NOT NULL AUTO_INCREMENT
, address VARCHAR(100) NOT NULL
, city VARCHAR(35) NOT NULL
, state CHAR(2) NOT NULL
, zip VARCHAR(6) NOT NULL
, PRIMARY KEY (store_id)
, KEY (zip));

/* zip codes within a given radius  with StoreLocation table*/
SELECT
address
, city
, state
, zip
FROM StoreLocation
WHERE zip IN (
SELECT x2.zcta
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
);

SELECT
address
, city
, state
, zip
FROM StoreLocation s1
INNER JOIN (
SELECT x2.zcta
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
) AS zips
ON s1.zip = zips.zcta;

SELECT
address
, city
, state
, zip
FROM ZCTA x1, ZCTA x2
INNER JOIN StoreLocation s1
ON x2.zcta = s1.zip
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5;

SELECT
address
, city
, state
, zip
, ROUND(ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956, 2) AS “Distance”
FROM ZCTA x1, ZCTA x2
INNER JOIN StoreLocation s1
ON x2.zcta = s1.zip
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
ORDER BY Distance;

Theta Style:
SELECT i.order_id, p.product_id, p.name, p.desc
FROM CustomerItem i, Product p
WHERE i.product_id = p.product_id
AND i.order_id = 84463;

ANSI Style:
SELECT i.order_id, p.product_id, p.name, p.desc
FROM CustomerItem i
INNER JOIN Product p ON i.product_id = p.product_id
WHERE i.order_id = 84463;

Sometimes, it’s hard to realized whether there is other style of SQL. Obviously there are two different SQL style. theta is older and more obscure but many people still use it.

1. create META-INF/context.xml

<?xml version=”1.0″ encoding=”UTF-8″?>
<Context docBase=”e-sports” path=”/e-sports” debug=”0″ reloadable=”true”
source=”org.eclipse.jst.j2ee.server:e-sports”>
<Logger className=”org.apache.catalina.logger.FileLogger”
prefix=”e-sports_log.” suffix=”.txt” timestamp=”true” />
<Resource name=”jdbc/myoracle”
auth=”Container”
type=”javax.sql.DataSource”
driverClassName=”oracle.jdbc.driver.OracleDriver”
factory=”org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory”
url=”jdbc:oracle:thin:@127.0.0.1:1521:ORA92″
username=”scott”
password=”tiger”
maxActive=”20″
maxIdle=”10″
maxWait=”-1″ />
</Context>

2. test.jsp
<%@ page import=”java.sql.Connection” %>
<%@ page import=”java.sql.ResultSet” %>
<%@ page import=”java.sql.SQLException” %>
<%@ page import=”java.sql.Statement” %>
<%@ page import=”javax.naming.Context” %>
<%@ page import=”javax.naming.InitialContext” %>
<%@ page import=”javax.naming.NamingException” %>
<%@ page import=”javax.sql.DataSource” %>

<%
Context ctx = null;
DataSource source = null;
Connection con = null;

try {
ctx = new InitialContext();
ctx = (Context) ctx.lookup(“java:comp/env”);
source = (DataSource) ctx.lookup(“jdbc/myoracle”);

System.out.println(“DataSource ===========================”+ source);

con = source.getConnection();
System.out.println(“Connection ============================”+ con);
} catch (NamingException ne) {
ne.printStackTrace();
}
%>