How to display real-time data in a web browser?

1
votes

I have been given a task to design an ASP.net web application that does the following:

(1) A related mobile application will be used by associates around the city. As they use the mobile application, their GPS locations are sent to a server.

(2) The web app must use Google Maps to draw a map of the city and display markers showing the location of each associate.

It was suggested to me that the web application should automatically refresh the data every three minutes.

However, I can't stop wondering why I'm stuck with a polling solution; this seems obsolete in the 21st century.

As far as the communication between the browser and the web server, I know of two solutions.

One is a long-running HTTP request kept open for the duration of the user's visit to the app. (Demo here: http://danielsadventure.info/webconsoledemo)

The other is HTML5 Web Sockets.

I think that either of the above solutions should be sufficient for streaming data from the server to the browser in near real-time.

That being said, the data model of the web application is in MS SQL. I know of no way to make it so that the web server knows when the data model is updated without making the web server poll the SQL server.

I suppose that one possible solution might be to get the associate locations from SQL when the web app loads and then when updates from associates come it, send them down to all connected web browsers at the same time that they are updated in SQL. That would entail having the thread that processing the incoming data from the associate automatically communicate with other threads that were sending location data to a browser and I don't know how to do that. Even then, I see myself having to poll one thread from another.

What is a good way of accomplishing my client's request without having to resort to a polling solution? If a polling solution is necessary, how can I minimize the drawbacks to it?

I know that there are solutions out there. Otherwise, how would GMail be able to tell you when a new message arrives?

Edit: A specific situation: Suppose that Adam is an associate in the field and Omar is an operator watching Adam's whereabouts using this application.

Omar logs in; his browser sends a request to the server, which sends back a response telling where Adam is located. Omar's browser then starts a long-running HTTP request to receive updates about where Adam goes.

A few minutes later, Adam hops into a company truck and drives down the road. As the truck begins to move, Adam's mobile application sends an update to the web server indicating that he has moved.

Given the above scenario, how can the web server communicate to Omar's web browser that Adam has moved (without Omar's browser or the web server engaging in any sort of polling)?

3
javascriptasp.netajaxgoogle-maps-api-3real-time
I believe there are ways to create rivers in MSSQL, and then emit updates to the clients with something like socket.io - adeneo
However, bulding the whole thing in Node seems more appropriate when you're doing this sorta thing. - adeneo
Can you not broadcast some kind of event when data is inserted into the MSSQL server that would in turn result in a websocket push? - Kevin B
What is this "broadcast event" you're talking about, @Keven B. Perhaps you could elaborate a bit and post your idea as an answer. - Vivian River
well, it depends heavily on what platform you are using to insert the data (asp, node, etc) and what platform you are using for the websocket server (asp, node, etc). When i say event, it could even be a function call of some kind. I'm not familiar enough with asp.net to suggest any kind of asp solution. - Kevin B

3 Answers

0
votes

Referring to the specific situation in your edit:

Realtime (the company I work for) has a cloud based messaging service based on pub/sub that would allow Omar to subscribe to Adam's channel using the JavaScript SDK (websockets with fallbacks under the hood).

When Adam changes location his app sends an update to the webserver. The webserver saves the data to the database and upon success publishes the new location to Adam's channel using the .NET SDK (assuming you're using .NET) or REST API (a simple POST).

The published message with Adam's location will be delivered by the Realtime servers to Omar's browser subscription (in fact to any other subscriptions for the same channel), which will parse the message and render the appropriate data in the Google map.

0
votes

PostgreSQL has pub/sub

PostgreSQL has PUBLISH, but if you're moving away form MSSQL you should move away from SQL altogether. See this DEV.TO article on PostgreSQL pub/sub and this comment on why it is a bad idea.

DBs with Realtime Updates or Change Feeds

If you do switch the data store I'd recommend GCP Firebase Realtime Database (< 500 users) or Redis 5+ "streams" data type. CosmosDB has change feeds and might be great if you're already using Azure Functions, but I think Firebase Realtime and Redis streams are much easier to use from a web server. (It seems like CosmosDB change feeds are designed for data replication or data warehouse, not for websocket push.)

For more background on polling-per-query (Meteor style) vs log tailing, and why neither approach works at scale, see https://medium.baqend.com/real-time-databases-explained-why-meteor-rethinkdb-parse-and-firebase-dont-scale-822ff87d2f87.

Last Mile (Push Services)

You can use Firebase directly from the browser, or from the web server. In addition to Firebase there are plenty of "push" services that will implement the last mile for you. The linked article was written by Baqend employee, but I've never tried them. I think "Pusher" is the big one. And for the MS/.NET folks SignalR Core is now a service on Azure.

0
votes

MSSQL Change Tracking

SQL Server doesn't have that. You might have to keep the location data in MSSQL if you're searching by geo lat/long.

If you keep this in MSSQL then the feature you're looking for is "Change Tracking". Change Tracking puts the PK of the changed row into a new table. You still have to poll the "change table" to get the changes. There's no way using MSSQL to push them to your web server.

Change Tracking is a modern version of CDC, and more efficient than SQL triggers. You didn't mention what version of SQL you're running, "Change Tracking" has been in SQL Server since 2008, but it had a lot of issues in earlier versions. The experts I read didn't recommend it until SQL 2017+ or Azure SQL.

Polling

Some high-end DBs have "change feed" feature (CosmosDB), but if polling is good enough for Grafana it is good enough for me. (Grafana is the default dashboard for Kubernetes.)

But you can't run a "query the DB per user", that will kill the DB quickly. I'd create a single background thread per web server to poll the DB, then dispatch new data to users with server-sent-events (SSE), socket.io, or SignalR.