1

When I upload a shapefile to PostgreSQL using the QGIS tool I can create the routing topology following this tutorial without any problem.

But when I use the PostGis Shapefile Import/Export Manager or the SQL generated by the shp2pgsql.exe I can't get any values for the x1,x2,y1 and y2 fields.

UPDATE edge_table SET x1 = st_x(st_startpoint(the_geom)),
  y1 = st_y(st_startpoint(the_geom)),
  x2 = st_x(st_endpoint(the_geom)),
  y2 = st_y(st_endpoint(the_geom)),
cost_len  = st_length_spheroid(the_geom, 'SPHEROID["WGS84",6378137,298.25728]'),
rcost_len = st_length_spheroid(the_geom, 'SPHEROID["WGS84",6378137,298.25728]');

Only cost_len and rcost_len have values after running that script.

And because of that I can't get any route at all. What am I missing?

cap7
  • 217
  • 2
  • 8
  • Everything looks good. Can you verify that X1, X2, Y1, Y2 are type double? – evv_gis Nov 04 '14 at 18:20
  • 1
    They all have the data type: double precision; – cap7 Nov 04 '14 at 18:22
  • 1
    I have checked all the data types. On the working table I have: the_geom geography(LineString,4326). On this one I have geom geometry(MultiLineString,4326). Can that be the issue? – cap7 Nov 04 '14 at 18:34
  • Ah, yeah it could be the issue. Here's a good post for converting the MultiLineString to a LineString - http://gis.stackexchange.com/questions/19864/how-to-convert-multilinestring-to-linestring and here is a link to the ST_LineMerge http://postgis.net/docs/manual-1.4/ST_LineMerge.html Let us know if this helps! – evv_gis Nov 04 '14 at 18:40
  • 1
    Thanks. I ended up using -S on the shp2pgsql.exe to generate single geometry elemens. – cap7 Nov 04 '14 at 22:06

1 Answers1

6

ST_StartPoint() only accepts LineString as input. You'll have to strip your table down from MultiLineString to LineString, either by taking only the first element up each geometry

ALTER TABLE foo 
  ALTER COLUMN geom 
  TYPE Geometry(LineString,4326) 
  USING ST_GeometryN(geom,1)

Or, more correctly, dumping the multis out, in case there are legit multis.

CREATE TABLE foo_single AS
   SELECT (ST_Dump(geom)).geom::Geometry(LineString,4326) AS geom
          gid
   FROM foo;
Paul Ramsey
  • 19,865
  • 1
  • 47
  • 57
  • 1
    Had exactely the same problem. ST_Distance() returning NULL because ST_StartPoint() was returning null as an argument. Your solution solved my problem :) thanks ! – Vincent Monteil May 03 '18 at 09:08
  • When I try to do this - I keep getting: ERROR: Geometry has Z dimension but column does not - How do I fix this? – user1655130 Nov 04 '18 at 23:24