JavaScript kann keine JSON-Felddaten aus HTML abrufen und keine SQL-Abfrage ausführen ⇐ JavaScript

[Guest]

Ich habe Probleme damit, dass mein JavaScript-Code als JSON gesendete Daten aus einem HTML-Formular nicht ordnungsgemäß abruft und verwendet. Hier ist der Prozess:
Ein Benutzer gibt eine einzelne oder mehrere Adressen in ein HTML-Formular ein.
Die Adressen werden analysiert und als JSON an den Server gesendet.
Das JavaScript soll den „streetName“ aus dem JSON extrahieren, ihn in einer SQL-Abfrage verwenden, um ihn mit dem Feld „NAME“ abzugleichen, und Werte wie FULL_ADD, LATITUDE und LONGITUDE an die HTML-Seite zurückgeben.
Das Problem liegt daran, dass das JavaScript die Felder nicht aus dem JSON aufnimmt, obwohl ich sehen kann, dass die Daten in der Konsole gesendet werden. Die SQL-Abfrage gibt keine Ergebnisse zurück, da die Daten nicht korrekt übergeben werden. Ich habe auch versucht, Werte in der SQL-Abfrage fest zu codieren, aber auch das hat nicht funktioniert.
Hier ist ein Link zu meinem Code:
https://jsfiddle.net/ 6owvz91y
HTML-Code:

Code: Select all

Address Geocoding and CSV Export

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;
}



Address Geocoding and CSV Export
Enter addresses (one per line):

Geocode Addresses
Download as CSV

0%


Geocoded Results:



Input Address
Street Number
Street Name
Street Type
Post Direction
Unit
Municipality
Province
Postal Code
Matched Address
Latitude
Longitude







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: /\bHWY\s+(?:[1-9]|[1-9]\d|4\d{2})\b/, words: 2 },
{ regex: /\bHIGHWAY\s+(?:[1-9]|[1-9]\d|4\d{2})\b/, words: 2 },
{ regex: /\b(?:[1-9]|[1-9]\d|4\d{2})\s+HWY\b/, words: 2 },
{ regex: /\b(?:[1-9]|[1-9]\d|4\d{2})\s+HIGHWAY\b/, words: 2 }
];

function isValidStreetType(word) {
return streetTypes.includes(word.toUpperCase());
}

function processAddress(input) {
return input
.replace(/,/g, ' ')
.replace(/[\/\\]/g, ' ')
.replace(/[^a-zA-Z0-9\s-]/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();
}

JavaScript-Code:

Code: Select all

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}`);
});