Getting to know postgis part ii

January 30, 2009

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


Grab US counties map:


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.

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)     | 
    "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.

comments powered by Disqus
Getting to know PostGIS Part II - January 30, 2009 - Jonah Duckles