Hope Foley's Blog

Another day in the life of a SQL Server DBA

Datazen Custom Map Craziness June 15, 2015

Filed under: DataZen — hopefoley @ 12:09 pm
Tags: ,

I often struggle coming up with ideas for blog posts. Thank you Datazen again for another idea!  After encountering the issue I posted earlier (link to post) when working with maps, I was very anxious to try to create a custom map.  I started working on one with my brewery data.  I ran into several bumps in the process so wanted to share with everyone.

 ***

I had collected several shapefiles through the years playing with spatial data.  I began trying a few of them in Datazen but had a hard time finding any that worked. When I would select them, they would just hang and sit there. Some were very large so I suspected there was a size limit though there wasn’t anything in the documentation. In my testing I couldn’t get anything over 1MB to work. Later Chris Finlan said that the actual limit is 512 KB! Rather limiting but there is a potential workaround by using mapshaper.org to minimize the size. Depending on what type of shapefile you are using it can distort your polygons potentially too much so that workaround only goes so far.

Tweet from Chris Finlan on shapefile size.

Tweet from Chris Finlan on shapefile size.

After getting the size issue nailed down, I continued on with my mission of getting my custom map working. Normally when you use shapefiles they contain a few files. Datazen uses the shp file and dbf file. The shp file contains the polygon information and details to draw the map. The dbf file contains the attributes about those shapes or the data you’ll need to tie to your own data. Below is a screenshot of my dbf file using DBF Viewer 2000 software (free trial then about $50 to purchase after 30 days at http://www.dbf2002.com/).

The original dbf file

The original dbf file

You can see it has a key field (ogr_fid) then zcta5ce10 which is actually field that contains zip code. The zcta5ce10 field is the key I needed to use to tie with my own data containing zip codes. When you use Datazen you can’t select the proper key, and as it turns out it must have the key as the first column in the dbf file! Crazy right?! Using the DBF Viewer 2000 software, I had move the column to be the first in the dbf file.

Updated dbf file to move key column to be first.

Updated dbf file to move key column to be first.

After a bit of head shaking I move on and at this point I was excited to see my custom map working! But wait, why is my data point not where I expect it to be? I was mapping zip codes of breweries in Indiana that have been rated on BeerAdvocate. There is unfortunately only one (though I’d argue that should be more) and it’s Three Floyds which is up near Chicago but the Datazen map wasn’t accurately showing it (see below).

The report displaying the location incorrectly.

The report displaying the location incorrectly.

I opened up the shapefiles in other programs (QGIS/Google Earth) and it was showing correctly both before and after minimization process. After talking with Datazen support it, they sent this info:

 ***

“While I can’t say for certain where things went wrong, I suspect it was due to improper minifying – something got misaligned between the SHP and the DBF and QGIS and Google Earth all read the SHX file to properly map the shapes to the database. Datazen does not do that. We assume a 1-to-1 relation between shape and data: the Nth shape corresponds to the Nth entry in the DBF.”

 ***

I had inadvertently changed the order of the data in the dbf file when I had to edit the order of the columns. Since other programs I use to view shapefiles also use the shx file, there wasn’t an issue with them correctly mapping the shape to the attribute even after accidentally changing the order. A shx file is basically a linking/index file between the shp and dbf files. Be very careful when editing the order of your columns in the dbf file. One accidental click on a column and it can ruin the dbf/shp file from working properly. If they don’t line up, your report can be reporting your data in random polygons! I’m glad I had a very simple dataset and that I knew where Three Floyds was supposed to be located as I may have very easily missed that. Make sure you do some validation on your reports to be safe.

 ***

I’m very interested to see what Microsoft does with Datazen now. I’m intrigued to see what/when any updates to it occur. I’m also interested in others experiences with custom maps in Datazen. Please share in the comments any issues/experiences you encountered.

Advertisements
 

13 Responses to “Datazen Custom Map Craziness”

  1. Hope – thanks so much for posting this. Knowing that the key had to be in the first column of the dbf file was a lifesaver! The other tips are really helpful to know, too.

  2. […] nice out of the box. Here’s a time saving tip when creating maps in Datazen. Make sure that you spell out the state names when you want to use them. The visualizations you create will allow you to drill into other […]

  3. John McAdoo Says:

    Is there anyway to display shapefile attributes or Labels in Datazen presentation of the layout view?

    • hopefoley Says:

      Great question but no unfortunately you cannot. You can only view your data on the map in Datazen. You can though pull data out of your shapefile, put into Excel, add to your data based on the key you were already using. I just tested in QGIS and was able to copy data from dbf file and paste into Excel. May take some Excel acrobatics to link to your existing data but seems doable…not ideal but doable.

      • John McAdoo Says:

        In the Datazen demo entitled ‘Regional Sales Breakdown’ a bubble map is displayed. Continent’s name appear. I could use this for a similar effect but Datazen support says in cannot be done. Copied the demo project to local box, ran it. The bubbles appear immediately with no operator action. The Continent’s name appears along with ‘Value’. I am confused why I cannot do same with my project. Very discouraging.

  4. John McAdoo Says:

    Support got back to me quickly when I complained. New version out very soon should rectify these issues.

    • hopefoley Says:

      Thanks John for sharing. Yeah the support folks for DataZen have been great! I have sent tons of questions their way and always very quick to respond. I hope that continues as they are more worked into the Microsoft fold.

  5. Tom Says:

    I put the NAME column to the first column, but datazen still uses the ID-KEY as pop-up label.

    What am I doing wrong here?

    NAME is the first in DVF-viewer, but would I need to explicitly indicate NAME is the new key?

    If so, how do I do that?

    Thanks a million!

    Kind Regards,
    Tom

    • hopefoley Says:

      No you shouldn’t have to do anything other than move the column in the dbf file to be the first column listed to have it hit your key. The pop up should display what you have listed in the Values field listed in the data view for the map.

  6. Abdul Mukhtar Says:

    Hi,

    My requirement is, i need to create a saudi arabia map and the grain of the map is branch.
    Help on the below things is appreciated.
    1) As a first step to custom maps we need a shape file, I need help on how to create a shape file for this.
    2) I have downloaded shape file for the testing purpose from this “http://www.diva-gis.org/gdata” site and uploaded to datazen server
    ii) drag the gradient heat map to designer
    iii) Click on map drop down option but uploaded map is not available for selection.

    Any help is highly appreciated.

    • hopefoley Says:

      If you need to create a shapefile there is a program called QGIS that may help you do that. It’s an open source tool. As for the issue with your map and Datazen server, I have heard of issues with maps uploaded to server. You may try to create your map just pointing to the file directly. Then when you are done, you can upload your completed dashboard to the server. It sounds like that bug isn’t resolved yet. Good luck!

      • Abdul Mukhtar Says:

        Thanks for the quick reply.
        I have created shape file and use the same by pointing to the file directly but to my dismal map is not showing.

        Step followed to create shape file.
        1) Created KML file using Google earth.
        2) Open KML file in QGIS and converted to shape file.
        3) use step 2 shape file in the dashboard

        But if i create any shape in QGIS directly and use the shape file in datazen it is displaying on the dashboard.

        I may be missing some attribute defining while creating KML file in google earth.

        Please correct me or guide me if i miss any step.

  7. evanvugt Says:

    Good catch on your part!

    Too bad Datazen uses the shapefile in an inappropriate way. This will frustrate users everywhere and was easily avoided, as the shapefile specs are documented very well. The Esri shapefile format has been around for almost 20 years. As a GIS specialist I quickly learned you have to keep the shp, shx and dbf together at all times, and you never edit the dbf outside your GIS.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s