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 bigserial NOT NULL,
  latitude numeric(9,4),
  longitude numeric(9,4),
  "location" geometry,
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(location) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(location) = 'POINT'::text OR location IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(location) = (-1))
)
WITH (
  OIDS=FALSE
);

NOTE the use of the geometry datatype above.

Now, what we want to do is insert lots of data in this table:
The following code inserts 100,000 random records into geoinfo table. We will do a search on this very data later.
The code is same as what we saw in: , only difference being this line:
geomFunc=”st_pointfromtext”;// we are using a PostGIS specific function to insert data in location column.


static String dbUrl = "jdbc:postgresql://localhost:5432/postgis?";
static String dbClass = "org.postgresql.Driver";
static String schemaPrefix="";
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="st_pointfromtext";

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 {
    //get DB Connection from somewhere.
    con = getConnection();

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

    query="SELECT id, latitude, longitude, ST_DISTANCE(location, 'POINT(" + lat + " " + lon + ")') as distance FROM geoinfo WHERE ST_DWithin(location, 'POINT(" + lat + " " + lon + ")', " + radius + ")";
    //IMPORTANT:
    //If : geography suffix is used against POINT in SQL - then the order should be - lon lat and not - lat lon.
    //And the data too should have been inserted as lon lat for geocode location column. Geography suffix uses distances in meters.
    //If geography suffix is not used - then we can simply use - lat lon format.

    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;

    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;
}
}

Thats It!

Advertisement