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.
-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
Next time, querying with spatial predicates….I promise it won’t take six months for the next post.comments powered by Disqus