Getting to know PostGIS Part II

By On · 3 Comments

So its been over six months since I made my first tutorial post about PostGIS. I now use PostGIS on a regular basis and thought it would be good to update the tutorial with some meat on how to use PostGIS.

Get some data and import it

Grab populated places points

wget http://edcftp.cr.usgs.gov/pub/data/nationalatlas/citiesx020.tar.gz

Grab US counties map:

wget http://edcftp.cr.usgs.gov/pub/data/nationalatlas/countyp020.tar.gz

Lots more data available in the National Atlas

Extract shapefiles from the two archives

tar -xzvf citiesx020.tar.gz
tar -xzvf  countyp020.tar.gz

Ogrinfo the files to have a look at them

ogrinfo citiesx020.shp           
INFO: Open of `citiesx020.shp'
      using driver `ESRI Shapefile' successful.
1: citiesx020 (Point) 

ogrinfo countyp020.shp 
INFO: Open of `countyp020.shp'
      using driver `ESRI Shapefile' successful.
1: countyp020 (Polygon)

Notice that the cities shapefile is a point layer and the county layer is a polygon layer.

Import the two shape files into your PostGIS db

To get started with PostGIS see my first tutorial.

ogr2ogr -f "PostgreSQL" -s_srs EPSG:4269 "PG:dbname=mydbname" countyp020.shp -nln us_counties
ogr2ogr -f "PostgreSQL" -s_srs EPSG:4269 "PG:dbname=mydbname" citiesx020.shp -nln us_cities

Make sure you change mydbname to your database’s name.

  • -f “PostgreSQL” says that our destination “file type” is a PostgreSQL database
  • The -s_srs sets the source spatial reference system for the shapefiles. Shapefiles from the National Atlas do not have a .prj file which defines their projection, so I looked in the .txt file which came with each archive from the National Atlas and found that the they are in NAD83, or EPSG:4269
  • “PG:dbname=mydbname” tells ogr2ogr what your destination is (in this case a PostGIS server). More options are possible in the OGR driver
  • the shapfile name specifies the source
  • -nln specifies a new layer name which will be the name of the table in our database

    • Checking the Import

    psql mydbname
mydbname# \d us_cities
                                   Table "public.us_cities"
    Column    |       Type       |                          Modifiers                          
--------------+------------------+-------------------------------------------------------------
 ogc_fid      | integer          | not null default nextval('us_cities_ogc_fid_seq'::regclass)
 wkb_geometry | geometry         | 
 citiesx020   | double precision | 
 feature      | character(27)    | 
 name         | character(48)    | 
 pop_range    | character(21)    | 
 pop_2000     | numeric(8,0)     | 
 fips55       | character(5)     | 
 county       | character(55)    | 
 fips         | character(5)     | 
 state        | character(2)     | 
 state_fips   | character(2)     | 
 display      | numeric(1,0)     | 
Indexes:
    "us_cities_pk" PRIMARY KEY, btree (ogc_fid)
    "us_cities_geom_idx" gist (wkb_geometry)
Check constraints:
    "enforce_dims_wkb_geometry" CHECK (ndims(wkb_geometry) = 2)
    "enforce_geotype_wkb_geometry" CHECK (geometrytype(wkb_geometry) = 'POINT'::text OR wkb_geometry IS NULL)
    "enforce_srid_wkb_geometry" CHECK (srid(wkb_geometry) = -1)

    You can see that ogr2ogr has imported the shapefile, created a primary key (ogc_fid) and created a spatial index. What you can’t see is that ogr2ogr was also nice enough to add an entry to the geometry_columns table so that postgis tools know that there is a geometry column (wkb_geometry) in this table.

    Looking at the Data

    Lets fire off qgis and see what we have
    city_county

    Next time, querying with spatial predicates….I promise it won’t take six months for the next post.

    Share →