latitude longitude coordinates from SQL Server and plot on Leaflet map

Report · Jun 05, 2015

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: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>

<head>

</head>

<body>

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>

<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>

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.

Report · Jun 05, 2015

You need to add Leaflet Markers like:

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

So you could loop in your script like:

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

</cfoutput>

Documentation here:

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

Report · Jun 05, 2015

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

</div>

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

</cfoutput>

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;

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

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.

Report · Jun 08, 2015

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

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);

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

Report · Jun 08, 2015

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>

<head>

<style>

body {

padding: 0;

margin: 0;

}

html, body, #map {

height: 100%;

width: 100%;

}

</style>

</head>

<body>

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>

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);

</cfoutput>

</script>

</body>

</html>

latitude longitude coordinates from SQL Server and plot on Leaflet map

1 Correct answer

Photos