Routing using Geographic Data Types


  • I want to use OSM data (preferably) to perform routing entirely in SQL server... 

    I liked the articles by Alastair Aitchison <Loading OSM to SQL>   and  <Loading OSM part-2>   and  the one which was looking for.. <Categorising and Route-finding>

    I also found old forum posts which indicated, that Route calculation was not done by many people when the geography data type was introduced .. and SQL Server didn't have the functionality at that time... (Thats what I got from my research about it)... 

    I am also aware of "ShortestLineTo"  function.... Have not tried it yet ..... 

    What I want to do is, based on my own weights given to the 'ways' (edges/roads/streets etc) of the network (Imported from OSM file), I want to perform routing, and wish to have 3-4 routes for each source-destination couples...  I have my own Visualization Software, to display vehicles running around on the map.....  The input to my Visualization Software would be a text file which has set of routes calculated by SQL.

    Routing is not the main purpose of my work, so I would rather use any pre-existing APIs/Engines/SQL-AddOns  than write my own routing code.

    If anyone who has implemented routing on SQL using OSM and kept as Open Source, I would be really glad to use it. I assure of proper acknowledgement to the coder.

    I hope I m clear of what I intend to do.

    Thanks a lot in advance.

    Sanket Gupte
    2011년 6월 24일 금요일 오후 1:04


모든 응답

  • Yes, I've done routing in SQL Server using a variant of the A* algorithm, which I demonstrated during my SQLBits8 session. You can see a short video of me talking about it backstage, here:

    Or you can see my whole session, here:

    (though, having just looked at this myself, the video encoding appears to be a bit shaky - might just be my videocard).

    I based my routing algorithm on the PGRouting open source routing library, adapting the functions to work with SQL Server rather than PostGIS, and optimising it for the Ordnance Survey dataset I was using. I've not published my code because I was only intending it as a technical demonstration - it's hardly production-ready. However, if you look at you'll get a good starting point to creating your own.

    twitter: @alastaira blog:
    • 편집됨 tanoshimiEditor 2011년 6월 27일 월요일 오후 12:15 linkified...
    • 답변으로 표시됨 Stephanie Lv 2011년 7월 4일 월요일 오전 8:43
    2011년 6월 27일 월요일 오후 12:14
  • I have watched you presentation and I'm wondering how did you connect the PgRouting libraries with SQL Server and MVS. I ask you because I'm working in a small project which approaches this matter and it is a little fuzzy for me to link those applications. My question to you is: Can you give a blueprint or other information regarding this issue.

    Thank you in advance.



    2012년 2월 2일 목요일 오후 2:02
  • I didn't "connect" PGRouting to SQL Server, as such. I created my own SQLCLR routing function based on the algorithm and logic used in PGRouting rewritten in C#. 
    twitter: @alastaira blog:
    2012년 2월 2일 목요일 오후 6:15
  • Also did some rsearch on pre-calculation for maximum speed?
    2012년 4월 17일 화요일 오전 8:14
  • Hello @tanoshimi,

    Can you please share your code to get better understanding for a newbie like me.



    2016년 5월 12일 목요일 오후 2:13