DEV Community

loading...
Cover image for Getting started with PostGIS

Getting started with PostGIS

Antara Datta
Developer: Java | Javascript | NodeJS | Angular | React | Postgres | PostGIS | Firebase
Updated on ・3 min read

If you have worked with geospatial data, you might want to harness the power of PostGIS. It is an extension of PostgreSQL which adds spatial ability to your database. Reading, analyzing and processing geospatial files like .shp, .tif etc requires special tools. However, doing SQL operations to achieve the same makes it more consistent for developers.

PostGIS allows to very simply load and query geospatial data from the database for representational purposes.

Installation

  1. Install Postgres on system using: postgres-downloads postgres

  2. It adds stackbuilder to your system automatically, run stack builder to set up postgis
    install postgis

  3. Select PostGIS bundle
    select postgis bundle

  4. Follow the steps given in the set up to complete the installation

Setting up PostGIS

Once the installation steps are completed, go to pgAdmin or any other database client and create a new database. Postgres by default contains one extension I.e. plpgsql. To add PostGIS as an extension, run the following query on your database.

create extension postgis;

After running the above query, new extension will be added which can be viewed under the extensions.
postgis extensions
Along with this extension, a new table named spatial_ref_systems with srids and projections will be created. This table contains all spatial references and is used for transformations.

Loading data

Geospatial data can be loaded in different ways based on the kind of files which are storing them. As of now, we will consider shape files which is one of the widely used forms of storing vector data. Shape files can be loaded in to the database in the following two ways:

Using PostGIS Bundle

Look for “postgis bundle 3 for PostgreSQL x64 Shapefile and DBF” in your applications, it is usually downloaded with PostGIS. Connect it to your currently created database with PostGIS extension using the hostname, port and database name. On successful connection you will see the following message:

Connecting: host=localhost port=5433 user=postgres password='********' dbname=test client_encoding=UTF8

Connection succeeded

Now click on import and select “Add File”. Select all the shape files you would like to import.
load data into postgis
Add the SRID if it is missing and click on import. This will import each shape file as a relational model in your database.

add srid

On describing any of these tables, you will be able to see the columns along with the geom column which represents the actual geospatial information. It comprises of the latitude and longitude.
lat long data

To view the data, you can query the geom column from any of the tables:
View geospatial data

Using command line:

The standard tool for loading shape file is “shp2pgsql”, it can be found in the bin folder of the postgres installation. This is how it can be used:

shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]
Enter fullscreen mode Exit fullscreen mode
shp2pgsql -s 4326 subway_stations public.nyc_subway_stations | psql -h localhost -p 5433 -U postgres -d test
Enter fullscreen mode Exit fullscreen mode

Querying data

This data can be queried in many forms to generate desired visualizations. A geom can either be a POINT, LINESTRING, POLYGON or a combination of these. Various operations like calculating the distance between two points, area, intersection etc. can be queried from the data using certain predefined postgis methods. Some of the commonly used methods are: ST_Length, ST_Area, ST_Contains, ST_Intersection, ST_Equals, ST_Within, ST_Scale, ST_Translate. You can read more about the use of these functions and the different ways of using them here: postgis functions

One of the most widely used and accepted formats of geospatial data that can be integrated with maps like leaflet is call geojson. PostGIS can be directly queried to generate response in the geojson format like:

select   
json_build_object( 'type', 'FeatureCollection', 
'features', json_agg(st_asgeojson(t.*)::json)) 
  from  
(select gid, name, geom from ${tablename} ) 
as t(id, name, geom); 
Enter fullscreen mode Exit fullscreen mode

This is how an actual geojson looks like:

{
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -76.935256783,
                    38.9081784965
                ]
            },
            "properties": {
                "gid": 1,
                "name": "one"
            }
        },
        {
            "type": "Feature",
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -76.9750541388,
                    38.8410857803
                ]
            },
            "properties": {
                "gid": 2,
                "name": "two"
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

A lot of modern tools today rely on geospatial data, PostGIS can provide insights by using these methods of storing and querying such data with greater ease.

Discussion (0)