How to Use PostGIS Extension in PostgreSQL: Simple Guide

To use the postgis extension in PostgreSQL, first enable it in your database with CREATE EXTENSION postgis;. This adds spatial functions and types so you can store and query geographic data easily.

Syntax

To enable PostGIS in a PostgreSQL database, use the CREATE EXTENSION command. This loads the PostGIS functions and types into your current database.

• CREATE EXTENSION postgis;: Enables PostGIS.
• DROP EXTENSION postgis;: Removes PostGIS from the database.

Make sure you run this command as a database superuser or a user with the right privileges.

sql

CREATE EXTENSION postgis;

Example

This example shows how to enable PostGIS, create a table with a geographic column, insert a point, and query its coordinates.

sql

CREATE EXTENSION postgis;

CREATE TABLE places (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  location GEOGRAPHY(Point, 4326)
);

INSERT INTO places (name, location) VALUES
('Home', ST_GeogFromText('SRID=4326;POINT(-71.060316 48.432044)'));

SELECT name, ST_AsText(location) AS location_wkt FROM places;

Output

 name |           location_wkt           
------+---------------------------------
 Home | POINT(-71.060316 48.432044)
(1 row)

Common Pitfalls

Common mistakes when using PostGIS include:

• Trying to use PostGIS functions before enabling the extension.
• Not specifying the correct SRID (spatial reference ID) for your data, which can cause wrong calculations.
• Confusing geometry and geography types; geometry is planar, geography is for spherical Earth coordinates.

Always check your database user has permission to create extensions.

sql

/* Wrong: Using PostGIS function before enabling extension */
SELECT ST_Point(1, 1);

/* Right: Enable extension first */
CREATE EXTENSION postgis;
SELECT ST_Point(1, 1);

Quick Reference

Command	Description
CREATE EXTENSION postgis;	Enable PostGIS in the current database
DROP EXTENSION postgis;	Remove PostGIS from the database
ST_GeogFromText('SRID=4326;POINT(lon lat)')	Create geography point from WKT text
ST_AsText(geography_column)	Convert geography to readable WKT text
GEOGRAPHY(Point, 4326)	Column type for geographic points with WGS 84 coordinate system

Key Takeaways

Always enable PostGIS with CREATE EXTENSION before using spatial functions.

Use the GEOGRAPHY or GEOMETRY types to store spatial data in tables.

Specify the correct SRID (usually 4326 for GPS coordinates) for accurate results.

Check user permissions to create extensions in your PostgreSQL database.

Use ST_AsText to view spatial data in a readable format.