demo (#22) - Shooting Star Tutorial (#66) - Message List
Hi,
I am trying to get the hang of using pgRouting with my data. My first problem would be how can I get the x1,x2,y1,y2 values please? Are these the values displayed at the lower left area of QGis? The map extents? Should I give of the small edge that covers both points which the route is to be planned upon?
Also how can I change my VERTEX point of the locations (using two firestations which are next to each other) to an integer type to use as source / target locations please?
This is the info of firestation 1 - with building code 294 : 56;56;0;"RFD #1";"220 S. Dawson St";"Raleigh";"M";2;0;0;0.000000000000000;0;1;0;0;0;1;"294";"RF";"RF01";58.000000000000000;"RF01";0.000000000000000;"0101000000753AE358169623415BAB9023D1760B41" And when I use astext for the the_geom : "POINT(641803.173608615 224986.142365779)"
And this is for firestation 2 - with blgd code 264 : 25;25;0;"RFD #3";"13 S. East St";"Raleigh";"M";1;0;0;0.000000000000000;0;0;1;0;0;1;"264";"RF";"RF03";27.000000000000000;"RF03";0.000000000000000;"0101000000214E7B95689E23415F6B2E505A7D0B41" And when I use astext() for the the_geom : "POINT(642868.291956369 225195.289151038)"
Thanks alot beforehand for the time to read this post :)
Regards
Matthew
This is the scheme of my streets table :
-- Table: streets
-- DROP TABLE streets;
CREATE TABLE streets (
gid serial NOT NULL, cat bigint, stseg bigint, stid bigint, stname character varying(80), dir_pre character varying(80), dir_suf character varying(80), stype character varying(80), "class" bigint, f_elev bigint, t_elev bigint, map character varying(80), priv character varying(80), frleft bigint, toleft bigint, frright bigint, toright bigint, frleft_a bigint, toleft_a bigint, frright_a bigint, toright_a bigint, stateroad bigint, cartoname character varying(80), corp character varying(80), speed bigint, one_way character varying(80), classname character varying(80), l_zipname character varying(80), r_zipname character varying(80), ft_cost numeric, tf_cost numeric, labelname character varying(80), f_node bigint, t_node bigint, cartolevel character varying(80), shape_len numeric, the_geom geometry, CONSTRAINT streets_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
) WITHOUT OIDS; ALTER TABLE streets OWNER TO yancho;
And this is for my firestations table / layer : - Table: firestations
-- DROP TABLE firestations;
CREATE TABLE firestations (
gid serial NOT NULL, cat bigint, id bigint, label character varying(80), "location" character varying(80), city character varying(80), mun_count character varying(80), pumpers bigint, pumper_tan bigint, tanker bigint, mini_pumpe numeric, rescue_ser bigint, aerial bigint, brush bigint, others bigint, water_resc bigint, muncoid bigint, bldgcode character varying(80), agency character varying(80), stationid character varying(80), recno numeric, cv_sid2 character varying(80), cvlag numeric, the_geom geometry, CONSTRAINT firestations_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
) WITHOUT OIDS; ALTER TABLE firestations OWNER TO yancho;
-
Message #238
First of all you need to build a topology. It means that you should execute assign_vertex_id function which will assign vertex ids (see TopologyCreation?).
It is quite easy to get x1,x2,y1 and y2 values.
x1 (x of the start point) = x(startpoint(the_geom))
y1 (y of the start point) = y(startpoint(the_geom))
x2 (x of the end point) = x(endpoint(the_geom))
y2 (y of the end point) = y(endpoint(the_geom))
Please, try it.
anton11/16/07 10:18:51 (3 years ago)-
Message #242
Thanks anton for your help :)
I followed the instructions on the Topology Creation Page (http://pgrouting.postlbs.org/wiki/TopologyCreation).
Basically, I ran these :
GRASS 6.3.0RC1 (thesis):~ > v.in.ogr dsn=/home/yancho/thesis_gis/GRASSDATA/streets.shp output=streets -o
GRASS 6.3.0RC1 (thesis):~ > v.build map=streets@yancho option=build,dump > streets.txt
./table_topo.pl streets streets.txt
Is there anything I have to run more? There is nothing mentioned about the function assign_vertex_id function.
This is the SQL Query I am trying .. anywhere you think I should correct and / or am understanding bad please?
SELECT * FROM shortest_path_shooting_star
('
SELECT
gid, (select the_geom as source from firestations where bldgcode = 294) as source, (select the_geom as target from firestations where bldgcode = 264) as target, length as cost, x(startpoint(the_geom)), y(startpoint(the_geom)), x(endpoint(the_geom)), y(endpoint(the_geom)), one_way, tf_cost
FROM streets',
36339, 22921, true, false );
Can be viewed here : http://pastebin.com/d348d2949 The query returned JUST a ":" .. no errors nothing .. just a semi-colon-> :
Any corrections would be extremely helpful :) Thanks once more
yancho11/16/07 20:05:52 (3 years ago)-
Message #243
*bump*
Just would like to ask if someone has any other ideas please bring them forward :)
Thanks beforehand
yancho11/19/07 03:08:35 (3 years ago) -
Message #244
Try to look at http://pgrouting.postlbs.org/wiki/Workshop-PrepareDikstra - it has an example of assign_vertex_id function invocation.
anton11/19/07 09:25:49 (3 years ago)-
Message #246
Thanks again anton for the tip :)
SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid'); UPDATE victoria SET length = length(the_geom);
what does the 0.001 reflect? if i keep it that number is it still good?
Thanks again for the help :)
yancho11/19/07 09:28:07 (3 years ago)-
Message #248
That number means the snapping distance between vertices. If a distance between two vertices is shorter than this value, they will be snapped. If your projection is in meters, this value will mean 0.001 meter (or 0.001 degree, if your projection's units are degrees).
anton11/19/07 09:45:50 (3 years ago)-
Message #249
So if I keep it 0.01 its more then enough for everything right? My projection is m so no road can be smaller than that 0.01 so should be enough! :)
So many thanks anton for your help :)
yancho11/19/07 09:49:04 (3 years ago)-
Message #250
Sure, it will be enough.
anton11/19/07 10:05:43 (3 years ago)-
Message #251
This is the query I am trying : SELECT assign_vertex_id('streets', 0.001, 'the_geom', 'gid');
This is the response : NOTICE: CREATE TABLE will create implicit sequence "vertices_tmp_id_seq" for serial column "vertices_tmp.id" CONTEXT: SQL statement "CREATE TABLE vertices_tmp (id serial)" PL/pgSQL function "assign_vertex_id" line 19 at execute statement
ERROR: column "source" of relation "streets" does not exist SQL state: 42703 Context: SQL statement "update streets SET source = 1, target = 2 WHERE gid = 1" PL/pgSQL function "assign_vertex_id" line 54 at execute statement
This is my schema : -- Table: streets
-- DROP TABLE streets;
CREATE TABLE streets (
gid serial NOT NULL, cat bigint, stseg bigint, stid bigint, stname character varying(80), dir_pre character varying(80), dir_suf character varying(80), stype character varying(80), "class" bigint, f_elev bigint, t_elev bigint, map character varying(80), priv character varying(80), frleft bigint, toleft bigint, frright bigint, toright bigint, frleft_a bigint, toleft_a bigint, frright_a bigint, toright_a bigint, stateroad bigint, cartoname character varying(80), corp character varying(80), speed bigint, one_way character varying(80), classname character varying(80), l_zipname character varying(80), r_zipname character varying(80), ft_cost numeric, tf_cost numeric, labelname character varying(80), f_node bigint, t_node bigint, cartolevel character varying(80), shape_len numeric, the_geom geometry, CONSTRAINT streets_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
) WITHOUT OIDS; ALTER TABLE streets OWNER TO yancho;
Anything I need to change please?
Thanks beforehand! :)
Matthew
yancho11/19/07 16:33:34 (3 years ago)-
Message #252
The link above, which Anton told you, would have answered you this question, too. It says:
ALTER TABLE streets ADD COLUMN source integer; ALTER TABLE streets ADD COLUMN target integer; ALTER TABLE streets ADD COLUMN length double precision;
Those columns need to be added before you run the assign_vertex_id function.
daniel11/19/07 17:18:56 (3 years ago)-
Message #256
Thanks alot for your help. It did work - I projected the route to the layers I had and worked perfect:)
My last question would be please : How can I transform this point : 642884.15625, 225182.78125 (x,y) to a whole number so I can use it as a starting point, and the same for the second number so I use it as the ending point?
Thanks and regards :)
Matthew
yancho11/20/07 00:57:20 (3 years ago) -
Message #254
Thanks daniel for your help :)
The problem is after I successfuly added those commands I am getting another error :
NOTICE: CREATE TABLE will create implicit sequence "vertices_tmp_id_seq" for serial column "vertices_tmp.id" CONTEXT: SQL statement "CREATE TABLE vertices_tmp (id serial)" PL/pgSQL function "assign_vertex_id" line 19 at execute statement
ERROR: relation with OID 51307 does not exist SQL state: 42P01 Context: SQL statement "SELECT id, the_geom FROM vertices_tmp WHERE distance(the_geom, $1 ) < $2 " PL/pgSQL function "point_to_id" line 7 at select into variables PL/pgSQL function "assign_vertex_id" line 50 at assignment
This is the query I am using : SELECT assign_vertex_id('streets', 0.001, 'the_geom', 'gid');
Sorry for all this troubles. Thanks for your help! :)
yancho11/19/07 18:52:05 (3 years ago)
-
-
-
Message #253
Sorry for double post :( darn Opera!
yancho11/19/07 18:47:04 (3 years ago)
-
-
-
-
-
-
-