search

RefugeRestrooms / refugerestrooms

REFUGE restrooms indexes and maps safe restroom locations for trans, intersex, and gender nonconforming individuals.
http://www.refugerestrooms.org
GNU Affero General Public License v3.0
883 stars 263 forks source link

Restrooms were submitted with latitude: null, longitude: null (geocode the backlog of old entries) #511

Closed DeeDeeG closed 4 years ago

DeeDeeG commented 5 years ago

Context

See:

Summary

Lots of our restroom entries these days have latitude: null and longitude: null.

(Any use-case of the app focused on a map view, or search-by-location, will highlight this problem. Since those are mostly how the mobile apps are used, this issue is most apparent to users of the mobile app. However, this affects the core database itself, so the web app and both mobile apps are likely just seeing the same issue; We're receiving entries with no actual geo-location data.)

Suggested Solution for Backlog

Solution to Prevent more Entries with Null Geolocation Data

DeeDeeG commented 5 years ago

To verify this is happening, query the "latest restrooms" API endpoint. Inspect latitude and longitude fields to see they are set as null.

Example data (10 most recent entries as of writing): ```json [ { "id": 45072, "name": "starbucks ", "street": "main street", "city": "lehi", "state": "utah", "accessible": true, "unisex": true, "directions": "", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-11T01:33:37.700Z", "updated_at": "2018-10-11T01:33:37.700Z", "downvote": 0, "upvote": 1, "country": "US", "changing_table": true }, { "id": 45071, "name": "Cinemark legacy", "street": "7201 Central Expy", "city": "Plano", "state": "Texas", "accessible": false, "unisex": true, "directions": "Inside the theater past the check in, between the men and women’s restroom. ", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-11T00:16:18.553Z", "updated_at": "2018-10-11T00:16:18.553Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": true }, { "id": 45070, "name": "Metropolitan Community Church of Tampa", "street": "408 East Cayuga St", "city": "Tampa ", "state": "FL", "accessible": true, "unisex": true, "directions": "", "comment": "LGBT friendly church, the pastor is trans ", "latitude": null, "longitude": null, "created_at": "2018-10-11T00:04:34.406Z", "updated_at": "2018-10-11T00:04:34.406Z", "downvote": 0, "upvote": 1, "country": "US", "changing_table": true }, { "id": 45069, "name": "Spokane Falls Community College Science Building (Bldg. 28)", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": true, "unisex": true, "directions": "Four single use, accessible, all-gender restrooms located in the Science Building, three on the ground floor (28-117, 28-125, 28-126) and one on the second floor (28-213). Two of them contain showers (28-125, 28-126).", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:49:30.646Z", "updated_at": "2018-10-10T22:49:30.646Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": true }, { "id": 45068, "name": "Spokane Falls Community College Building 24", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": true, "unisex": true, "directions": "Two single use, accessible, all-gender restrooms located in Building 24: room 24-234 and 24-334. Both are found off the small hallway near the elevator.", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:43:44.141Z", "updated_at": "2018-10-10T22:43:44.141Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": false }, { "id": 45067, "name": "Spokane Falls Community College Human Services Building (Bldg. 16)", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": true, "unisex": true, "directions": "Two single use, accessible, all-gender restrooms located in Building 16, rooms 16-116 and 16-118.", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:38:38.609Z", "updated_at": "2018-10-10T22:38:38.609Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": false }, { "id": 45066, "name": "Spokane Falls Community College Music Building (Bldg. 15)", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": true, "unisex": true, "directions": "Three single use, accessible, all-gender restrooms located in the Music Building, one on each level. On the lower level (basement), the restroom is located between the men's and women's dressing rooms, room 15-017. On the ground floor, the restroom is located near the band room, room 15-111. On the second floor, restroom is located off the office suite, hallway on the west wing (closest to library).", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:35:57.229Z", "updated_at": "2018-10-10T22:35:57.229Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": true }, { "id": 45065, "name": "Spokane Falls Community College Stadium (Building 13)", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": false, "unisex": true, "directions": "", "comment": "Two single use, all-gender locker rooms with showers located inside the stadium building, one toward each end of the hallway, rooms 13-102A and 13-107A. Showers are narrow and not ADA accessible.", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:30:06.713Z", "updated_at": "2018-10-10T22:30:06.713Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": false }, { "id": 45064, "name": "Spokane Falls Community College Photography Building (Bldg. 11)", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": true, "unisex": true, "directions": "Single use, accessible, all-gender restroom. Passing through the lobby, take a right, then look to the right again. Number over the door says 120K.", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:26:04.714Z", "updated_at": "2018-10-10T22:26:04.714Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": false }, { "id": 45063, "name": "Spokane Falls Community College Athletics Building (Bldg. 7)", "street": "3410 W Fort George Wright Drive", "city": "Spokane", "state": "WA", "accessible": true, "unisex": true, "directions": "Three single use, accessible, all-gender restrooms located in the Athletics Building (Bldg. 7). Room 7-109 is a locker room with an accessible shower. Room 7-123 contains a changing table. On the second floor there is another single-use all-gender restroom, 7-205.", "comment": "", "latitude": null, "longitude": null, "created_at": "2018-10-10T22:22:45.629Z", "updated_at": "2018-10-10T22:22:45.629Z", "downvote": 0, "upvote": 0, "country": "US", "changing_table": true } ] ```

^ click to expand.

DeeDeeG commented 5 years ago

I believe we are over our query limit for the Maps API. This is the full output of what happens in testing when I submit a new restroom:

web_1  | Started POST "/restrooms" for 172.18.0.1 at 2018-10-11 18:40:52 +0000
web_1  | Processing by RestroomsController#create as HTML
web_1  |   Parameters: {"utf8"=>"✓", "authenticity_token"=>"qPZcmZwkgPT7gkp1HWwz0XGnDR7PCyFTi3sG0ck54doh9WWuBu3U07MDU9Aw6Ef/cxLsEcF9mIWfyUppzhWdxA==", "restroom"=>{"name"=>"a", "street"=>"a", "city"=>"a", "state"=>"a", "country"=>"US", "latitude"=>"", "longitude"=>"", "accessible"=>"false", "unisex"=>"false", "changing_table"=>"false", "directions"=>"hi", "comment"=>"hello"}, "commit"=>"Save Restroom"}
web_1  |    (0.2ms)  BEGIN
web_1  |   ↳ app/controllers/restrooms_controller.rb:31
web_1  | Google Geocoding API error: over query limit.
web_1  |   Restroom Create (0.8ms)  INSERT INTO "restrooms" ("name", "street", "city", "state", "directions", "comment", "created_at", "updated_at", "country") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["name", "a"], ["street", "a"], ["city", "a"], ["state", "a"], ["directions", "hi"], ["comment", "hello"], ["created_at", "2018-10-11 18:40:52.810075"], ["updated_at", "2018-10-11 18:40:52.810075"], ["country", "US"]]
web_1  |   ↳ app/controllers/restrooms_controller.rb:31
web_1  |    (26.2ms)  COMMIT
web_1  |   ↳ app/controllers/restrooms_controller.rb:31
web_1  | [i18n-debug] en.restroom.flash.new => "A new restroom entry has been created for %{name}."
web_1  | Redirected to http://127.0.0.1:3000/restrooms/22
web_1  | Completed 302 Found in 439ms (ActiveRecord: 27.3ms)

particularly of note is this line:

web_1  | Google Geocoding API error: over query limit.

And that the new restroom entry page sent "latitude"=>"", "longitude"=>"" to our API when POSTing the new entry to [in this case the testing version of] our db.

DeeDeeG commented 5 years ago

Here's a FAQ entry for addressing this issue, over at the Maps API documentation.

How do I resolve the error codes: OVER_DAILY_LIMIT or OVER_QUERY_LIMIT? https://developers.google.com/maps/faq#over-limit-key-error

DeeDeeG commented 5 years ago

Extra Credit: We might spend some time ensuring how we use our API key matches these guidelines:

https://developers.google.com/maps/documentation/geocoding/web-service-best-practices https://developers.google.com/maps/api-key-best-practices

(As usual with Maps API documentation, there's at least two pages for almost identical subjects, and it's not always clear which one pertains to our particular usage... But hopefully either of these guides is relevant, and hopefully they aren't contradictory...)

Edit: I think it's the "API key best practices" one.

mi-wood commented 5 years ago

Reopening because I still have to fix all the old entries

DeeDeeG commented 4 years ago

Just documenting that we had another extended geocoding outage that lasted until 2020-04-11T18:04:55.252Z in API/server time.

The outage appears to have started at 2019-11-11T12:08:53.407Z API/server time.

This represents roughly 1790 entries, (that is, roughly 179 pages of 10 entries each), though some are duplicates, and some of these were successfully geocoded, perhaps via the "guess my location" client-side button.

mi-wood commented 4 years ago

Was able to fix these:

Restroom.where(latitude: nil).each do |r|
  r.save
end

Down to 24 with nil lat. Looks like they're mostly spam that go through somehow. I'll look later and maybe delete them.