Recently I was got into situation where I have tables of country,state,
city with
proper foreign key and referenced in other data but I need latitude &
longitude for city.I does not wanted to create new table so I altered City
Table.
My City Altered Table schema in MySQL after modification look like below
CREATE TABLE `City` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`countryId` int unsigned NOT NULL,
`stateId` int unsigned NOT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
`deletedAt` datetime DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `countryId` (`countryId`),
KEY `stateId` (`stateId`),
CONSTRAINT `City_ibfk_5` FOREIGN KEY (`countryId`) REFERENCES `Country` (`id`),
CONSTRAINT `City_ibfk_6` FOREIGN KEY (`stateId`) REFERENCES `State` (`id`)
) ENGINE=InnoDB
Now I left with way to find latitude & longitude for each city ,some data is
available freely online but it is needed to inserted into new table then extract
latitude & longitude for my table.
I came across a API provider by geocode.maps.co that lets you get latitude &
longitude from address. I registered for API Key ,its free plan lets us make
100,000 api calls per month with limit 1 request per second.
Below is code in Node.js to accomplish same.
What I am doing in code.
1) Getting City which does not have latitude & longitude in City table along
with country & state names
2) Making API call to get latitude & longitude based on city name, state
name,country name.
3) Update the City record with its latitude & longitude.
NPM dependencies used in code:
npm i mysql2
Node.js Code:
const mysql = require('mysql2/promise');
const apiKey = '{Your API Key}';
// Function to create a delay
function delay(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function getCityWithoutLatLong() {
let connection;
try {
connection = await getConnection();
const query = 'SELECT country.id as countryId, country.name
countryName, state.id as stateId, state.name stateName,
city.id as cityId, city.name cityName FROM City city
INNER JOIN Country country ON city.countryId = country.id
INNER JOIN State state ON city.stateId = state.id
WHERE city.countryId = 101;';
const [results] = await connection.query(query);
// Process each city sequentially with a delay
for (const element of results) {
const address = `${element.countryName},${element.stateName},
${element.cityName}`;
const apiEndpoint = `https://geocode.maps.co/search?
q=${address}&api_key=${apiKey}`;
console.log(`Calling API with cityId=${element.cityId}`);
await callAPI(element.cityId, apiEndpoint);
await delay(1000); // Wait for 1 second before the next API call
}
} catch (err) {
console.error('Error in getCityWithoutLatLong:', err);
} finally {
if (connection && connection.end) await connection.end();
console.log("Exited Process in function getCityWithoutLatLong");
process.exit();
}
}
// Function to call an API and process the response
async function callAPI(cityId, apiEndpoint) {
try {
const requestOptions = {
method: "GET",
redirect: "follow"
};
const response = await fetch(apiEndpoint, requestOptions);
if (!response.ok) {
throw new Error(`HTTP error! Status: ${response.status}`);
}
const data = await response.json();
const lat = data[0].lat;
const long = data[0].lon;
console.log(`lat=${lat}, long=${long}`);
const updateResult = await updateCityWithLatLong(cityId, lat, long);
console.log("City updated:", updateResult);
} catch (error) {
console.error('Fetch error:', error);
}
}
// Function to update the city with latitude and longitude in the database
async function updateCityWithLatLong(cityId, lat, long) {
let connection;
try {
connection = await getConnection();
const query = `UPDATE City SET latitude = ?, longitude = ? WHERE id = ?`;
const [results] = await connection.query(query, [lat, long, cityId]);
return results;
} catch (err) {
console.error('Database query error:', err);
} finally {
if (connection && connection.end) await connection.end();
}
}
// Function to get a connection to the database
async function getConnection() {
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'sangram#81',
database: 'MatrimonyDb'
});
return connection;
}
getCityWithoutLatLong();
In My Code I Hardcoded Id of Country India i.e. 101.Modify My Code as per your
table structure,You need to replace API KEY in above code.
