Add support for geospatial data in PostgreSQL

[joshdunk98]

Describe your feature request

Currently, when I try to execute a select query on a table with geospatial data, a PanicException occurs saying not implemented: geometry. It would be beneficial to query geospatial data with connectorx so that we can then convert it to a geopandas dataframe or do whatever else we need to do with the geometry data.

[gregorywaynepower]

For clarity, this is for PostGIS's geometry type and not anything for Postgres's geometric types. What version of PostGIS are you throwing your queries at?

[joshdunk98]

Correct. The version my team is using is PostGIS version 3.1.4. We are trying to handle large datasets of geospatial data and would like to use connectorx, if possible. Right now, we are using modin pandas.

[gregorywaynepower]

Would DuckDB with the spatial extension be suitable for your use case? You can attach it to your Postgres instance pretty easily. It runs quite fast out of the box, and even faster by utilizing their native types (POINT_2D, LINESTRING_2D, POLYGON_2D, and BOX_2D) for calculations since it's columnar database.

[joshdunk98]

Not really. While that is something I would like to explore in my personal time, the use case for this doesn't provide a great argument to transition to using another database management system. We currently load data using the read_sql functions on pandas/geopandas to make it easier for our team to not have to learn complicated SQL to handle the geospatial data. We want to be able to pull in the data into a dataframe (and quickly) and do all of the manipulation in Python so that we can make our code scale in the future using Dask or some other scaling library.