Handle location in a SQL database I am creating a cataloging system for rocks that come from all around the world. As part of this system, I need to handle the location of the where the rocks were found.

The problem is that the location needs to contain the country, a sub-item (e.g. State, Province), possibly another sub-item (e.g. County), then maybe a city, and then finally the mine/quarry/forest/etc that it was found in.

I don't think that I should allow the user to enter in the location every time in plain-text, as that runs the risk of multiple spellings for the same location. Also, because there are so many possibilities for the way location is structured (United states would be USA:CALIFORNIA:ORANGE:LOS ANGELES but Canada might be CANADA:BRITISH COLOMBIA:VANCOUVER) I don't know how to effectively store and query this information.

What is the best way to do this?
Are you aware of the "what3words" system??  https://en.wikipedia.org/wiki/What3words

Or, how to address mail from the U.S. to anywhere in the world  - a problem similar to yours - https://blink.ucsd.edu/facilities/services/mail/international/addressing/index.html

I've seen geographical data represented with latitude/longitude or similar coordinates, plus a code representing how specific or accurate the coordinates are.

You could use coordinates together with a textual address.  The text is searchable but the coordinates -are-specific.

I think the proper way at the moment to store arbitrary text with markups for field identities - name of country / county / town etc. - is XML.  A similar effort of your own would be just re-inventing XML.

How does the Smithsonian do it?  Or is that where you're doing this?