demo (#22) - Shooting Star Tutorial (#66) - Message List

Shooting Star Tutorial

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;

yancho
11/16/07 02:32:06 (3 years ago)
Tree View Flat View (newer first) Flat View (older first)
  • 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.

    anton
    11/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

      yancho
      11/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

        yancho
        11/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.

        anton
        11/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 :)

          yancho
          11/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).

            anton
            11/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 :)

              yancho
              11/19/07 09:49:04 (3 years ago)
              • Message #250

                Sure, it will be enough.

                anton
                11/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

                  yancho
                  11/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.

                    daniel
                    11/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

                      yancho
                      11/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! :)

                      yancho
                      11/19/07 18:52:05 (3 years ago)
                      • Message #255

                        Ok found out that the problem was due to that the function gets compiled only once per connection, so I had to reconnect :)

                        Now it is just a matter of time to let it execute .. I screened it :)

                        Thanks for ur assistance :)

                        yancho
                        11/19/07 23:46:55 (3 years ago)
                • Message #253

                  Sorry for double post :( darn Opera!

                  yancho
                  11/19/07 18:47:04 (3 years ago)
Tree View Flat View (newer first) Flat View (older first)