Đã Đóng

cDatabase Administration, Replace STATE-CITY w/ ZONE ID/CITY ID

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]

Kĩ năng: Quản trị cơ sở dữ liệu, Excel, MySQL, PHP, Python

Xem nhiều hơn: wordpress codex, what i need to have a programmer mind, what can you do with python programming, php, mysql, python programming help, python database programming, python cross platform, programming theory, Programming Logic, programmer excel add in programming, pay for python programming, need help with wordpress programming, mysql city state database, matching logic, list get python, i need a python programmer, i need a programmer in python, import administration, how to work as a python programmer

Về Bên Thuê:
( 1 Nhận xét ) OCEAN COUNTY, United States

ID dự án: #9609199

13 freelancer đang chào giá trung bình $236 cho công việc này

rajeshsonisl

Hello! With 98% to 99% completion rate, 900+ successfully completed projects, and a 4.99 reputation (maximum possible, 5.0) (can be verified on my profile page https://www.freelancer.com/u/rajeshsonisl.html !!)... y Thêm

$736 USD trong 5 ngày
(704 Nhận xét)
7.8
Teloquence

A proposal has not yet been provided

$250 USD trong 2 ngày
(300 Nhận xét)
7.5
coolguyinus2004

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.

$277 USD trong 2 ngày
(334 Nhận xét)
7.6
anuyadav1

A proposal has not yet been provided

$411 USD trong 5 ngày
(74 Nhận xét)
6.1
AwaisChaudhry

Hi there! I have read what you exactly need, however I would like to ask you a few questions. I would call myself a master of what I do, I do work smart and do not rest until I get the job done. Please feel free to pin Thêm

$144 USD trong 3 ngày
(10 Nhận xét)
4.9
chromiumtech

Hi sir, I have proven skills in Wordpress tested by Freelancer Inc. 1st fact is that my bid amount is always final and I never ask any amount higher until scope of work remains same. You can contact me via chat wit Thêm

$111 USD trong 1 ngày
(10 Nhận xét)
4.5
$222 USD trong 3 ngày
(11 Nhận xét)
3.7
nksundaram

Experienced software professional with over 20 years in software development. Good in writing SQL queries. If you can provide the data in excel sheets / csv files / access to the database, I can do the matching for y Thêm

$138 USD trong 3 ngày
(1 Nhận xét)
0.8
kubust

Hi, I can make it for You or show how it should be done using SQL. If You still looking for someone please contact me.

$222 USD trong 3 ngày
(0 Nhận xét)
1.9
kksmurf336

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

$155 USD trong 3 ngày
(0 Nhận xét)
0.0
naikachyut

I will provide final data in Excel or CSV format with business uniqueId, State/ Zone Id and cityId mapping . Columns included in the final Excel / CSV (Note: I can add additional fields if required based on the Thêm

$177 USD trong 5 ngày
(0 Nhận xét)
0.0
Brainstormm

Hi You have typical task of data transformation+migration. I'm data management professional and can do this very fast (1-3 days). you can find one of my data transformation examples in my portfolio. I have specia Thêm

$144 USD trong 3 ngày
(0 Nhận xét)
0.0
dtrn

Hi! I have years of experience with exactly this sort of task. In my previous job, I lead a team that was building a data bridge to extract, transform, and load up to millions of rows of messy, inconsistent data. No Thêm

$85 USD trong 5 ngày
(0 Nhận xét)
0.0