Page 1 of 3 (52 posts)

  • talks about »
  • postgis

Tags

Last update:
Tue Aug 14 12:35:12 2018

A Django site.

QGIS Planet

Movement data in GIS #15: writing a PL/pgSQL stop detection function for PostGIS trajectories

Do you sometimes start writing an SQL query and around at line 50 you get the feeling that it might be getting out of hand? If so, it might be useful to start breaking it down into smaller chunks and wrap those up into custom functions. Never done that? Don’t despair! There’s an excellent PL/pgSQL tutorial on postgresqltutorial.com to get you started.

To get an idea of the basic structure of a PL/pgSQL function and to proof that PostGIS datatypes work just fine in this context, here’s a basic function that takes a trajectory geometry and outputs its duration, i.e. the difference between its last and first timestamp:

CREATE OR REPLACE FUNCTION AG_Duration(traj geometry) 
RETURNS numeric LANGUAGE 'plpgsql'
AS $BODY$ 
BEGIN
RETURN ST_M(ST_EndPoint(traj))-ST_M(ST_StartPoint(traj));
END; $BODY$;

My end goal for this exercise was to implement a function that takes a trajectory and outputs the stops along this trajectory. Commonly, a stop is defined as a long stay within an area with a small radius. This leads us to the following definition:

CREATE OR REPLACE FUNCTION AG_DetectStops(
   traj geometry, 
   max_size numeric, 
   min_duration numeric)
RETURNS TABLE(sequence integer, geom geometry) 
-- implementation follows here!

Note how this function uses RETURNS TABLE to enable it to return all the stops that it finds. To add a line to the output table, we need to assign values to the sequence and geom variables and then use RETURN NEXT.

Another reason to use PL/pgSQL is that it enables us to write loops. And loops I wanted for my stop detection function! Specifically, I wanted to go through all the points in the trajectory:

FOR pt IN SELECT (ST_DumpPoints(traj)).geom LOOP
-- here comes the magic!
END LOOP;

Eventually the function should go through the trajectory and identify all segments that stay within an area with max_size diameter for at least min_duration time. To test for the area size, we can use:

IF ST_MaxDistance(segment,pt) <= max_size THEN is_stop := true; 

Putting everything together, my current implementation looks like this:

CREATE OR REPLACE FUNCTION AG_DetectStops(
   traj geometry,
   max_size numeric,
   min_duration numeric)
RETURNS TABLE(sequence integer, geom geometry) 
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE 
   pt geometry;
   segment geometry;
   is_stop boolean;
   previously_stopped boolean;
   stop_sequence integer;
   p1 geometry;
BEGIN
segment := NULL;
sequence := 0;
is_stop := false;
previously_stopped := false;
p1 := NULL;
FOR pt IN SELECT (ST_DumpPoints(traj)).geom LOOP
   IF segment IS NULL AND p1 IS NULL THEN 
      p1 := pt; 
   ELSIF segment IS NULL THEN 
      segment := ST_MakeLine(p1,pt); 
      p1 := NULL;
      IF ST_Length(segment) <= max_size THEN is_stop := true; END IF; 
   ELSE 
      segment := ST_AddPoint(segment,pt); 
      -- if we're in a stop, we want to grow the segment, otherwise we remove points to the specified min_duration 
      IF NOT is_stop THEN 
         WHILE ST_NPoints(segment) > 2 AND AG_Duration(ST_RemovePoint(segment,0)) >= min_duration LOOP
            segment := ST_RemovePoint(segment,0); 
         END LOOP;
      END IF;
      -- a stop is identified if the segment stays within a circle of diameter = max_size
      IF ST_Length(segment) <= max_size THEN is_stop := true; 
      ELSIF ST_Distance(ST_StartPoint(segment),pt) > max_size THEN is_stop := false;
      ELSIF ST_MaxDistance(segment,pt) <= max_size THEN is_stop := true; 
      ELSE is_stop := false; 
      END IF; 
      -- if we found the end of a stop, we need to check if it lasted long enough 
      IF NOT is_stop AND previously_stopped THEN 
         IF ST_M(ST_PointN(segment,ST_NPoints(segment)-1))-ST_M(ST_StartPoint(segment)) >= min_duration THEN
            geom := ST_RemovePoint(segment,ST_NPoints(segment)-1); 
            RETURN NEXT;
            sequence := sequence + 1;
            segment := NULL;
            p1 := pt;
         END IF;
      END IF;
   END IF;
   previously_stopped := is_stop;
END LOOP;
IF previously_stopped AND AG_Duration(segment) >= min_duration THEN 
   geom := segment; 
   RETURN NEXT; 
END IF;
END; $BODY$;

While this function is not really short, it’s so much more readable than my previous attempts of doing this in pure SQL. Some of the lines for determining is_stop are not strictly necessary but they do speed up processing.

Performance still isn’t quite where I’d like it to be. I suspect that all the adding and removing points from linestring geometries is not ideal. In general, it’s quicker to find shorter stops in smaller areas than longer stop in bigger areas.

Let’s test! 

Looking for a testing framework for PL/pgSQL, I found plpgunit on Github. While I did not end up using it, I did use its examples for inspiration to write a couple of tests, e.g.

CREATE OR REPLACE FUNCTION test.stop_at_beginning() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(
   ST_GeometryFromText('LinestringM(0 0 0, 0 0 1, 0.1 0.1 2, 2 2 3)'),
   1,1) stop 
)
SELECT ST_M(ST_StartPoint((stop).geom)), 
       ST_NPoints((stop).geom) FROM temp INTO t0, n0;	
IF t0 = 0 AND n0 = 3
   THEN RAISE INFO 'PASSED - Stop at the beginning of the trajectory';
   ELSE RAISE INFO 'FAILED - Stop at the beginning of the trajectory';
END IF;
END; $BODY$;

Basically, each test is yet another PL/pgSQL function that doesn’t return anything (i.e. returns void) but outputs messages about the status of the test. Here I made heavy use of the PERFORM statement which executes the provided function but discards the results:

Movement data in GIS #12: Why you should be using PostGIS trajectories

In short: both writing trajectory queries as well as executing them is considerably faster using PostGIS trajectories (as LinestringM) rather than the commonly used point-based approach.

Here are a couple of examples to give you an impression of the differences.

Spoiler alert! Trajectory queries are up to 500 times faster than comparable point-based queries.

A quick look at indexing

In both cases, we have indexed the tracker id, geometry, and time columns to speed up query processing.

The trajectory table has 3 indexes

  • gist (time_range)
  • gist (track gist_geometry_ops_nd)
  • btree (tracker)

The point-based table has 4 indexes

  • gist (pt)
  • btree (trajectory_id)
  • btree (tracker)
  • btree (t)

Length

First, let’s see how to determine trajectory length for all observed moving objects (identified by a tracker id).

Using the point-based approach, we first need to ensure that the points are in the correct temporal order, create the lines, and finally sum up their length:

WITH ordered AS (
 SELECT trajectory_id, tracker, t, pt
 FROM geolife.trajectory_pt
 ORDER BY t
), tmp AS (
 SELECT trajectory_id, tracker, st_makeline(pt) traj
 FROM ordered 
 GROUP BY trajectory_id, tracker
)
SELECT tracker, round(sum(ST_Length(traj::geography)))
FROM tmp
GROUP BY tracker 
ORDER BY tracker

With trajectories, we can go right to computing lengths:

SELECT tracker, round(sum(ST_Length(track::geography)))
FROM geolife.trajectory_ext
GROUP BY tracker
ORDER BY tracker

On my test system, the trajectory query run time is 22.7 sec instead of 43.0 sec for the point-based approach:

Duration

Compared to trajectory length, duration is less complicated in the point-based approach:

WITH tmp AS (
 SELECT trajectory_id, tracker, min(t) start_time, max(t) end_time
 FROM geolife.trajectory_pt
 GROUP BY trajectory_id, tracker
)
SELECT tracker, sum(end_time - start_time)
FROM tmp
GROUP BY tracker
ORDER BY tracker

Still, the trajectory query is less complex and much faster at 31 ms instead of 6.0 sec:

SELECT tracker, sum(upper(time_range) - lower(time_range))
FROM geolife.trajectory_ext
GROUP BY tracker
ORDER BY tracker

Temporal filter

Extracting trajectories that occurred during a certain time frame is another common use case:

WITH tmp AS (
 SELECT trajectory_id, tracker, min(t) start_time, max(t) end_time
 FROM geolife.trajectory_pt
 GROUP BY trajectory_id, tracker
)
SELECT trajectory_id, tracker, start_time, end_time
FROM tmp
WHERE end_time > '2008-11-26 11:00'
AND start_time < '2008-11-26 15:00'
ORDER BY tracker

This point-based query takes 6.0 sec while the shorter trajectory query finishes in 12 ms:

SELECT id, tracker, time_range
FROM geolife.trajectory_ext
WHERE time_range && '[2008-11-26 11:00+1,2008-11-26 15:00+01]'::tstzrange

or equally fast (12 ms) by making use of the n-dimensional index:

WHERE track &&&	ST_Collect(
 ST_MakePointM(-180, -90, extract(epoch from '2008-11-26 11:00'::timestamptz)),
 ST_MakePointM(180, 90, extract(epoch from '2008-11-26 15:00'::timestamptz))
)

Spatial filter

Finally, of course, let’s have a look at spatial filters, for example, trajectories that start in a certain area:

WITH my AS ( 
 SELECT ST_Buffer(ST_SetSRID(ST_MakePoint(116.31894,39.97472),4326),0.0005) areaA
), tmp AS (
 SELECT trajectory_id, tracker, min(t) t 
 FROM geolife.trajectory_pt
 GROUP BY trajectory_id, tracker
)
SELECT distinct traj.tracker, traj.trajectory_id 
FROM tmp
JOIN geolife.trajectory_pt traj
ON tmp.trajectory_id = traj.trajectory_id AND traj.t = tmp.t
JOIN my
ON ST_Within(traj.pt, my.areaA)

This point-based query takes 6.0 sec while the shorter trajectory query finishes in 488 ms:

WITH my AS ( 
 SELECT ST_Buffer(ST_SetSRID(ST_MakePoint(116.31894, 39.97472),4326),0.0005) areaA
)
SELECT id, tracker, ST_AsText(track)
FROM geolife.trajectory_ext
JOIN my
ON areaA && track
AND ST_Within(ST_StartPoint(track), areaA)

For more generic “does this trajectory intersect another geometry”, the points can also be aggregated to a linestring on the fly but that takes 21.9 sec:

I’ll be presenting more work on PostGIS trajectories at GI_Forum in Salzburg in July. In the talk, I’ll also have a look at the custom PG-Trajectory datatype. Here’s the full open-access paper:

Graser, A. (2018) Evaluating Spatio-temporal Data Models for Trajectories in PostGIS Databases. GI_Forum ‒ Journal of Geographic Information Science, 1-2018, 16-33. DOI: 10.1553/giscience2018_01_s16.


This post is part of a series. Read more about movement data in GIS.

Movement data in GIS #10: open tools for AIS tracks from MarineCadastre.gov

MarineCadastre.gov is a great source for AIS data along the US coast. Their data formats and tools though are less open. Luckily, GDAL – and therefore QGIS – can read ESRI File Geodatabases (.gdb).

MarineCadastre.gov also offer a Track Builder script that creates lines out of the broadcast points. (It can also join additional information from the vessel and voyage layers.) We could reproduce the line creation step using tools such as Processing’s Point to path but this post will show how to create PostGIS trajectories instead.

First, we have to import the points into PostGIS using either DB Manager or Processing’s Import into PostGIS tool:

Then we can create the trajectories. I’ve opted to create a materialized view:

The first part of the query creates a temporary table called ptm (short for PointM). This step adds time stamp information to each point. The second part of the query then aggregates these PointMs into trajectories of type LineStringM.

CREATE MATERIALIZED VIEW ais.trajectory AS
 WITH ptm AS (
   SELECT b.mmsi,
     st_makepointm(
       st_x(b.geom), 
       st_y(b.geom), 
       date_part('epoch', b.basedatetime)
     ) AS pt,
     b.basedatetime t
   FROM ais.broadcast b
   ORDER BY mmsi, basedatetime
 )
 SELECT row_number() OVER () AS id,
   st_makeline(ptm.pt) AS st_makeline,
   ptm.mmsi,
   min(ptm.t) AS min_t,
   max(ptm.t) AS max_t
 FROM ptm
 GROUP BY ptm.mmsi
WITH DATA;

The trajectory start and end times (min_t and max_t) are optional but they can help speed up future queries.

One of the advantages of creating trajectory lines is that they render many times faster than the original points.

Of course, we end up with some artifacts at the border of the dataset extent. (Files are split by UTM zone.) Trajectories connect the last known position before the vessel left the observed area with the position of reentry. This results, for example, in vertical lines which you can see in the bottom left corner of the above screenshot.

With the trajectories ready, we can go ahead and start exploring the dataset. For example, we can visualize trajectory speed and/or create animations:

Purple trajectory segments are slow while green segments are faster

We can also perform trajectory analysis, such as trajectory generalization:

This is a first proof of concept. It would be great to have a script that automatically fetches the datasets for a specified time frame and list of UTM zones and loads them into PostGIS for further processing. In addition, it would be great to also make use of the information in the vessel and voyage tables, thus splitting up trajectories into individual voyages.


Read more:

Refresh your maps FROM postgreSQL !

Continuing our love story with PostgreSQL and QGIS, we asked QGIS.org a grant application during early 2017 spring.

The idea was to take benefit of very advanced PostgreSQL features, that probably never were used in a Desktop GIS client before.

Today, let’s see what we can do with the PostgreSQL NOTIFY feature!

Ever dreamt of being able to trigger things from outside QGIS? Ever wanted a magic stick to trigger actions in some clients from a database action?

X All The Y Meme | REFRESH QGIS FROM THE DATABASE !!! | image tagged in memes,x all the y | made w/ Imgflip meme maker

 

NOTIFY is a PostgreSQL specific feature allowing to generate notifications on a channel and optionally send a message — a payload in PG’s dialect .

In short, from within a transaction, we can raise a signal in a PostgreSQL queue and listen to it from a client.

In action

We hardcoded a channel named “qgis” and made QGIS able to LISTEN to NOTIFY events and transform them into Qt’s signals. The signals are connected to layer refresh when you switch on this rendering option.

Optionnally, adding a message filter will only redraw the layer for some specific events.

This mechanism is really versatile and we now can imagine many possibilities, maybe like trigger a notification message to your users from the database, interact with plugins, or even code a chat between users of the same database  (ok, this is stupid) !

 

More than just refresh layers?

The first implementation we chose was to trigger a layer refresh because we believe this is a good way for users to discover this new feature.

But QGIS rocks hey, doing crazy things for limited uses is not the way.

Thanks to feedback on the Pull Request, we added the possibility to trigger layer actions on notification.

That should be pretty versatile since you can do almost anything with those actions now.

Caveats

QGIS will open a permanent connection to PostgreSQL to watch the notify signals. Please keep that in mind if you have several clients and a limited number of connections.

Notify signals are only transmitted with the transaction, so when the COMMIT is raised. So be aware that this might not help you if users are inside an edit session.

QGIS has a lot of different caches, for attribute table for instance. We currently have no specific way to invalidate a specific cache, and then order QGIS to refresh it’s attribute table.

There is no way in PG to list all channels of a database session, that’s why we couldn’t propose a combobox list of available signals in the renderer option dialog. Anyway, to avoid too many issues, we decided to hardcode the channel name in QGIS with the name “qgis”. If this is somehow not enough for your needs, please contact us!

Conclusion

The github pull request is here : https://github.com/qgis/QGIS/pull/5179

We are convinced this would be really useful for real time application, let us know if that makes some bells ring on your side!

More to come soon, stay tuned!

 

 

Undo Redo stack is back QGIS Transaction groups

Let’s keep on looking at what we did in QGIS.org grant application of early 2017 spring.

At Oslandia, we use a lot the transaction groups option of QGIS. It was an experimental feature in QGIS 2.X allowing to open only one common Postgres transaction for all layers sharing the same connection string.

Transaction group option

When activated, that option will bring many killer features:

  • Users can switch all the layers in edit mode at once. A real time saver.
  • Every INSERT, UPDATE or DELETE is forwarded immediately to the database, which is nice for:
    • Evaluating on the fly if database constraints are satisfied or not. Without transaction groups this is only done when saving the edits and this can be frustrating to create dozens of features and having one of them rejected because of a foreign key constraint…
    • Having triggers evaluated on the fly.  QGIS is so powerful when dealing with “thick database” concepts that I would never go back to a pure GIS ignoring how powerful databases can be !
    • Playing with QgsTransaction.ExecuteSQL allows to trigger stored procedures in PostgreSQL in a beautiful API style interface. Something like
SELECT invert_pipe_direction('pipe1');
  • However, the implementation was flagged “experimental” because some caveats where still causing issues:
    • Committing on the fly was breaking the logic of the undo/redo stack. So there was no way to do a local edit. No Ctrl+Z!  The only way to rollback was to stop the edit session and loose all the work. Ouch.. Bad!
    • Playing with ExecuteSQL did not dirty the QGIS edit buffer. So, if during an edit session no edit action was made using QGIS native tools, there was no clean way to activate the “save edits” icon.
    • When having some failures in the triggers, QGIS may loose DB connection and thus create a silent ROLLBACK.

We decided to try to restore the undo/redo stack by saving the history edits in PostgreSQL SAVEPOINTS and see if we could restore the original feature in QGIS.

And.. it worked!

Let’s see that in action:

 

Potential caveats ?

At start, we worried about how heavy all those savepoints would be for the database. It turns out that maybe for really massive geometries, and heavy editing sessions, this could start to weight a bit, but honestly far away from PostgreSQL capabilities.

 

Up to now, we didn’t really find any issue with that..

And we didn’t address the silent ROLLBACK that occurs sometimes, because it is generated by buggy stored procedures, easy to solve.

Some new ideas came to us when working in that area. For instance, if a transaction locks a feature, QGIS just… wait for the lock to be released. I think we should find a way to advertise those locks to the users, that would be great! If you’re interested in making that happen, please contact us.

 

More to come soon, stay tuned!

 

 

Drive-time Isochrones from a single Shapefile using QGIS, PostGIS, and Pgrouting

This is a guest post by Chris Kohler .

Introduction:

This guide provides step-by-step instructions to produce drive-time isochrones using a single vector shapefile. The method described here involves building a routing network using a single vector shapefile of your roads data within a Virtual Box. Furthermore, the network is built by creating start and end nodes (source and target nodes) on each road segment. We will use Postgresql, with PostGIS and Pgrouting extensions, as our database. Please consider this type of routing to be fair, regarding accuracy, as the routing algorithms are based off the nodes locations and not specific addresses. I am currently working on an improved workflow to have site address points serve as nodes to optimize results. One of the many benefits of this workflow is no financial cost to produce (outside collecting your roads data). I will provide instructions for creating, and using your virtual machine within this guide.

Steps:–Getting Virtual Box(begin)–

Intro 1. Download/Install Oracle VM(https://www.virtualbox.org/wiki/Downloads)

Intro 2. Start the download/install OSGeo-Live 11(https://live.osgeo.org/en/overview/overview.html).

Pictures used in this workflow will show 10.5, though version 11 can be applied similarly. Make sure you download the version: osgeo-live-11-amd64.iso. If you have trouble finding it, here is the direct link to the download (https://sourceforge.net/projects/osgeo-live/files/10.5/osgeo-live-10.5-amd64.iso/download)
Intro 3. Ready for virtual machine creation: We will utilize the downloaded OSGeo-Live 11 suite with a virtual machine we create to begin our workflow. The steps to create your virtual machine are listed below. Also, here are steps from an earlier workshop with additional details with setting up your virtual machine with osgeo live(http://workshop.pgrouting.org/2.2.10/en/chapters/installation.html).

1.  Create Virutal Machine: In this step we begin creating the virtual machine housing our database.

Open Oracle VM VirtualBox Manager and select “New” located at the top left of the window.

VBstep1

Then fill out name, operating system, memory, etc. to create your first VM.

vbstep1.2

2. Add IDE Controller:  The purpose of this step is to create a placeholder for the osgeo 11 suite to be implemented. In the virtual box main window, right-click your newly-created vm and open the settings.

vbstep2

In the settings window, on the left side select the storage tab.

Find “adds new storage controller button located at the bottom of the tab. Be careful of other buttons labeled “adds new storage attachment”! Select “adds new storage controller button and a drop-down menu will appear. From the top of the drop-down select “Add IDE Controller”.

vbstep2.2

vbstep2.3

You will see a new item appear in the center of the window under the “Storage Tree”.

3.  Add Optical Drive: The osgeo 11 suite will be implemented into the virtual machine via an optical drive. Highlight the new controller IDE you created and select “add optical drive”.

vbstep3

A new window will pop-up and select “Choose Disk”.

vbstep3.2

Locate your downloaded file “osgeo-live 11 amd64.iso” and click open. A new object should appear in the middle window under your new controller displaying “osgeo-live-11.0-amd64.iso”.

vbstep3.3

Finally your virtual machine is ready for use.
Start your new Virtual Box, then wait and follow the onscreen prompts to begin using your virtual machine.

vbstep3.4

–Getting Virtual Box(end)—

4. Creating the routing database, and both extensions (postgis, pgrouting): The database we create and both extensions we add will provide the functions capable of producing isochrones.

To begin, start by opening the command line tool (hold control+left-alt+T) then log in to postgresql by typing “psql -U user;” into the command line and then press Enter. For the purpose of clear instruction I will refer to database name in this guide as “routing”, feel free to choose your own database name. Please input the command, seen in the figure below, to create the database:

CREATE DATABASE routing;

You can use “\c routing” to connect to the database after creation.

step4

The next step after creating and connecting to your new database is to create both extensions. I find it easier to take two-birds-with-one-stone typing “psql -U user routing;” this will simultaneously log you into postgresql and your routing database.

When your logged into your database, apply the commands below to add both extensions

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

step4.2

step4.3

5. Load shapefile to database: In this next step, the shapefile of your roads data must be placed into your virtual machine and further into your database.

My method is using email to send myself the roads shapefile then download and copy it from within my virtual machines web browser. From the desktop of your Virtual Machine, open the folder named “Databases” and select the application “shape2pgsql”.

step5

Follow the UI of shp2pgsql to connect to your routing database you created in Step 4.

step5.2

Next, select “Add File” and find your roads shapefile (in this guide we will call our shapefile “roads_table”) you want to use for your isochrones and click Open.

step5.3

Finally, click “Import” to place your shapefile into your routing database.

6. Add source & target columns: The purpose of this step is to create columns which will serve as placeholders for our nodes data we create later.

There are multiple ways to add these columns into the roads_table. The most important part of this step is which table you choose to edit, the names of the columns you create, and the format of the columns. Take time to ensure the source & target columns are integer format. Below are the commands used in your command line for these functions.

ALTER TABLE roads_table ADD COLUMN "source" integer;
ALTER TABLE roads_table ADD COLUMN "target" integer;

step6

step6.2

7. Create topology: Next, we will use a function to attach a node to each end of every road segment in the roads_table. The function in this step will create these nodes. These newly-created nodes will be stored in the source and target columns we created earlier in step 6.

As well as creating nodes, this function will also create a new table which will contain all these nodes. The suffix “_vertices_pgr” is added to the name of your shapefile to create this new table. For example, using our guide’s shapefile name , “roads_table”, the nodes table will be named accordingly: roads_table_vertices_pgr. However, we will not use the new table created from this function (roads_table_vertices_pgr). Below is the function, and a second simplified version, to be used in the command line for populating our source and target columns, in other words creating our network topology. Note the input format, the “geom” column in my case was called “the_geom” within my shapefile:

pgr_createTopology('roads_table', 0.001, 'geom', 'id',
 'source', 'target', rows_where := 'true', clean := f)

step7

Here is a direct link for more information on this function: http://docs.pgrouting.org/2.3/en/src/topology/doc/pgr_createTopology.html#pgr-create-topology

Below is an example(simplified) function for my roads shapefile:

SELECT pgr_createTopology('roads_table', 0.001, 'the_geom', 'id')

8. Create a second nodes table: A second nodes table will be created for later use. This second node table will contain the node data generated from pgr_createtopology function and be named “node”. Below is the command function for this process. Fill in your appropriate source and target fields following the manner seen in the command below, as well as your shapefile name.

To begin, find the folder on the Virtual Machines desktop named “Databases” and open the program “pgAdmin lll” located within.

step8

Connect to your routing database in pgAdmin window. Then highlight your routing database, and find “SQL” tool at the top of the pgAdmin window. The tool resembles a small magnifying glass.

step8.2

We input the below function into the SQL window of pgAdmin. Feel free to refer to this link for further information: (https://anitagraser.com/2011/02/07/a-beginners-guide-to-pgrouting/)

CREATE TABLE node AS
   SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,
          foo.p AS the_geom
   FROM (     
      SELECT DISTINCT roads_table.source AS p FROM roads_table
      UNION
      SELECT DISTINCT roads_table.target AS p FROM roads_table
   ) foo
   GROUP BY foo.p;

step8.3

  1.  Create a routable network: After creating the second node table from step 8,  we will combine this node table(node) with our shapefile(roads_table) into one, new, table(network) that will be used as the routing network. This table will be called “network” and will be capable of processing routing queries.  Please input this command and execute in SQL pgAdmin tool as we did in step 8. Here is a reference for more information:(https://anitagraser.com/2011/02/07/a-beginners-guide-to-pgrouting/)   

step8.2

 

CREATE TABLE network AS
   SELECT a.*, b.id as start_id, c.id as end_id
   FROM roads_table AS a
      JOIN node AS b ON a.source = b.the_geom
      JOIN node AS c ON a.target = c.the_geom;

step9.2

10. Create a “noded” view of the network:  This new view will later be used to calculate the visual isochrones in later steps. Input this command and execute in SQL pgAdmin tool.

CREATE OR REPLACE VIEW network_nodes AS 
SELECT foo.id,
 st_centroid(st_collect(foo.pt)) AS geom 
FROM ( 
  SELECT network.source AS id,
         st_geometryn (st_multi(network.geom),1) AS pt 
  FROM network
  UNION 
  SELECT network.target AS id, 
         st_boundary(st_multi(network.geom)) AS pt 
  FROM network) foo 
GROUP BY foo.id;

step10

11.​ Add column for speed:​ This step may, or may not, apply if your original shapefile contained a field of values for road speeds.

In reality a network of roads will typically contain multiple speed limits. The shapefile you choose may have a speed field, otherwise the discrimination for the following steps will not allow varying speeds to be applied to your routing network respectfully.

If values of speed exists in your shapefile we will implement these values into a new field, “traveltime“, that will show rate of travel for every road segment in our network based off their geometry. Firstly, we will need to create a column to store individual traveling speeds. The name of our column will be “traveltime” using the format: ​double precision.​ Input this command and execute in the command line tool as seen below.

ALTER TABLE network ADD COLUMN traveltime double precision;

step11

Next, we will populate the new column “traveltime” by calculating traveling speeds using an equation. This equation will take each road segments geometry(shape_leng) and divide by the rate of travel(either mph or kph). The sample command I’m using below utilizes mph as the rate while our geometry(shape_leng) units for my roads_table is in feet​. If you are using either mph or kph, input this command and execute in SQL pgAdmin tool. Below further details explain the variable “X”.

UPDATE network SET traveltime = shape_leng / X*60

step11.2

How to find X​, ​here is an example​: Using example 30 mph as rate. To find X, we convert 30 miles to feet, we know 5280 ft = 1 mile, so we multiply 30 by 5280 and this gives us 158400 ft. Our rate has been converted from 30 miles per hour to 158400 feet per hour. For a rate of 30 mph, our equation for the field “traveltime”  equates to “shape_leng / 158400*60″. To discriminate this calculations output, we will insert additional details such as “where speed = 30;”. What this additional detail does is apply our calculated output to features with a “30” value in our “speed” field. Note: your “speed” field may be named differently.

UPDATE network SET traveltime = shape_leng / 158400*60 where speed = 30;

Repeat this step for each speed value in your shapefile examples:

UPDATE network SET traveltime = shape_leng / X*60 where speed = 45;
UPDATE network SET traveltime = shape_leng / X*60 where speed = 55;

The back end is done. Great Job!

Our next step will be visualizing our data in QGIS. Open and connect QGIS to your routing database by right-clicking “PostGIS” in the Browser Panel within QGIS main window. Confirm the checkbox “Also list tables with no geometry” is checked to allow you to see the interior of your database more clearly. Fill out the name or your routing database and click “OK”.

If done correctly, from QGIS you will have access to tables and views created in your routing database. Feel free to visualize your network by drag-and-drop the network table into your QGIS Layers Panel. From here you can use the identify tool to select each road segment, and see the source and target nodes contained within that road segment. The node you choose will be used in the next step to create the views of drive-time.

12.Create views​: In this step, we create views from a function designed to determine the travel time cost. Transforming these views with tools will visualize the travel time costs as isochrones.

The command below will be how you start querying your database to create drive-time isochrones. Begin in QGIS by draging your network table into the contents. The visual will show your network as vector(lines). Simply select the road segment closest to your point of interest you would like to build your isochrone around. Then identify the road segment using the identify tool and locate the source and target fields.

step12

step12.2

Place the source or target field value in the below command where you see ​VALUE​, in all caps​.

This will serve you now as an isochrone catchment function for this workflow. Please feel free to use this command repeatedly for creating new isochrones by substituting the source value. Please input this command and execute in SQL pgAdmin tool.

*AT THE BOTTOM OF THIS WORKFLOW I PROVIDED AN EXAMPLE USING SOURCE VALUE “2022”

CREATE OR REPLACE VIEW "​view_name" AS 
SELECT di.seq, 
       di.id1, 
       di.id2, 
       di.cost, 
       pt.id, 
       pt.geom 
FROM pgr_drivingdistance('SELECT
     gid::integer AS id, 
     Source::integer AS source, 
     Target::integer AS target,                                    
     Traveltime::double precision AS cost 
       FROM network'::text, ​VALUE::bigint, 
    100000::double precision, false, false)
    di(seq, id1, id2, cost)
JOIN network_nodes pt ON di.id1 = pt.id;

step12.3

13.Visualize Isochrone: Applying tools to the view will allow us to adjust the visual aspect to a more suitable isochrone overlay.

​After creating your view, a new item in your routing database is created, using the “view_name” you chose. Drag-and-drop this item into your QGIS LayersPanel. You will see lots of small dots which represent the nodes.

In the figure below, I named my view “take1“.

step13

Each node you see contains a drive-time value, “cost”, which represents the time used to travel from the node you input in step 12’s function.

step13.2

Start by installing the QGIS plug-in Interpolation” by opening the Plugin Manager in QGIS interface.

step13.3

Next, at the top of QGIS window select “Raster” and a drop-down will appear, select “Interpolation”.

step13.4

 

A new window pops up and asks you for input.

step13.5

Select your “​view”​ as the​ vector layer​, select ​”cost​” as your ​interpolation attribute​, and then click “Add”.

step13.6

A new vector layer will show up in the bottom of the window, take care the type is Points. For output, on the other half of the window, keep the interpolation method as “TIN”, edit the ​output file​ location and name. Check the box “​Add result to project​”.

Note: decreasing the cellsize of X and Y will increase the resolution but at the cost of performance.

Click “OK” on the bottom right of the window.

step13.7

A black and white raster will appear in QGIS, also in the Layers Panel a new item was created.

step13.8

Take some time to visualize the raster by coloring and adjusting values in symbology until you are comfortable with the look.

step13.9

step13.10

14. ​Create contours of our isochrone:​ Contours can be calculated from the isochrone as well.

Find near the top of QGIS window, open the “Raster” menu drop-down and select Extraction → Contour.

step14

Fill out the appropriate interval between contour lines but leave the check box “Attribute name” unchecked. Click “OK”.

step14.2

step14.3

15.​ Zip and Share:​ Find where you saved your TIN and contours, compress them in a zip folder by highlighting them both and right-click to select “compress”. Email the compressed folder to yourself to export out of your virtual machine.

Example Isochrone catchment for this workflow:

CREATE OR REPLACE VIEW "2022" AS 
SELECT di.seq, Di.id1, Di.id2, Di.cost,                           
       Pt.id, Pt.geom 
FROM pgr_drivingdistance('SELECT gid::integer AS id,                                       
     Source::integer AS source, Target::integer AS target, 
     Traveltime::double precision AS cost FROM network'::text, 
     2022::bigint, 100000::double precision, false, false) 
   di(seq, id1, id2, cost) 
JOIN netowrk_nodes pt 
ON di.id1 = pt.id;

References: Virtual Box ORACLE VM, OSGeo-Live 11  amd64 iso, Workshop FOSS4G Bonn(​http://workshop.pgrouting.org/2.2.10/en/index.html​),

QGIS versioning plugin

We developped a tool to manage data history, branches, and to work offline with your PostGIS-stored data and QGIS. Read more to get the insight of QGIS Versioning plugin.

The QGIS plugin is available in QGIS plugin repository, and you can `fork it on GitHub too https://github.com/Oslandia/qgis-versioning !

Introduction

Even if the necessity of data versioning often arises, no standard solution exist for databases.

The GeoGit project proposes a solution to store versioned geospatial data. There is also an existing plugin for QGIS, pgversion, which uses views and triggers to version a PostGIS database. Unfortunately those solutions were not adapted to the specific constrains of this project, namely: using a PostGIS database as the main repository (excludes GeoGit) and the ability to working off-line (excludes pgversion).The project we developed QGIS/PostGIS versioning looks like the following.

 

Design

The database is stored in a PostGIS schema, the complete schema is versioned (i.e. not individual tables). Revisions are identified by a revision number. A revision table in the versioned schema, called ‘revisions’, keeps track of the date, author, commit message and branch of all revisions.

Once a table structure is defined, three operations can be performed on rows: INSERT, DELETE and UPDATE. To be able to track history, every row is kept in the tables. Deleted rows are marked as such and updated rows are a combined insertion-deletion where the deleted and added rows are linked to one another as parent and child.|

A total of five columns are needed for versioning the first branch:

PRIMARY KEY
a unique identifier across the table

branch_rev_begin
revision when this record was inserted

branch_rev_end
last revision for which this record exist (i.e. revision when it was deleted minus one)

branch_parent
in case the row has been inserted as the result of an update, this fields stores the hid of the row that has been updated

branch_child
in case the row has been marked as deleted as the result of an update, this field stores the hid of the row that has been inserted in its place.

For each additional branch, four additional columns are needed (the ones with the prefix branch_).

Note:
If the branch_rev_begin is null, it means that a row belongs to another branch.

SQL views are used to see the database for a given revision number. If we note ‘rev’ the revision we want to see. For each table, the condition for a row to be present is the view is::

(branch_rev_end IS NULL OR branch_rev_end >= rev) AND branch_rev_begin <= rev

In the special case of the current revision, or head revision, the condition reads::

branch_rev_end IS NULL AND branch_rev_begin IS NOT NULL

Note:
Since elements are not deleted (but merely marked as such) from an historized table, care must be taken with the definition of constrains, in particular the conceptual unicity of a field values.

Withing the PostGIS database, the views on revisions must be read-only  and historized tables should not be edited directly. This is a basic principle for version control: editions must be made to working copies an then committed to the database. Please note that by default PostGIS 9.3 creates updatable views.

Workflow schema

This setup allows for multiple users to use and edit data offline from a central repository, and commit their modifications concurrently.

Working copies

Two kinds of working copies are available:

SpatiaLite working copies
They are meant to be used off-line. They consist of the versioned tables of a given versioned database (i.e. PostGIS schema) or any subset. For each table, only the elements that have not been marked as deleted in the head revision need to be present. Furthermore only a subset of the elements the user needs to edit can be selected (e.g. a spatial extend).  To create a working copy (i.e. to checkout), tables from the versioned schema (or the aforementioned subsets) are converted to a SpatiaLite database using ogr2ogr.

PostGIS working copies
They are meant to be used when the connection to the original database will remain available. They are quite similar to pgversion working copies since they only store differences from a given revision (the one checked out).

The following description is aimed at understanding the inner workings of the qgis versioning plugin. The user does not need to perform the described operations manually.

For each versioned table in the working copy, a view is created with the suffix _view (e.g. mytable_view). Those views typically filters out the historization columns and shows the head revision. A set of triggers is defined to allow updating on those views (DELETE, UPDATE and INSERT).

The DELETE trigger simply marks the end revision of a given record.

The INSERT trigger create a new record and fills the branch_rev_begin field.

The UPDATE trigger create a new record and fills the branch_rev_begin and branch_parent fields. It then marks the parent record as deleted, and fills the branch_rev_end and branch_child fields.

Updating the working copy

Changes can be made to the database while editing the working copy. In order to reconcile those edition, the user needs to update the working copy.

When updating, a set of records can be in conflicts: the records for which the end revision has been set since the initial checkout or last update if any.

Multiple editions can be made to the same record. Therefore the child relation must be followed to the last child in order to present tu user with the latest state of a given conflicting feature.

Conflicts are stored in a table and identified with a conflict id and the tag ‘theirs’ or ‘mine’. A DELETE trigger on this table is used for conflict resolution. On deletion of ‘mine’, the working copy edition is discarded, on deletion of ‘theirs’ the working copy edition is appended to the feature history (i.e. the working copy feature becomes a child of the last state of the feature in the historized database).

Committing the editions to the versionned database

If a working copy is up to date, the editions can be integrated in the versioned database. This operation consists simply in the insertion of a record in the revisions table, and, for each versioned table, the update of rows that are different and inserting rows that are not present.

Branching

A branch can be created from any revision by adding the four history columns and setting the branch_rev_begin field of features that are present in their revision.

Plugin interface tutorial

Groups are used for all versioning operations in QGIS since the versions are for a complete PostGIS schema or SpatiaLite database.

The versioning toolbar will change depending on the group selected in the QGIS legend.

Note:
The group elements must share the same connection information (i.e. share the same database and schema for PostGIS working copies and revision views or share same SpatiaLite database for SpatiaLite working copies).

Versioning a PostGIS schema

Starting with an unversioned database, import a number of layers from a schema that needs to be versioned into a QGIS project.

Once the layers are imported, they must be grouped together.

Selecting the newly created group will cause the versioning toolbar to display the historize button (green V). On click a confirmation is requested to version the database schema.

The versioned layers are imported in a new group and the original layers are removed from the project.

Note:
The symobology is not kept in the process.

Working with a versioned PostGIS schema

Versioned layers can be imported in QGIS. The layers must be from a head revision or a view on any revision.

 

Once the layers are in QGIS, they must be grouped.

 

For PostGIS groups at head revision, the versioning plugin allows the user to create a SpatiaLite or a PostGIS working copy, create a view on a given revision or create a branch. A corresponding group will be imported in QGIS.

If the user chooses to create a SpatiaLite working copy, he will be asked to select a file to store the working copy.

 

Commiting changes

Once the working copy is imported in QGIS, the user can start edition of its layers. With SpatiaLite working copies, this edition can be done off-line.

When the user is done with edition, he can commit the changes to the database and if commit is feasible (i.e. the working copy is up to date with the versioned database), he will be prompted for a commit message and subsequently be informed of the revision number he committed.

 

If the commit is not feasible, the user will be informed that he must update his working copy prior to commit.

Resolving conflicts

Conflicts are detected during update, the user is informed, and conflicts layers are imported into QGIS.

To resolve conflicts, the user can open the conflict layer’s attribute table. Selected entries are also selected on the map canvas and the user can decide which version, either his or the database’s, he wants to keep. User version is tagged with ‘mine’ and database version with ‘theirs’. The conflict is resolved by deleting the unwanted entry in the conflict layer.

Note:
On deletion of one conflict entry, both entries are removed (by a trigger) but the attribute table (and canvas) are not refreshed. As a workaround, the user can close and re-open the attribute table to see the actual state of the conflict table.

Once the conflict table is empty, the commit can be done.

Restrictions

Due to design choices and tools used for conversion to SpatiaLite, a number of restrictions apply to the versioned database:

  • |schemas, tables and branch names should not have space, caps or quotes
  • tables must have primary keys
  • columns are lowercase (because of conversion to SpatiaLite) but can have spaces (not that it’s recommended
  • geometry column is geom in PostGIS, GEOMETRY in SpatiaLite

Note
Do not edit OGC_FID or ROWID

Note
The constrains on the tables are be lost in the PostGIS to SpatiaLite conversion.

Known bug

The conflict layer won’t be loaded automatically is it has no geometry. The user will have to load it manually.

QGIS plugin for water management

Oslandia releases today a new plugin for the QGIS processing framework, allowing for water distribution network simulation. It integrates the opensource EPANET simulation software. EPANET models water distribution networks. It’s a widely used public-domain simulation software developed by the US Environmental Protection Agency.

Hydraulic simulation is used to understand water distribution in distribution network, to forecast the impact of network alterations, to dimension network elements or study extreme case scenarios (e.g. important demand for firefighting, pipes breakages, interruption in supply).

QGIS provides a graphical user interface that can be used to import/edit/export hydraulic model elements and simulation parameters from various sources, launch simulation and visualize results directly inside QGIS.

Hydraulic model

A hydraulic model consists of junctions (POINT) and pipes (LINESTRING) along with various other elements like tanks, pumps and valves. Those elements can be stored as features in a spatially enabled database. Features attributes can be simple (e.g. pipe diameter) or complex (e.g. pumps characteristic curves or water consumption). Complex attributes are stored via a foreign key in other alphanumeric tables.

This is the kind of data QGIS is designed to handle. It can import/export them from/to a variety of sources and also display and edit them.

Simulation parameters

Simulation parameters and options (e.g. simulation time step or accuracy) are key-value pairs. The values can be stored in a table which columns are keys. Each set of simulation parameters is then a record in this table. This kind of table can be loaded in QGIS as a vector layer without geometry.

Integration in the processing framework

Once the hydraulic model and simulation parameters are loaded in QGIS, the simulation can be launched through the Processing toolbox. The plugin uses the standalone command line interface of EPANET (CLI) which path needs to be specified in processing Options and configuration.

The plugin assembles an EPANET input file, runs EPANET and parses its output to generate result layers.

One interesting aspect with processing modules is that they can be used for chained processing: the user can use other modules to do additional transformations of simulation results, as feeding them into another simulation model.

Result visualization

Simulation results are water pressure and velocity at all points in the network along with state of network elements (e.g. volume in tanks, power of pumps) for all simulation time steps . This represent a huge amount of data that are usually displayed either as time-plots or as map-plots of time aggregated data (e.g. max and min during simulation).

Results of particular interest are:

  • time-plots of:
    • volume in reservoirs
    • flow at pumps
    • pressure in pipes and at junctions
  • map-plots of:
    • low speed (stagnation)
    • high and low pressure (risk of breakage, unhappy consumer)
    • lack of level variation in reservoirs (stagnation)
    • empty reservoir
    • reservoir overflow
    • abnormal pressure (typical of error in the altitude of a node in the model)
    • flow direction

QGIS is naturally suited for map-plots. Time-aggregated simulation results are automatically joined to map layers when the result table is added to the map. Rule-based symbology is used to highlight zones of concern (e.g. low water velocity or empty reservoirs).

The matplotlib library provides 2D plotting facilities in python and QGIS provides an extensive set of selection tools (on the map or in tables). The plugin’s button plots the appropriate value depending on the selected feature type (e.g. water level for tanks, pressure for junctions).

Screencast

For a full demo of this plugin, see the following video :

 

Where and who

The plugin is available on GitHub and should be available soon on QGIS plugin repository : https://github.com/Oslandia/qgis-epanet

This work has been funded by European Funds. Many thanks to the GIS Office of Apavil, Valcea County (Romania). Oslandia has developped this plugin, and provides support and development around QGIS, PostGIS and this plugin. Get in touch if you need more : [email protected]

We are looking for a free dataset with full informations (pumps, tanks, valves, pipes and their characteristics…) to distribute with this plugin as a test case and demonstration. If you can provide this, mail us !

We also are implementing a Processing plugin for SWMM, the public domain Waste-water simulation tool. If you are interested to participate to the development, please contact us.

PostGIS 3D &#8211; Foss4g video and workshop

The latest PostGIS and QGIS 3D enhancements presented at FOSS4G by Oslandia are available online.We suggest you to have a look on our PostGIS 3D / QGIS 3D video demonstration using SFCGAL library and the QGIS Horao plugin.

A step by step workshop, (really close to the video workflow) is also available online  https://github.com/Oslandia/Workshops/tree/master/FOSS4G_2013_PostGIS_3D

We can provide you the full virtual machine on demand, with proper software environment (6GB Virtual Box Image).

We would be really interested in having your advice on these new 3D features, and the use cases you could be interested in. Do not hesitate to get in touch.

Contact us at [email protected] for any information.

QGIS Community meeting in Brighton

Developers and contributors from the QGIS project are used to gather physically twice a year across different countries. Such an event allows people to synchronize their effort, and discuss new possible developments.cThe latest QGIS community meeting took place in Brighton from the 12th to the 16th of September, just before the FOSS4G event. It was the biggest community meeting organized so far, with close to 50 people attending ! Everything went smooth thanks to the perfect organization by Lutra Consulting.

This session was of particular interest in the project’s history, since it was dedicated to the release of the eagerly-awaited new 2.0 version of QGIS.

Oslandia is used to take part in the event and even organized the march 2012 session in Lyon.


Presentations

Despite being originally oriented toward code and translations, some presentations took place during the event. Some of them have been video recorded, some did not. Hereafter is a subset of them.

A new website

In parallel to the release of the 2.0 version, the QGIS website has been updated. Its look and feel, but also the way it is now build. Richard Duivenvoorde presented the efforts that have been put on the support of multiple languages, adaptation to mobile devices, and the reuse of tools used for building the documentation of the project. The new website is now online.

Richard presenting the new website

 

Presentation of the new website : http://www.ustream.tv/recorded/38687971

Constraints on attributes

Some more developer-oriented presentations and discussions also took place. Matthias Kuhn and Nathan Woodrow presented an idea about extending the way attributes are handled by QGIS. In particular, the concept of constrained attributes emerged. The idea is to be able to express, manipulate and edit contrains on attributes (possible range of values for instance) as it is found in databases. This could then be used to constrain user editing of layers, presenting to the user an appropriate widget (combo box for an enumeration for instance), especially for layers that do not have native support for these constraints.

QGIS for Android tablets

RealworldSystems presented their work on what they called the “QGIS Mobility framework”, based on previous works by Marco Bernasocchi on QGIS for Android. It is dedicated to the design of custom QGIS applications for deployment on Android tablets (for on-the-field editing campains for instance). It looks promising and has already been used in a real-world application for gaz pipeline inspection. The framework can be found on github.

QGIS webserver

Andreas Neumann presented evolutions of QGIS webserver and webclient. More can be found in the corresponding video.

Andreas presenting the work on QGIS webserver and webclient

Video 1 http://www.ustream.tv/recorded/38741015

Evolution of the Globe plugin

Matthias Kuhn presented evolutions he made to the Globe plugin that allows to display a 3D earth with different kinds of data on it. Lots of osgearth features are now integrated into the Globe plugin (in particular the support for 2D vector layers).

Matthias presenting its work on the Globe plugin

Video 2 http://www.ustream.tv/recorded/38737991

Visualisation of 3D data

Oslandia presented also its ongoing work on the integration of Postgis 3D. After a thourought evaluation of osgearth, which is the base of the Globe plugin, we decided to develop our own 3D visualisation stack directly on top of OpenSceneGraph.

A QGIS plugin has also been developed in order to be able to view QGIS layers in 3D.

With this new 3D visualisation stack we are able to display and manipulate data of a whole city between 20 and 60 frames per second on a laptop (here the demo has been designed on data from the city of Lyon) , when we were hardly able to display a small city quarter with Globe.

Oslandia presenting its work on its 3D visualisation stack

Video 3 http://www.ustream.tv/recorded/38738897

Slides https://github.com/Oslandia/presentations/tree/master/qgis_hf_2013

QGIS 2.0

All the work done during this community meeting allowed to polish the 2.0 version of QGIS which has been publicly announced during the FOSS4G in Nottingham by Tim Sutton.
Waiting now for the 2.1 release 🙂

Movement data in GIS: issues & ideas

Since I’ve started working, transport and movement data have been at the core of many of my projects. The spatial nature of movement data makes it interesting for GIScience but typical GIS tools are not a particularly good match.

Dealing with the temporal dynamics of geographic processes is one of the grand challenges for Geographic Information Science. Geographic Information Systems (GIS) and related spatial analysis methods are quite adept at handling spatial dimensions of patterns and processes, but the temporal and coupled space-time attributes of phenomena are difficult to represent and examine with contemporary GIS. (Dr. Paul M. Torrens, Center for Urban Science + Progress, New York University)

It’s still a hot topic right now, as the variety of related publications and events illustrates. For example, just this month, there is an Animove two-week professional training course (18–30 September 2016, Max-Planck Institute for Ornithology, Lake Konstanz) as well as the GIScience 2016 Workshop on Analysis of Movement Data (27 September 2016, Montreal, Canada).

Space-time cubes and animations are classics when it comes to visualizing movement data in GIS. They can be used for some visual analysis but have their limitations, particularly when it comes to working with and trying to understand lots of data. Visualization and analysis of spatio-temporal data in GIS is further complicated by the fact that the temporal information is not standardized in most GIS data formats. (Some notable exceptions of formats that do support time by design are GPX and NetCDF but those aren’t really first-class citizens in current desktop GIS.)

Most commonly, movement data is modeled as points (x,y, and optionally z) with a timestamp, object or tracker id, and potential additional info, such as speed, status, heading, and so on. With this data model, even simple questions like “Find all tracks that start in area A and end in area B” can become a real pain in “vanilla” desktop GIS. Even if the points come with a sequence number, which makes it easy to identify the start point, getting the end point is tricky without some custom code or queries. That’s why I have been storing the points in databases in order to at least have the powers of SQL to deal with the data. Even so, most queries were still painfully complex and performance unsatisfactory.

So I reached out to the Twitterverse asking for pointers towards moving objects database extensions for PostGIS and @bitnerd, @pwramsey, @hruske, and others replied. Amongst other useful tips, they pointed me towards the new temporal support, which ships with PostGIS 2.2. It includes the following neat functions:

  • ST_IsValidTrajectory — Returns true if the geometry is a valid trajectory.
  • ST_ClosestPointOfApproach — Returns the measure at which points interpolated along two lines are closest.
  • ST_DistanceCPA — Returns the distance between closest points of approach in two trajectories.
  • ST_CPAWithin — Returns true if the trajectories’ closest points of approach are within the specified distance.

Instead of  points, these functions expect trajectories that are stored as LinestringM (or LinestringZM) where M is the time dimension. This approach makes many analyses considerably easier to handle. For example, clustering trajectory start and end locations and identifying the most common connections:

animation_clusters

(data credits: GeoLife project)

Overall, it’s an interesting and promising approach but there are still some open questions I’ll have to look into, such as: Is there an efficient way to store additional info for each location along the trajectory (e.g. instantaneous speed or other status)? How well do desktop GIS play with LinestringM data and what’s the overhead of dealing with it?


Getting multipolygon vertexes using PostGIS

EN | PT

Today I needed to create a view in PostGIS that returned the vertexes of a multi-polygon layer. Besides, I needed that they were numerically ordered starting in 1, and with the respective XY coordinates.

Screenshot from 2015-11-05 23:58:19

It seemed to be a trivial task – all I would need was to use the ST_DumpPoints() function to get all vertexes – if it wasn’t for the fact that PostGIS polygons have a duplicate vertex (the last vertex must be equal to the first one) that I have no interess in showing.

After some try-and-fail, I came up with the following query:

CREATE OR REPLACE VIEW public.my_polygons_vertexes AS
WITH t AS -- Transfor polygons in sets of points
    (SELECT id_polygon,
            st_dumppoints(geom) AS dump
     FROM public.my_polygons),
f AS -- Get the geometry and the indexes from the sets of points 
    (SELECT t.id_polygon,
           (t.dump).path[1] AS part,
           (t.dump).path[3] AS vertex,
           (t.dump).geom AS geom
     FROM t)
-- Get all points filtering the last point for each geometry part
SELECT row_number() OVER () AS gid, -- Creating a unique id
       f.id_polygon,
       f.part,
       f.vertex,
       ST_X(f.geom) as x, -- Get point's X coordinate
       ST_Y(f.geom) as y, -- Get point's Y coordinate
       f.geom::geometry('POINT',4326) as geom -- make sure of the resulting geometry type
FROM f 
WHERE (f.id_polygon, f.part, f.vertex) NOT IN
      (SELECT f.id_polygon,
              f.part,
              max(f.vertex) AS max
       FROM f
       GROUP BY f.id_polygon,
                f.part);

The interesting part occurs in the WHERE clause, basically, from the list of all vertexes, only the ones not included in the list of vertexes with the maximum index by polygon part are showed, that is, the last vertex of each polygon part.

Here’s the result:

Screenshot from 2015-11-05 23:58:40

The advantage of this approach (using PostGIS) instead of using “Polygons to Lines” and “Lines to points” processing tools is that we just need to change the polygons layer, and save it, to see our vertexes get updated automatically. It’s because of this kind of stuff that I love PostGIS.

Labels leading lines with QGIS and Postgis

EN | PT

Recently I had the need to add labels to features with very close geometries, resulting in their collision.

Capturar_3

Using data-defined override for label’s position (I have used layer to labeled layer plugin to set this really fast) and the QGIS tool to move labels, it was quite easy to relocate them to better places. However, in same cases, it was difficult to understand to which geometry they belonged.

Capturar_2

I needed some kind of leading lines to connect, whenever necessary, label and feature. I knew another great plugin called “Easy Custom Labeling“, by Regis Haubourg, that did what I needed, but it would create a memory duplicate of the original layer, wish meant that any edition on the original layer wouldn’t be updated in the labels.

Since the data were stored in a PostgreSQL/Postgis database, I have decided to create a query that would return a layer with leading lines. I used the following query in DB manager:

SELECT
  gid,
  label,
  ST_Makeline(St_setSRID(ST_PointOnSurface(geom),27493), St_setSRID(St_Point(x_label::numeric, y_label::numeric),27493))
FROM
  epvu.sgev
WHERE
  x_label IS NOT NULL AND
  y_label IS NOT NULL AND
  NOT ST_Within(ST_Makeline(St_setSRID(ST_PointOnSurface(geom),27493), St_setSRID(St_Point(x_label::numeric, y_label::numeric),27493)),geom))

This query creates a line by using the feature centroid as starting point and the label coordinate as end point. The last condition on the WHERE statement assures that the lines are only created for labels outside the feature.

Capturar_1

With the resulting layer loaded in my project, all I need is to move my labels and save the edition (and press refresh) to show a nice leading line.

guidelines

A new QGIS tool (based on ogr2ogr) to import vectors in PostGIS, the fast way

In QGIS there are many tools that can be used to import vectors inside a PostGIS database, each one has pros and cons: SPIT core plugin: available since long ago but now seems to be a unmaintained tool and therefore will be probably removed in a future QGIS release. It  has the advantage to allow […]

Dissolver polígonos em Postgres\Postgis

Trata-se de um cenário muito recorrente em análise espacial. Tendo uma camada\tabela composta por diversos polígonos, queremos “juntá-los” de acordo com valores distintos de um ou mais atributos (exemplo: de uma camada com os limites de freguesias, queremos obter os concelhos, ou, da COS ao 3º nível, obter o 2º ou o 1º)

Este artigo tem como objectivo mostrar como fazê-lo em Postgres\Postgis.

Tabela de exemplo

Como exemplo vou usar uma tabela como o seguinte formato:

CREATE TABLE tabela_1
    (gid serial PRIMARY KEY,
     campo1 character varying(128),
     campo2 integer,
     geom geometry(MultiPolygon,27493);

tabela1_original_tabela

tabela1_original

Dissolver todos os polígonos

Em primeiro lugar podemos simplesmente agregar todos os elementos num multi-polígono único. Para tal usamos a função ST_Union().

SELECT
    ST_Union(t.geom) as geom
FROM
    tabela_1 as t;

tabela1_union

Separar polígonos que não sejam contíguos

Se por outro lado não quisermos que o resultado apresente multi-polígonos usamos a função ST_Dump() recolhendo o campo da geometria.

SELECT
    (ST_Dump(ST_Union(t.geom))).geom as geom
FROM
    tabela_1 as t;

tabela1_union_dump

Dissolver polígonos com base em valores dos campos

Se quisermos dissolver os polígonos que tenham valores iguais num ou mais campos, basta incluí-los na cláusula GROUP BY. Se quisermos que esses campos apareçam no resultado (geralmente queremos) há que referi-los no início do SELECT.

SELECT
    campo1,
    campo2,
    (ST_Dump(ST_Union(t.geom))).geom as geom
FROM
    tabela_1 as t
GROUP BY
    campo1,
    campo2;

tabela1_union_by_value

Nota 1: Para quem prefere usar interfaces gráficos, preencher formulários e clicar em botões, o uso de SQL para fazer este tipo de operações pode parecer demasiado complicado e até um pouco retrógrado. Mas uma coisa garanto, com alguma prática as dificuldades iniciais são ultrapassadas e os benefícios que se retiram deste tipo de abordagem são muito recompensadores.

Nota 2: Visualizar o resultado deste tipos consultas de agregação (que usam a cláusula GROUP BY) no QGIS pode ser desafiante, este artigo explica como ultrapassar essa dificuldade.


Training courses calendar: QGIS (Desktop, Server and Web) and PostGIS

We just published our Training Courses calendar for the period September 2014 – January 2015. This includes training courses about QGIS (Desktop, Server and Web) and PostgreSQL/PostGIS in both Italy and Portugal. Training courses about QGIS python programming are available on demand. For details (locations, prices, discounts, etc.) about training courses in Portugal see: http://www.faunalia.eu/pt/training.html […]

Packaging PostGIS dev releases with Docker

Packaging PostGIS dev releases with Docker

We recently added support for GML curves to PostGIS, which enables TinyOWS to deliver WFS requests with curve geometries. More on this in a later post. This enhancement is in the current PostGIS developement version (SVN master) and not released yet. To enable our customer testing this functionality, we had to build packages for their server environment which is Ubuntu Precise with UbuntuGIS repositories. After working with Linux LXC containers and it's predecessor VServer for years, Docker was a logical choice for a clean reproducible build environment.

Rebuilding a Debian package is usually quite easy:

apt-get build-dep <package>
apt-get source <package>
cd <packagedir>
#Make your changes
dch -i
dpkg-buildpackage

But getting build dependencies for PostGIS currently fails with libssl-dev conflicts, maybe because the dev packages got out of sync after the recent Heartblead updates. So the Dockerfile uses equivs to build a dummy package which satisfies the dependencies.

The command

docker run -v /tmp:/pkg sourcepole/postgis-svn-build-env sh -c 'cp /root/*postgis*.deb /pkg'

loads the Docker image with packages built from the latest SVN version of PostGIS in /root and copies the deb files from the containter into /tmp.

Now we're ready to install these packages on the Ubuntu server:

sudo dpkg -i /tmp/*postgis*.deb

Thats it. Feedback welcome!

@PirminKalberer

P.S.

If you happen to be a developer, then you may prefer running a cutting-edge version of PostGIS in a Docker container instead of building packages. Our colleagues from Oslandia just published how to do this.

Packaging PostGIS dev releases with Docker

Packaging PostGIS dev releases with Docker

We recently added support for GML curves to PostGIS, which enables TinyOWS to deliver WFS requests with curve geometries. More on this in a later post. This enhancement is in the current PostGIS developement version (SVN master) and not released yet. To enable our customer testing this functionality, we had to build packages for their server environment which is Ubuntu Precise with UbuntuGIS repositories. After working with Linux LXC containers and it's predecessor VServer for years, Docker was a logical choice for a clean reproducible build environment.

Rebuilding a Debian package is usually quite easy:

apt-get build-dep <package>
cd <packagedir>
#Make your changes
dch -i
dpkg-buildpackage

But getting build dependencies for PostGIS currently fails with libssl-dev conflicts, maybe because the dev packages got out of sync after the recent Heartblead updates. So the Dockerfile uses equivs to build a dummy package which satisfies the dependencies.

The command

docker run -v /tmp:/pkg sourcepole/postgis-svn-build-env sh -c 'cp /root/*postgis*.deb /pkg'

loads the Docker image with packages built from the latest SVN version of PostGIS in /root and copies the deb files from the containter into /tmp.

Now we're ready to install these packages on the Ubuntu server:

sudo dpkg -i /tmp/*postgis*.deb

Thats it. Feedback welcome!

@PirminKalberer

P.S.

If you happen to be a developer, then you may prefer running a cutting-edge version of PostGIS in a Docker container instead of building packages. Our colleagues from Oslandia just published how to do this.

Installing PostGIS on Fedora 20

In order to explore all the new interfaces to PostGIS (from QGIS, GDAL, GRASS GIS 7 and others) I decided to install PostGIS 2.1 on my Fedora 20 Linux box. Eventually it is an easy job but I had to visit a series of blogs to refresh my dark memories from past PostGIS installations done some years ago… So, here the few steps:

# become root
su -
# grab the PostgreSQL 9.3 server and PostGIS 2.1
yum install postgresql-server postgresql-contrib postgis

Now the server is installed but yet inactive and not configured. The next step is to initialize, configure and start the PostgreSQL server:

# initialize DB:
postgresql-setup initdb
# start at boot time:
chkconfig postgresql on
# fire up the daemon:
service postgresql start

A test connection will show that we need to configure TCP/IP connections:

# this will fail
psql -l
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

So we enable TCP/IP connections listening on port 5432:

# edit "postgresql.conf" with editor of choice (nano, vim, ...),
# add line "listen_addresses = '*'":
vim /var/lib/pgsql/data/postgresql.conf
...
listen_addresses = '*'
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
...

Now restart the PostgreSQL daemon:

# restart to re-read configuration
service postgresql restart

Check if the server is now listening on TCP/IP:

# check network connections:
netstat -l | grep postgres
tcp        0      0 0.0.0.0:postgres        0.0.0.0:*               LISTEN     
tcp6       0      0 [::]:postgres           [::]:*                  LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     2236495  /var/run/postgresql/.s.PGSQL.543

So far so nice. Still PostGIS is not yet active, and we need a database user “gisuser” with password:

# switch from root user to postgres user:
su - postgres
# create new DB user with password (will prompt you for it, choose a strong one):
createuser --pwprompt --encrypted gisuser

Finally we create a first database “gis”:

# create new DB
createdb --encoding=UTF8 --owner=gisuser gis

We enable it for PostGIS 2.1:

# insert PostGIS SQL magic (it should finish with a "COMMIT"):
psql -d gis -f /usr/share/pgsql/contrib/postgis-64.sql

That’s it! Now exit from the “postgres” user account a the “root” account:

# exit from PG user account (back to "root" account level):
exit

In case you want to reach the PostgreSQL/PostGIS server from outside your machine (i.e. from the network), you need to enable PostgreSQL for that:

# enable the network in pg_hba.conf (replace host line; perhaps comment IP6 line):
vim /var/lib/pgsql/data/pg_hba.conf

...
host    all             all             all                     md5
...

# save and restart the daemon:
service postgresql restart

Time for another connection test:

# we try our new DB user account on the new database (hostname is the 
# name of the server in the network):
psql --user gisuser -h hostname -l
Password for user gisuser: xxxxxx

Wonderful, we are connected!

# exit from root account:
exit
[[email protected] ] $

Now have our PostGIS database ready!

What’s left? Get some spatial data in as a normal user:

# nice tool
shp2pgsql-gui
shp2pgsql-gui

Using the shp2pgsql-gui

Next pick a SHAPE file and upload it to PostGIS with “Import”.

Now connect to your PostGIS database with QGIS or GRASS GIS and enjoy!

See also: https://fedoraproject.org/wiki/PostgreSQL

The post Installing PostGIS on Fedora 20 appeared first on GFOSS Blog | GRASS GIS Courses.

OSGeo Code Sprint, Vienna

This is how OSGeo happens.  These are the folk who bring us a lot of that open-source geo-spatial goodness. You can follow the code sprint on Twitter using the hashtags #csprint and #viennacodesprint14

 


  • Page 1 of 3 ( 52 posts )
  • >>
  • postgis

Back to Top

Sponsors