4 Replies Latest reply on Jun 8, 2015 8:24 AM by Kulp

    latitude longitude coordinates from SQL Server and plot on Leaflet map

    Kulp

      My question is essentially this:

      How do I transform data from SQL Server and map it in Leaflet?

      HINT: We are running Coldfusion 11, SQL Server 2000, Leaflet and JQuery if that helps in any way. If I need to download a javascript library I can easily do so.

       

       

      I have ambulance and fire events in a database with xy coordinates. My goal is to query out the current events and place them on a Leaflet map. All of our ambulances and fire trucks have gps in them and they are constantly broadcasting their location and status. Here is my query for getting the latest gps data for each truck;

       

       

      /*

      SQL Server 2000

      David Kulpanowski

      4 June 2015

      Lee County EMS

      */

      SELECT

      DateTimeStamp

      , RTRIM(CallSign) AS [CallSign]

      , RTRIM(UnitStatus) AS [UnitStatus]

      , Latitude

      , Longitude

      , CONVERT(VARCHAR(15), DateTimeStamp, 106) AS [CurrentDate]

      , CONVERT(VARCHAR(10), DateTimeStamp, 108) AS [CurrentTime]

      FROM FireEMSGPSDatabase

      RIGHT OUTER JOIN

      (

      SELECT

        RTRIM(CallSign) AS [Ambulance]

      , MAX(DateTimeStamp) AS [MostRecentTime]

      FROM HISTORY_201506

      WHERE RTRIM(CallSign) LIKE 'LCM__'

      GROUP BY RTRIM(CallSign)

      )

      AS [MostRecent] ON DateTimeStamp = MostRecent.MostRecentTime

      ORDER BY RTRIM(CallSign) ASC

      ;

       

       

      Shown Below is a sample output from this query;

       

       

      DateTimeStamp CallSign UnitStatus Latitude Longitude CurrentDate CurrentTime

      2015-06-04 17:14:17.357 LCM01 AQ 26.56428 -81.87044 04 Jun 2015 17:14:17

      2015-06-04 17:14:17.357 LCM01 AQ 26.56428 -81.87044 04 Jun 2015 17:14:17

      2015-06-04 17:14:18.670 LCM02 AQ 26.64074 -81.86507 04 Jun 2015 17:14:18

      2015-06-04 17:14:34.420 LCM03 AR 26.64157 -81.90973 04 Jun 2015 17:14:34

      2015-06-04 17:14:20.420 LCM04 TA 26.63885 -81.94159 04 Jun 2015 17:14:20

      2015-06-04 17:14:21.297 LCM05 AQ 26.4377 -82.07806 04 Jun 2015 17:14:21

       

       

      Shown below is my Coldfusion page with the leaflet map added in and the cfquery with cfoutput;

       

       

      <!DOCTYPE html>

      <html lang="en">

      <head>

      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

      <meta name="viewport" content="width=device-width, initial-scale=1.0">

      <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7/leaflet.css"/>

      <script src="http://cdn.leafletjs.com/leaflet-0.7.3/leaflet.js"></script>

      </head>

      <body>

      <cfquery datasource="GPSDatabase" name="AmbulanceLocation">

      SELECT

      DateTimeStamp

      , RTRIM(CallSign) AS [CallSign]

      , RTRIM(UnitStatus) AS [UnitStatus]

      , Latitude

      , Longitude

      , CONVERT(VARCHAR(15), DateTimeStamp, 106) AS [CurrentDate]

      , CONVERT(VARCHAR(10), DateTimeStamp, 108) AS [CurrentTime]

      FROM FireEMSGPSDatabase

      RIGHT OUTER JOIN

      (

      SELECT

        RTRIM(CallSign) AS [Ambulance]

      , MAX(DateTimeStamp) AS [MostRecentTime]

      FROM HISTORY_201506

      WHERE RTRIM(CallSign) LIKE 'LCM__'

      GROUP BY RTRIM(CallSign)

      )

      AS [MostRecent] ON DateTimeStamp = MostRecent.MostRecentTime

      ORDER BY RTRIM(CallSign) ASC

      </cfquery>

      <cfoutput query="AmbulanceLocation">

      <table>

      <tr>

      <td>#DateTimeStamp#</td>

      <td>#CallSign#</td>

      <td>#UnitStatus#</td>

      <td>#Latitude#</td>

      <td>#Longitude#</td>

      <td>#CurrentDate#</td>

      <td>#CurrentTime#</td>

      </tr>

      </table>

      </cfoutput>

      <div id="map"></div>

      <script>

      var map = L.map('map', {center: [26.5641867068354,-81.8704100173261],fullscreenControl: true, zoom: 12});

      L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png').addTo(map);

      </script>

      </body>

      </html>

       

       

      To sum it all up - I have a query for SQL Server that extracts the latest xy coordinates of my ambulances. I have a Coldfusion page that has a Leaflet map and the cfquery. My question is how to map out the coordinates in a Leaflet map. I cannot figure out the middle piece of getting the results of my query onto the Leaflet map.

      Also, this is a dynamic environment. Ambulances are constantly moving. This page needs to be dynamic so the page can be refreshed to show the latest data.

        • 1. Re: latitude longitude coordinates from SQL Server and plot on Leaflet map
          sdsinc_pmascari Level 2

          You need to add Leaflet Markers like:

           

          L.marker([50.5, 30.5]).addTo(map);

           

          So you could loop in your script like:

           

          <cfoutput query="AmbulanceLocation">

              L.marker([#Latitude#, #Longitude#]).addTo(map);

          </cfoutput>

           

          Documentation here:

          Documentation - Leaflet - a JavaScript library for mobile-friendly maps

          • 2. Re: latitude longitude coordinates from SQL Server and plot on Leaflet map
            Kulp Level 1

            I am trying this, and I think we are on to something here. After my cfquery I have the following:

            <div id="map">

            </div>

             

            <cfoutput query="AmbulanceLocation">

            L.marker([#Latitude#, #Longitude#]).addTo(map);

            </cfoutput>

            <script>

            var map = L.map('map', {center: [26.5641867068354,-81.8704100173261],fullscreenControl: true, zoom: 12});

            L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png').addTo(map);

            </script>

            But this preceding code only gives me the output that looks like json data and then following that - a blank map.

             

            Something else I attempted to do was enclose the script with a cfoutput like the following;

            <cfoutput query="AmbulanceLocation">

            <script>

            var map = L.map('map', {center: [26.5641867068354,-81.8704100173261],fullscreenControl: true, zoom: 12});

            L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png').addTo(map);

            L.marker([#Latitude#, #Longitude#]).addTo(map);

            </script>

            </cfoutput>

            But with this code I get an error message in Coldfusion Builder. It has a red X and it says

            "

            missing ] after element list

            illegal character

            illegal character

            missing ] after element list

            illegal character

            missing ] after element list

            illegal character

            missing ] after element list

            "

            I am not sure how to overcome this. But we are a step further. We have output that looks like the json should. Now I just need help getting the json output into the map.

            • 3. Re: latitude longitude coordinates from SQL Server and plot on Leaflet map
              sdsinc_pmascari Level 2

              You need to create the map and tile layer before you place markers and then run your loop inside the script tag.

               

              <script>

              var map = L.map('map', {center: [26.5641867068354,-81.8704100173261],fullscreenControl: true, zoom: 12});

              L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png').addTo(map);

              <cfoutput query="AmbulanceLocation">

              L.marker([#Latitude#, #Longitude#]).addTo(map);

              </cfoutput>

              </script>

               

              I highly recommend reading through the tutorials on the Leaflet site.  They go over these things in detail.

              Quick Start Guide - Leaflet - a JavaScript library for mobile-friendly maps

              • 4. Re: latitude longitude coordinates from SQL Server and plot on Leaflet map
                Kulp Level 1

                Thank you very much sdsinc_pmascari

                That was very helpful.

                I actually did try the code as you illustrated, however Coldfusion Builder threw an error stating "Illegally formed XML syntax". I went ahead and FTP it to my server and it appears to be working just fine.

                for illustrative purposes for individuals who may have the same issue, shown below is the code that is the final product. This code appears to be working well;

                 

                <!DOCTYPE html>

                <html lang="en">

                <head>

                <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

                <meta name="viewport" content="width=device-width, initial-scale=1.0">

                <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7/leaflet.css"/>

                <script src="http://cdn.leafletjs.com/leaflet-0.7.3/leaflet.js"></script>

                <style>

                body {

                padding: 0;

                margin: 0;

                }

                html, body, #map {

                height: 100%;

                width: 100%;

                }

                </style>

                </head>

                <body>

                <cfquery name="AmbulanceLocation">

                SELECT

                DateTimeStamp

                , RTRIM(CallSign) AS [CallSign]

                , RTRIM(UnitStatus) AS [UnitStatus]

                , Latitude

                , Longitude

                , CONVERT(VARCHAR(15), DateTimeStamp, 106) AS [CurrentDate]

                , CONVERT(VARCHAR(10), DateTimeStamp, 108) AS [CurrentTime]

                FROM HISTORY_201506

                RIGHT OUTER JOIN

                (

                SELECT

                  RTRIM(CallSign) AS [Ambulance]

                , MAX(DateTimeStamp) AS [MostRecentTime]

                FROM HISTORY_201506

                WHERE RTRIM(CallSign) LIKE 'LCM__'

                GROUP BY RTRIM(CallSign)

                )

                AS [MostRecent] ON DateTimeStamp = MostRecent.MostRecentTime

                ORDER BY RTRIM(CallSign) ASC

                </cfquery>

                <div id="map"></div>

                <script>

                var map = L.map('map', {center: [26.5641867068354,-81.8704100173261],fullscreenControl: true, zoom: 12});

                L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png').addTo(map);

                <cfoutput query="AmbulanceLocation">

                L.marker([#Latitude#, #Longitude#]).addTo(map);

                </cfoutput>

                </script>

                </body>

                </html>