How to use Geo Spatial with MySQL?

Problem Statement
Find all locations that are within a certain radius of a particular location.

Solution Approach
We will demonstrate this with pseudo-code.

Lets create a table in MySQL that will store geo information:

create table geoinfo (
id bigint(20) NOT NULL AUTO_INCREMENT,
latitude DECIMAL(9,4) NULL ,
longitude DECIMAL(9,4) NULL ,
location POINT NOT NULL ,
PRIMARY KEY (ID) )
ENGINE = MyISAM DEFAULT CHARSET=utf8

-- Note the use of POINT data type against the location column above.
-- Spatial Indexes cannot be created on InnoDB tables.
create spatial index sp_index ON geocode(location);

Now, what we want to do is insert lots of data in this table:

static String dbUrl = "jdbc:mysql://localhost:3306/test?";
static String dbClass = "com.mysql.jdbc.Driver";
static String schemaPrefix="test."
static String DB_USERNAME="whatever";
static String DB_PASSWD="whatever";

Class.forName(dbClass);
Connection con = DriverManager.getConnection(dbUrl, DB_USERNAME, DB_PASSWD);
Statement stmt = con.createStatement();

String query1;
long startTime = Calendar.getInstance().getTimeInMillis();
//Randomize the lat, long inputs
Random rand = new Random();
String geomFunc="";

geomFunc="GeomFromText";

for (int i = 0; i < 100000; i++) {
double lat = rand.nextDouble() * 180 - 90;
BigDecimal bd1 = new BigDecimal(lat).setScale(2, RoundingMode.HALF_EVEN);
lat = bd1.doubleValue();
double lon = rand.nextDouble() * 360 - 180;
BigDecimal bd = new BigDecimal(lon).setScale(2, RoundingMode.HALF_EVEN);
lon = bd.doubleValue();
query1 = "INSERT INTO " + schemaPrefix + "geoinfo(latitude, longitude, location) VALUES (" + lat + "," + lon + ", " + geomFunc + "('POINT(" + lat + " " + lon + ")') )";
System.out.println(query1);
int res = stmt.executeUpdate(query1);
// System.out.println("res: " + res);
}

long timeTaken = Calendar.getInstance().getTimeInMillis() - startTime;
System.out.println("Total Time Taken to insert data : " + timeTaken + " milliseconds");

Now we want to do a geo spatial search on geoinfo table
i.e find all rows from the geoinfo table whose location is within a certain miles(radius) from a particular location(lat,lon):

We will write a findGeoLocations function that will print all records that meet this criteria.

public void findGeoLocations(double lat, double lon, double radiusInMiles) {

Connection con = null;

try {
con = Constants.getConnection();

//Statement stmt = con.createStatement();
String query="";
double radius=radiusInMiles/69;

double x1 = lat - radius; double y1 = lon - radius;
double x2 = lat + radius; double y2 = lon - radius;
double x3 = lat + radius; double y3 = lon + radius;
double x4 = lat - radius; double y4 = lon + radius;
String polygonStr = x1 + " " + y1 + "," + x2 + " " + y2 + "," + x3 + " " + y3 + "," + x4 + " " + y4 + "," + x1 + " " + y1;
String pointStr = lat + " " + lon;

query = "SELECT id, latitude, longitude, AsText(location),";
query = query + " SQRT(POW( ABS( X(location) - " + lat + "), 2) +";
query = query + " POW( ABS(Y(location) - " + lon + "), 2 )) AS distance";
query = query + " FROM geoinfo ";
query = query + " WHERE Intersects( location, GeomFromText('POLYGON((" + polygonStr + "))'))";
query = query + " AND (SQRT(POW( ABS( X(location) - X(GeomFromText('POINT(" + pointStr + ")'))), 2) +";
query = query + " POW( ABS(Y(location) - Y(GeomFromText('POINT(" + pointStr + ")'))), 2 ))) < " + radius ;
query = query + " order by distance";

System.out.println("Query to use: " + query);

PreparedStatement pStmt = con.prepareStatement(query);
ResultSet rs;

long startTime = Calendar.getInstance().getTimeInMillis();
rs = pStmt.executeQuery();
long timeTaken = Calendar.getInstance().getTimeInMillis() - startTime;

System.out.println("Time Taken: " + timeTaken + " milliseconds");

int rows = 0;
while (rs.next()) {
rows++;
/*
System.out.println ("RS: Id: " + rs.getInt("id") +
" Lat: " + rs.getDouble("latitude") + " Lon: " +
rs.getDouble("longitude") + " Dist: " +
rs.getDouble("distance"));
*/
}
System.out.println("Total Rows: " + rows);

} // end try

catch (SQLException e) {
e.printStackTrace();
} finally {
con=null;
}
}

So Thats It!

Advertisements

4 responses

  1. This saved several days of research. Very well done. Thank you.

  2. awesome! exactly what i needed! thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: