search

duckdb / duckdb_spatial

MIT License
483 stars 37 forks source link

ST_DISTANCE always returns 1.0 #77

Closed marklit closed 1 year ago

marklit commented 1 year ago

Any idea why I keep getting 1.0 for the distance calculation?

SELECT *,
       ST_DISTANCE(geom, ST_POINT(-8.6, 41.1331)) distance
FROM stations
ORDER BY distance;
┌─────────────┬────────────────────┬──────────────────────────┬───────────┬─────────┬─────────┬──────────┐
│ station_id  │    station_name    │           geom           │ elevation │ wmo_id  │ is_gsn  │ distance │
│   varchar   │      varchar       │         geometry         │  double   │ varchar │ boolean │  double  │
├─────────────┼────────────────────┼──────────────────────────┼───────────┼─────────┼─────────┼──────────┤
│ PO000008282 │ TAVIRA             │ POINT (-7.65 37.1167)    │      25.0 │ 08282   │ false   │      1.0 │
│ PO000008506 │ HORTA (AZORES)     │ POINT (-28.63 38.52)     │      62.0 │ 08506   │ true    │      1.0 │
│ PO000008522 │ FUNCHAL            │ POINT (-16.8997 32.63)   │      25.0 │ 08522   │ true    │      1.0 │
│ PO000008535 │ LISBOA GEOFISICA   │ POINT (-9.15 38.7167)    │      77.0 │ 08535   │ true    │      1.0 │
│ PO000008546 │ PORTO              │ POINT (-8.6 41.1331)     │      93.0 │ 08546   │ false   │      1.0 │
│ PO000008549 │ COIMBRA            │ POINT (-8.4167 40.2)     │     141.0 │ 08549   │ false   │      1.0 │
│ PO000008562 │ BEJA               │ POINT (-7.8667 38.0167)  │     246.0 │ 08562   │ false   │      1.0 │
│ PO000008575 │ BRAGANCA           │ POINT (-6.7331 41.8)     │     690.0 │ 08575   │ false   │      1.0 │
│ POE00100957 │ EXTREMO            │ POINT (-8.47 41.96)      │     380.0 │         │ false   │      1.0 │
│ POE00100958 │ PONTE DE LIMA      │ POINT (-8.59 41.77)      │      15.0 │         │ false   │      1.0 │
│ POE00100959 │ BARCELOS           │ POINT (-8.6197 41.53)    │      25.0 │         │ false   │      1.0 │
│ POE00100960 │ AMARANTE           │ POINT (-8.07 41.27)      │      90.0 │         │ false   │      1.0 │
│ POE00100961 │ CAMPI              │ POINT (-8.22 40.68)      │     446.0 │         │ false   │      1.0 │
│ POE00100962 │ AGUIAR DA BEIRA    │ POINT (-7.54 40.81)      │     670.0 │         │ false   │      1.0 │
│ POE00100963 │ ALMEIDINHA         │ POINT (-7.13 40.6)       │     815.0 │         │ false   │      1.0 │
│ POE00100964 │ GAFANHA DA NAZAR   │ POINT (-8.7164 40.62)    │       8.0 │         │ false   │      1.0 │
│ POM00008501 │ FLORES             │ POINT (-31.131 39.455)   │      34.1 │ 08501   │ false   │      1.0 │
│ POM00008512 │ PONTA DELGADA      │ POINT (-25.698 37.741)   │      78.9 │ 08512   │ true    │      1.0 │
│ POM00008515 │ SANTA MARIA        │ POINT (-25.171 36.971)   │      93.9 │ 08515   │ false   │      1.0 │
│ POM00008521 │ FUNCHAL/S.CATARINA │ POINT (-16.767 32.683)   │      49.0 │ 08521   │ false   │      1.0 │
│ POM00008524 │ PORTO SANTO        │ POINT (-16.35 33.073)    │     103.9 │ 08524   │ false   │      1.0 │
│ POM00008548 │ COIMBRA/CERNACHE   │ POINT (-8.467 40.15)     │     179.0 │ 08548   │ false   │      1.0 │
│ POM00008554 │ FARO               │ POINT (-7.966 37.014)    │       7.3 │ 08554   │ false   │      1.0 │
│ POM00008558 │ EVORA/C. COORD     │ POINT (-7.9 38.533)      │     246.0 │ 08558   │ false   │      1.0 │
│ POM00008570 │ CASTELO BRANCO     │ POINT (-7.483 39.833)    │     384.0 │ 08570   │ false   │      1.0 │
│ POW00013201 │ LAJES AB           │ POINT (-27.3217 38.7303) │    1016.2 │ 08509   │ false   │      1.0 │
├─────────────┴────────────────────┴──────────────────────────┴───────────┴─────────┴─────────┴──────────┤
│ 26 rows                                                                                      7 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Maxxen commented 1 year ago

Hi! Thanks for raising this issue. I've pushed a fix for this. If you're working with points you can cast to POINT_2D and use that overload for the ST_Distance function, which should still work, in the meantime.

marklit commented 1 year ago

That ::POINT_2D trick works well, thank you. That said, the latest master branch isn't compiling atm.

$ git pull
$ GEN=ninja OPENSSL_ROOT_DIR=/usr/local/Cellar/openssl\@3/3.1.0/ make release
[10/193] Building CXX object extension/spatial/CMakeFiles/sp...xtension.dir/spatial/src/spatial/core/functions/common.cpp.
FAILED: extension/spatial/CMakeFiles/spatial_extension.dir/spatial/src/spatial/core/functions/common.cpp.o 
/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/c++ -DCURL_STATICLIB -DDUCKDB_BUILD_LIBRARY -DPROJ_DLL="" -I/Volumes/Seagate/duckdb_spatial/duckdb/src/include -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/fsst -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/fmt/include -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/hyperloglog -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/fastpforlib -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/fast_float -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/re2 -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/miniz -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/utf8proc/include -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/miniparquet -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/concurrentqueue -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/pcg -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/tdigest -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/mbedtls/include -I/Volumes/Seagate/duckdb_spatial/duckdb/third_party/jaro_winkler -I/Volumes/Seagate/duckdb_spatial/spatial/include -I/Volumes/Seagate/duckdb_spatial/spatial/third_party/ryu/include -I/Volumes/Seagate/duckdb_spatial/spatial/third_party/yyjson/include -isystem /Volumes/Seagate/duckdb_spatial/build/release/deps/local/include -isystem /Volumes/Seagate/duckdb_spatial/build/release/dependencies/local/include -isystem /Volumes/Seagate/duckdb_spatial/build/release/dependencies/local/include/geos -isystem /usr/local/Cellar/openssl@3/3.1.0/include -O3 -DNDEBUG -O3 -DNDEBUG   -std=c++11 -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX13.3.sdk -mmacosx-version-min=13.2 -fPIC -fcolor-diagnostics -MD -MT extension/spatial/CMakeFiles/spatial_extension.dir/spatial/src/spatial/core/functions/common.cpp.o -MF extension/spatial/CMakeFiles/spatial_extension.dir/spatial/src/spatial/core/functions/common.cpp.o.d -o extension/spatial/CMakeFiles/spatial_extension.dir/spatial/src/spatial/core/functions/common.cpp.o -c /Volumes/Seagate/duckdb_spatial/spatial/src/spatial/core/functions/common.cpp
In file included from /Volumes/Seagate/duckdb_spatial/spatial/src/spatial/core/functions/common.cpp:2:
/Volumes/Seagate/duckdb_spatial/spatial/include/spatial/core/functions/common.hpp:17:49: error: unknown type name 'CastLocalStateParameters'
        static unique_ptr<FunctionLocalState> InitCast(CastLocalStateParameters &context);
                                                       ^
/Volumes/Seagate/duckdb_spatial/spatial/src/spatial/core/functions/common.cpp:14:9: error: use of undeclared identifier 'make_uniq'; did you mean 'make_unique'?
        return make_uniq<GeometryFunctionLocalState>(state.GetContext());
               ^~~~~~~~~
               make_unique
/Volumes/Seagate/duckdb_spatial/duckdb/src/include/duckdb/common/helper.hpp:40:15: note: 'make_unique' declared here
unique_ptr<T> make_unique(Args &&... args) {
              ^
/Volumes/Seagate/duckdb_spatial/spatial/src/spatial/core/functions/common.cpp:14:9: warning: use of function template name with no prior declaration in function call with explicit template arguments is a C++20 extension [-Wc++20-extensions]
        return make_uniq<GeometryFunctionLocalState>(state.GetContext());
               ^
/Volumes/Seagate/duckdb_spatial/spatial/src/spatial/core/functions/common.cpp:17:69: error: unknown type name 'CastLocalStateParameters'
unique_ptr<FunctionLocalState> GeometryFunctionLocalState::InitCast(CastLocalStateParameters &parameters) {
                                                                    ^
/Volumes/Seagate/duckdb_spatial/spatial/src/spatial/core/functions/common.cpp:18:9: warning: use of function template name with no prior declaration in function call with explicit template arguments is a C++20 extension [-Wc++20-extensions]
        return make_uniq<GeometryFunctionLocalState>(*parameters.context.get());
               ^
2 warnings and 3 errors generated.
[16/193] Building CXX object tools/sqlite3_api_wrapper/CMake..._sqlite3_api_wrapper.dir/test/test_sqlite3_api_wrapper.cpp.
ninja: build stopped: subcommand failed.
make: *** [release] Error 1
Maxxen commented 1 year ago

We now track the DuckDB master branch, so if you've cloned the repo previously you probably need to do a make pull to pull the latest duckdb submodule.

marklit commented 1 year ago

I got the latest version to compile and I can confirm it's working now. Thank you for your help.

D SELECT
> ST_POINT(longitude::DOUBLE, latitude::DOUBLE) AS geom,
> ST_DISTANCE(geom, ST_POINT(-8.6, 41.1331))
> FROM read_csv('pt_ghcnd_stations.csv',
>                   auto_detect=True,
>                   header=False,
>                   columns={'station_id':   'VARCHAR',
>                            'latitude':     'VARCHAR',
>                            'longitude':    'VARCHAR',
>                            'elevation':    'VARCHAR',
>                            'station_name': 'VARCHAR',
>                            'gsn':          'VARCHAR',
>                            'wmo_id':       'VARCHAR'});
┌──────────────────────────┬────────────────────────────────────────────┐
│           geom           │ st_distance(geom, st_point(-8.6, 41.1331)) │
│         geometry         │                   double                   │
├──────────────────────────┼────────────────────────────────────────────┤
│ POINT (-7.65 37.1167)    │                         4.1272229113533445 │
│ POINT (-28.63 38.52)     │                         20.199732463822386 │
│ POINT (-16.8997 32.63)   │                         11.882244304002503 │
│ POINT (-9.15 38.7167)    │                          2.478202768136615 │
│ POINT (-8.6 41.1331)     │                                        0.0 │
│ POINT (-8.4167 40.2)     │                         0.9509334887361957 │
│ POINT (-7.8667 38.0167)  │                         3.2015118069437123 │
│ POINT (-6.7331 41.8)     │                          1.982440722947346 │
│ POINT (-8.47 41.96)      │                         0.8370565154157771 │
│ POINT (-8.59 41.77)      │                         0.6369785004221142 │
│ POINT (-8.6197 41.53)    │                          0.397388600742399 │
│ POINT (-8.07 41.27)      │                          0.547395295924253 │
│ POINT (-8.22 40.68)      │                         0.5913540479272958 │
│ POINT (-7.54 40.81)      │                         1.1081487309923688 │
│ POINT (-7.13 40.6)       │                         1.5636801495190749 │
│ POINT (-8.7164 40.62)    │                         0.5261374060072156 │
│ POINT (-31.131 39.455)   │                          22.59340568860746 │
│ POINT (-25.698 37.741)   │                          17.43123479303747 │
│ POINT (-25.171 36.971)   │                           17.0856992075244 │
│ POINT (-16.767 32.683)   │                         11.751769186382107 │
│ POINT (-16.35 33.073)    │                         11.181579137581597 │
│ POINT (-8.467 40.15)     │                         0.9920557494415323 │
│ POINT (-7.966 37.014)    │                          4.167606124623579 │
│ POINT (-7.9 38.533)      │                          2.692678965268602 │
│ POINT (-7.483 39.833)    │                         1.7140446347747194 │
│ POINT (-27.3217 38.7303) │                          18.87526155394939 │
├──────────────────────────┴────────────────────────────────────────────┤
│ 26 rows                                                     2 columns │
└───────────────────────────────────────────────────────────────────────┘