Calculated field
MAKELINE(MAKEPOINT([lat],[lon]),MAKEPOINT([lat]+0.1,[lon]+0.1))
Sample data
| shipment | shipped | received | text | year | month | day | hour | minute | second | lat | lon | wkt |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2024-12-29 | 2024-12-29T16:25:40 | 2024-12-29 | 2025 | 2 | 25 | 9 | 25 | 40 | 10 | 106 | POINT (106 10) |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 2025 | 2 | 26 | 10 | 25 | 40 | 10.1 | 106.1 | POINT (106.1 10.1) |
| 3 | 2025-01-01 | 2025-01-01T16:25:40 | 2025-01-01 | 2025 | 2 | 27 | 11 | 25 | 40 | 10.2 | 106.2 | POINT (106.2 10.2) |
| 4 | 2025-01-31 | 2025-01-31T16:25:40 | 2025-01-31 | 2025 | 2 | 28 | 12 | 25 | 40 | 10.3 | 106.3 | POINT (106.3 10.3) |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | 2025 | 2 | 29 | 13 | 25 | 40 | 10.4 | 106.4 | POINT (106.4 10.4) |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 2025 | 2 | 30 | 14 | 25 | 40 | 10.5 | 106.5 | POINT (106.5 10.5) |
| 7 | null | null | null | 2025 | 2 | 31 | 15 | 25 | 40 | 10.6 | 106.6 | POINT (106.6 10.6) |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 2025 | 2 | 32 | 16 | 25 | 40 | 10.7 | 106.7 | POINT (106.7 10.7) |
The same calculation as SQL
SELECT json_object('type','LineString','coordinates',json_array(json_array(lon,lat),json_array(lon+0.1,lat+0.1))) AS result FROM input ORDER BY shipmentRuns the example above.
SELECT ST_ASGEOJSON(ST_MAKELINE(ST_MAKEPOINT(lon,lat),ST_MAKEPOINT(lon+0.1,lat+0.1))) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT ST_ASGEOJSON(ST_MAKELINE(ST_GEOGPOINT(lon,lat),ST_GEOGPOINT(lon+0.1,lat+0.1))) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
Result
| shipment | shipped | received | text | result |
|---|---|---|---|---|
| 1 | 2024-12-29 | 2024-12-29T16:25:40 | 2024-12-29 | {"type": "LineString", "coordinates": [[106, 10], [106.1, 10 |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | {"type": "LineString", "coordinates": [[106.1, 10.1], [106.1 |
| 3 | 2025-01-01 | 2025-01-01T16:25:40 | 2025-01-01 | {"type": "LineString", "coordinates": [[106.2, 10.2], [106.3 |
| 4 | 2025-01-31 | 2025-01-31T16:25:40 | 2025-01-31 | {"type": "LineString", "coordinates": [[106.3, 10.3], [106.3 |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | {"type": "LineString", "coordinates": [[106.4, 10.4], [106.5 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | {"type": "LineString", "coordinates": [[106.5, 10.5], [106.6 |
| 7 | null | null | null | {"type": "LineString", "coordinates": [[106.6, 10.6], [106.6 |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | {"type": "LineString", "coordinates": [[106.7, 10.7], [106.8 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Line construction preserves endpoint order.
Naive SQL
SELECT json_object('type','LineString','coordinates',json_array(json_array(lon+0.1,lat+0.1),json_array(lon,lat))) AS result FROM input ORDER BY shipmentshipment shipped received text result 1 2024-12-29 2024-12-29T16:25:40 2024-12-29 {"type": "LineString", "coordinates": [[106.1, 10.1], [106, 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 {"type": "LineString", "coordinates": [[106.19999999999999, 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 {"type": "LineString", "coordinates": [[106.3, 10.2999999999 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 {"type": "LineString", "coordinates": [[106.39999999999999, 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 {"type": "LineString", "coordinates": [[106.5, 10.5], [106.4 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 {"type": "LineString", "coordinates": [[106.6, 10.6], [106.5 7 null null null {"type": "LineString", "coordinates": [[106.69999999999999, 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 {"type": "LineString", "coordinates": [[106.8, 10.7999999999 Matches the calculation
SELECT json_object('type','LineString','coordinates',json_array(json_array(lon,lat),json_array(lon+0.1,lat+0.1))) AS result FROM input ORDER BY shipmentshipment shipped received text result 1 2024-12-29 2024-12-29T16:25:40 2024-12-29 {"type": "LineString", "coordinates": [[106, 10], [106.1, 10 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 {"type": "LineString", "coordinates": [[106.1, 10.1], [106.1 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 {"type": "LineString", "coordinates": [[106.2, 10.2], [106.3 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 {"type": "LineString", "coordinates": [[106.3, 10.3], [106.3 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 {"type": "LineString", "coordinates": [[106.4, 10.4], [106.5 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 {"type": "LineString", "coordinates": [[106.5, 10.5], [106.6 7 null null null {"type": "LineString", "coordinates": [[106.6, 10.6], [106.6 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 {"type": "LineString", "coordinates": [[106.7, 10.7], [106.8
Signature
MAKELINE(point, point)