Svenyo commented 7 years ago

So, here are the final CSVs covering POIs and categories from the last part of the campaign. Can you import the photos according to the workflow and rake task used in https://github.com/sozialhelden/wheelmap/issues/630 ?

1000miles commented 7 years ago

Thanks for the file, @Svenyo . 👍

1000miles commented 7 years ago

TODO

1. Check & import in Development

[x] Import BE_Final.csv
[x] Import DE_Final.csv
[x] Import DK_Final.csv
[x] Import ES_Fina.csv
[x] Import FR_Fina.csv
[x] Import IR_Fina.csv
[x] Import IT_Fina.csv
[x] Import NL_Fina.csv
[x] Import UK_Fina.csv

2. Check & import on Staging

[x] Import BE_Final.csv

# 2017-09-11

POIS not found: 5.
POIS and OSM_ID nil: 5.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 7649, Photos: 35779.
EXISTING AFTER IMPORT: ProvidedPois: 7649, Photos: 35779.

[x] Import DE_Final.csv

# 2017-09-11

POIS not found: 4837.
POIS and OSM_ID nil: 6164.
CREATED: Photos: 4586
CREATED: Provided Pois: 2013
EXISTING BEFORE IMPORT: ProvidedPois: 7649, Photos: 35779.
EXISTING AFTER IMPORT: ProvidedPois: 9662, Photos: 40365.

[x] Import DK_Final.csv

# 2017-09-11

POIS not found: 67.
POIS and OSM_ID nil: 90.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 9662, Photos: 40365.
EXISTING AFTER IMPORT: ProvidedPois: 9662, Photos: 40365.

[x] Import ES_Final.csv

# 2017-09-11

POIS not found: 256.
POIS and OSM_ID nil: 279.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 9662, Photos: 40365.
EXISTING AFTER IMPORT: ProvidedPois: 9662, Photos: 40365.

[x] Import FR_Final.csv

# 2017-09-11

POIS not found: 63.
POIS and OSM_ID nil: 87.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 9662, Photos: 40365.
EXISTING AFTER IMPORT: ProvidedPois: 9662, Photos: 40365.

[x] Import IR_Final.csv

# 2017-09-11

POIS not found: 157.
POIS and OSM_ID nil: 172.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 9662, Photos: 40365.
EXISTING AFTER IMPORT: ProvidedPois: 9662, Photos: 40365.

[x] Import IT_Final.csv

# 2017-09-11

POIS not found: 1753.
POIS and OSM_ID nil: 1834.
CREATED: Photos: 4
CREATED: Provided Pois: 2
EXISTING BEFORE IMPORT: ProvidedPois: 9662, Photos: 40365.
EXISTING AFTER IMPORT: ProvidedPois: 9664, Photos: 40369.

[x] Import NL_Final.csv

# 2017-09-11

POIS not found: 174.
POIS and OSM_ID nil: 187.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 9664, Photos: 40369.
EXISTING AFTER IMPORT: ProvidedPois: 9664, Photos: 40369.

[x] Import UK_Final.csv

# 2017-09-11

POIS not found: 876.
POIS and OSM_ID nil: 1109.
CREATED: Photos: 14
CREATED: Provided Pois: 6
EXISTING BEFORE IMPORT: ProvidedPois: 9664, Photos: 40369.
EXISTING AFTER IMPORT: ProvidedPois: 9670, Photos: 40383.

Result Staging


# 2017-09-11

>> 40.383 existing photos (in general)
>> 14.565 existing photos (uploaded by streetspotr)
=> 14.537 imported photos (uploaded by streetspotr) from 2017-09-07 to 2017-09-11

2. Check & import on Production

[x] Import BE_Final.csv


# 2017-09-11

POIS not found: 0.
POIS and OSM_ID nil: 0.
CREATED: Photos: 0
CREATED: Provided Pois: 0
EXISTING BEFORE IMPORT: ProvidedPois: 29884, Photos: 108478.
EXISTING AFTER IMPORT: ProvidedPois: 29884, Photos: 108478.

[x] Import DE_Final.csv

# 2017-09-11

POIS not found: 348.
POIS and OSM_ID nil: 427.
CREATED: Photos: 6115
CREATED: Provided Pois: 2328
EXISTING BEFORE IMPORT: ProvidedPois: 29884, Photos: 108478.
EXISTING AFTER IMPORT: ProvidedPois: 32212, Photos: 114603.

[x] Import DK_Final.csv

# 2017-09-11

POIS not found: 5.
POIS and OSM_ID nil: 5.
CREATED: Photos: 4
CREATED: Provided Pois: 2
EXISTING BEFORE IMPORT: ProvidedPois: 32212, Photos: 114603.
EXISTING AFTER IMPORT: ProvidedPois: 32214, Photos: 114607.

[x] Import ES_Final.csv

# 2017-09-11

POIS not found: 1.
POIS and OSM_ID nil: 1.
CREATED: Photos: 6
CREATED: Provided Pois: 3
EXISTING BEFORE IMPORT: ProvidedPois: 32214, Photos: 114607.
EXISTING AFTER IMPORT: ProvidedPois: 32217, Photos: 114613.

[x] Import FR_Final.csv

# 2017-09-11

POIS not found: 0.
POIS and OSM_ID nil: 0.
CREATED: Photos: 5
CREATED: Provided Pois: 2
EXISTING BEFORE IMPORT: ProvidedPois: 32217, Photos: 114613.
EXISTING AFTER IMPORT: ProvidedPois: 32219, Photos: 114618.

[x] Import IR_Final.csv

# 2017-09-11

POIS not found: 1.
POIS and OSM_ID nil: 1.
CREATED: Photos: 201
CREATED: Provided Pois: 96
EXISTING BEFORE IMPORT: ProvidedPois: 32219, Photos: 114618.
EXISTING AFTER IMPORT: ProvidedPois: 32325, Photos: 114819.

[x] Import IT_Final.csv

# 2017-09-11

POIS not found: 16.
POIS and OSM_ID nil: 17.
CREATED: Photos: 374
CREATED: Provided Pois: 184
EXISTING BEFORE IMPORT: ProvidedPois: 32315, Photos: 114819.
EXISTING AFTER IMPORT: ProvidedPois: 32499, Photos: 115193.

[x] Import NL_Final.csv

# 2017-09-11

POIS not found: 9.
POIS and OSM_ID nil: 12.
CREATED: Photos: 158
CREATED: Provided Pois: 79
EXISTING BEFORE IMPORT: ProvidedPois: 32499, Photos: 115193.
EXISTING AFTER IMPORT: ProvidedPois: 32578, Photos: 115351.

[x] Import UK_Final.csv

# 2017-09-11

POIS not found: 33.
POIS and OSM_ID nil: 46.
CREATED: Photos: 626
CREATED: Provided Pois: 278
EXISTING BEFORE IMPORT: ProvidedPois: 32578, Photos: 115351.
EXISTING AFTER IMPORT: ProvidedPois: 32856, Photos: 115977.

Result Production


# 2017-09-11

>> 115.977 existing photos (in general)
>> 44.886 existing photos (uploaded by streetspotr)
=> 7.494 imported photos (uploaded by streetspotr) on # 2017-09-11

Clean up

[x] Remove csv files from staging
[x] Remove csv files from production

1000miles commented 7 years ago

@Svenyo cc @holgerd @anngreenberg

Staging import is done. We imported 9.933 photos on staging with BE, DE, DK, ES, FR, IR, IT, NL and UK today. We noticed that there were a lot of osm_id that did not exist anymore, please find the stats above for each country.

On monday, 2017-09-11, we will import the same csv on production.

1000miles commented 7 years ago

@Svenyo cc @holgerd @anngreenberg

Check and import on production is done, please find the updated stats above . According to our import stats only 5 new photos have been imported from the last recent Streetspotr sept 2017 batch which implies most of them have already been imported or the poi could not be found anymore.


# 2017-09-11

+--------+------------------+------------+---------------------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------+
| id     | image_processing | poi_id     | created_at          | image_width | image_height | source_url                                                                                                         |
+--------+------------------+------------+---------------------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------+
| 124107 |                0 | 2956247446 | 2017-09-11 09:12:54 |        3264 |         2448 | https://download.streetspotr.com/c3dbc8885eb9780f18eee77666f52b3aeb7af405/79f41321e9371c83c85303722b82d3b184eafaa8 |
| 124108 |                0 | 2956247446 | 2017-09-11 09:12:54 |        2448 |         3264 | https://download.streetspotr.com/5e921e868dac49be0cf076e7e5eacf4614f854e8/d761020cb1008ee33bd066fda42fe57d7bdf303a |
| 124109 |                0 | 2956247446 | 2017-09-11 09:12:55 |        2448 |         3264 | https://download.streetspotr.com/67a6f4c3f3f797d4dc1954b2970a48f97d8f1cb0/3aa1a7113514acb762a16276dc68c935164ad178 |
| 124110 |                0 | 3690038250 | 2017-09-11 09:24:31 |        1728 |         2880 | https://download.streetspotr.com/22af7b1e1a45656c3732cdf2d98df3162a5fce08/57c5b4a20d4c9d51e6010bc34e090b295ba61e39 |
| 124111 |                0 | 3690038250 | 2017-09-11 09:24:32 |        2880 |         1728 | https://download.streetspotr.com/d8d5589c2247d886a2dc06be6648127f7400257f/bc5f1364017d60d8bc52f901a99e59f278289538 |
+--------+------------------+------------+---------------------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------+
5 rows in set, 1 warning (0.06 sec)

Svenyo commented 7 years ago

Hi, are you sure you imported the right set of CSV files? The new files I attached above all had the naming COUNTRYCODE_Final.csv and I wonder why the csv's you've listed are called COUNTRYCODE_2016.csv.

1000miles commented 7 years ago

@Svenyo cc @holgerd @anngreenberg

Oh, you are right. I am sorry. I had another Streetspotr folder in the same directory. Will fix this. Thanks for the hint.

Svenyo commented 7 years ago

Ok, great! Thanks!

1000miles commented 7 years ago

@Svenyo cc @holgerd @anngreenberg

Update, Mon Sep 11 17:22:21 CEST 2017

7494 photos have been imported on production today of which 4125 photos still have to go through the carrierwave image processing delayed_job until all photo versions are live. As soon as all of them are finished, we will let you know here.

1000miles commented 7 years ago

@Svenyo cc @holgerd @anngreenberg

The image processing is done. Please log into the wheelmap admin dashboard if you want to review the images.

# mysql> SELECT COUNT(*) FROM photos WHERE user_id = xxxx AND image_processing = 1 AND created_at LIKE "2017-09-11%" ;

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

Svenyo commented 7 years ago

Dear @1000miles, thanks, looks fine to me. :-)

ghost commented 7 years ago

Thank you for checking, @Svenyo. Import done, therefore closing.

sozialhelden / wheelmap-classic

Import new pics from Streetspotr spots #651

TODO

1. Check & import in Development

2. Check & import on Staging

Result Staging

2. Check & import on Production

Result Production

Clean up

Update, Mon Sep 11 17:22:21 CEST 2017