2

I would like to perform routing in MS SQL 2012 similar to pgRouting.

In pgRouting, I'd do the following steps to create a routable network:

  1. Create a database routing with template template_routing

  2. Create table road_network with following constraints

    CHECK (st_ndims(the_geom) = 2)
    CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL)
    CHECK (st_srid(the_geom) = 4030)
    
    Then
    -- @ CREATE INDEX FOR THE ROAD TABLE -------------------- IMPORTANT
    
    CREATE INDEX spatialindex_road
        ON road_network
        USING gist
        (the_geom);
    
  3. Perform the following queries:

    ALTER TABLE road_network ADD COLUMN "source" integer;
    ALTER TABLE road_network ADD COLUMN "target" integer;
    
    SELECT assign_vertex_id('road_network', 0.00001, 'the_geom', 'gid');
    
    CREATE INDEX source_idx ON road_network("source");
    CREATE INDEX target_idx ON road_network("target");
    
    ALTER TABLE road_network  ADD COLUMN length double precision;
    UPDATE road_network  SET length = length(the_geom);
    
    
    ALTER TABLE road_network  ADD COLUMN reverse_cost double precision;
    UPDATE road_network  SET reverse_cost = length;
    
    ALTER TABLE road_network  ADD COLUMN x1 double precision;
    ALTER TABLE road_network  ADD COLUMN y1 double precision;
    ALTER TABLE road_network  ADD COLUMN x2 double precision;
    ALTER TABLE road_network  ADD COLUMN y2 double precision;
    
    UPDATE road_network  SET x1 = x(ST_PointN(the_geom, 1));
    UPDATE road_network  SET y1 = y(ST_PointN(the_geom, 1));
    UPDATE road_network  SET x2 = x(ST_PointN(the_geom, ST_NumPoints(the_geom)));
    UPDATE road_network  SET y2 = y(ST_PointN(the_geom, ST_NumPoints(the_geom)));
    alter table road_network add column cost double precision default 0;
    update road_network set cost=0.1 where type='NH';
    update road_network set cost=0.2 where type='SH';
    update road_network set cost=0.3 where type='major';
    update road_network set cost=0.4 where type='minor';
    update road_network set cost=1.2 where type='colony';
    update road_network set cost=0.8 where type='third';
    
  4. Now the network table is created.

    To check, run this query on your postgreSQL.... assign table name name and create a table

    CREATE TABLE shortest_path_astar_table_3(gid int4) with oids;
    SELECT AddGeometryColumn( 'shortest_path_astar_table_3', 'the_geom', 4030, 'MULTILINESTRING', 2 );
    INSERT INTO shortest_path_astar_table_3(the_geom) 
    SELECT the_geom FROM astar_sp_directed('road_network',37,43,true,true);
    

    Open shortest_path_astar_table_3 on QGIS and check the path.

Is there any similar way to perform similar queries in SQL Server 2012?

  • When you say "SQL" I assume you actually mean "MS SQL Server"? Worth being specific, since "SQL" is just a query language... – Craig Ringer Apr 3 '14 at 12:50
  • Yes it is !! Ms SQL Server 2012 – Vikram Apr 4 '14 at 4:09
  • There is no in database solutions for routing that i know for MS SQL. But if you have network topology table in MS SQL you can write one , or use 3rd party middleware – simplexio Apr 4 '14 at 6:30
  • Can you list out the 3rd party middleware ? And how to use network topology table in MS SQL. Please share some links or steps @simplexio – Vikram May 8 '14 at 5:23
3

SQL Server do not support routing, afaik.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.