Eric Frohnhoefer
 
Home > Blog > GeoCoding with PostGIS

GeoCoding with PostGIS

Posted by admin on July 19, 2010

This is a demonstration of using PostGIS and US Census Tiger Line data to create a GeoCoding database.

Prerequisites:

Ubuntu 10.04 LTS (info)
PostgreSQL 8.4.4 (info)
PostGIS 1.5.1 (info)
Proj4 4.7.0 (info)
Geos 3.2.2 (info)
GDAL 1.7.2 (info)

Getting Started:

  1. Install Ubuntu 10.04 LTS
  2. Install PostgreSQL 8.4.4 
    sudo apt-get install postgresql
    sudo apt-get install postgresql-client
    sudo apt-get install postgresql-server-dev-8.4
    sudo apt-get install pgadmin3 (Optional GUI)
    sudo apt-get install libpq-java
  3. Build and install Proj4 4.7.0 
    tar -xvf proj-4.7.0.tar.gz
    cd proj-4.7.0/
    ./configure
    make
    sudo make install
  4. Build and install Geos 3.2.2
    bunzip2 geos-3.2.2.tar.bz2
    tar -xvf geos-3.2.2.tar
    cd geos-3.2.2/
    ./configure
    make
    sudo make install

  5. Build and Install PostGIS 1.5.1

    sudo apt-get install libxml2-dev
    tar -xvf postgis-1.5.1.tar.gz
    cd postgis-1.5.1/
    ./configure
    make
    sudo make install
  6. Build and install GDAL 1.7.2
    sudo apt-get install libjpeg62-dev
    sudo apt-get install libjasper-dev
    tar -xvf gdal-1.7.2.tar.gz
    cd gdal-1.7.2
    ./configure
    make
    sudo make install
  7. Ensure that /usr/local/lib is added to /etc/ld.so.conf then run ldconfig

Creating the database:

  1. Create the database and enable pl/pgsql
    sudo -u postgres createdb geodb 
    sudo -u postgres createlang plpgsql geodb
  2. Enable PostGIS extensions
    cd /usr/share/postgresql/8.4/contrib/postgis-1.5/
    sudo -u postgres psql -d geodb -f postgis.sql
    sudo -u postgres psql -d geodb -f spatial_ref_sys.sql
  3. Create a new user
    sudo -u postgres psql postgres
    CREATE USER geouser WITH PASSWORD 'password'
    GRANT ALL PRIVILEGES ON DATABASE geodb TO geouser
  4. Import the data
    sudo -u postgres ogr2ogr -f PostgreSQL PG:dbname=geodb -nln edges tl_2009_51107_edges.shp
    sudo -u postgres ogr2ogr -f PostgreSQL PG:dbname=geodb -nln featnames tl_2009_51059_featnames.dbf

Query the database

  1. Find all streets named 'Fairfax Dr'
    select ST_AsText(wkb_geometry), * from edges where fullname = 'Fairfax Dr' limit 10

  2. Find all streets within 100 meters of Lat: 39.039863 Lon -77.3539911

    SELECT ST_AsText(wkb_geometry), * 
    FROM edges 
    WHERE ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-77.353991 39.039863)'),ST_Transform(wkb_geometry,4326)) < 100
  3. Improved query
    SELECT ST_AsText(wkb_geometry), *
    FROM edges 
    WHERE 
    wkb_geometry && ST_Expand(ST_GeomFromText(ST_Transform('SRID=4326;POINT(-77.353991 39.039863)',4269)),.005)
    and
    ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-77.353991 39.039863)'),ST_Transform(wkb_geometry,4326)) < 10