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?
1 / 0