locked
Workarounds for getting spatial data into MDS 2012? RRS feed

  • Question

  • I know spatial data isn't supported out of the box in MDS. I was wondering if anyone had any workarounds.

    What I have tried (somewhat successfully) was have a decimal column for latitude, a decimal column for longitude, and a third column which I monkied on the back-end to define "as Geography::Point(<lat>,<long>,4326)". It worked, converting all my lat/long data into geography datatype (the display was different through Excel vs SSMS, but it was consistent with other spatial data that I have worked with before, that's why I say it worked). However, I say only "somewhat" successful because there were some problems adding additional columns, and problems when I had null lat/long data.

    Ideally what I would like to do is load in spatial data to begin with, and not have the lat/long columns. I modified the data type on Staging and the "real" table to both be Geography. SSIS successfully loaded my spatial data into the correct column on staging, and the batch showed up in the Integration Management section, and I queued it to run.

    Interestingly, none of my data was actually loaded. It said that it ran successfully (into the "real" tables), but there is no data to view on the front-end.

    Has anyone else experimented with loading spatial data into MDS 2012? Did you find anything that worked?

    Thank you!

    --Edit 5/30 - I have made more progress in my workaround, but I'm still curious to see if others have done something different.

    In order to fix my problem of getting data moved from staging to prod, I opened the stored procedure (as if to alter) and compiled it - it showed me exactly where the errors were - it was trying to have a null placeholder of "~NULL~", so of course you can't put that text into a geography column. I haven't done much testing, but I commented out the places where this would apply, and just set it to null rather than the place holder. I don't know how this will work, but at least it moved my data from staging into "production" for now. I'll do more testing and see what else I come up with.

     

    Wednesday, May 29, 2013 8:18 PM

Answers

  • Hi,

    We had a similar situation. In order to get working with the spatial data, we followed the below approach: -

    1. Created an external sql database (2008R2 or 2012) and created the tables with spatial datatypes.

    2. Create an entity in MDS which will hold the spatial data reference from the external DB.

    I hope this approach helps.

    Thanks.

    Friday, May 31, 2013 4:54 AM