Convert WKB to GeoJSON with ClickHouse?

[marklit]

Is there any built-in geospatial functionality which would allow me to convert a field containing Well-known Binary data into GeoJSON? If not, are there any plans to add such functionality?

For example, the following string value:

0106000000010000000103000000010000000B000000FCC6D79E59F755C0B874CC79C65C4540029EB47059F755C0C3BAF1EEC85C4540BA83D89942F755C0200A664CC15C4540027E8D2441F755C06DA7AD11C15C4540F3E2C4573BF755C06DA7AD11C15C45406A2DCC423BF755C093DFA293A55C45403EB0E3BF40F755C0F2E7DB82A55C4540BF266BD443F755C046425BCEA55C45400551F70148F755C0814067D2A65C45405BCF108E59F755C0D1CABDC0AC5C4540FCC6D79E59F755C0B874CC79C65C4540

Would convert into:

{
    "type": "MultiPolygon",
    "coordinates": [
        [
            [
                [-87.864845, 42.724807],
                [-87.864834, 42.724882],
                [-87.86344, 42.724649],
                [-87.863351, 42.724641999999996],
                [-87.862997, 42.724641999999996],
                [-87.86299199999999, 42.723803],
                [-87.863327, 42.723800999999995],
                [-87.86351499999999, 42.72381],
                [-87.86377, 42.723841],
                [-87.864841, 42.724022],
                [-87.864845, 42.724807]
            ]
        ]
    ]
}

[alexey-milovidov]

We have functions for reading WKT, but not for WKB.

[blackrez]

Hello Mark,
I'm working on my free time to add geo support in clickhouse with georust, actually I got a very basic support of WKB reading with UDF (written in rust). I need to clean my code and I will share it

@alexey-milovidov the support of WKT is very very limited in clickhouse, it only supports Polygon (and there is a lot of types in GIS), this is quite sad that clickhouse don't have opengis function support.

[marklit]

@blackrez I spoke with a whole bunch of ClickHouse devs earlier this year about GIS support in ClickHouse. They're aware of https://www.ogc.org/standard/sfs/ and I suspect they're working on something but either haven't announced it or if they did, I didn't hear about it.

Let me know when I can take a look at your GeoRust integrations. I'm interested to see what's possible with it.

[blackrez]

@marklit I published the very first draft https://github.com/blackrez/clickgis.