Spatial Field Imported as Blob

[Garrett_Stoker]

I have a large amount of data I'm reading from a 3rd party database connection. There is one spatial field that is a Geometry data type on their SQL Server that I would like to come in as a SpatialObj. It's currently importing as a Blob and I either need to find a way to import it as a shape or convert that Blob to a SpatialObj type. The goal is to use that spatial field in a much longer workflow that can check whether that shape overlaps another shape field from a different input.

There was a question about this from 8 years ago here, but the information there wasn't quite enough (I'm focused on importing, not outputting) and potentially out of date.

The connection I'm using is an ODBC connection (SQL Server Driver), and I've tried several different types of connections including OLE DB. I've tried converting the Blob to a different format, but I'm not sure where to go from there.

[usmanbashir]

Are you able to provide any sample data?

[apathetichell]

https://stackoverflow.com/questions/36141323/spatial-datatype-geometry-to-geojson

your core issue to convert the geometry to geojson - so look around for those usecases. the conversion needs to be done in SQL Server vs in Alteryx.

[Garrett_Stoker]

For this input connection, we don't have write access to that database. Is that doable without creating a new table in the source?

[apathetichell · Accepted solution]

My hunch is you can run some kind of function through a formula in-db tool to create a new field which would be in geojson - but the specifics of how to do it aren't something I've spent any time on. StackOverflow is probably your best resource here.

[Garrett_Stoker]

This didn't end up being the solution I landed on, but you did help me get the ball rolling.

I didn't recognize that the objects we hex encoded in the database, and Alteryx wasn't recognizing it because there were two errant characters at the beginning of each record. Removing those converted it to a sequence of coordinates that I was able to format and use Poly-Build on.

[apathetichell]

Great to here - and glad you were able to find a solution.