{"id":2791,"date":"2021-02-24T16:30:46","date_gmt":"2021-02-24T03:30:46","guid":{"rendered":"http:\/\/blogs.lincoln.ac.nz\/gis\/?p=2791"},"modified":"2023-05-07T03:10:51","modified_gmt":"2023-05-07T03:10:51","slug":"those-unformatted-dragonflies-again","status":"publish","type":"post","link":"https:\/\/blogs.lincoln.ac.nz\/gis\/those-unformatted-dragonflies-again\/","title":{"rendered":"Those Unformatted Dragonflies Again…"},"content":{"rendered":"

Analysis and mapping is almost always preceded\u00a0by time spent getting data ready to be mapped.\u00a0 In this post we cover an instance of reformatting some raw spatial data so that they can be used for mapping.<\/em><\/p>\n

Over the years, there have been a few posts about dragonflies of the Pacific, from using a smartphone app<\/a> to collect data in the field, to acquiring elevation data<\/a>,\u00a0and\u00a0effective Pacific coordinate systems<\/a>.\u00a0 Those dragonflies aren’t going anywhere (we hope!) and still need to be catalogued, so my colleague, Milen, is still travelling the Pacific (though not in the past year), most recently in the island group of Wallis and Futuna<\/a>.\u00a0 Where are these islands?<\/p>\n

\"\"<\/a>
By TUBS – Own work\u00a0This W3C-unspecified vector image was created with Adobe Illustrator.\u00a0This file was uploaded with Commonist.\u00a0This vector image includes elements that have been taken or adapted from this file:\u00a0Polynesian triangle.svg (by\u00a0Gringer)., CC BY-SA 3.0, https:\/\/commons.wikimedia.org\/w\/index.php?curid=15127571<\/em><\/span><\/figcaption><\/figure>\n

Note how Wallis and Futuna are south of the equator and just on the other side of the international dateline from us – that will become more important later.<\/p>\n

Now Milen is well versed with using the app to collect his observations, but we needed some locations collected by someone else to be mapped.\u00a0 He was sent a Word document with the points that looked something like this (around 45 points in all):<\/p>\n

\"\"<\/a><\/p>\n

Here was my reaction:<\/p>\n

\"\"<\/a><\/p>\n

Why?\u00a0 Clearly, there is spatial information in each entry given by the latitude and longitude BUT it is in a form that is not easily imported into ArcGIS.\u00a0 This is a very frequent occurrence, I’m afraid, and one must be prepared to massage the data into a more useful form.<\/p>\n

A few things to note before we dive into this.\u00a0 The coordinates are essential for getting the points on the map, but they aren’t quite in the right form.\u00a0 For instance,\u00a0\"\"<\/a>\u00a0give me latitude and longitude, but the “S” (for South of the equator) and the “W” (for West of the Prime Meridian<\/a>) won’t make sense to Pro.\u00a0 What is preferred is either a positive or a negative value to indicate N\/S of the equator or E\/W of the prime meridian, as shown below:<\/p>\n

\"\"<\/a><\/p>\n

http:\/\/www.fekete.com\/san\/webhelp\/feketeharmony\/harmony_webhelp\/content\/html_files\/reference_material\/General_Concepts\/GIS_Theory.htm<\/a><\/span><\/em><\/p>\n

For mapping in our part of the world, latitudes need to be negative, so I’ll have to take this into account at some point.\u00a0 And being just the other side of the dateline, the longitudes will be negative as well.<\/p>\n

But the biggest problem by far is getting these data organised into something more closely resembling something mappable – GIS does not like Word docs.\u00a0 There are a number of different ways to do this, and more often than not I fall back on Excel (How would you do it?)\u00a0 I can’t open a Word file in Excel so as a first step, I saved the Word file as a straight text file and then opened it in Excel.<\/p>\n

\"\"<\/a><\/p>\n

This dialogue will allow me start parsing the text into more useful columns.\u00a0 The file is not well aligned with columns so I chose to use Delimited > Next:<\/p>\n

\"\"<\/a><\/p>\n

With some judicious use of delimeters (semicolons, commas and open parentheses) I’ve got a good first cut.\u00a0 I accepted the defaults on the next screen, hit Finish and here’s what I got:<\/p>\n

\"\"<\/a><\/p>\n

Not a bad start but I’ve still got some mucking around to do.\u00a0 I’m aiming to get all the latitudes and longitudes into their own column – there are 45 rows in this file so I’ll bite the bullet and do some manual rearranging, being careful not to lose any data:<\/p>\n

\"\"<\/a><\/p>\n

Notice that I’ve added column names – we’ll need those as field names when we add the file to Pro.\u00a0 I know I’m going to be creating some new lat\/long columns along the way so I add a “1” to these column names.\u00a0 Next I’ll remove the “S” from the latitudes and make the number negative using a formula with the LEFT <\/a>function.\u00a0 This function lets me extract a set number of characters starting from the left of the first one:<\/p>\n

\"\"<\/a>\u00a0C2 is the cell that has my South enabled latitude.\u00a0 Most of my coordinates have seven characters (including the “.”) multiplying this by -1 gives me the right result:<\/p>\n

\"\"<\/a><\/p>\n

That’s half the battle – now to my longitudes.\u00a0 The LEFT function works well here too:<\/p>\n

\"\"<\/a><\/p>\n

I’m mostly done though there was a bit of jiggery pokery to be done with a few coordinates that marked the beginnings and ends of transects and tidying up the comments, so I copied the relevant columns to a new sheet and saved the Excel file:<\/p>\n

\"\"<\/a><\/p>\n

(The ones highlighted in green are the transect points.)\u00a0 In Pro, I can now add that sheet to a map and use Display X Y Data<\/a> to get the points plotted:<\/p>\n

\"\"<\/a><\/p>\n

Job done!\u00a0 To wrap this up, here’s the figure that’s most likely to go in the paper for this study, coming to a reputable journal near you:<\/p>\n

\"\"<\/a><\/p>\n

As many of you GIS users out there will know, much of “doing GIS” means prepping your data before the glory work of analysis and mapping – and this was a good example of the work that often needs to go into the raw data that someone sends you and getting them on the map.\u00a0 I don’t feel as downtrodden as Van Gogh portrayed above after this…but I’ll certainly encourage the next expedition to use the bloomin’ app!<\/p>\n

Oh wait, what’s this I see in my inbox?\u00a0 It’s a note from Milen with a Word doc of additional dragonfly observations…<\/p>\n

C<\/p>\n","protected":false},"excerpt":{"rendered":"

Analysis and mapping is almost always preceded\u00a0by time spent getting data ready to be mapped.\u00a0 In this post we cover an instance of reformatting some raw spatial data so that they can be used for mapping. Over the years, there have been a few posts about dragonflies of the Pacific, from using a smartphone app […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2791","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/2791","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/comments?post=2791"}],"version-history":[{"count":1,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/2791\/revisions"}],"predecessor-version":[{"id":4091,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/2791\/revisions\/4091"}],"wp:attachment":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/media?parent=2791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/categories?post=2791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/tags?post=2791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}