I have two sets of data
Table A: Stations - contains postal code (which I can convert to lat/long), name and code. Table B: Connections - contains a possible list of connections including source, target (both expressed as three letter codes), distance and maximum speed (I can calculate a cost from these two parameters).
I want to create a routable network - from looking, I have found PGRouting may be able to do what I need but almost all the tutorials I've found are based on road networks.
What is the best way to convert the data I have into a routable network? I'm not set on PostGIS/PGRouting but I haven't found a better solution yet - eventually there'll be around 3000 stations and 5000+ connections.
There should be no problem using PgRouting for this purpos, and it should be pretty straight forward to prepare your data as routable network.
I build a network similar to yours based on transtools data for Europe, and it was working within an hour. Load the data into the database in whatever way you have them, and they can be prepared with SQL - use this example and you should be on your way:
Routing network from OSM
I'm looking for some good tool to import map.osm to postgres and next create some routes which will be displayed by geoserver. I need route, with some text information about vertexes (e.g. city, address, address number, and so on. )
osm2pgrouting - Import OSM data into pgRouting Database
osm2postgis -Import OSM data to PostGIS
osm2po - tool to convert OSM data into a routable format
osm4routing - OpenStreetMap data parser to turn them into a nodes-edges adapted for routing applications
I do not have many experiences with GIS, so how tool is the best for me? I try osm2pgrouting, but in result I have tables, which do not contains data about vertexes(only lat. and alt.) Thanks for answers. UPDATE App Info: I will be have web and android client where user enter text value of start and end node, and next over geoserver get wms with vertexes of entered route for example
My result from could be be some edges and nodes like this like this: sequence_num, edge_distance, and informations about edge vertexes like osm_id, some text value, lat alt, etc.
SQL: How to get source/target of given coordinates in a line shapefile in PostGIS table?
I loaded a line shapefile in PostGIS and used pgr_createTopology in pgRouting to make the table routable by adding source and target (assign 2 end nodes of the line source number and target number). The following figure was part of the table:
Now I have some coordinates of end nodes, which belong to lines in the table, and I want to know that what number of sources/targets were corresponding to these coordinates.
For example, in the table above, suppose that 259463.392, 2737830.062 was one of end nodes of line > and line >, then this coordinate has source/target=175
I am newbie to SQL and tried some query but got errors:
I am thinking I could just know what I want if column source/target contain information of node coordinate, but seems not, they are just column containing numbers.
I got the vertices table as below:
I used the following query to get the table below:
and I am still looking for if I could get my need through the 2 tables above.
So I tried the query below and got 2 lines near the given coordinate:
and later I found from the table showed in the first figure that the coordinate is source/target=54 of id 170/51, respectively, but it's still inefficient.
I am wondering that is there ways to find the same source/target number, which in this case line > and line > both contain, after I found the given coordinate is located between these two lines?
Based on the vertices table, I used the following query to get corresponding source number, which is also point id, of the given coordinate:
Using MovingPandas Trajectory objects
Mobilitydb-sqlalchemy also provides first-class support for MovingPandas Trajectory objects, which can be installed as an optional dependency of this library. Using this Trajectory class instead of plain DataFrames allows us to make use of much richer functionality over trajectory data like analysis speed, interpolation, splitting and simplification of trajectory points, calculating bounding boxes, etc. To make use of this feature, you have set the use_movingpandas flag to True while declaring your model, as shown in the below code snippet.
Now when you query over this table, you automatically get the data parsed into Trajectory objects without having to do anything else. This also works during insertion of data – you can directly assign your movingpandas Trajectory objects to the trip column. In the below code snippet we show how inserting and querying works with movingpandas mode.
The data presented in this post stems from a survey conducted among public transport users, especially commuters (available online at: https://de.surveymonkey.com/r/57D33V6). Among other questions, the questionnair asks where the commuters start their journey and where they are heading.
The answers had to be cleaned up to correct for different spellings, spelling errors, and multiple locations in one field. This cleaning and the following geocoding step were implemented in Python. Afterwards, the flow information was aggregated to count t he number of nominations of each connection between different places. Finally, these connections (edges that contain start id, destination id and number of nominations) were stored in a text file. In addition, the locations were stored in a second text file containing id, location name, and co-ordinates.
Why was this data collected?
Besides travel demand, Hans-Jörg’s survey also asks participants about their coffee consumption during train rides. Here’s how he tells the story behind the data:
As a nearly daily commuter I like to enjoy a hot coffee on my train rides. But what has bugged me for a long time is the fact the coffee or hot beverages in general are almost always served in a non-reusable, “one-use-only-and-then-throw-away” cup. So I ended up buying one of these mostly ugly and space-consuming reusable cups. Neither system seem to satisfy me as customer: the paper-cup produces a lot of waste, though it is convenient because I carry it only when I need it. With the re-usable cup I carry it all day even though most of the time it is empty and it is clumsy and consumes the limited space in bag.
So I have been looking for a system that gets rid of the disadvantages or rather provides the advantages of both approaches and I came up with the following idea: Installing a system that provides a re-usable cup that I only have with me when I need it.
In order to evaluate the potential for such a system – which would not only imply a material change of the cups in terms of hardware but also introduce some software solution with the convenience of getting back the necessary deposit that I pay as a customer and some software-solution in the back-end that handles all the cleaning, distribution to the different coffee-shops and managing a balanced stocking in the stations – I conducted a survey
The next step was the geographic visualization of the flow data and this is where QGIS comes into play.
The flow map
Survey data like the one described above is a common input for flow maps. There’s usually a point layer (here: “nodes”) that provides geographic information and a non-spatial layer (here: “edges”) that contains the information about the strength or weight of a flow between two specific nodes:
The first step therefore is to create the flow line features from the nodes and edges layers. To achieve our goal, we need to join both layers. Sounds like a job for SQL!
More specifically, this is a job for Virtual Layers: Layer | Add Layer | Add/Edit Virtual Layer
This SQL query joins the geographic information from the nodes table to the flow weights in the edges table based on the node IDs. In the last line, there is a check that start and end node ID should be different in order to avoid zero-length lines.
By styling the resulting flow lines using data-driven line width and adding in some feature blending, it’s possible to create some half decent maps:
However, we can definitely do better. Let’s throw in some curved arrows!
The arrow symbol layer type automatically creates curved arrows if the underlying line feature has three nodes that are not aligned on a straight line.
Therefore, to turn our straight lines into curved arrows, we need to add a third point to the line feature and it has to have an offset. This can be achieved using a geometry generator and the offset_curve() function:
Additionally, to achieve the effect described in New style: flow map arrows, we extend the geometry generator to crop the lines at the beginning and end:
By applying data-driven arrow and arrow head sizes, we can transform the plain flow map above into a much more appealing map:
The two different arrow colors are another way to emphasize flow direction. In this case, orange arrows mark flows to the west, while blue flows point east.
Movement data in GIS extra: trajectory generalization code and sample data
Today’s post is a follow-up of Movement data in GIS #3: visualizing massive trajectory datasets. In that post, I summarized a concept for trajectory generalization. Now, I have published the scripts and sample data in my QGIS-Processing-tools repository on Github.
To add the trajectory generalization scripts to your Processing toolbox, you can use the Add scripts from files tool:
It is worth noting, that Add scripts from files fails to correctly import potential help files for the scripts but that’s not an issue this time around, since I haven’t gotten around to actually write help files yet.
The scripts are used in the following order:
- Extract characteristic trajectory points
- Group points in space
- Compute flows between cells from trajectories
The sample project contains input data, as well as output layers of the individual tools. The only required input is a layer of trajectories, where trajectories have to be LINESTRINGM (note the M!) features:
Trajectory sample based on data provided by the GeoLife project
In Extract characteristic trajectory points, distance parameters are specified in meters, stop duration in seconds, and angles in degrees. The characteristic points contain start and end locations, as well as turns and stop locations:
The characteristic points are then clustered. In this tool, the distance has to be specified in layer units, which are degrees in case of the sample data.
Finally, we can compute flows between cells defined by these clusters:
Flow lines scaled by flow strength and cell centers scaled by counts
If you use these tools on your own data, I’d be happy so see what you come up with!
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.
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.
This post is part of a series. Read more about movement data in GIS.
Finally, of course, let’s have a look at spatial filters, for example, trajectories that start in a certain area:
This point-based query takes 6.0 sec while the shorter trajectory query finishes in 488 ms:
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:
You can find my fork of the PG-Trajectory project – including all necessary fixes – on Bitbucket.
This post is part of a series. Read more about movement data in GIS.
Drive-time Isochrones from a single Shapefile using QGIS, PostGIS, and Pgrouting
This is a guest post by Chris Kohler @Chriskohler8.
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 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.
Then fill out name, operating system, memory, etc. to create your first VM.
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.
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”.
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”.
A new window will pop-up and select “Choose Disk”.
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”.
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.
–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:
You can use “c routing” to connect to the database after creation.
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
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”.
Follow the UI of shp2pgsql to connect to your routing database you created in Step 4.
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.
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.
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:
Below is an example(simplified) function for my roads shapefile:
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.
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.
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 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/ )
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.
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.
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”.
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.
Repeat this step for each speed value in your shapefile examples:
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.
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”
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“.
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.
Start by installing the QGIS plug-in “ Interpolation” by opening the Plugin Manager in QGIS interface.
Next, at the top of QGIS window select “Raster” and a drop-down will appear, select “Interpolation”.
A new window pops up and asks you for input.
Select your “view” as the vector layer, select ”cost” as your interpolation attribute, and then click “Add”.
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.
A black and white raster will appear in QGIS, also in the Layers Panel a new item was created.
Take some time to visualize the raster by coloring and adjusting values in symbology until you are comfortable with the look.
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.
Fill out the appropriate interval between contour lines but leave the check box “Attribute name” unchecked. Click “OK”.
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.