Database table layout question

See more:

SQL

database

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