Skip to main content

Spatial and Geographic objects

In this tutorial, we will be covering Spatial and Geographic objects; one of the advanced features in PostgreSQL. By the end of this tutorial, you should have a good understanding of how spatial and geographic objects work in PostgreSQL, and how to use them in your database projects.

Spatial and Geographic objects are a crucial part of modern database systems, especially when dealing with location-based data. PostgreSQL offers a robust set of functions to work with these types of data.

What are Spatial and Geographic Objects?​

Spatial objects represent the physical location and shape of geometric objects. These objects can be point locations or complex entities such as countries, roads, or lakes.

Geographic objects, on the other hand, are spherical objects that are measured in degrees of longitude and latitude.

In PostgreSQL, the PostGIS extension adds support for geographic objects allowing location queries to be run in SQL.

Adding PostGIS extension​

In order to use Spatial and Geographic Objects in PostgreSQL, you need to add the PostGIS extension to your database. Here is how you do it:

CREATE EXTENSION postgis;

This command adds the PostGIS extension to your current database.

Basic Geographic Types​

There are several basic geographic types that PostGIS adds to the PostgreSQL:

  • POINT: Represents a point in space.
  • LINESTRING: Represents a series of points that form a line.
  • POLYGON: Represents a polygon.

In addition to these, there are other types like MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.

Creating a Table with Geographic Types​

Now, let's create a table that uses these geographic types:

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

Here, we have a places table with an id, name, and location field. The location field is of type GEOMETRY and it represents a point in space.

Inserting Data​

We can insert data into the places table like this:

INSERT INTO places (name, location)
VALUES ('New York', ST_GeomFromText('POINT(-73.935242 40.730610)', 4326));

The ST_GeomFromText function is used to generate a point from a text.

Querying Data​

You can query the data using standard SQL commands. For example:

SELECT * FROM places WHERE ST_Contains(
    ST_GeomFromText('POLYGON((-73.935242 40.730610, -73.935242 40.730610, -73.935242 40.730610, -73.935242 40.730610))', 4326),
    location
);

This query returns all places that are contained in the specified polygon.

Conclusion​

In this tutorial, we've covered the basics of working with Spatial and Geographic objects in PostgreSQL. We've learned how to add the PostGIS extension, create a table with geographic types, insert data into that table, and query the data. This is just the tip of the iceberg, there's a lot more you can do with Spatial and Geographic objects in PostgreSQL.

Remember, Spatial and Geographic objects are a powerful tool in PostgreSQL's arsenal. They can be used to represent complex entities and perform operations that are not possible with standard SQL commands. So, keep exploring and happy querying!