I'm currently working on a problem where I have to use PostGIS's pgr_dijkstra to calculate the shortest route from a starting point to a destination, and then use ST_AsGeoJSON to convert the route to a representation in GeoJSON. I came up with this query, which worked fine when I loaded a small number of geometries:

SELECT ST_AsGeoJSON(wkb_geometry) FROM (
SELECT seq, id1 AS node, id2 AS edge, route.cost, dt.wkb_geometry, dt.sidewalk_edge_id FROM pgr_dijkstra('
                SELECT sidewalk_edge_id AS id,
                         ST_Length(wkb_geometry)::double precision AS cost
                        FROM sidewalk_edge',
                469256344, 986535192, false, false) as route
                join sidewalk_edge  dt
                on route.id2 = dt.sidewalk_edge_id
) as routegeometries

sidewalk_edge is the table containing the geometries, wkb_geometry is the geometry column, and source and target are the columns containing source and column IDs (stored as varchar).

However, when I loaded the table with a larger number of geometries, some of which have bigger source and target IDs, I got this error:

ERROR:  value "5443376856" is out of range for type integer
********** Error **********

ERROR: value "5443376856" is out of range for type integer
SQL state: 22003

I tried changing source::integer and target::integer to source::bigint and target::bigint respectively, but PostGIS didn't like that:

ERROR:  Error, columns 'source', 'target' must be of type int4, 'cost' must be of type float8
********** Error **********

ERROR: Error, columns 'source', 'target' must be of type int4, 'cost' must be of type float8
SQL state: XX000

So it seems that my source and target IDs are to big to store as integers, but PostGIS will not accept anything other than integers. Is there any way to work around this problem?

  • You can do a a priori localising on the OSM data you use for the calculations. Like a bounding box around the start and endpoint. Then create the nodes anew. Source and target IDs will be smaller then. After that use the pgr_dijkstra shortest path query.You can do this in one query. Wrap it to a function if you want. – Stefan Aug 10 '15 at 15:50
  • @Stefan That sounds interesting..could you give me some pointers as to how I might code that? (Sorry I'm very new to GIS in general, so I'd have no idea where to even start lol) – tlng05 Aug 10 '15 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.