Getting Started With Postgis Extension
This tutorial will not dive deeply into how to use Postgres. We'll assume a baseline knowledge of how to install and set up a basic Postgresql database. If you need help with that, we recommend checking out the docs.
Install Postgis
Once you have your Postgresql database instance up and running, installing the Postgis extension is easy. On a Mac, you can add it via Homebrew.
Restart Postgres
After you install Postgis, you'll need to restart your Postgres instance for the change to take effect. You can do that via PGAdmin, or by running the following in psql in the command line.
Create Database
Create a normal database using PGAdmin, or by running the following command in the psql command line.
Enable Extension
The extension is now running on the SQL server. Next, you'll need to run the CREATE EXTENSION command that runs the Postgis extension in the SQL window. We recommend doing this via the PGAdmin interface. Connect to your SQL instance in PGAdmin and run the following command.
This creates all the needed database objects, tables, indexes, etc that Postgis gives us to work with. You can confirm that Postgis is running with the following query:
Load Sample Data
Finally, you'll need some sample data. We'll use the data bundle from the Postgis tutorial. Connect to your PGAdmin database and right-click on your database name, then select "Restore." Browse to the location where you downloaded the sample data and select the nyc_data.backup file. This will insert data from the backup file into your database.
Next, right-click on the database name again and select "Refresh." You should now see a list of new tables and data. PGAdmin has a handy Geometry Viewer, which you can click to see the layout of the streets.
Run Complex Queries
When a database instance runs the Postgis extension, created databases will include the geometry data type, and will be geospatially aware. The geometry column is an opaque binary that requires Postgis functions to turn the data that gets stored there into usable points.
Query a Single Point
To get a single set of latitude and longitude coordinates from the geom column, run the following query:
The result should look something like this
The ST_AsText function turns the opaque binary geom column into a pair of latitude and longitude points.
Finding Distance
Finding simple distances can be done by returning the length of a linestring. A linestring is a path between two points. It is represented as an ordered series of two or more points. Roads and rivers are considered linestrings.
In our sample data, we have linestring data for the streets in New York City. The distances of streets can be found by using the ST_Length function and passing it the name of a linestring (street.) For example:
Geometry vs Geography
Let's quickly talk about the difference between geometry and geography, particularly as it applies to geospatial data analysis. Postgis draws the distinction between geometry and geography thus: geometry is flat, while geography is curved.
Small distances on the Earth (i.e. between two points in a city) can be easily calculated using cartesian geography. Longer distances, however, (for example, points in different cities or states) must include additional calculations to account for the curvature of the Earth.
Postgis provides this functionality through the geography type. This type should be used instead of the geometry type when calculating distances between countries or cities.
Spatial Indexes
Spatial indexes, like regular database indexes, make querying spatial data faster and working with very large datasets more manageable. Without a spatial index, searches would require a "sequential scan" of all rows in the table. An index organizes the data into a search tree for faster processing.
To test this, let's remove the spatial index from nyc_census_blocks.
Then run the following query in PGAdmin and watch the "timing" indicator.
Now, the table is small, so granted, the time it takes to scan it should be quite small (around 300ms.) But let's see what happens when we add the index back again.
When you run the same query, the PGAdmin timing indicator should show a significantly faster processing time (somewhere around 50ms.)
Conclusion
In this article, we looked at how to set up a Postgresql database with Postgis and work with the geometry data type. We loaded some test data provided by the Postgis docs and used the special Postgis functions to query for geospatial data. We also used the Geography Viewer to view all the points on a map.
FAQs
Does Postgres support geospatial data?
Yes. While Postgres does not have built-in support for geolocation, there are several Postgres extensions including Postgis that allow you to treat geospatial data as first-class objects in your database.
What is the data type for latitude and longitude in Postgresql?
In Postgresql, latitude and longitude are represented by the POINT data type. This is a geometry data type that is stored in the opaque binary geom column. It can be accessed by running the Postgis ST_AsText function.
Is Postgis included in Postgresql?
No, Postgis is an add-on, or extension. It is a spatial database extender that adds support for geographic objects and allows geospatial queries to be run in a Postgres database.
