• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

latitude longitude coordinates from SQL Server and plot on Leaflet map

New Here ,
Jun 05, 2015 Jun 05, 2015

Copy link to clipboard

Copied

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.

Views

3.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Engaged , Jun 08, 2015 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.

<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

...

Votes

Translate

Translate
Engaged ,
Jun 05, 2015 Jun 05, 2015

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 05, 2015 Jun 05, 2015

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jun 08, 2015 Jun 08, 2015

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 08, 2015 Jun 08, 2015

Copy link to clipboard

Copied

LATEST

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation