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 routegeometriessidewalk_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 integerSQL state: 22003I 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 float8SQL state: XX000So 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?
أكثر...
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 routegeometriessidewalk_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 integerSQL state: 22003I 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 float8SQL state: XX000So 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?
أكثر...