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