Posts tagged ‘postgres’

PostgreSQL geo locations with PostGIS

I been playing with some geo location data. Naturally when working with the data one needs to save it to a database for later retrieval and selection. If you happen to be using Postgres your in luck. You can add the PostGIS extension that will make your database OpenGIS compatible.
The most important thing is to have geo location aware database when dealing with geo data. That means that you can select objects using proximity right out of the database. It makes easy to figure out how far away different locations are, what are the nearest locations etc. If you don’t have geo location aware database you would have to figure out the proximity in code. Of course that would mean a lot of calculations and CPU cycles.

How I set it up on my Ubuntu / Mint linux box. First install PostGIS on the box then you want to add it to your database as below.

createlang plpgsql yourdatabase
cd /usr/share/postgresql/8.4/contrib/postgis-1.5
psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f spatial_ref_sys.sql

Then make sure PostGIS has been added to the database.

SELECT PostGIS_full_version()
1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Couple examples of usage.

To see how far away from my place ( Boulder ) some locations in the database are.
SELECT Distance(geom, SetSRID(MakePoint( 40.0150, –105.2705 ),4326)) FROM location_geo;
Then get the five closest ones to my place
SELECT * FROM location_geo ORDER BY distance(geom, SetSRID(MakePoint( 40.0150, –105.2705 ),4326)) LIMIT 5;

You can even set boundaries, use polygons etc.

Java Postgres connection sample

I had to setup Postgres on my linux box, configure and make sure it was working. Since that was the case I started the install, then wrote a very simple Java program to connect and retrieve some data from one of the database tables. The install is simple and pretty easy to google. Once your Postrgres database is up and running you should be able to connect to it. You will need a java Postgres database driver, the one I downloaded was the postgresql-9.1-901.jdbc4.jar then add the jar to your project. And finally to make sure your database is accessible from a program you can use the sample below.

//
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

// Simple test class to prove connection to Postgres database
public class Main
{

        public Main()
                { super(); }

        public static void main(String[] args)
        {
       
                System.out.println(” Postgres Test”);
                Connection myConn = null;
               
                try
                {
                        // The driver
                        Class.forName(“org.postgresql.Driver”);
                        System.out.println(“PostgreSQL JDBC Driver Registered!”);
               
                        myConn = DriverManager.getConnection( “jdbc:postgresql://127.0.0.1:5432/testDb”,
                                        “testUser”, “myPassword” );

                        // Connected ?
                        if (myConn != null)
                                System.out.println(“Successfully connected to Postgres Database”);
   
                        // Get data from the database
                Statement stGetCount = myConn.createStatement();
            ResultSet rs = stGetCount.executeQuery(“SELECT * from  cars”);
           
            while( rs.next() )
            {
                System.out.println(“result=” + rs.getString(1) + “,” + rs.getString(2) + “,” + rs.getString(3) );
            }
        }
        catch( Exception ex )
        {
            ex.printStackTrace();
        }
        finally
        {
                try{myConn.close();}catch(Exception IDontCare ){}
        }

        }
       
}  // EOC