Map of uncompleted streets in my city


#41

Easier said than done :frowning_face:


#42

Can you share a bit more code? Perhaps we can help.


#43

One way to fix this in a bit of a hacky way is to set the limit on the first query to 10000, and add another ‘limit=1000’ to a later step.
This consumes a bit more resources though, but I don’t know if that is a problem.


#44

This is in place now

The trouble with this approach is that the query can find 1k Nodes but then remove many of those on the way to the browser.
So you could conceivably have a request that only returns a couple Nodes (1k Nodes found, then 998 completed nodes / streets removed).
(scroll far to the right to see the description of what each line is doine)

user       = User.find(params[:user_id])                                                                                                                     # get the user
node_ids   = user.completed_node_ids                                                                                                                         # get the user's completed node ids for later use
street_ids = user.completed_street_ids                                                                                                                       # get the user's completed street ids for later use
@nodes     = Node.where("ST_Intersects(ST_MakeEnvelope(#{sw_lon},#{sw_lat},#{ne_lon},#{ne_lat}, 4326), geog)").order('id asc').limit(1000)                   # get all the nodes within this box (ordered and limited)
                 .map { |node| [node.latitude.to_f, node.longitude.to_f, node.id] unless node_ids.include?(node.id) || street_ids.include?(node.street_id) } # format the results in a way that the map can use & at the same time reject completed nodes or nodes for completed streets
                 .compact!                                                                                                                                   # drop null entries that are a result of the previous line

This is another option

The trouble with this approach is that a user can have many (tens/hundreds of) thousands of completed nodes and thousands of completed streets.
This makes the full SQL query ridiculously huge, but I haven’t tested it to know whether or not that actually matters.
(scroll far to the right to see the description of what each line is doine)

user       = User.find(params[:user_id])                                                                                                   # get the user
node_ids   = user.completed_node_ids                                                                                                       # get the user's completed node ids for later use
street_ids = user.completed_street_ids                                                                                                     # get the user's completed street ids for later use
@nodes     = Node.where("ST_Intersects(ST_MakeEnvelope(#{sw_lon},#{sw_lat},#{ne_lon},#{ne_lat}, 4326), geog)").order('id asc').limit(1000) # get all the nodes within this box (ordered and limited)
                 .where('id not in (?)', node_ids)                                                                                         # and that do not have any of these id values
                 .where('street_id not in (?)', street_ids)                                                                                # and that do not have any of these street_id values
                 .map { |node| [node.latitude.to_f, node.longitude.to_f, node.id] }                                                        # format the results in a way that the map can use

There’s a Weird Thing about Ruby in that it figures out what all the SQL is and collects it all into a single query.
So it isn’t actually running three different SQL queries in that last example with 3 different where lines.
The map is not SQL, though - that’s in-memory Ruby code.


#45

Would it be possible to put the node function in citymap instead of lifemap.
That way the query would be locked to one city and wouldn’t have to be so data intensive.

I would be satisfied with going node hunting one city at the time.
I guess most people run one city at the time anyway,


#46

The second bit of code is the order I’d choose, but I don’t know enough of this instance of SQL to predict what its performance would be.
Perhaps it’s nothing, and you can test to see if it works anyway.

Another way to keep things in check would be to first reduce the sets of user-completed nodes and streets to only include those that fall within the current view. That makes these sets smaller, and may reduce the SQL impact during the bigger operation, which will be then entirely limited by the current view…


#47

That might help, in that the query for Nodes within the current browser view could also limit by City ID. The question of how to efficiently remove finished Nodes and finished Streets wouldn’t be covered by that, though.

Either way - @hjkiddk & @rutgermooy - you helped me out a bunch! I have a new version of Node Hunter online now, which seems to work much better for me. Give it a try now, and let me know if it’s behaving better.

  • Properly excluding finished Nodes
  • Properly excluding finished Streets
  • Functional even without any of your LifeMap visible on the page
  • Functional even if you’ve finished a lot of Nodes/Streets

:crossed_fingers:


#48

As, far as I can tell, it works perfectly now, except one thing:

It does not exclude unfinished nodes from finished streets yet.

(your second point above)


#49

Ah ha! There’s a bug in my query where it will show unfinished Nodes for finished Streets if all of the Street’s nodes aren’t completely within the browser view. :grimacing: :man_facepalming:

:thinking: :thinking: :thinking:

Update: OK, that’s fixed … The actual issue turned out to be way more embarrassing. :flushed: The query that retrieved all the completed Streets within view wasn’t even returning the correct field. :man_facepalming:
I should be able to release this soon.