I want to create an application displaying road network with traffic details.
I am using OpenLayers 3 and GeoServer 2.14 for this purpose. My data (stored in PostgreSQL+ PostGIS) has around half a million geometries (road network) which is published in Geoserver through SQL view. Geometry of the data is fixed but its attributes related to traffic are changing every 2 minutes. The approach we are currently using is slow. Is there any other option available to render data faster.
We have road network data with unique link id assigned to each road segment. There is one more column which stores speed data. According to the speed we display color of the road segment (red, orange, green etc.) on map using openlayers. This speed info updates every 2 min while road geometry is fixed. We have indexes built on the table. Currently we are fetching these details using SQL view using Geoserver where we are calling a function which fetches required data. For better performance we are also passing bbox (via view params) of tile into the function to fetch data of specific bbox only. This has improved the performance when data count is approx 2.5 lacs. Now the data count has just doubles and this approach is not working as expected.