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,
source::integer,
target::integer,
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?