Geography and Geometry. Putting the Genie Back in the Bottle...

[markddrake]

If I select a Georgraphy column, such as SpatialLocation in AdventureWorks.HumanResources.Address I get a nicely formatted JSON representation of the data in the column in SRID 4326 format.

Unfortunately If I attempt to insert that format back to the a geography column using a prepared statement I get a System.FormatException. I am currently forced to define the input as NVarChar(4000) since binding a Geometry doesn't appear to work. (https://github.com/tediousjs/node-mssql/issues/743).

I suspect that I am going to covert the JSON representation into some other format in order for the database to be able to consume it. Does anyone have any suggestions on a javascript library or SQL operator that can be used to perform the required conversion

[sainsb]

Don't know if this will be helpful to you or not but I recently came up against the same challenge of inserting Geog/Geom into the DB -- specifically in the context of bulk loading... The workflow I came up with is to convert the json (geojson) to WKT, insert it as a string into a temp table, -- once the insert is complete I issue a secondary statement that moves the data from the temp table into the target table and converts the WKT (string) to SQL Server Geom/Geog using this: geometry::STGeomFromText(insertedWKT)

[dhensby]

It sounds like there's a lot of work to be done around geometry/geography with this library. It's not something I have the knowledge or time to deal with, tbh. So this is going to remain a problem until someone who wants it has the time/inclination to fix it.

[s4p0]

Why don't you use a computed column for that?

CREATE TABLE [geoms]( [id] [int] NOT NULL, [geotext] varchar NULL, [srid] [int] NULL CONSTRAINT [DF_geoms_srid] DEFAULT ((0)), [geom] AS ([geometry]::STGeomFromText([geotext],[srid])) PERSISTED, )

[samlof]

I have a similar problem. I select from one table, use toTable on it to get sql.Table object and try to bulk load that into another db. The geometry field wants it as varbinary. Checking github it's replaced at https://github.com/tediousjs/node-mssql/blob/26f5aab635f7db674aa6d9d996b4544be18c1f85/lib/tedious/request.js#L604 from binary buffer into the parsed representation, with no way to skip it or get the original buffer. Would be nice if a way to get the original binary for these insert and bulk purposes

[dhensby]

Would be nice if a way to get the original binary for these insert and bulk purposes

You can supply your own value handlers to avoid the built in geometry parsing if you want to keep the raw buffer. you can then parse it on demand if you need to.

[samlof]

Thanks! Didn't see that.

Then get a bcp issue about wrong type when using the buffer. However I think that's a tedious issue and not mssql. https://github.com/tediousjs/tedious/issues/487