I am sure I can cobble something together and make this work, but I am seeking some advice from people with experience before I get going on this.
First some background. I am trying to organize all our photos in such a way that it is easy to find photos. I want to categorize the photos in 3 different ways. I will be using C++/MFC for the code, and MS SQLServer Express for the database.
The first way, which is the easiest, is to arrange them by date taken.For that I am going to use the EXIF data in the jpeg file to arrange the photos into folders with the heirarchy of "Photos->Year->Month->Day->photo.jpeg". Easy to find a photo that way
Next I want to arrange them by people in the photo. For this I am going to set up a SQLServer database with the following table structure:
Table TPeople
PK_PeopleID
... details
Table TPhotos
PK_PhotoID
FK_LocationNodeID -- explained below
... details
Table TPeoplePhotos
FK_PeopleID
FK_PhotoID
Because there will be a many to many relationship between TPeople and TPhotos I will use TPeoplePhotos to track the relationships. Not as easy as arranging by date, but not too difficult to figure out.
Now the last one is where I am struggling. I want to arrange the photos by location taken. for that I want to use the GPS data in the EXIF information of the jpeg file. I found a website (Geocode.maps.co where I can submit the GPS location, and it will spit back an address in either JSON or XML format. Here is a random example of a returned JSON:
{
"place_id":292365672,
"licence":"Data © OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
"osm_type":"relation",
"osm_id":1425486,
"lat":"41.4022198",
"lon":"-99.7272241",
"display_name":"Custer County, Nebraska, United States",
"address":
{
"county":"Custer County",
"state":"Nebraska",
"ISO3166-2-lvl4":"US-NE",
"country":"United States",
"country_code":"us"
},
"boundingbox":
[
"41.0462573",
"41.741322",
"-100.252945",
"-99.201462"
]
}
To parse this in a way I can drill down from top to bottom, I could get a string from the address part like:
country_code|country|ISO3166-2-lvl3|state|county
which becomes, if I ignore the country code and ISO number
United States|Nebraska|Custer County
This is all and well, but I have been running a bunch of tests on the website to see what all the different returned layouts are. So far I have got 268 different location layouts. They are for example:
country_code|country|postcode|ISO3166-2-lvl4|state|county|village|road|house_number
country_code|country|postcode|ISO3166-2-lvl4|state|county|village|hamlet|road|building
country_code|country|ISO3166-2-lvl8|city
country_code|country|region|ISO3166-2-lvl4|state|county|road
military
So my thought was to break this down into nodes and when a user(me) wants to find a photo, I would populate a tree control with all the top level nodes (ie countries). When a country is selected, populate the next level down, either a region, state, or even city or village; depending on how the geolocation was returned from the website. That way I could select all the pictures taken in a country, or drill down to the state, or city, or even a specific address if that is available.
Ok, now for the question.
How would one set up the database to be able to handle this?
My uneducated thought is to have a table that holds all the location nodes
Table TLocationNode
PK_LocationNodeID int not null
Name varchar
ParentNodeID int
That way, when I select a country in the tree control, I could select all the nodes where the ParentNodeID is the country node, and so on down the line. With the phote pointing to the bottom node.
The ParentNodeID
would point to another node in the same table, so it is technically not a foreign key.
Is this possible? is it wise? would it even work?
I just want opinions and maybe some guidance from an expert or two. I do not want any code, as I really enjoy trying to figure that part on my own once I know what direction to go.
Thanks
What I have tried:
So far I have not tried anything, as this more of a 'what direction do I go?' type question rather than a 'How do I do it?' question.
Thanks for understanding