Shingo's Blog

Import a shapefile into PostGIS

Import a shapefile into PostGIS

A part of my PostGIS project

Shingo Obata's photo
Shingo Obata
·

3 min read

In this post, I present the procedure to import a US state boundary shapefile into PostGIS. Works I am going to do are

  • Import a shapefile to the staging table.
  • Import shapefile of the Fortune 500 headquarters location.
  • Count the number of the companies by state.

Original data

Original data is tiger state boundary data in 2018. Visually check it in QGIS, it is like... Screen Shot 2021-03-07 at 12.35.15 PM.png

Read the shapefiles in PostGIS

Then, I am going to read it in PostGIS using the following script. 

shp2pgsql -s 4269 -g geom_4269 -I 
"/Users/shingo/Dropbox/GEO/Postgres/postGISInAction/data/fortune/tlusstate" 
staging.tl_2018_states 
| psql -h localhost -p 5432 -d postgis_in_action -U shingo

I usually use pgAdmin4 but the script above returns an error as pgAdmin4 does not support the command shp2pgsql. gis.stackexchange.com/questions/217456/shap..

So far, the easiest way to read shapefile is from command-line or QGIS.

-s 4269 specifies the Spatial Reference System. -g geom_4269 sets the name of geometry column in the database. Default name is geometry. I subsequently execute the SQL by piping it to psql.

Like the state boundary, I read Fortune 500 headquarters location shapefile into the staging table. The data was downloaded from arcgis.com/home/item.html?id=3b622641d8b440..

shp2pgsql -s 4269 -g geom_4269 -I  
"/Users/shingo/Dropbox/GEO/Postgres/postGISInAction/data/fortune/Fortune_500_Corporate_Headquarters" 
staging.fortune 
| psql -h localhost -p 5432 -d postgis_in_action -U shingo

Move the table from staging schema to working schema

So far, I created my initial spatial tables in staging schema. Now, I create a new schema named toyproject (Yes! this project is just a prelude for my read project).

CREATE SCHEMA toyproject ;
CREATE TABLE toyproject.state (
    gid integer,
    region char(2),
    division char(2),
    statefp char(2),
    stusps char(2),
    name char(100),
    aland double precision,
    geom_4269 geometry
);
INSERT INTO toyproject.state (gid, region, division, statefp,stusps,name,aland,geom_4269)
SELECT gid, region, division, statefp,stusps,name,aland,geom_4269
FROM staging.tl_2018_states

Next, create the Fortune 500 company headquarters table to toyproject schema. 

CREATE TABLE toyproject.fortune (
    id integer,
    rank numeric,
    name char(38),
    address char(35),
    city char(19),
    state char(2),
    county char(20),
    employees char(20),
    revenues numeric,
    profit numeric,
    geom geometry
)
INSERT INTO toyproject.fortune (
    id,
    rank,
    name,
    address,
    city,
    state,
    county,
    employees,
    revenues,
    profit,
    geom)
SELECT     
    id,
    rank,
    name,
    address,
    city,
    state,
    county,
    employees,
    revenues,
    profit,
    geom
FROM staging."Fortune_500_Corporate_Headquarters";

At this point, I am ready to start analyzing two data tables.

hub.arcgis.com/datasets/esri::usa-major-cit..

PostgreSQLGeospatial