I need help understand how to perform this action. It can be done in MySQL, Python, PHP or EXCEL. I have the tables already in databases. The idea is I need to relate human readable City-State to a table that charts all us City-States and assigned them ID's, Then print the ID's next to the human readable columns in its own row. The goal of this is to prepare data for import from a old directory platform to a new. There are 50,000 businesses with human readable City, State Columns in the old data. I need to add in the right id's in order for the import to work and new directory to be able to use its geolocation features. Since there are multiple cities with the same name, City-State will have to be relative. At the bottom, you will find a diagram of how I think I maybe able to do this. I am a new programmer so any help would be great, I would be even willing to pay for assistance. I have attached more info below. Another important factor, is I will be manually replacing the STATE with its relevent ZONE ID, since there are only 50 states I can Find Replace based on the a seperate table mapping states to ZONE ID's.
1) This is the city table, This contains the names of all US Cities in a readable forum and States as a ID(There are only 50 states so I can manually map them in the next step).
CITY-STATE CODEX TABLE SCREENSHOT
[url removed, login to view]
Column D "City Name" is the city name. City ID is what we ultimately will need in the end which is relative to the city name. Some town names are the same, so we must account for the column Zone ID which represents state to match the next list as well, that condition will have to be checked first then sort states inside that zone.
2) This should in theory have a match in the previous list. There are 50,000 businesses in this list. First I will manually Find/Replace the states with the correct Zone ID. Then it should look into the City ID's in that Zone. The following table "geolocation_city" must be compared to the last table, when a match is found, it should then print the city ID next to the matching city name. Keep in mind I DO NOT want to remove the old columns, just append them with NEW ID columns zone and city id. We will need the human readible format down the line to call information for our API.
BUSINESS INFORMATION TABLE SCREENSHOT
[url removed, login to view]
In the end, the importer will only accept Zone ID(State) and City ID(City). This is so the database can sort locations and display different towns and states businesses. Our old database doesn't follow the ID procedure introduced in this wordpress directory, so we will need to cross compare to the global list of 34000 cities included with the directory script. We just have the city and state names in human readable format, so I was hoping I could use mysql to quickly find and append the information I need to complete the inport into this directories location system.
I think there will likely need to be some PHP or Python logic here to get this done, which aisnt a issue. I just want to see how it could be done logic wise and what MySQL functions I would have my disposal.
I made a programming logic diagram, not the best as it doesnt show yes or no but its understand. Remember, I can replace the zone ID's which are states manually as there are only 50 states. Replacing the 32,000 cities will prove to be a issue. Finally step will be inserting into new table as well, not overwriting existing data.
PROGRAMMING LOGIC I CAN ONLY POST 2 LINKS, SO PLEASE USE BELOW [url removed, login to view]
13 freelancer đang chào giá trung bình $236 cho công việc này
From Human readable datas you need to get the city and state names and compare it with the database of city and state to get city id,state id and zone id and match it accordingly.
Hello, I had to do something similar at my previous employer and am confident I can get this done for you as well. Please let me know. Thanks, Kaleigh