Getting Started with PostGIS – Mapping US States

You will need a PostgreSQL server with PostGIS extensions enabled on a database.

Download some sample data. This tutorial uses the US States.

https://www.kaggle.com/washimahmed/usa-latlong-for-state-abbreviations

As the postgres user, connect to the GIS database

$ psql -d gisdb

Create the states table

gisdb=# create table states (
gisdb(# abbr text,
gisdb(# latitude double precision,
gisdb(# longitude double precision,
gisdb(# city text);

create table states (
abbr text,
latitude double precision,
longitude double precision,
city text);

In pgAdmin 4, right-click the states table and select Import/Export

 

Toggle the first input to Import, enable the Header option, and change the delimiter to a comma. Select the CSV downloaded and click OK.

When viewing the data in the states table, you should see the imported data.

There is now a table of coordinates without any geometry. Add a geometry column with the following statement.

gisdb=# alter table states add column geom geometry(POINT,4326);

Generate the geomerty column for each state with the following command.

gisdb=# update states set geom = st_setsrid(st_point(longitude,latitude),4326);

You should now see a geom column in the table in pgAdmin. View the mapped data by clicking on the eye icon in the geom column header.

The mapped data may be added as a layer in QGIS Desktop as well.

 

Was this helpful?

YesNo

1 / 0

#database #gis #postgis