Potential Data Cleanup activities

[copiousfreetime]

In the unsplash_photos.photo_location_country and unsplash_photos.photo_location_city the values appear to be freeform text that was probably direct user input, with effectively duplicate entries for example,

unsplash_lite=# select '>' || photo_location_city || '<' as city, '>' || photo_location_country || '<' as country,  count(*) from unsplash_photos where lower(photo_location_city) like '%london%' group by 1,2;
 ?column?  |       ?column?       | count
-----------+----------------------+-------
 >LONDON < | >United Kingdom <    |     1
 >London<  | >Canada<             |     7
 >London<  | >Egyesült Királyság< |     1
 >London<  | >England<            |     1
 >London<  | >U.K.<               |     2
 >London<  | >U.K<                |     1
 >London<  | >United Kingdom <    |     1
 >London<  | >United Kingdom<     |    73
 >London<  |                      |     3
(9 rows)```

It looks like there needs to be some data cleaning on these fields, definitely some stripping white space and such. Is it assumed that we should do our own location normalization on this and possibly add in a normalized_photo_location_country and normalized_photo_location_city ?

Also - over in unsplash_conversion.conversion_country this appears to be ISO 2 letter country codes. Is this guaranteed to be a valid ISO 2 letter country code? And was this data created based upon a maxmind geoip lookup or something similar?

Thanks so much for this dataset, I think it is going to be quite useful for demonstrational purposes. I hope these questions help increase the quality of what is a already great dataset.

[TimmyCarbone]

Hey! Thank you for all this feedback!

We haven't fully normalized these location fields internally yet, that's why they're still in "free form" (we do offer Google Maps suggestions in the form but sometimes they're not being used). There are multiple challenges here (including i18n) and I don't think this is a priority for us in the near future. I would say that, in this case and for now, it'd be up to the person utilizing the dataset to do some cleaning if they want better data quality on specific fields.

The conversion_country is a guaranteed ISO 3166-1 code (2 letter country code). The lookup is based upon the GeoLite2-City database from Maxmind.

Thank you, hopefully we can improve the dataset over time thank to everyone's feedback.
Also, of course, whenever we're implementing new things internally, it'll be reflected on the datasets.

[copiousfreetime]

@TimmyCarbone no worries, just wanting to make sure to utilize this dataset correctly. And I'll plan on doing various cleanup activities 😄

[TimmyCarbone]

Let's keep this issue opened so it stays on our and everyones radar though :)

I believe that having clean fields is important. We might and will probably get to normalizing the location fields at some point in the future, it's just not really planned yet. I'll keep you and everyone posted on this issue as soon as we have a plan to tackle it.

Also, if anyone wants to give it a shot, we'd be happy to implement good solutions from the open-source community!

[copiousfreetime]

Doing a few more text checks, and it looks like there are a few more fields that have leading / trailing whitespace. I figure I'll just throw other potential cleanup items on here.

• unsplash_photos.exif_camera_make
• unsplash_photos.exif_camera_model
• unsplash_photos.photo_location_name
• unsplash_conversions.keyword
• unsplash_keywords.keyword

As an example:

unsplash_lite=# select '>' || keyword || '<', count(*) from unsplash_keywords where keyword IN (' wallpaper', 'wallpaper', 'wallpaper ') group by 1 order by 2 desc;
   ?column?   | count
--------------+-------
 >wallpaper<  |  1951
 > wallpaper< |    12
 >wallpaper < |     5

[TimmyCarbone]

For now, we'll address the trailing/leading whitespace issue on all the fields you mentioned.
These issues will be fixed in the 1.1.0 release.

We'll keep the normalization feature aside for now since it's a much bigger project that requires more resources than we have at the moment.