With the release of pgAdmin 4 version 3.3 comes the geometry viewer that will display your geometry (or geography) data on OpenStreetMap. In this GeoFile, we'll show you how to set up pgAdmin 4, import some data, add PostGIS, and start using the geometry viewer.

In our last article on pgAdmin 4 version 1.6, we covered its installation and how to use it with Compose for PostgreSQL databases. Since pgAdmin 4 was released last year, it's had some significant updates and bug fixes. With the latest release, version 3.3, we got something really helpful for GIS developers: the geometry viewer.

The geometry viewer was built thanks to a Google Summer of Code project by Xuri Gong, which allows you to view geometry and geography data in the pgAdmin browser on an OpenStreetMap map. In this example, we'll show you how to briefly set up pgAdmin, upload some data, and use the geometry viewer to view your geospatial queries in pgAdmin.

Setting things up

First download and install pgAdmin 4. Next, start up pgAdmin and add a Compose for PostgreSQL database by clicking Servers > Create > Server from the left pane.

Now, add your database information into the Create window under the connection tab. On the general tab, create a name. We've named this one "Compose".

Make sure you click on the SSL tab and select Require.

Depending on what you named your server in the Create window, you'll see something like the following in your left pane.

You can select the server and then select Databases and click on any database you've set up. If this is a new deployment, you'll have postgres and compose. For this example, we'll use compose. If you have another database you want to use, you can use that, too.

Click on Schemas > public and right click Tables. You'll have the option to create a table here.

Importing the data

The data set that we'll use for this example is Traffic Violation data from Montgomery Country, Maryland from 2013 to 2017. There are over 1 million rows in this CSV file. Download the CSV file from this page.

Then, to create a table quickly, select the Tools button at the top of the browser and click Query Tool.

This will open up a new window where you can write queries. Here, we'll create a new table for our data. I've created the query for you to copy and paste this into your query window:

CREATE TABLE example  
(
    date_of_stop date,
    time_of_stop time without time zone,
    agency text,
    subagency text,
    description text,
    location text,
    latitude double precision,
    longitude double precision,
    accident text,
    belts text,
    personal_injury text,
    property_damage text,
    fatal text,
    commercial_license text,
    hazmat text,
    commercial_vehicle text,
    alcohol text,
       work_zone text,
    state text,
    vehicletype text,
    year integer,
    make text,
    model text,
    color text ,
    violation_type text ,
    charge text ,
    article text ,
    contributed_to_accident text ,
    race text ,
    gender text ,
    driver_city text ,
    driver_state text ,
    dl_state text ,
    arrest_type text ,
    geolocation text 
);

After that, right click on Tables and select Refresh to see the new table. Right click on that table and select Import/Export.

A window will pop up where you can import your CSV file into your database.

Click on the tab that says Export. That will change the button to Import. Put the path of Traffic Violations CSV file into filename and change the Header button to Yes, and set the Delimiter to a comma , then click OK at the bottom.

Once that's done, all your data will be in the database and we can start querying data.

Setting up PostGIS

So, now that we have some data, we can look at it in the viewer. Make sure that PostGIS is first installed on your PostgreSQL database. You can use the Query Tool to add it using

CREATE EXTENSION postgis;  

Now, we need to set up a geometry column that will store the locations of each traffic point. To do that, run the following in the Query Tool:

SELECT AddGeometryColumn('example', 'geom', 4326, 'POINT', 2);  

All this is doing is adding a geometry column called geom with an SRID set to 4326 and also telling the database that only location points will be stored setting them to 2D map. To learn more about geospatial SRIDs, take a peek at the Geofile article Spatial Reference Systems and Databases.

After that, populate the column with the geometry transformation that'll use the latitude and longitude columns from our table.

UPDATE example SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);  

Now you're all set to start querying the data.

Querying the data and using the geometry viewer

For this example, let's look at the top 10 speeding violations that were caught by laser. We can group these by date and also get the number of violations per day. Using the Query Tool write:

SELECT count(*), date_of_stop  
FROM example  
WHERE arrest_type = 'Q - Marked Laser'  
GROUP BY date_of_stop  
ORDER BY count DESC  
LIMIT 10;  

Push the That'll give us:

  count | date_of_stop 
 -------+--------------
    287 | 2013-09-05
    274 | 2013-09-06
    247 | 2013-08-16
    235 | 2013-09-04
    232 | 2013-06-05
    225 | 2014-10-14
    224 | 2016-03-07
    222 | 2014-06-20
    221 | 2013-03-05
    218 | 2015-06-10

Taking the top date, let's use that to view the locations of these violations on September 5th, 2013.

SELECT vehicletype, state, make, model, year, color, violation_type, gender, geom  
FROM example  
WHERE date_of_stop = '2013-09-05' AND  
geom <> 'NULL' AND  
arrest_type = 'Q - Marked Laser'  

This will produce about 270 results since we removed any null values from the geom column.

Next to the geom column, you'll see a blue button with an eye icon. This is where the geometry view is. Click that button and you'll see all the results displayed on an OpenStreetMap map.

You'll notice right away that there is a long line of points in a row. Let's investigate that by zooming in on the map. As you might be able to see, these run down a major highway, I-270.

To view only those point on I-270, we can run another query that will only select those points.

SELECT date_of_stop, location, geom  
FROM example  
WHERE  
date_of_stop = '2013-09-05' AND  
arrest_type = 'Q - Marked Laser' AND  
location like '%270%'  

You'll get about 66 points here. Viewing them in the geometry viewer you'll see them across the entire highway.

And that's only for a single day.

Keep in mind

There is a caveat, however, to the geometry viewer and that has to do with the SRID. Since the geometry viewer uses OpenStreetMap maps, your data must be set to SRID 4326. If it isn't, then the points will be displayed, but the map will be blank - it will only be a white surface. Therefore, if you want to view data on OpenStreetMap take that into consideration. If your maps are already projected into another SRID and you have the borders of your data within another database, you can join those together to generate a map in this viewer.

Summing up

This is a short tutorial introducing you to pgAdmin's geometry viewer. It's a really handy tool that can benefit GIS developers who want to run geospatial queries and view the results in the browser. It's particularly useful in situations where you're testing geospatial queries, but only if they the geometry data is set to SRID 4326. Nonetheless, we're looking forward to improvements to this tool, in particular supporting other SRIDs.