Cast BLOB to GEOMETRY

[kylebarron]

Hi 👋 . I'm trying to figure out how to cast BLOB to GEOMETRY so that I can manually coerce GEOMETRY to WKB.

The goal with this is to provide an API where the user just passes in con, and where I transparently convert the GEOMETRY to WKB if necessary, instead of the user always having to call ST_AsWKB() on the geometry column.

import duckdb

con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")

result = con.execute("SELECT * FROM ST_Read('/Users/kyle/Downloads/UScounties.fgb');")
arrow_table = result.arrow()

wkb_geom_col = con.execute("SELECT ST_AsWKB(geom) FROM arrow_table");

This gives:

BinderException: Binder Error: No function matches the given name and argument types 'ST_AsWKB(BLOB)'. You might need to add explicit type casts.
	Candidate functions:
	ST_AsWKB(GEOMETRY) -> WKB_BLOB

LINE 1: SELECT ST_AsWKB(geom) FROM arrow_table

Is there a public API to coerce BLOB to GEOMETRY? I'm poor at SQL so I might've missed it. Or is this non-public because the geometry binary format isn't stable?

[yharby]

Hi @kylebarron, I believe the UScounties.fgb dataset available at https://github.com/flatgeobuf/flatgeobuf/blob/master/test/data/UScounties.fgb?raw=true is already cast to geometry. So, if you want to convert it to WKB_BLOB, you should do the following:

DROP TABLE IF EXISTS UScounties;
CREATE TABLE UScounties AS
SELECT * FROM ST_Read('https://github.com/flatgeobuf/flatgeobuf/blob/master/test/data/UScounties.fgb?raw=true')

SELECT ST_AsWKB(geom) as WKB_BLOB FROM UScounties;

If you want to convert it back:

SELECT ST_GeomFromWKB(ST_AsWKB(geom)) as geometry_from_WKB_BLOB FROM UScounties

You can see the full example here: https://colab.research.google.com/drive/1uSsdRJwsQeFQ6xp_NcOH6HDfS46wCbgp?usp=sharing

[kylebarron]

already cast to geometry

The difference is that I'm re-reading the table from Arrow, not from the existing DuckDB memory. That's why it's showing up as a blob

[yharby]

Oh sorry, I focused on the query 👀 … The only way I found is converting it before reading the table from Arrow … like :

import duckdb
con = duckdb.connect()
con.install_extension("spatial")
con.load_extension("spatial")

query = """
SELECT * EXCLUDE(geom), ST_AsWKB(geom) AS geom FROM ST_Read('UScounties.fgb')
"""

result_arrow = con.execute(query).arrow()
print(result_arrow)

output:

pyarrow.Table
STATE_FIPS: string
COUNTY_FIP: string
FIPS: string
STATE: string
NAME: string
LSAD: string
geom: binary
----
STATE_FIPS: [["23","33","50","50","23",...,"15","15","15","15","15"]]
COUNTY_FIP: [["009","007","009","019","007",...,"003","007","009","001","005"]]
FIPS: [["23009","33007","50009","50019","23007",...,"15003","15007","15009","15001","15005"]]
STATE: [["ME","NH","VT","VT","ME",...,"HI","HI","HI","HI","HI"]]
NAME: [["Hancock","Coos","Essex","Orleans","Franklin",...,"Honolulu","Kauai","Maui","Hawaii","Kalawao"]]
LSAD: [["County","County","County","County","County",...,"County","County","County","County","County"]]
geom: [[01060000000E0000000103000000010000002A0000005B3D7B23FD2151C08B6A3CA1982A4640A8FFACF9F12151C033C005D9B22A464043423DD9EC21

[kylebarron]

The only way I found is converting it before reading the table from Arrow

Right, but that requires extra effort on the user's part to know they need to convert the geometry column to WKB. I want to have a simple API where the user doesn't need to think about that.

[yharby]

Also, check out how I am trying to create a partitioned GeoParquet v1.1 with DuckDB and PyArrow from a 1 GB CSV file:
https://colab.research.google.com/drive/1c2MSiktw3Ahuu3RChFOS-tcdlPG4cHT1?usp=sharing
In this query:

DROP TABLE IF EXISTS dataset;
CREATE TABLE dataset AS
SELECT * EXCLUDE (YEAR, GEOPOINT, longitude, latitude),
       STRUCT_PACK(
           xmin := ST_XMin(ST_Envelope(ST_GeomFromWKB(geometry))),
           ymin := ST_YMin(ST_Envelope(ST_GeomFromWKB(geometry))),
           xmax := ST_XMax(ST_Envelope(ST_GeomFromWKB(geometry))),
           ymax := ST_YMax(ST_Envelope(ST_GeomFromWKB(geometry)))
       ) AS bbox,
       EXTRACT(YEAR FROM CAST(observation_date AS TIMESTAMP)) AS year,
       EXTRACT(MONTH FROM CAST(observation_date AS TIMESTAMP)) AS month
FROM (
    SELECT *,
           ST_AsWKB(ST_Point(longitude, latitude)) AS geometry
    FROM read_csv_auto('/content/drive/MyDrive/CNG/My_Tutorials/KSA/Saudi_Arabia_Hourly_Climate_Integrated_Surface_Data/Data/saudi-hourly-weather-data.csv')
) sub;

1. Here, I am using ST_AsWKB(ST_Point(longitude, latitude)) to be compatible with GeoParquet specs, which require using WKB because if I run only ST_Point(DOUBLE, DOUBLE), it returns a GEOMETRY.

2. You can see the part ST_Envelope(ST_GeomFromWKB(geometry)), there might be a better approach with latitude and longitude, but I used it like this to make it more generic for any geometry type in the future. The function ST_Envelope(GEOMETRY) returns a GEOMETRY, and my geometry column, created with ST_AsWKB(GEOMETRY), is a WKB_BLOB. Thus, I have to convert it with ST_GeomFromWKB(BLOB) before using it with ST_Envelope(GEOMETRY), as I did in ST_Envelope(ST_GeomFromWKB(geometry)).

I hope this makes it clearer if I understand your question correctly.

[kylebarron]

With a bit more research, I discovered that my life is easier as long as I rely on a DuckDBPyRelation object from Python instead of a DuckDBPyConnection object. The former gives me the column types, so I can easily see which column is a spatial column. But the crucial part is that I can make another query to convert to WKB reusing that existing DuckDBPyRelation and without going through Arrow and without losing the type metadata.

This is all just a workaround until #153 is implemented.