I’m having trouble with my JavaScript code not properly retrieving and using data sent as JSON from an HTML form. Here’s the process:
A user enters a single or multiple addresses in an HTML form.
The addresses are parsed and sent as JSON to the server.
The JavaScript is supposed to extract the “streetName” from the JSON, use it in an SQL query to match the “NAME” field, and return values like FULL_ADD, LATITUDE, and LONGITUDE to the HTML page.
The problem is that the JavaScript is not picking up the fields from the JSON, even though I can see the data is being sent in the console. The SQL query is not returning any results because the data isn’t being passed correctly. I’ve also tried hardcoding values in the SQL query, but that didn’t work either.
Here’s a link to my code:
https://jsfiddle.net/6owvz91y
HTML Code:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Address Geocoding and CSV Export</title>
<style>
body {
font-family: Arial, sans-serif;
margin: 20px;
}
textarea {
width: 100%;
height: 150px;
margin-bottom: 20px;
}
table {
width: 100%;
border-collapse: collapse;
margin-top: 20px;
}
table, th, td {
border: 1px solid black;
}
th, td {
padding: 8px;
text-align: left;
}
.invalid {
background-color: lightcoral;
}
button {
margin-top: 10px;
padding: 10px 15px;
font-size: 16px;
cursor: pointer;
}
#progress-container {
margin-top: 20px;
width: 100%;
background-color: #f3f3f3;
border: 1px solid #ccc;
border-radius: 5px;
overflow: hidden;
height: 25px;
}
#progress-bar {
height: 100%;
width: 0%;
background-color: #4caf50;
text-align: center;
line-height: 25px;
color: white;
font-weight: bold;
}
</style>
</head>
<body>
<h1>Address Geocoding and CSV Export</h1>
<p>Enter addresses (one per line):</p>
<textarea id="addressInput" placeholder="Enter addresses here..."></textarea>
<button onclick="geocodeAddresses()">Geocode Addresses</button>
<button onclick="downloadCSV()">Download as CSV</button>
<div id="progress-container">
<div id="progress-bar">0%</div>
</div>
<h2>Geocoded Results:</h2>
<table id="resultTable">
<thead>
<tr>
<th>Input Address</th>
<th>Street Number</th>
<th>Street Name</th>
<th>Street Type</th>
<th>Post Direction</th>
<th>Unit</th>
<th>Municipality</th>
<th>Province</th>
<th>Postal Code</th>
<th>Matched Address</th>
<th>Latitude</th>
<th>Longitude</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script>
const streetTypes = ["AV", "AVE", "AVENUE", "BLVD", "BOULEVARD", "BV", "CE", "CENTRE", "CIR", "CIRCLE", "CL", "CLOSE", "CM", "CMN", "COMMON", "COURT", "CR", "CRES", "CRESCENT", "CROSSING", "CRT", "CS", "CT", "CTR", "DR", "DRIVE", "GARDEN", "GARDENS", "GATE", "GDN", "GDNS", "GR", "GREEN", "GROVE", "GT", "GV", "HEIGHTS", "HIGHWAY", "HILL", "HL", "HOLLOW", "HT", "HW", "HWY", "LANDING", "LANE", "LG", "LI", "LINE", "LN", "LNDG", "MANOR", "MR", "PARK", "PARKWAY", "PASS", "PATH", "PH", "PK", "PKWY", "PL", "PLACE", "POINT", "PS", "PT", "PY", "RD", "RN", "ROAD", "RUN", "SIDEROAD", "SQ", "SQUARE", "SR", "ST", "STREET", "TER", "TERRACE", "TL", "TLIN", "TOWNLINE", "TR", "TRAIL", "VIEW", "VW", "WALK", "WAY", "WK", "WO", "WOOD", "WY"];
const directions = ["E", "EAST", "N", "NORTH", "S", "SOUTH", "W", "WEST"];
const exceptions = [
{ regex: /bHWYs+(?:[1-9]|[1-9]d|4d{2})b/, words: 2 },
{ regex: /bHIGHWAYs+(?:[1-9]|[1-9]d|4d{2})b/, words: 2 },
{ regex: /b(?:[1-9]|[1-9]d|4d{2})s+HWYb/, words: 2 },
{ regex: /b(?:[1-9]|[1-9]d|4d{2})s+HIGHWAYb/, words: 2 }
];
function isValidStreetType(word) {
return streetTypes.includes(word.toUpperCase());
}
function processAddress(input) {
return input
.replace(/,/g, ' ')
.replace(/[/\]/g, ' ')
.replace(/[^a-zA-Z0-9s-]/g, '')
.replace(/(d+)s+(d+)/g, '$1-$2')
.replace(/s-s/g, '-')
.toUpperCase()
.trim();
}
async function geocodeAddresses() {
const addressInput = document.getElementById("addressInput").value.trim();
const addresses = addressInput.split('n');
const resultsTable = document.getElementById("resultTable").getElementsByTagName("tbody")[0];
const progressBar = document.getElementById("progress-bar");
resultsTable.innerHTML = '';
const totalAddresses = addresses.length;
let currentIndex = 0;
function updateProgress() {
const progress = Math.round((currentIndex / totalAddresses) * 100);
progressBar.style.width = `${progress}%`;
progressBar.textContent = `${progress}%`;
}
for (const address of addresses) {
try {
let processedAddress = processAddress(address);
let unit = '', streetNumber = '', streetName = '', streetType = '', postDirection = '', province = '', postalCode = '', municipality = '';
// Step 1: Handle exceptions, assign address and unit number
let exceptionHandled = false;
if (/^d/.test(processedAddress)) {
for (const exception of exceptions) {
const match = processedAddress.match(exception.regex);
if (match) {
const matchedWords = match[0];
streetName = matchedWords;
streetType = "";
processedAddress = processedAddress.replace(matchedWords, '').trim();
exceptionHandled = true;
break;
}
}
} else {
// If the address does not start with a number, apply new logic
const parts = processedAddress.split(' ');
let firstStreetTypeIndex = -1;
for (let i = 0; i < parts.length; i++) {
if (isValidStreetType(parts[i])) {
firstStreetTypeIndex = i;
break;
}
}
//More Parsing logic
// Send JSON POST request for each field
const response = await fetch('http://localhost:3000/geocode', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({
parsedData: {
streetNumber,
streetName,
streetType,
postDirection,
unit,
municipality,
}
}),
});
const responseData = await response.json();
// Extract data from the response
const matchedAddress = responseData.FULL_ADD || 'No match found';
const latitude = responseData.latitude || 'N/A';
const longitude = responseData.longitude || 'N/A';
// Add a new row to the results table
const row = resultsTable.insertRow();
row.insertCell(0).textContent = address;
row.insertCell(1).textContent = streetNumber;
row.insertCell(2).textContent = streetName;
row.insertCell(3).textContent = streetType;
row.insertCell(4).textContent = postDirection;
row.insertCell(5).textContent = unit;
row.insertCell(6).textContent = municipality;
row.insertCell(7).textContent = province;
row.insertCell(8).textContent = postalCode;
row.insertCell(9).textContent = matchedAddress;
row.insertCell(10).textContent = latitude;
row.insertCell(11).textContent = longitude;
} catch (error) {
console.error('Error geocoding address:', error);
// Add a row with an error message
const row = resultsTable.insertRow();
row.insertCell(0).textContent = address;
row.insertCell(1).colSpan = 11;
row.insertCell(1).textContent = 'Error processing this address';
row.classList.add('invalid');
}
currentIndex++;
updateProgress();
}
}
function downloadCSV() {
const table = document.getElementById("resultTable");
const rows = table.querySelectorAll("tr");
const csv = [];
// Include the header row from the table
const headers = table.querySelectorAll("th");
const headerRow = Array.from(headers).map(header => header.innerText.trim());
csv.push(headerRow.join(',')); // Adding header row to CSV
// Include each data row from the table
rows.forEach((row, index) => {
// Skip the header row, we already added it
if (index > 0) {
const data = Array.from(row.querySelectorAll("td"))
.map(cell => {
let cellValue = cell.innerText.trim();
return cellValue === "" ? "" : `"${cellValue}"`; // Wrap non-empty values in quotes, keeping empty values as they are
});
csv.push(data.join(',')); // Adding the data row to CSV
}
});
// Convert the array to a CSV string and trigger a download
const csvString = csv.join('n');
const blob = new Blob([csvString], { type: 'text/csv' });
const link = document.createElement("a");
link.href = URL.createObjectURL(blob);
link.download = 'geocoded_addresses.csv';
link.click();
}
</script>
</body>
</html>
JavaScript Code:
const express = require('express');
const cors = require('cors');
const { Connection, Request, TYPES } = require('tedious'); // Ensure TYPES is imported
const path = require('path'); // Import path module to resolve file paths
// Initialize Express app
const app = express();
app.use(cors());
app.use(express.json()); // To parse JSON request bodies
// Server configuration
const PORT = 3000;
// Database configuration using NTLM (Windows Authentication)
const config = {
server: 'REXT21',
options: {
database: 'SPATIAL',
port: 1433,
encrypt: false,
trustServerCertificate: true,
},
authentication: {
type: 'ntlm',
options: {
domain: 'TEST-NET',
userName: 'MILO',
password: 'Setter4',
},
},
};
// Create the connection
const connection = new Connection(config);
let isConnected = false;
// Ensure the connection is fully established before starting any requests
connection.on('connect', (err) => {
if (err) {
console.error('Connection Failed:', err);
} else {
console.log('Connected to SQL Server');
isConnected = true;
}
});
// Connect to the database
connection.connect();
// Endpoint to handle geocoding requests
app.post('/geocode', async (req, res) => {
const { addresses } = req.body;
if (!addresses || !Array.isArray(addresses) || address.length === 0) {
return res.status(400).json({ error: 'At least one address is required' });
}
if (!isConnected) {
return res.status(500).json({ error: 'Database not connected' });
}
let results = [];
let processedCount = 0;
// Process each address asynchronously
for (const streetName of addresses) {
// SQL query with additional logic to handle addresses without commas
const sqlQuery = `
SELECT TOP 1 LATITUDE, LONGITUDE, FULL_ADD, NAME
FROM Addresses_p
WHERE @streetName = NAME
`;
const request = new Request(sqlQuery, (err) => {
if (err) {
console.error('Error executing query:', err);
results.push({ streetName, error: 'Error executing query' });
processedCount++;
}
});
request.addParameter('streetName', TYPES.NVarChar, streetName);
request.on('row', (columns) => {
const result = {
streetName,
latitude: columns[0].value,
longitude: columns[1].value,
FULL_ADD: columns[3].value,
};
// Log the extracted fields to the console
console.log('Extracted fields for address:', streetName);
console.log(result);
results.push(result);
});
request.on('requestCompleted', () => {
processedCount++;
// Once the request is completed, send the response
if (processedCount === addresses.length) {
res.json(results);
}
});
connection.execSql(request);
}
});
// Serve the index.html file
app.use(express.static(__dirname));
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, 'index.html'));
});
// Start the Express server
app.listen(PORT, () => {
console.log(`Server is running on http://localhost:${PORT}`);
});
