12
votes
Accepted
How do PostGIS functions handle buffers and external storage?
No, most of these "higher order analysis" functions do not have any special handling for data sets that are larger than can fit in memory. If you run them on such data sets, you'll just OOM the ...
- 20k
11
votes
Accepted
PgRouting : Shortest path starting from the closest vertex not the closest node
While still not in the official release, the withPoints family of functions is in a stable state since pgRouting 2.2 (?) and provides a dynamic interface for temporary nodes in a graph, i.e. routing ...
- 30.9k
9
votes
Wrong shortest path using pg routing
The two lookup points you pass into the (K)NN sub-queries can't be referenced in EPSG:3857 - their coordinate values seem to be in degree and are likely EPSG:4326!
Assuming planet_osm_roads.way has ...
- 30.9k
8
votes
Accepted
pgr_createTopology with large datasets
The following is what I am using. Some of it is specific to our deployment environment since we are using docker and some bash scripts to deploy and set up the server. You could easily get rid of all ...
- 326
6
votes
Accepted
pgrouting no result // topology problem
EDIT 3:
After a long discussion in the comments I reviewed my answer, taking the sample data and it´s upload into PostgreSQL from within QGIS into account. I deleted my EDIT 2 as it became useless.
...
- 30.9k
6
votes
Multiple shortest paths with pgRouting using a list as a secondary table
You want to simply run that function on every row in the <list> table; generally speaking, this is no different than any other function applied to a column/row. In practice, this here is a ...
- 30.9k
6
votes
Dashed lines in pgRouting
The reason for these kind of errors is ususally a wrong network topology. In particular you must make sure, that you use the correct unit (meter or degree) for the tolerance value in the ...
- 4,796
6
votes
Accepted
pgr_dijkstra is considering one-way streets as bidirectional
You are passing in the metric length ST_Length(geom::GEOGRAPHY) of each edge E as both cost & reverse_cost - rendering it equal to to the undirected counterpart.
pgRouting realizes ordered graph ...
- 30.9k
6
votes
Accepted
Is the geometry type of PostGIS represented as WKT or just the Point type?
PostGIS internally stores geometry data as binary objects which are optimized for scanning and processing. Geometry objects include the SRID.
WKT, EWKT, WKB and EWKB are just representations for ...
- 5,283
6
votes
Calculating cost values in pgRouting using ST_DWithin
You should simply
UPDATE
utrecht_osm_foot_2po_4pgr_duplicate
SET
cost_greenery = cost / 1000
WHERE
ST_DWithin(geom_way, osm_meta, 30)
;
If no update happened
make sure your geometries have a ...
- 30.9k
6
votes
pg_routing error when generating alpha shapes
Note that pgr_pointsAsPolygon has been moved to legacy as of pgRouting 3.0.0 - I won't address this function.
Read the docs carefully; the full function signature
pgr_alphaShape(geometry, [...
- 30.9k
5
votes
pgr_dijkstra function with geometry column in result
Join to table ways is as for now only solution (BTW recommended in all official documentation and workshops)
- 2,617
5
votes
Accepted
5
votes
win10/CREATE EXTENSION Pgrouting--could not access file "$libdir/libpgrouting-2.3"
This sounds like you are using the BigSQL distribution. Do not use the installers for that since the PostgreSQL folder layout of BigSQL is different from PostgreSQL EDB distribution (which is what ...
- 10.6k
5
votes
Setting up pgRouting
You are trying to follow instructions from 2011. They don't apply to current versions of PostGIS/PgRouting.
Instead, just do
CREATE EXTENSION Postgis;
CREATE EXTENSION pgrouting;
PgRouting should ...
- 84.5k
5
votes
Accepted
Problem using pgr_createTopology
The geometrytype should be LineString, not LineStringM.
Try changing your geom column to LineString with this:
ALTER TABLE public.roads
ALTER COLUMN geom TYPE geometry(LineString,3006)
USING ...
- 10.6k
5
votes
Accepted
pgrouting with oneway
You would need to prepare your data beforehand. Add cost and reverse_cost columns to your ways_noded table. Then, you populate them using the way length if usable in this direction, or an insanely ...
- 43.1k
5
votes
Shortest paths between points over multiple linestrings
As you already pointed out, this indeed can be solved with pgRouting and will have to be added to your database as an extension. You will have to do some preparations with the data to get it going.
...
- 995
4
votes
Add one-way streets to network data in postgis
In pgRouting the direction is always from source to target.
When you run pgr_createTopology(), then source is the start point of your line geometry and target is the end point.
If you want to model ...
- 4,796
4
votes
Accepted
How can I get the length of segments from the output of pgr_dijkstra?
pgr_dijkstra(edges_sql, start_vid, end_vid, directed:=true ) is a SQL function. It returns the following, regardless of what you input into its TEXT edges_sql argument.
RETURNS SET OF (seq, path_seq,...
- 3,417
4
votes
How to give start and end vertex ids dynamically in pgr_dijkstra?
You can use the array() function around a SELECT statement to pass in multiple vertex IDs. See example below:
SELECT ( SUM(w.km)*0.621371 ) As miles,r.start_vid,r.end_vid
FROM pgr_dijkstra('...
- 3,640
4
votes
pgr_createTopology - how tolerance?
I guess that you took the example createTopology function which is shown for WGS84 geometries, using degrees as a projection unit. 0.00001 degree tolerance means that your points have to be less than ...
- 185
4
votes
Accepted
Using pgRouting for 'multiple pedestrians' on an existing table of 'students'?
It seems that the problem is with the the multi-student sub-query below you were using to replace the single student query:
--student
(SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <-&...
- 11.2k
4
votes
pgRouting: Nodes vs. Source/Target
For shortest path Dijkstra or driving distance queries you only need a valid network topology with source and target attributes in the network table.
The _vertices_pgr table is generated when you ...
- 4,796
4
votes
pgr_createTopology with large datasets
Thank you @James for sharing this. It helped a lot.
For those of you who want to rebuild the whole topology: normally, this is achieved by clean:=true.
Since the ways table gets processed step by ...
- 141
4
votes
Accepted
Using pgr_drivingDistance for isochrones?
The problem with the calculation was the network based on the osm.shp. After a closer look on the lines I realised that a lot of the lines provided from osm a very long segments. Therefore fewer nodes ...
- 316
4
votes
Accepted
Performing isochrone in PostGIS by pgRouting?
I got the answer by removing the order by keyword and included false in pgr algorithm as there is no specific target.
create table htokdisdri3 as SELECT t1.seq, t1.id1 AS Node, t1.id2 AS Edge, t1....
- 249
4
votes
Why pgr_dijkstra() only finds the routes that are pretty straight?
My problem is solved by the solution that wfgeo and ThingumaBob gave me.
I changed the query as:
select * from pgr_dijkstra('select id , source, target, cost FROM q_roads', 10237, 10551, false)
in ...
- 729
4
votes
Accepted
Distance in Meters/Kilometers from pgr_drivingdistance
You are using the wrong function; pgr_drivingDistance returns a table of all nodes that can be reached within the given distance value, e.g. the 'catchment'!
If a shortest path between two nodes is ...
- 30.9k
4
votes
Accepted
Why is pgr_dijkstra not giving shortest path
Even though you have instructed to use an undirected graph, you must still use set a cost and a reverse_cost (which can be the same).
SELECT gid as id, source, target,
length:: double ...
- 43.1k
Only top scored, non community-wiki answers of a minimum length are eligible