Spaces:
Sleeping
Sleeping
File size: 7,388 Bytes
967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d 044aa5c 967688d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | import sql from './db';
// Open-Meteo Elevation API
// https://open-meteo.com/en/docs/elevation-api
const API_URL = 'https://api.open-meteo.com/v1/elevation';
async function updateElevations() {
try {
console.log('Fetching routes without elevation profile...');
// Debug: Check total routes
const count = await sql`SELECT count(*) FROM routes`;
console.log(`Total routes in DB: ${count[0].count}`);
// Debug: Check one route
// const sample = await sql`SELECT id, path, elevation_profile FROM routes LIMIT 1`;
// if (sample.length > 0) {
// console.log('Sample route:', JSON.stringify(sample[0], null, 2));
// console.log('Type of elevation_profile:', typeof sample[0].elevation_profile);
// console.log('Value of elevation_profile:', sample[0].elevation_profile);
// }
// Fetch a batch of routes to update
// We order by ID to be deterministic
const routes = await sql`
SELECT id, name, path
FROM routes
ORDER BY id ASC
LIMIT 50
`;
console.log(`Found ${routes.length} routes to update.`);
for (const route of routes) {
console.log(`Processing route: ${route.name} (ID: ${route.id})`);
try {
let geojson = route.path;
if (typeof geojson === 'string') {
geojson = JSON.parse(geojson);
}
const coordinates = geojson.coordinates; // Array of [lon, lat]
if (!coordinates || coordinates.length === 0) {
console.log('No coordinates found, skipping.');
continue;
}
// Open-Meteo accepts max 100 locations per request in free tier usually,
// but let's check documentation. It says "Multiple locations can be specified".
// To be safe and avoid URL length limits, we might need to chunk if the path is very long.
// For now, let's sample or chunk.
// Let's limit to 100 points for the profile to keep it simple and fast
// If more than 100 points, we sample.
const sampledCoordinates = sampleCoordinates(coordinates, 100);
const lats = sampledCoordinates.map(c => c[1]);
const lngs = sampledCoordinates.map(c => c[0]);
const url = `${API_URL}?latitude=${lats.join(',')}&longitude=${lngs.join(',')}`;
const response = await fetch(url);
if (!response.ok) {
throw new Error(`API Error: ${response.statusText} (${response.status})`);
}
const data = await response.json();
const elevations = data.elevation;
if (!elevations || elevations.length !== sampledCoordinates.length) {
console.warn('Mismatch in elevation data length');
continue;
}
// Smooth elevations to avoid noisy data causing huge elevation gains
const smoothedElevations = smoothElevations(elevations, 5);
// Create elevation profile
// We can calculate distance along the path to create a proper profile { distance, elevation }
const profile = [];
let totalDistance = 0;
for (let i = 0; i < sampledCoordinates.length; i++) {
if (i > 0) {
const prev = sampledCoordinates[i - 1];
const curr = sampledCoordinates[i];
const distKm = getDistanceFromLatLonInKm(prev[1], prev[0], curr[1], curr[0]);
totalDistance += distKm * 1000; // Convert to meters
}
profile.push({
distance: Math.round(totalDistance * 10) / 10, // in meters, rounded
elevation: smoothedElevations[i] // in meters
});
}
// Update the route in the database
// We are NOT updating the main 'path' geometry with 3D coordinates here to preserve the original detailed geometry
// We are just saving the profile.
// postgres.js handles JSON serialization automatically if we pass the object
const result = await sql`
UPDATE routes
SET elevation_profile = ${sql.json(profile)}
WHERE id = ${route.id}
`;
console.log(`Updated elevation profile for route ${route.id}. Points: ${profile.length}. Result: ${result.count}`);
// Log the full profile for verification
console.log(`Elevation Profile Data for Route ${route.id}:`);
console.log(JSON.stringify(profile, null, 2));
// Respect API rate limits
// Open-Meteo free tier: < 600 calls/minute (10/sec).
// We'll be conservative with 2 second delay.
await new Promise(resolve => setTimeout(resolve, 2000));
} catch (err: any) {
if (err.message && err.message.includes('429')) {
console.warn('Rate limit hit, waiting 10 seconds...');
await new Promise(resolve => setTimeout(resolve, 10000));
} else {
console.error(`Error updating route ${route.id}:`, err);
}
}
}
console.log('Elevation update completed.');
} catch (err) {
console.error('Error in updateElevations:', err);
}
}
function sampleCoordinates(coordinates: number[][], maxPoints: number): number[][] {
if (coordinates.length <= maxPoints) return coordinates;
const result = [];
const step = (coordinates.length - 1) / (maxPoints - 1);
for (let i = 0; i < maxPoints; i++) {
const index = Math.round(i * step);
result.push(coordinates[index]);
}
return result;
}
// Haversine formula for distance
function getDistanceFromLatLonInKm(lat1: number, lon1: number, lat2: number, lon2: number) {
const R = 6371; // Radius of the earth in km
const dLat = deg2rad(lat2 - lat1);
const dLon = deg2rad(lon2 - lon1);
const a =
Math.sin(dLat / 2) * Math.sin(dLat / 2) +
Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) *
Math.sin(dLon / 2) * Math.sin(dLon / 2);
const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
const d = R * c; // Distance in km
return d;
}
function deg2rad(deg: number) {
return deg * (Math.PI / 180);
}
function smoothElevations(elevations: number[], windowSize: number = 3): number[] {
const smoothed = [];
for (let i = 0; i < elevations.length; i++) {
let sum = 0;
let count = 0;
for (let j = Math.max(0, i - Math.floor(windowSize / 2)); j <= Math.min(elevations.length - 1, i + Math.floor(windowSize / 2)); j++) {
sum += elevations[j];
count++;
}
smoothed.push(sum / count);
}
return smoothed;
}
updateElevations();
|