Guide to Using PostGIS

postgis is the geospatial extension module for PostgreSQL that provides geometric and geospatial types and functions, enabling PostgreSQL to store and query geospatial data. This article will detail how to install and use postgis in ServBay.

Installing PostGIS

ServBay already includes the postgis extension module. You just need to enable it in your database. Here are the steps to enable postgis:

Connect to the PostgreSQL database:
bash
```
psql -U your_username -d your_database
```
1
Create the extension:
sql
```
CREATE EXTENSION postgis;
```
1
Verify the installation:
sql
```
\dx
```
1

Configuring PostGIS

After enabling postgis, you can create and manage geospatial data types and use a variety of geospatial functions.

Creating a Geospatial Table

Here's an example showing how to create a table containing geospatial data.

Create the table:

sql

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

Insert sample data:

sql

INSERT INTO locations (name, geom) VALUES
('Location A', ST_GeomFromText('POINT(116.4074 39.9042)', 4326)),
('Location B', ST_GeomFromText('POINT(121.4737 31.2304)', 4326));

Creating a Geospatial Index

To improve query performance, it is recommended to create an index for the geospatial column.

Create a GiST index:

sql

CREATE INDEX idx_geom ON locations USING GIST (geom);

Performing Geospatial Queries with PostGIS

Here are some common examples of geospatial queries.

Querying Distances

Query the distance between two points:

sql

SELECT ST_Distance(
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326),
    ST_GeomFromText('POINT(121.4737 31.2304)', 4326)
);

Querying Contains Relationships

Query if a point is within a specific area:

sql

SELECT name FROM locations
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((116.0 39.0, 117.0 39.0, 117.0 40.0, 116.0 40.0, 116.0 39.0))', 4326),
    geom
);

Querying Nearest Neighbors

Query points near a specific point:

sql

SELECT name FROM locations
ORDER BY geom <-> ST_GeomFromText('POINT(116.4074 39.9042)', 4326)
LIMIT 5;

Performing Geospatial Analysis with PostGIS

postgis offers a rich set of geospatial analysis functions. Here are some commonly used analysis features.

Buffer Analysis

Create a buffer zone:

sql

SELECT ST_Buffer(geom, 0.01) FROM locations WHERE name = 'Location A';

Intersection Analysis

Query the intersection of two geometries:

sql

SELECT ST_Intersection(
    ST_GeomFromText('POLYGON((116.0 39.0, 117.0 39.0, 117.0 40.0, 116.0 40.0, 116.0 39.0))', 4326),
    geom
) FROM locations WHERE name = 'Location A';

Union Analysis

Union multiple geometries:
sql
```
SELECT ST_Union(geom) FROM locations;
```
1

Visualizing Geospatial Data

You can use various GIS tools (such as QGIS) or web mapping services (such as Leaflet, OpenLayers) to visualize geospatial data in postgis.

Using QGIS

Connect to the PostgreSQL database:
- Open QGIS
- Select “Data Source Manager” -> “PostGIS”
- Enter the database connection information and connect
Load geospatial data:
- Select the table or view to load
- Click the “Add” button

Using Leaflet

Create a web map:

html

<!DOCTYPE html>
<html>
<head>
    <title>Leaflet PostGIS Example</title>
    <link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
    <script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
</head>
<body>
    <div id="map" style="width: 600px; height: 400px;"></div>
    <script>
        var map = L.map('map').setView([39.9042, 116.4074], 10);
        L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
            attribution: '© OpenStreetMap contributors'
        }).addTo(map);

        // Add a marker from PostGIS data
        var marker = L.marker([39.9042, 116.4074]).addTo(map)
            .bindPopup('Location A')
            .openPopup();
    </script>
</body>
</html>

Summary

postgis is a powerful geospatial extension module that allows for efficient storage and querying of geospatial data in PostgreSQL through simple configuration and usage. Since ServBay comes with the postgis extension module, you just need to follow the steps in this article to install and configure it. By using various geospatial queries and analysis functions, you can better utilize geospatial data, providing reliable database support for your applications.

Guide to Using PostGIS ​

Installing PostGIS ​

Configuring PostGIS ​

Creating a Geospatial Table ​

Creating a Geospatial Index ​

Performing Geospatial Queries with PostGIS ​

Querying Distances ​

Querying Contains Relationships ​

Querying Nearest Neighbors ​

Performing Geospatial Analysis with PostGIS ​

Buffer Analysis ​

Intersection Analysis ​

Union Analysis ​

Visualizing Geospatial Data ​

Using QGIS ​

Using Leaflet ​

Summary ​