handstuyennn/geo

geo's Issues

Error compilation for Amazon Linux 2

Hello,

I'm building a layer for AWS lambda and I have a compilation error.

Scanning dependencies of target duckdb_geo_geos
gmake[3]: Leaving directory `/var/task/geo/build/release'
gmake[3]: Entering directory `/var/task/geo/build/release'
[ 55%] Building CXX object extension/geo/third_party/geos/CMakeFiles/duckdb_geo_geos.dir/geos_c.cpp.o
In file included from /var/task/geo/geo/third_party/geos/geos_c.cpp:18:0:
/var/task/geo/geo/third_party/geos/include/geos/geom/CoordinateSequence.hpp:38:15: error: ‘unique_ptr’ in namespace ‘std’ does not name a template type
  typedef std::unique_ptr<CoordinateSequence> Ptr;
               ^~~~~~~~~~
/var/task/geo/geo/third_party/geos/include/geos/geom/CoordinateSequence.hpp:123:15: error: ‘unique_ptr’ in namespace ‘std’ does not name a template type
  virtual std::unique_ptr<CoordinateSequence> clone() const = 0;
               ^~~~~~~~~~
gmake[3]: *** [extension/geo/third_party/geos/CMakeFiles/duckdb_geo_geos.dir/geos_c.cpp.o] Error 1
gmake[3]: Leaving directory `/var/task/geo/build/release'
gmake[2]: *** [extension/geo/third_party/geos/CMakeFiles/duckdb_geo_geos.dir/all] Error 2
gmake[2]: Leaving directory `/var/task/geo/build/release'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/var/task/geo/build/release'
make: *** [release] Error 2

The check from cmake

cmake --build build/release
-- The C compiler identification is GNU 7.3.1
-- The CXX compiler identification is GNU 7.3.1
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Detecting C compile features
-- Detecting C compile features - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Looking for pthread.h
-- Looking for pthread.h - found
-- Looking for pthread_create
-- Looking for pthread_create - not found
-- Looking for pthread_create in pthreads
-- Looking for pthread_create in pthreads - not found
-- Looking for pthread_create in pthread
-- Looking for pthread_create in pthread - found
-- Found Threads: TRUE
-- Found Git: /usr/bin/git (found version "2.38.1")
-- git hash 67dab2a30b, version v0.6.2-dev2059
CMake Warning at CMakeLists.txt:409 (message):
  Please use a recent compiler for debug builds

The Dockerfile

FROM public.ecr.aws/lambda/python:3.9
CMD yum -y groupinstall "Development Tools" &&\
    yum -y install cmake3 && \
    ln -s /usr/bin/cmake3 /usr/bin/cmake && \
    git clone https://github.com/handstuyennn/geo && \
    cd geo && \
    make release

Ready for DuckDb 0.7.0?

Thanks

Compilation error with duckdb 0.7.0

Hello,

I use the last version from git and the compilation doesn't work.

On Amazon Linux 2

gmake[3]: Leaving directory `/build/geo/build/release'
gmake[3]: Entering directory `/build/geo/build/release'
[ 83%] Building CXX object extension/geo/CMakeFiles/geo_extension.dir/geo-extension.cpp.o
[ 83%] Building CXX object extension/geo/CMakeFiles/geo_extension.dir/geo-functions.cpp.o
/build/geo/geo/geo-functions.cpp: In function ‘void duckdb::GeometryDumpOperator(duckdb::string_t, duckdb::idx_t, duckdb::LogicalType, duckdb::Vector&)’:
/build/geo/geo/geo-functions.cpp:1036:38: error: passing ‘const duckdb::LogicalType’ as ‘this’ argument discards qualifiers [-fpermissive]
   value.type().CopyAuxInfo(child_type);
                                      ^
In file included from /build/geo/duckdb/src/include/duckdb/function/cast/default_casts.hpp:11:0,
                 from /build/geo/duckdb/src/include/duckdb/function/cast/cast_function_set.hpp:11,
                 from /build/geo/geo/include/geo-functions.hpp:11,
                 from /build/geo/geo/geo-functions.cpp:1:
/build/geo/duckdb/src/include/duckdb/common/types.hpp:301:14: note:   in call to ‘void duckdb::LogicalType::CopyAuxInfo(const duckdb::LogicalType&)’
  inline void CopyAuxInfo(const LogicalType& other) {
              ^~~~~~~~~~~
gmake[3]: *** [extension/geo/CMakeFiles/geo_extension.dir/geo-functions.cpp.o] Error 1
gmake[3]: Leaving directory `/build/geo/build/release'
gmake[2]: *** [extension/geo/CMakeFiles/geo_extension.dir/all] Error 2
gmake[2]: Leaving directory `/build/geo/build/release'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/build/geo/build/release'
make: *** [release] Error 2

On MacOS :

[ 85%] Built target duckdb_geo_json
[ 85%] Building CXX object extension/geo/CMakeFiles/geo_extension.dir/geo-extension.cpp.o
[ 85%] Building CXX object extension/geo/CMakeFiles/geo_extension.dir/geo-functions.cpp.o
/Users/nabil/project/geo/geo/geo-functions.cpp:1069:3: error: 'this' argument to member function 'CopyAuxInfo' has type 'const duckdb::LogicalType', but function is not marked const
                value.type().CopyAuxInfo(child_type);
                ^~~~~~~~~~~~
/Users/nabil/project/geo/duckdb/src/include/duckdb/common/types.hpp:301:14: note: 'CopyAuxInfo' declared here
        inline void CopyAuxInfo(const LogicalType& other) {
                    ^
1 error generated.
gmake[3]: *** [extension/geo/CMakeFiles/geo_extension.dir/build.make:90: extension/geo/CMakeFiles/geo_extension.dir/geo-functions.cpp.o] Error 1
gmake[2]: *** [CMakeFiles/Makefile2:8935: extension/geo/CMakeFiles/geo_extension.dir/all] Error 2
gmake[1]: *** [Makefile:136: all] Error 2
gmake[1] : on quitte le répertoire « /Users/nabil/project/geo/build/release »
make: *** [release] Error 2

Hello!

Hi! I'm working on geospatial @ duckdblabs, would you be up for a chat? I could not find any contact information to reach you, but please feel free to email me at [email protected]

Support rotation

It would be great if this extension supported rotating latitude and longitude around the three spherical axes.

ST_CONTAINS gives me a SegmentationFault

Hi,

I try to check if a point is in a polygon, but it gives me a SegmentationFault :

SELECT 
    ST_CONTAINS(st_geogfromgeojson('{ "type": "Polygon", "coordinates": [[[-74.248352, 40.555548],[-73.824005, 40.555548],[-73.824005, 40.82628],[-74.248352, 40.82628],[-74.248352, 40.555548]]]}'),
    ST_MAKEPOINT(40.703546, -74.042358));

I tried with duckdb v0.7.2-dev899 88b1bfa74d, on Mac OS 13.1 (M2 processor)

Thanks a lot for your help.

Speed up compilations

The current master branch doesn't use multiple cores when building DuckDB or this extension. Consider adding something along the lines of the following to the Makefile.

$ CMAKE_BUILD_PARALLEL_LEVEL=$(nproc) \
        cmake --build build/release

Build fails

I currently get an error when attempting to build the extension:

[ 80%] Building CXX object extension/geo/CMakeFiles/geo_extension.dir/geo-extension.cpp.o
In file included from /tmp/from-git/geo/duckdb/src/include/duckdb/function/aggregate_function.hpp:16:0,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/function/udf_function.hpp:12,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/main/connection.hpp:14,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb.hpp:11,
                 from /tmp/from-git/geo/geo/include/geo-extension.hpp:10,
                 from /tmp/from-git/geo/geo/geo-extension.cpp:3:
/tmp/from-git/geo/duckdb/src/include/duckdb/common/vector_operations/aggregate_executor.hpp: In instantiation of ‘static void duckdb::AggregateExecutor::Destroy(duckdb::Vector&, duckdb::AggregateInputData&, duckdb::idx_t) [with STATE_TYPE = duckdb::ClusterDBScanState; OP = duckdb::ClusterDBScanOperation; duckdb::idx_t = long unsigned int]’:
/tmp/from-git/geo/duckdb/src/include/duckdb/function/aggregate_function.hpp:289:40:   required from ‘static void duckdb::AggregateFunction::StateDestroy(duckdb::Vector&, duckdb::AggregateInputData&, duckdb::idx_t) [with STATE = duckdb::ClusterDBScanState; OP = duckdb::ClusterDBScanOperation; duckdb::idx_t = long unsigned int]’
/tmp/from-git/geo/geo/include/geo_aggregate_function.hpp:321:109:   required from here
/tmp/from-git/geo/duckdb/src/include/duckdb/common/vector_operations/aggregate_executor.hpp:359:36: error: no matching function for call to ‘duckdb::ClusterDBScanOperation::Destroy<duckdb::ClusterDBScanState>(duckdb::AggregateInputData&, duckdb::ClusterDBScanState*&)’
    OP::template Destroy<STATE_TYPE>(aggr_input_data, sdata[i]);
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from /tmp/from-git/geo/geo/geo-extension.cpp:14:0:
/tmp/from-git/geo/geo/include/geo_aggregate_function.hpp:279:14: note: candidate: template<class STATE> static void duckdb::ClusterDBScanOperation::Destroy(STATE*)
  static void Destroy(STATE *state) {
              ^~~~~~~
/tmp/from-git/geo/geo/include/geo_aggregate_function.hpp:279:14: note:   template argument deduction/substitution failed:
In file included from /tmp/from-git/geo/duckdb/src/include/duckdb/function/aggregate_function.hpp:16:0,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/function/udf_function.hpp:12,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/main/connection.hpp:14,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb.hpp:11,
                 from /tmp/from-git/geo/geo/include/geo-extension.hpp:10,
                 from /tmp/from-git/geo/geo/geo-extension.cpp:3:
/tmp/from-git/geo/duckdb/src/include/duckdb/common/vector_operations/aggregate_executor.hpp:359:36: note:   candidate expects 1 argument, 2 provided
    OP::template Destroy<STATE_TYPE>(aggr_input_data, sdata[i]);
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from /tmp/from-git/geo/duckdb/src/include/duckdb/function/aggregate_function.hpp:16:0,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/function/udf_function.hpp:12,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/main/connection.hpp:14,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb.hpp:11,
                 from /tmp/from-git/geo/geo/include/geo-extension.hpp:10,
                 from /tmp/from-git/geo/geo/geo-extension.cpp:3:
/tmp/from-git/geo/duckdb/src/include/duckdb/common/vector_operations/aggregate_executor.hpp: In instantiation of ‘static void duckdb::AggregateExecutor::Destroy(duckdb::Vector&, duckdb::AggregateInputData&, duckdb::idx_t) [with STATE_TYPE = duckdb::ClusterDBScanState; OP = duckdb::ClusterDBScanOperation; duckdb::idx_t = long unsigned int]’:
/tmp/from-git/geo/duckdb/src/include/duckdb/function/aggregate_function.hpp:289:40:   required from ‘static void duckdb::AggregateFunction::StateDestroy(duckdb::Vector&, duckdb::AggregateInputData&, duckdb::idx_t) [with STATE = duckdb::ClusterDBScanState; OP = duckdb::ClusterDBScanOperation; duckdb::idx_t = long unsigned int]’
/tmp/from-git/geo/geo/include/geo_aggregate_function.hpp:321:109:   required from here
/tmp/from-git/geo/duckdb/src/include/duckdb/common/vector_operations/aggregate_executor.hpp:359:36: error: no matching function for call to ‘duckdb::ClusterDBScanOperation::Destroy<duckdb::ClusterDBScanState>(duckdb::AggregateInputData&, duckdb::ClusterDBScanState*&)’
    OP::template Destroy<STATE_TYPE>(aggr_input_data, sdata[i]);
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from /tmp/from-git/geo/geo/geo-extension.cpp:14:0:
/tmp/from-git/geo/geo/include/geo_aggregate_function.hpp:279:14: note: candidate: template<class STATE> static void duckdb::ClusterDBScanOperation::Destroy(STATE*)
  static void Destroy(STATE *state) {
              ^~~~~~~
/tmp/from-git/geo/geo/include/geo_aggregate_function.hpp:279:14: note:   template argument deduction/substitution failed:
In file included from /tmp/from-git/geo/duckdb/src/include/duckdb/function/aggregate_function.hpp:16:0,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/function/udf_function.hpp:12,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb/main/connection.hpp:14,
                 from /tmp/from-git/geo/duckdb/src/include/duckdb.hpp:11,
                 from /tmp/from-git/geo/geo/include/geo-extension.hpp:10,
                 from /tmp/from-git/geo/geo/geo-extension.cpp:3:
/tmp/from-git/geo/duckdb/src/include/duckdb/common/vector_operations/aggregate_executor.hpp:359:36: note:   candidate expects 1 argument, 2 provided
    OP::template Destroy<STATE_TYPE>(aggr_input_data, sdata[i]);
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~~~~
gmake[3]: *** [extension/geo/CMakeFiles/geo_extension.dir/geo-extension.cpp.o] Error 1
gmake[3]: Leaving directory `/tmp/from-git/geo/build/release'
gmake[2]: *** [extension/geo/CMakeFiles/geo_extension.dir/all] Error 2
gmake[2]: *** Waiting for unfinished jobs....
[ 81%] Building CXX object extension/geo/CMakeFiles/geo_loadable_extension.dir/postgis/lwgeom_inout.cpp.o
gmake[3]: *** [extension/geo/CMakeFiles/geo_loadable_extension.dir/geo-extension.cpp.o] Error 1
gmake[3]: *** Waiting for unfinished jobs....
gmake[3]: Leaving directory `/tmp/from-git/geo/build/release'
[ 81%] Built target parquet_loadable_extension
[ 82%] Linking CXX executable test_sqlite3_api_wrapper
gmake[3]: Leaving directory `/tmp/from-git/geo/build/release'
[ 82%] Built target test_sqlite3_api_wrapper
gmake[3]: Leaving directory `/tmp/from-git/geo/build/release'
gmake[2]: *** [extension/geo/CMakeFiles/geo_loadable_extension.dir/all] Error 2
[ 83%] Linking CXX executable unittest
gmake[3]: Leaving directory `/tmp/from-git/geo/build/release'
[ 83%] Built target unittest
gmake[2]: Leaving directory `/tmp/from-git/geo/build/release'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/tmp/from-git/geo/build/release'
make: *** [release] Error 2
The command '/bin/sh -c cd /tmp/from-git/geo && make' returned a non-zero code: 2

Error: Process completed with exit code 2.

Clarify License

👋 Hello there!

Would you be able to add a top-level license file to clarify the license of the project? Are your additions also GPL2 licensed?

No enum constant org.duckdb.DuckDBColumnType.GEOGRAPHY

I might be missing something simple, but couldn't find anything online. When calling ST_MAKEPOINT I receive the error below:

No enum constant org.duckdb.DuckDBColumnType.GEOGRAPHY

I'm using DuckDB 0.7.0, compiling on an M1 Mac.

Add index support ?

Hello,

For accelerate the query, I don't have a precise idea but I have a few leads :

Spatial index :
https://libspatialindex.org/en/latest/#

https://github.com/pvigier/Quadtree

How Sedona and PostGIS do :
https://sedona.apache.org/latest-snapshot/api/sql/Optimizer/#geoparquet

https://thegeekyasian.com/optimizing-query-performance-with-postgresql-geospatial-indexing/

ST_Centroid eats 100% CPU

Hello,

The function ST_Centroid eats all the CPU

SELECT ST_ASTEXT(ST_CENTROID(ST_GEOMFROMWKB(geom))) FROM '/Users/Nabil/Downloads/mutations_d13.parquet'  WHERE valeurfonc=378000000.0;

I have to kill the duckdb process.

I tested with shapely and it works without issue. I think it's not the geometry but the function.

>>> r = con.execute("SELECT ST_ASGEOJSON(ST_GEOMFROMWKB(geom)) FROM '/Users/Nabil/Downloads/mutations_d13.parquet'  WHERE valeurfonc=378000000.0;")
>>> g = r.fetchall()
>>> s = shape(json.loads(g[0][0])
>>> s.centroid
<POINT (5.469 43.294)>

Extension size 398mb?

I did a custom build of the geo extension, and I see a extension size of 398mb:

$ ls -la --block-size=M /tmp/release"
total 398M
drwxr-xr-x 2 root root   1M Mar 28 12:52 .
drwxrwxrwt 1 root root   1M Mar 28 12:52 ..
-rwxr-xr-x 1 root root 398M Mar 28 12:52 geo.duckdb_extension

Is this a problem with my build, or is it really that large?

Copyright information removed

Hello! Congratulations on starting a spatial data project! It's a very cool area of work, welcome to the geospatial club :)

I see you've decided to make some use of PostGIS internals. That's great! We do however, require you to retain our copyright headers (we did write those files, after all) and place your work under the GPLv2 (that's the GPL bargain, share and share alike). Thanks!

'duckdb/function/aggregate/sum_helpers.hpp' file not found

When I build extension with the command make I receive following error:

/Users/lukasz/Documents/Projects/python/geo/geo/geo-extension.cpp:9:10: fatal error: 'duckdb/function/aggregate/sum_helpers.hpp' file not found
#include "duckdb/function/aggregate/sum_helpers.hpp"
         ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 error generated.
/Users/lukasz/Documents/Projects/python/geo/geo/geo-extension.cpp:9:10: fatal error: 'duckdb/function/aggregate/sum_helpers.hpp' file not found
#include "duckdb/function/aggregate/sum_helpers.hpp"
         ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
gmake[3]: *** [extension/geo/CMakeFiles/geo_extension.dir/build.make:76: extension/geo/CMakeFiles/geo_extension.dir/geo-extension.cpp.o] Error 1
gmake[3]: *** Waiting for unfinished jobs....
1 error generated.
gmake[3]: *** [extension/geo/CMakeFiles/geo_loadable_extension.dir/build.make:76: extension/geo/CMakeFiles/geo_loadable_extension.dir/geo-extension.cpp.o] Error 1
gmake[3]: *** Waiting for unfinished jobs....
[ 82%] Building CXX object extension/geo/CMakeFiles/geo_loadable_extension.dir/postgis/lwgeom_functions_analytic.cpp.o
gmake[3]: Leaving directory '/Users/lukasz/Documents/Projects/python/geo/build/release'
gmake[2]: *** [CMakeFiles/Makefile2:8615: extension/geo/CMakeFiles/geo_loadable_extension.dir/all] Error 2
gmake[2]: *** Waiting for unfinished jobs....
gmake[3]: Leaving directory '/Users/lukasz/Documents/Projects/python/geo/build/release'
gmake[2]: *** [CMakeFiles/Makefile2:8576: extension/geo/CMakeFiles/geo_extension.dir/all] Error 2
gmake[2]: Leaving directory '/Users/lukasz/Documents/Projects/python/geo/build/release'
gmake[1]: *** [Makefile:136: all] Error 2
gmake[1]: Leaving directory '/Users/lukasz/Documents/Projects/python/geo/build/release'
make: *** [release] Error 2

I use MacOSX Ventura 13.2 (M1 Pro Chip)

Could you please let me know how to create the build properly?

Version issues

Is there a way to specify the version ?
I can pull the matching version for the DuckDB CLI binaries, and am able to load and install the geo extension.
However, when using DuckDB JDBC 0.7.1 I get the following issue

version (v0.7.2-dev2366) does not match DuckDB version (v0.7.1)

Become an official extensions

Hello,

Actually it's quite impossible to use the extensions outside the command line of DuckDB. ODBC, librairies and other don't support unsigned extensions in odbc.

Thanks.

duckdb/duckdb#5355
duckdb/duckdb#5578

duckdb geo in Observable

DuckDB is now very much used in Observable. Is it possible to use this extension in this context? If yes, how?
https://observablehq.com/@observablehq/duckdb