Store traffic light coordinates as well as node IDs about better-intersections HOT 12 CLOSED

Great work on this - the speed up of the first page load is huge!

Updating lat/lon whenever hitting the OSM API is a much better idea than generating SQL for a once off batch update, that was a great call.

from better-intersections.

We can't add a lat and long if its invalid so would probably make sense to not allow submission if node Id does not exist ?

There are cases when the OSM data is missing the intersection (or the tags aren't correct - it might be tagged as an uncontrolled crossing rather than traffic signals).

In these cases I think it's preferable for the user to still be able to make a submission, hopefully with a well detailed text description of the location, and then I manually add an OSM node ID later (creating and updating the node tags in OSM if required)

Since we are checking the node's lat and long, would you want me to also alert the user if the node ID isn't valid.

This is a great point - I implemented this and merged a PR (I was able to entirely remove attemptFindNodeLocation.

from better-intersections.

I will take this up.

from better-intersections.

Thanks - happy to give work in progress feedback if you'd like as I think it might get complicated.

I think it might be best to store coordinates at measurement time, and add a latitude & longitude float field to the measurements. This indirectly stores node location history and is likely the simpler solution. A naive method for finding the location of a node would be finding the most recent measurement of that node and returning that latitude and longitude (which could be down in SQL for speed).

If alternatively we were to store nodes in a table (which I previously thought might be best but after writing this I think would be suboptimal):

• Add a new node type to the schema which functions like a cache, but stored in the DB rather than compiled statically at build time
• Adding longitude and latitude as float types in the node schema with sufficient accuracy, along with node id (and possibly other attributes if it makes sense)
• When inserting measurements, create the node in the nodes table if it doesn't exist (possibly future work: if it does exist update the coordinates).
• Write a script to pre-load the nodes table with existing node locations from the statically generated cache (as there are certainly nodes that are in the cache which don't exist in OSM prod any more), and a script to add in nodes from prod OSM data (overwriting in cases where nodes have shifted slightly)
• Modifying the cache so that nodes are looked up in the DB first and the static cache is removed. This should result in a 100% hit rate and speed up performance considerably. This could probably be achieved relatively simply by modifying the function that fetches node info from OSM to first look in the local DB first.

I'm open to other approaches but that is what came to mind!

from better-intersections.

Thanks for the additional context. Allow me to recap what I've understood and highlight any areas where I may need further clarification.

Problem: Sometimes nodes disappear from OSM prod. So when we request their lat and longitude, we don't get anything meaningful back when rendering the map and markers.

Solution: To me, the former solution of storing latitudes and longitudes with the measurement itself seems more reasonable.

Step 1-

1. Let's start with modifying db to also store lat and long in measurements ? We can make Lat & Long a float, so when a new measurement is recorded - we call the OSM API immediately and store these values. There is also the matter of already existing measurements, which can probably be set to null or we can get their lat and long from cache or ask OSM for them and store it in DB. How would you want to proceed with this ?

**Question- **I didn't understand what you meant by "A naive method for finding the location of a node would be finding the most recent measurement of that node and returning that latitude and longitude." - If we are storing the lat and long for each measurement, why would we need to do this ?

Step 2-
Will proceed with how the rest works after above clarification.

Thanks

from better-intersections.

Let's start with modifying db to also store lat and long in measurements ? We can make Lat & Long a float, so when a new measurement is recorded - we call the OSM API immediately and store these values.

Yes this sounds good! Just updating the schema and inserting new values when creating a new measurement would be a great first PR. A good second PR would be then checking the DB if a lat & lon exists for a OSM node when displaying measurements on the map (and if not, falling back to the cache check, and then falling back to the OSM API call).

There is also the matter of already existing measurements, which can probably be set to null

Yes this sounds good - if the type of lat and long is an optional float (ie. like sqllatitude real, as opposed to sallatitude real not null then they will be null by default.

we can get their lat and long from cache or ask OSM for them and store it in DB. How would you want to proceed with this ?

As long as you've added the new fields in the SQL schema I can load in data from the cache and from current prod where available! (we can split out this work into a follow up issue).

**I didn't understand what you meant by "A naive method for finding the location of a node would be finding the most recent measurement of that node and returning that latitude and longitude." - If we are storing the lat and long for each measurement, why would we need to do this ?

So we will have a lat & long per measurement, but when we place a pin on the map we need a lat and lon per OSM node id. Chances are the lat & lon values will be the same for every measurement for that OSM node, but the most correct value would be the latest (ie. where the timestamp for the measurement is closest to the current time). This means if multiple measurements have been taken for an intersection, and the intersection node gets moved slightly in between measurements (say to better align with aerial imagery), the pin will display in the most accurate and up to date location.

Feel free to ask further follow up questions - sorry for the delay on this reply.

from better-intersections.

There is another thing I wanted to ask - Would you want to record lat and long for those measurements in which the OSM Node Id has been input manually and not through the map we display. I believe an API call will have to made for those cases to fetch information on that node

from better-intersections.

Would you want to record lat and long for those measurements in which the OSM Node Id has been input manually and not through the map we display.

Yes I think we should, even though we'll need to do a bit more work.

I believe an API call will have to made for those cases to fetch information on that node

Yep agreed.

from better-intersections.

I've just discovered that when viewing a node on the OSM website they show up, even when deleted: https://www.openstreetmap.org/node/2268947005

I looked through the OSM API docs and it looks like the history endpoint will give us a simple and automated way to recover locations of deleted nodes: https://api.openstreetmap.org/api/0.6/node/2268947005/history

I think it still makes sense to store node locations to minimise the load on the OSM API server, speed up the first load, and also save us from manually creating the cache JSON file every so often. I'll try and merge some code in tonight utilising this history endpoint for when a node is missing.

from better-intersections.

I merged a PR adding a fallback request to the history endpoint, and also added an initial implementation of storing / accessing coordinates to the DB. I apologise if I duplicated any work you've already implemented.

Some possible follow up PRs if you're interested are:

• Adding logging when a node location is not found in the DB (so we can manually add it)
• A url endpoint that grabs locations for nodes missing a lat/lon and generates a line of SQL for each similar to UPDATE "public"."measurements" SET "latitude"=..., "longitude"=... WHERE "id"=...;
• Remove the code and file for the JSON cache.

from better-intersections.

Hey Jake, that's all good. I created #46 to address -
A url endpoint that grabs locations for nodes missing a lat/lon and generates a line of SQL for each similar to UPDATE "public"."measurements" SET "latitude"=..., "longitude"=... WHERE "id"=...;
Remove the code and file for the JSON cache.

I did not create a url endpoint though, just created a function to update and called it when the dbcache is missed. Seems to do the job on my db. But can add a url if you think that's useful.

Also regarding this -

There is another thing I wanted to ask - Would you want to record lat and long for those measurements in which the OSM Node Id has been input manually and not through the map we display. I believe an API call will have to made for those cases to fetch information on that node

Since we are checking the node's lat and long, would you want me to also alert the user if the node ID isn't valid. We can't add a lat and long if its invalid so would probably make sense to not allow submission if node Id does not exist ?

from better-intersections.

As far as I can tell this task is now done!

from better-intersections.