> This page location: Extensions > postgis
> Full Neon documentation index: https://neon.com/docs/llms.txt

# The postgis extension

Work with geospatial data in Postgres using PostGIS

The `postgis` extension provides support for spatial data - coordinates, maps and polygons, encompassing geographical and location-based information. It introduces new data types, functions, and operators to manage and analyze spatial data effectively.

This guide introduces you to the `postgis` extension - how to enable it, store and query spatial data, and perform geospatial analysis with real-world examples. Geospatial data is crucial in fields like urban planning, environmental science, and logistics.

**Note:** PostGIS is an open-source extension for Postgres that can be installed on any Neon Project using the instructions below. Detailed installation instructions and compatibility information can be found at [PostGIS Documentation](https://postgis.net/documentation/).

For information about PostGIS-related extensions, including `pgrouting`, H3_PostGIS, PostGIS SFCGAL, and PostGIS Tiger Geocoder, see [PostGIG-related extensions](https://neon.com/docs/extensions/postgis-related-extensions).

**Version availability:**

Please refer to the [list of all extensions](https://neon.com/docs/extensions/pg-extensions) available in Neon for up-to-date information.

## Enable the `postgis` extension

You can enable the extension by running the following `CREATE EXTENSION` statement in the Neon **SQL Editor** or from a client such as `psql` that is connected to Neon.

```sql
CREATE EXTENSION IF NOT EXISTS postgis;
```

For information about using the Neon SQL Editor, see [Query with Neon's SQL Editor](https://neon.com/docs/get-started/query-with-neon-sql-editor). For information about using the `psql` client with Neon, see [Connect with psql](https://neon.com/docs/connect/query-with-psql-editor).

## Example usage

**Create a table with spatial data**

Suppose you're managing a city's public transportation system. You can create a table to store the locations of bus stops.

```sql
CREATE TABLE bus_stops (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    location GEOGRAPHY(Point)
);
```

Here, the location column is of type `GEOGRAPHY(Point)`, which is a spatial data type provided by the `postgis` extension and used to store points on the Earth's surface.

**Inserting data**

Data can be inserted into the table using regular `INSERT` statements.

```sql
INSERT INTO bus_stops (name, location)
VALUES
    ('Main St & 3rd Ave', ST_Point(-73.935242, 40.730610)),
    ('Elm St & 5th Ave', ST_Point(-73.991070, 40.730824));
```

The `ST_Point` function is used to create a point from the specified longitude and latitude.

**Querying spatial data**

Now, we can perform spatial queries using the built-in functions provided by `PostGIS`. For example, below we try to find points within a certain distance from a reference.

Query:

```sql
SELECT name FROM bus_stops
WHERE ST_DWithin(location, ST_Point(-73.95, 40.7305)::GEOGRAPHY, 2000);
```

This query returns the following:

```text
| name               |
|--------------------|
| Main St & 3rd Ave  |
```

The `ST_DWithin` function returns true if the distance between two points is less than or equal to the specified distance (when used with the `GEOGRAPHY` type, the unit is meters).

## Spatial data types

PostGIS extends Postgres data types to handle spatial data. The primary spatial types are:

- **GEOMETRY**: A flexible type for spatial data, supporting various shapes. It models shapes in the cartesian coordinate plane. Each `GEOMETRY` value is also associated with a spatial reference system (SRS), which defines the coordinate system and units of measurement.
- **GEOGRAPHY**: Specifically designed for large-scale spatial operations on the Earth's surface, factoring in the Earth's curvature. The coordinates for a `GEOGRAPHY` shape are specified in degrees of longitude and latitude.

The actual shapes are stored as a set of coordinates. For example, a point is stored as a pair of coordinates, a line as a set of points, and a polygon as a set of lines.

## Longer example

PostGIS provides a number of other functions for spatial analysis - area, distance, intersection, and more. To illustrate, we'll create dataset representing a small set of landmarks and roads in a fictional city and run spatial queries on it.

**Creating the test dataset**

```sql
CREATE TABLE landmarks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    location GEOMETRY(Point)
);

CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    path GEOMETRY(LineString)
);

INSERT INTO landmarks (name, location)
VALUES
    ('Park', ST_Point(100, 200)),
    ('Museum', ST_Point(200, 300)),
    ('Library', ST_Point(300, 200));

INSERT INTO roads (name, path)
VALUES
    ('Main Street', ST_MakeLine(ST_Point(100, 200), ST_Point(200, 300))),
    ('Second Street', ST_MakeLine(ST_Point(200, 300), ST_Point(300, 200)));
```

**Nearest landmark to a given point**

Finding the nearest places to a given point is a common spatial query. We can use the `ST_Distance` function to find the distance between two points and order the results by distance.

```sql
SELECT name, ST_Distance(location, ST_GeomFromText('POINT(150 250)')) AS distance
FROM landmarks
ORDER BY distance
LIMIT 1;
```

This query returns the following:

```text
| name   | distance |
|--------|----------|
| Park   | 70.7107  |
```

**Intersection of Roads**

We can use the `ST_Intersects` function to find if two roads intersect. To ensure we don't get duplicate pairs of roads, we filter out pairs where the first road has a higher `id` than the second road.

```sql
SELECT a.name, b.name
FROM roads a AS name_A, roads b AS name_B
WHERE a.id < b.id AND ST_Intersects(a.path, b.path);
```

This query returns the following:

```text
| name_A         | name_B         |
|----------------|----------------|
| Main Street    | Second Street  |
```

**Buffer zone around a landmark**

Say, the municipal council wants to create a buffer zone of 50 units around landmarks and check which roads intersect these zones. `ST_Buffer` computes an area around the given point with the specified radius.

```sql
SELECT l.name AS landmark, r.name AS road
FROM landmarks l, roads r
WHERE ST_Intersects(r.path, ST_Buffer(l.location, 50));
```

This query returns the following:

```text
| landmark | road          |
|----------|---------------|
| Park     | Main Street   |
| Museum   | Main Street   |
| Museum   | Second Street |
| Library  | Second Street |
```

**Line of Sight Between Landmarks**

To check if there's a direct line of sight (no roads intersecting) between two landmarks, we can combine two `postgis` functions.

```sql
SELECT
    'No direct line of sight' AS info
FROM
    landmarks l1, landmarks l2, roads r
WHERE
    l1.name = 'Park' AND l2.name = 'Library' AND
    ST_Intersects(ST_MakeLine(l1.location, l2.location), r.path)
LIMIT 1;
```

This query returns the following:

```text
| info                     |
|--------------------------|
| No direct line of sight  |
```

This tells us there's no direct line of sight between the Park and the Library.

## Performance considerations

When working with PostGIS, thinking about performance is crucial, especially when dealing with large datasets or complex spatial queries.

### Indexing

**GIST** (Generalized Search Tree) is the default spatial index in PostGIS. GiST indexes are well-suited for multidimensional data, like points, lines, and polygons. It can significantly improve query performance, especially for spatial search operations and joins.

```sql
CREATE INDEX spatial_index_name ON landmarks USING GIST(location);
```

### Query optimization

- **Unnecessary Casting**: `GEOMETRY` and `GEOGRAPHY` are the two primary data types in `postgis`, and a lot of functions are overloaded to work with both. However, casting between the two types can be expensive, so it's best to store data in the more frequently used type.
- **Use Appropriate Precision**: Reducing the precision of coordinates can often improve performance without significantly impacting the results.

## Conclusion

These examples provide a quick introduction to handling and analyzing spatial data in PostgresQL. We saw how to create tables with spatial data, insert data, and perform spatial queries using the `postgis` extension. It offers a powerful set of tools, with functions for calculating distances, identifying spatial relationships, and aggregating spatial data.

## Resources

- [PostGIS Documentation](https://postgis.net/documentation)
- [PostGIS Intro Workshop](https://postgis.net/workshops/postgis-intro/)

---

## Related docs (Extensions)

- [Extension explorer](https://neon.com/docs/extensions/extension-explorer)
- [anon](https://neon.com/docs/extensions/postgresql-anonymizer)
- [btree_gin](https://neon.com/docs/extensions/btree_gin)
- [btree_gist](https://neon.com/docs/extensions/btree_gist)
- [citext](https://neon.com/docs/extensions/citext)
- [cube](https://neon.com/docs/extensions/cube)
- [dblink](https://neon.com/docs/extensions/dblink)
- [dict_int](https://neon.com/docs/extensions/dict_int)
- [earthdistance](https://neon.com/docs/extensions/earthdistance)
- [fuzzystrmatch](https://neon.com/docs/extensions/fuzzystrmatch)
- [hstore](https://neon.com/docs/extensions/hstore)
- [intarray](https://neon.com/docs/extensions/intarray)
- [ltree](https://neon.com/docs/extensions/ltree)
- [neon](https://neon.com/docs/extensions/neon)
- [neon_utils](https://neon.com/docs/extensions/neon-utils)
- [online_advisor](https://neon.com/docs/extensions/online_advisor)
- [pgcrypto](https://neon.com/docs/extensions/pgcrypto)
- [pgvector](https://neon.com/docs/extensions/pgvector)
- [pgrag](https://neon.com/docs/extensions/pgrag)
- [pg_cron](https://neon.com/docs/extensions/pg_cron)
- [pg_graphql](https://neon.com/docs/extensions/pg_graphql)
- [pg_mooncake](https://neon.com/docs/extensions/pg_mooncake)
- [pg_partman](https://neon.com/docs/extensions/pg_partman)
- [pg_prewarm](https://neon.com/docs/extensions/pg_prewarm)
- [pg_session_jwt](https://neon.com/docs/extensions/pg_session_jwt)
- [pg_stat_statements](https://neon.com/docs/extensions/pg_stat_statements)
- [pg_repack](https://neon.com/docs/extensions/pg_repack)
- [pg_search](https://neon.com/docs/extensions/pg_search)
- [pg_tiktoken](https://neon.com/docs/extensions/pg_tiktoken)
- [pg_trgm](https://neon.com/docs/extensions/pg_trgm)
- [pg_uuidv7](https://neon.com/docs/extensions/pg_uuidv7)
- [pgrowlocks](https://neon.com/docs/extensions/pgrowlocks)
- [pgstattuple](https://neon.com/docs/extensions/pgstattuple)
- [postgis-related](https://neon.com/docs/extensions/postgis-related-extensions)
- [postgres_fdw](https://neon.com/docs/extensions/postgres_fdw)
- [tablefunc](https://neon.com/docs/extensions/tablefunc)
- [timescaledb](https://neon.com/docs/extensions/timescaledb)
- [unaccent](https://neon.com/docs/extensions/unaccent)
- [uuid-ossp](https://neon.com/docs/extensions/uuid-ossp)
- [wal2json](https://neon.com/docs/extensions/wal2json)
- [xml2](https://neon.com/docs/extensions/xml2)