[PostGIS](https://postgis.net/) is a spatial data extension for [[PostgreSQL]] databases. PostGIS adds support to store geospatial data and run location queries in SQL. ## Installation PostGIS can be installed during installation of PostgreSQL using Stack Builder. If you did not install PostGIS, open Stack Builder to install. If you did not install Stack Builder when installing PostgreSQL, see the [Download](https://postgis.net/install/) instructions. ## Getting Started PostGIS must be enabled on each database to use. After you create a database, connect to your database with [[psql]] or [[pgAdmin]]. > [!Danger] > DO NOT install PostGIS in the database called `postgres`. Run the following SQL, installing only the features you want. To get started, I recommend both the `postgis` extension for geometry (points, lines, polygons) and geography and the `postgis_raster` extension for raster data. Additional features are available, see the [Getting Started](https://postgis.net/install/) instructions. ```sql -- Enable PostGIS (as of 3.0 contains just geometry/geography) CREATE EXTENSION postgis; -- enable raster support (for 3+) CREATE EXTENSION postgis_raster; ``` > [!Tip] > Additional spatial extensions include `pointcloud` for LIDAR support and `h3` for Uber h3 hexagon indexing. ## Common Workflows ### Load a shapefile Use the [shp2pgsql](https://postgis.net/docs/manual-3.3/using_postgis_dbmanagement.html#shp2pgsql_usage) data loader to convert shapefiles into SQL that can be read into a PostGIS-enabled PostgreSQL database. Use `shp2pgsql -?` to review all available options. Here is an example to read a `departments.shp` shapefile and save it to a table named `departments` in the `public` schema. The SQL is saved to a file named `departments.sql` and then read into the database `mydb` with `psql`. You can combine both statements using a pipe ( `|` ) to complete the command in a single line. ```bash shp2pgsql -s 4326 -c -D -I departments.shp public.departments > departments.sql psql -U postgres -d mydb -f departments.sql ``` Option | Effect -----| ----- `-s 4236` | specifies the data are in spatial reference system WGS84 (epsg=4326) `-c` | creates a new table `-D` | uses the PostgreSQL "dump" format (faster than the default insert format) `-I` | creates a spatial index (using GiST) on the geometry column Using `psql`, the file `departments.sql` is loaded into the database `mydb`. Use `-W` to set the encoding of the shapefile if the encoding is not UTF8. The data will be converted from the specified encoding to UTF8. See related how to: [[Check the encoding of a shapefile]]. > [!Tip] > You can view the geometry data (in pgAdmin 4 or later) by clicking the small map icon in the geometry column header. > [!tip] > If you see the error `shp2pgsql: command not found`, make sure to add [[psql]] to your [[PATH]] variable. > [!Tip] > A GUI version of `shp2pgsql` is also available. Search for the application **PostGIS Bundle for PostgreSQL Shapefile and DBF Importer** in the Windows Start Menu. ### Export to shapefile Use `pgsql2shp` to dump a spatial table or query to shapefile. ```bash # dump a table pgsql2shp <options> <database> <schema>.<table> -f <filename> # dump from a query pgsql2shp <options> <database> <query> ``` ### Load a raster Use [raster2pgsql](https://postgis.net/docs/manual-3.3/using_raster_dataman.html#RT_Raster_Loader) to load raster data to a PostGIS-enabled PostgreSQL database. Use `raster2pgsql -?` to review all available options. Here is an example to read a `dem.tif` raster file and save it to a table named `demelevation` in the `public` schema. The SQL is saved to a file named `dem.sql` and then read into the database `mydb` with `psql`. You can combine both statements using a pipe ( `|` ) to complete the command in a single line. ```bash raster2pgsql -s 4326 -I -M -C dem.tif -F public.demelevation > dem.sql psql -U postgres -d mydb -f dem.sql ``` Option | Effect ----- | ----- `-s 4236` | specifies the data are in spatial reference system WGS84 (epsg=4326) `-I` | creates a spatial index (using GiST) on the geometry column `-M` | vaccuum analyze to improve query performance `-C` | applies raster contraints to ensure raster is properly registered in `raster_columns` view `-F` | adds a column with the same name of the file `-G` lists all acceptable raster formats. Common raster formats accepted include `GeoTIFF`, `R Raster`, `Earth Engine Data API Image`, and `GeoPackage`. ### Create a spatial table Create a table to store geometry data by providing a column type `geometry`. Optionally specify the type of geometry to store (e.g., point, line, polygon) and the [[spatial reference system]]. The default spatial reference system is WGS84 (epsg=4326). ```sql CREATE TABLE cities ( id SERIAL PRIMARY KEY, name VARCHAR(64), geom geometry(POINT,4326) ); ``` To insert data into this table wrap the geometry in single quotes. ```sql INSERT INTO cities (id, name, geom) VALUES (1, 'Carcha', 'POINT(-90.01476 15.41235)') ``` > [!warning] > Provide longitude first when specifying a point. > [!Tip] > Use the data type `double precision` when reading individual coordinates (latitude or longitude) if not using the `POINT` data type. ### Modify an existing table If you have an existing table that includes spatial data and you want to create a geometry column, create a calculated field. Most commonly, you will add a `POINT` column from `latitude` and `longitude` columns. ```sql ALTER TABLE cities ADD COLUMN geom geometry(POINT,4326); UPDATE cities SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326); ``` #### Add a spatial index A spatial index will improve query performance. Add a spatial index to a table if it was not created initially. After building an index, it can also be helpful to use `VACUUM ANALYZE` to optimize query plans. ```sql CREATE INDEX <indexname> ON <tablename> USING GIST ( <geometryfield> ); VACUUM ANALYZE <tablename> [(<column_name>)]; ``` ### Create a view ### Validate geometry PostGIS does not automatically validate geometry on load by default. Add a constraint to check validity of geometry if you don't trust your data source. ```sql ALTER TABLE mytable ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(geom)); ``` ### View all geometry columns PostGIS maintains a metadata table of all geometry columns. ```sql \d geometry_columns ``` ## Basic spatial queries ```sql SELECT points_table.* FROM points_table JOIN polygon_table ON ST_Within(points.geom, polygon_table.geom) WHERE points_table.geom IS NOT NULL; ``` > [!example] Additional Resources > - [Boston GIS Getting Started with PostGIS: An Almost Idiot's Guide](https://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01) > - https://postgis.gishub.org/chapters/intro.html #refactor Load entire gdb: https://youtu.be/RyGKVaVcbzc