How can I get WKB as a hex string?

[marklit]

I want to get the following value out as 010300000001000000d9020000715dbf6037d656c0b1a206d330b8...

D SELECT geom from table limit 1;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                          geom                                                           │
│                                                        geometry                                                         │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POLYGON ((-91.3471299999999 35.43899, -91.34711 35.43949, -91.3471699999999 35.44007, -91.34737 35.4408500000001, -91…  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

D select ST_AsWKB(geom) from table limit 1;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                     st_aswkb(geom)                                                      │
│                                                        wkb_blob                                                         │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POLYGON ((-91.3471299999999 35.43899, -91.34711 35.43949, -91.3471699999999 35.44007, -91.34737 35.4408500000001, -91…  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

D select ST_AsWKB(geom)::BIGINT from table limit 1;
Error: Conversion Error: Unimplemented type for cast (WKB_BLOB -> BIGINT)

D select geom::BIGINT from table limit 1;
Error: Conversion Error: Unimplemented type for cast (GEOMETRY -> BIGINT)

D select ST_AsWKB(geom)::TEXT from table limit 1;
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             CAST(st_aswkb(geom) AS VARCHAR)                                             │
│                                                         varchar                                                         │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POLYGON ((-91.3471299999999 35.43899, -91.34711 35.43949, -91.3471699999999 35.44007, -91.34737 35.4408500000001, -91…  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

D select ST_AsText(geom) from table limit 1;
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                     st_astext(geom)                                                     │
│                                                         varchar                                                         │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POLYGON ((-91.3471299999999 35.43899, -91.34711 35.43949, -91.3471699999999 35.44007, -91.34737 35.4408500000001, -91…  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

D select printf('%x', ST_AsWKB(geom)) from table limit 1;
 69% ▕█████████████████████████████████████████▍                  ▏ Run Time (s): real 2.551 user 4.143314 sys 0.336847
Error: Invalid Error: invalid type specifier

[Maxxen]

Cast to BLOB should work

[marklit]

I can get the binary out.

D select ST_ASWKB(geom)::blob::text from table limit 1;
100% ▕████████████████████████████████████████████████████████████▏ 
CAST(CAST(st_aswkb(geom) AS BLOB) AS VARCHAR) = \x01\x03\x00\x

Any idea how that binary can then be turned into a hex string?

D select printf('%x',ST_ASWKB(geom)::blob) from table limit 1;
Error: Invalid Error: invalid type specifier

D select printf('%x',ST_ASWKB(geom)::blob::bigint) from table limit 1;
Error: Conversion Error: Unimplemented type for cast (BLOB -> BIGINT)

D select printf('%x',ST_ASWKB(geom)::blob::text) from table limit 1;
Error: Invalid Error: invalid type specifier

[marklit]

I recompiled the latest master branch. How could I convert the binary output of ST_AsWKB into a text string? Below I was hoping to see "01010000..." for the geom field but it's the binary form of this instead.

D COPY (SELECT * EXCLUDE(GEOMETRY_BIN),
> 
>              printf('%x',
>               h3_latlng_to_cell(
>                   ST_Y(ST_CENTROID(GEOMETRY_BIN::GEOMETRY)),
>                   ST_X(ST_CENTROID(GEOMETRY_BIN::GEOMETRY)),
>                   7)::bigint) as h3_7,
> 
>              printf('%x',
>               h3_latlng_to_cell(
>                   ST_Y(ST_CENTROID(GEOMETRY_BIN::GEOMETRY)),
>                   ST_X(ST_CENTROID(GEOMETRY_BIN::GEOMETRY)),
>                   8)::bigint) as h3_8,
> 
>              printf('%x',
>               h3_latlng_to_cell(
>                   ST_Y(ST_CENTROID(GEOMETRY_BIN::GEOMETRY)),
>                   ST_X(ST_CENTROID(GEOMETRY_BIN::GEOMETRY)),
>                   9)::bigint) as h3_9,
> 
>              ST_AsWKB(GEOMETRY_BIN::GEOMETRY) AS geom
> 
>       FROM st_read('SF_Professional_AK.gdb/a00000011.gdbtable')
>       LIMIT 1)
> TO 'test.pq' (FORMAT 'PARQUET',
>               CODEC  'Snappy');

D select * from read_parquet('test.pq');

┌──────────────────────┬───────────┬─────────────────┬───┬─────────────────┬─────────────────┬──────────────────────┐
│    ASSESSMENT_LID    │ FIPS_CODE │ FIPS_STATE_CODE │ … │      h3_8       │      h3_9       │         geom         │
│       varchar        │  varchar  │     varchar     │   │     varchar     │     varchar     │         blob         │
├──────────────────────┼───────────┼─────────────────┼───┼─────────────────┼─────────────────┼──────────────────────┤
│ 111111111111111111…  │ 11111     │ 11              │ … │ 880d1c89b7fffff │ 890d1c89b6fffff │ \x01\x01\x00\x00\x…  │
├──────────────────────┴───────────┴─────────────────┴───┴─────────────────┴─────────────────┴──────────────────────┤
│ 1 rows                                                                                      424 columns (6 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

If I add ::TEXT it turns it back into WKT and some of these WKB strings are over a MB in length so BIGINT won't be big enough. Ideally, I'd like the field kept as varchar of the hex string representation in the PQ file so BigQuery can read them in without any transformation step.

[Maxxen]

You should be able to use the new ST_AsHEXWKB function which formats geometry to the same hex format as postgis uses to print geom (without the '\0x...'). Still, the WKB written into the parquet file in your example should be raw binary, its only duckdb that formats blobs as \0x...' when printing in the the CLI. The underlying data is still binary, not text.

[marklit]

Perfect, thank you.