Find Jobs
Hire Freelancers

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

$30-250 USD

Suljettu
Julkaistu noin 8 vuotta sitten

$30-250 USD

Maksettu toimituksen yhteydessä
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 [login to view URL] 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 [login to view URL] 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 [login to view URL]
Projektin tunnus (ID): 9609199

Tietoa projektista

13 ehdotukset
Etäprojekti
Aktiivinen 8 vuotta sitten

Haluatko ansaita rahaa?

Freelancerin tarjouskilpailun edut

Aseta budjettisi ja aikataulu
Saa maksu työstäsi
Kuvaile ehdotustasi
Rekisteröinti ja töihin tarjoaminen on ilmaista
13 freelancerit tarjoavat keskimäärin $236 USD tätä projektia
Käyttäjän avatar
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 !!)... you can never go wrong choosing me :) I am available to get started on your project right away. I look forward to your reply. Thanks. Kind Regards, Rajesh Soni
$736 USD 5 päivässä
5,0 (704 arvostelua)
7,8
7,8
Käyttäjän avatar
A proposal has not yet been provided
$250 USD 2 päivässä
4,9 (300 arvostelua)
7,5
7,5
Käyttäjän avatar
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 2 päivässä
4,9 (334 arvostelua)
7,6
7,6
Käyttäjän avatar
A proposal has not yet been provided
$411 USD 5 päivässä
4,8 (74 arvostelua)
6,1
6,1
Käyttäjän avatar
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 ping me anytime so we can have a detailed discussion. If I can deliver I will deliver in best possible way. Thanks
$144 USD 3 päivässä
4,9 (10 arvostelua)
4,9
4,9
Käyttäjän avatar
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 you
$138 USD 3 päivässä
5,0 (1 arvostelu)
0,8
0,8
Käyttäjän avatar
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 3 päivässä
0,0 (0 arvostelua)
1,9
1,9
Käyttäjän avatar
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 3 päivässä
0,0 (0 arvostelua)
0,0
0,0
Käyttäjän avatar
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 inputs available) Businessname, BusinessUniqueId, State, StateId / ZONEID (uniqueId), City, CityId (UniqueID) For this I need following Details: 1. Business detail master in Excel 2. State and city mapping data in Excel. 3. Excel Schema for new Table. I will need one weeks time to complete this activity.
$177 USD 5 päivässä
0,0 (0 arvostelua)
0,0
0,0
Käyttäjän avatar
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 special tools to handle data so I can read all your data and make all needed matches, selects and transformations and make any output. It needs a little programming, just data transformation configuration. So the benefit for you - we will be able to make any changes in algorithms during transformation very fast. I can work with csv, mysql tables and almost all data formats. Input and output data sources can be any type - from csv files to direct database access.
$144 USD 3 päivässä
0,0 (0 arvostelua)
0,0
0,0
Käyttäjän avatar
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. Note that got 93% on the SQL test on this site. I guarantee I will do a quality job for you. You will get this at a low cost because it's hard to get started on this site as a newcomer. The one question I have for you is whether this is just a one-time data operation, or whether there needs to also be an ongoing solution for incoming data in the future also? If you have any questions for me, please let me know. Cheers, Dorlan
$85 USD 5 päivässä
0,0 (0 arvostelua)
0,0
0,0

Tietoja asiakkaasta

Maan UNITED STATES lippu
OCEAN COUNTY, United States
5,0
1
Maksutapa vahvistettu
Liittynyt heinäk. 2, 2008

Asiakkaan vahvistus

Kiitos! Olemme lähettäneet sinulle sähköpostitse linkin, jolla voit lunastaa ilmaisen krediittisi.
Jotain meni pieleen lähetettäessä sähköpostiasi. Yritä uudelleen.
Rekisteröitynyttä käyttäjää Ilmoitettua työtä yhteensä
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Ladataan esikatselua
Lupa myönnetty Geolocation.
Kirjautumisistuntosi on vanhentunut ja sinut on kirjattu ulos. Kirjaudu uudelleen sisään.