Postgres PostGIS, installation, spatial queries, sql queries by radius

Installation and examples of using PostGIS

PostGIS is an extension for the PostgreSQL database that adds support for geographic objects and features. This is a powerful tool that allows you to spatial queries and analyze geodata.

Installing PostGIS

To install PostGIS, follow these steps:

1. Make sure you have a PostgreSQL database installed.
2. Open a command prompt and run sudo apt-get install postgis to install PostGIS on Ubuntu.
3. After installation, run sudo -u postgres psql to enter the interactive shell PostgreSQL.
4. Run the command CREATE EXTENSION postgis; to create a PostGIS extension in your database.

Use examples

Creating a table with geographic data

To create a table with geographic data, run the following SQL query:

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

Inserting geographic data

To insert geographic data into a table, use the following SQL query:

INSERT INTO cities (name, location)
VALUES ('Moscow', ST_GeographyFromText('POINT(37.617778 55.751944)'));

Running spatial queries

PostGIS allows you to perform various spatial queries, for example, searching for nearby objects or determining the area of a polygon. Below is an example request for search for the nearest city to a given point:

SELECT name
FROM cities
ORDER BY location <-> ST_GeographyFromText('POINT(30.315868 59.939095)')
LIMIT 1;

Running spatial queries

PostGIS allows you to perform various spatial queries, including searching for objects within a given radius. Below is an example of a query to search for cities located in within a radius of 10 kilometers from a given point:

SELECT name
FROM cities
WHERE ST_DWithin(location, ST_GeographyFromText('POINT(37.617778 55.751944)'), 10000)
ORDER BY location <-> ST_GeographyFromText('POINT(37.617778 55.751944)');

In this example, the ST_DWithin function is used to determine objects that are within a specified radius from a given point. Meaning 10000 represents the distance in meters.

Note: Make sure that the coordinates of the point are in request correspond to the required coordinates.

Conclusion

PostGIS is a powerful extension for the PostgreSQL database, which provides support for geographic data and functions. It opens up wide possibilities for working with geodata and performing spatial analysis. Try installing PostGIS and start using it in your projects!

#DevOps #Postgres #postgis

03.10.2023

377