Function table / Spatial / MAKEPOINT

MAKEPOINT: from Tableau to SQL

Creates a WGS84 point from latitude and longitude. Below, 8 sample rows go through the calculation and through the SQL, and both return the same numbers.

Calculated field

MAKEPOINT([lat],[lon])

Sample data

shipmentshippedreceivedtextyearmonthdayhourminutesecondlatlonwkt
12024-12-292024-12-29T16:25:402024-12-2920252259254010106POINT (106 10)
22024-12-302024-12-30T16:25:402024-12-30202522610254010.1106.1POINT (106.1 10.1)
32025-01-012025-01-01T16:25:402025-01-01202522711254010.2106.2POINT (106.2 10.2)
42025-01-312025-01-31T16:25:402025-01-31202522812254010.3106.3POINT (106.3 10.3)
52025-02-282025-02-28T16:25:402025-02-28202522913254010.4106.4POINT (106.4 10.4)
62025-03-152025-03-15T16:25:402025-03-15202523014254010.5106.5POINT (106.5 10.5)
7nullnullnull202523115254010.6106.6POINT (106.6 10.6)
82024-02-292024-02-29T16:25:402024-02-29202523216254010.7106.7POINT (106.7 10.7)

The same calculation as SQL

SELECT json_object('type','Point','coordinates',json_array(lon,lat)) AS result FROM input ORDER BY shipment

Runs the example above.

Result

shipmentshippedreceivedtextresult
12024-12-292024-12-29T16:25:402024-12-29{"type": "Point", "coordinates": [106, 10]}
22024-12-302024-12-30T16:25:402024-12-30{"type": "Point", "coordinates": [106.1, 10.1]}
32025-01-012025-01-01T16:25:402025-01-01{"type": "Point", "coordinates": [106.2, 10.2]}
42025-01-312025-01-31T16:25:402025-01-31{"type": "Point", "coordinates": [106.3, 10.3]}
52025-02-282025-02-28T16:25:402025-02-28{"type": "Point", "coordinates": [106.4, 10.4]}
62025-03-152025-03-15T16:25:402025-03-15{"type": "Point", "coordinates": [106.5, 10.5]}
7nullnullnull{"type": "Point", "coordinates": [106.6, 10.6]}
82024-02-292024-02-29T16:25:402024-02-29{"type": "Point", "coordinates": [106.7, 10.7]}

Same rows from the calculation and from the SQL in DuckDB.

Where a naive translation goes wrong

  • Latitude and longitude argument order differs from SQL point constructors.

    Naive SQL

    SELECT json_object('type','Point','coordinates',json_array(lat,lon)) AS result FROM input ORDER BY shipment
    shipmentshippedreceivedtextresult
    12024-12-292024-12-29T16:25:402024-12-29{"type": "Point", "coordinates": [10, 106]}
    22024-12-302024-12-30T16:25:402024-12-30{"type": "Point", "coordinates": [10.1, 106.1]}
    32025-01-012025-01-01T16:25:402025-01-01{"type": "Point", "coordinates": [10.2, 106.2]}
    42025-01-312025-01-31T16:25:402025-01-31{"type": "Point", "coordinates": [10.3, 106.3]}
    52025-02-282025-02-28T16:25:402025-02-28{"type": "Point", "coordinates": [10.4, 106.4]}
    62025-03-152025-03-15T16:25:402025-03-15{"type": "Point", "coordinates": [10.5, 106.5]}
    7nullnullnull{"type": "Point", "coordinates": [10.6, 106.6]}
    82024-02-292024-02-29T16:25:402024-02-29{"type": "Point", "coordinates": [10.7, 106.7]}

    Matches the calculation

    SELECT json_object('type','Point','coordinates',json_array(lon,lat)) AS result FROM input ORDER BY shipment
    shipmentshippedreceivedtextresult
    12024-12-292024-12-29T16:25:402024-12-29{"type": "Point", "coordinates": [106, 10]}
    22024-12-302024-12-30T16:25:402024-12-30{"type": "Point", "coordinates": [106.1, 10.1]}
    32025-01-012025-01-01T16:25:402025-01-01{"type": "Point", "coordinates": [106.2, 10.2]}
    42025-01-312025-01-31T16:25:402025-01-31{"type": "Point", "coordinates": [106.3, 10.3]}
    52025-02-282025-02-28T16:25:402025-02-28{"type": "Point", "coordinates": [106.4, 10.4]}
    62025-03-152025-03-15T16:25:402025-03-15{"type": "Point", "coordinates": [106.5, 10.5]}
    7nullnullnull{"type": "Point", "coordinates": [106.6, 10.6]}
    82024-02-292024-02-29T16:25:402024-02-29{"type": "Point", "coordinates": [106.7, 10.7]}

Signature

MAKEPOINT(latitude, longitude)