Help Converting CSV file to KML

I need to create a large map of my stake with pins for each household. I see that I can download a csv file with coordinates for most members. I am not a super savvy computer guy, but I gather that if I can convert the file to a KML file I can import the members into mapping software. Is there a simple way to do this?

maps.lds.org already does this. Do you need the pins or do you hope to do more with it than just have the locations? If you just need the locations, why not print this from there? It'll save you a lot of time. You probably need to be a stake clerk or presidency member to have the option, though.

If the later, there have been extensive discussions on the forum on how to do this. Unless you have professional software, my experience is that it's going to be at least time consuming to do this with free software. You can use gis encoders to convert address information to kml data (if you select the right options). I have used http://www.gpsvisualizer.com/ with reasonable success before maps.lds.org came out to prepare for a boundary change. It was time consuming, and there is definitely a learning curve.

There are privacy issues to be aware of, and some of the options that used to be available were shut down due to privacy concerns. However, some searches in the forum will definitely point you to extensive discussions on this topic.

crislapi wrote:Do you need the pins or do you hope to do more with it than just have the locations?

I need to be able to overlay the boundaries of several school districts and cities and potential geocode locations for MLS.

crislapi wrote:If the later, there have been extensive discussions on the forum on how to do this. Unless you have professional software, my experience is that it's going to be at least time consuming to do this with free software. You can use gis encoders to convert address information to kml data (if you select the right options). I have used http://www.gpsvisualizer.com/ with reasonable success before maps.lds.org came out to prepare for a boundary change. It was time consuming, and there is definitely a learning curve.

I was hoping that since the church now allows you to download a csv file with each stake member's coordinates, there would be an easy way to to convert that data into a file useable in mapping software. I will try your ideas. Thanks.

What I've done in the past used this:

1. Download the boundary KML files available in CDOL and import them into Google Earth
2. Create KML files from member addresses and import them into Google Earth (I used http://batchgeo.com/)
3. Find School District maps and import them as layers in Google Earth
4. Use the tools in Google Earth to create new boundary proposals to be presented to the Stake Presidency.

The great thing about doing this in Google Earth is that the layering and transparency technology allows you to view or not view different components... if you want to see the High School boundaries, you can adjust the transparency so that it shows up... when you are finished, adjust the transparency (or turn off the layer). This has been highly successful for us.

That is exactly what I wanted to do, but I have been struggling with creating the KML file for the member locations. I will try your link.

JohnShaw wrote:What I've done in the past used this:

2. Create KML files from member addresses and import them into Google Earth (I used http://batchgeo.com/)

Just to be clear, do you use the member addresses to create the KML file, or do you use the coordinates downloaded from the lds.org maps tool?

JohnShaw wrote: 2. Create KML files from member addresses and import them into Google Earth (I used http://batchgeo.com/)

I've done this also. But I used an MLS export to do this. To do that export requires MLS administrator rights. I do not see how this can be done using the on-line directory.

jdlessley wrote:I've done this also. But I used an MLS export to do this. To do that export requires MLS administrator rights. I do not see how this can be done using the on-line directory.

You don't use the directory -- you use LDS Maps (lds.org/maps), look at "My Ward & Stake", then click the Download link (the green down-arrow) at the top right of the list of households.

aebrown wrote:You don't use the directory -- you use LDS Maps (lds.org/maps), look at "My Ward & Stake", then click the Download link (the green down-arrow) at the top right of the list of households.

Good to know. I didn't see that because I was looking for a menu item.

you said: Download the boundary KML files available in CDOL and import them into Google Earth


Where do I find the boundary KLM boundaries.

you also said to download school boundaries and import them to google maps. All I've found are pdf files for school boundaries, is there a way to import pdf into google?

Thank you

Wayne Lewis

You said: "Download the boundary KML files available in CDOL and import them into Google Earth"

Where do we find these "KML" files? (ie: what/where is "CDOL")

You also said to import school boundaries into google maps. I've only found our school boundaries as pdf files. Is it possible to import pdf files into google maps?

http://cdol.lds.org - it's the church directory, if you don't have access, you'll need a member of the Bishopric or clerks/execsec to download it for you.

You can import the pdf's as layers in google maps, yes, it's been a while, you might need to convert it to an image, but I don't think so.... add it as a layer and then you can use the transparency slide bars to make it visible when needed, and invisible when not needed.

I've been working on this all afternoon and I cannot find a way to import the kmz boundary files into google earth. Nor can I figure out how to move the google map created by BatchGeo from google maps to google earth. You indicate I should use the tools available in google earth but I can't find anything that implys 'overlay' or 'import'. I would be grateful for any further advice.

None of the files are imported but rather opened. Go to File > "Open...". By using the places and layers panes to select what data you would like to display from the opened files you can leave the opened files as is or combine them by saving what you have opened and displayed. Google Earth help will provide some understanding.

Thank you, that did it. Now, two last questions...

How do I overlay or import pdf files? I've figured out how to do so with tif, gif, jpeg, etc. type images but nothing for pdf and when I do, can it be moved around and resized so boundary sizes match?

Second question, how do I open the map batchgeo created in google earth? It is online and not saved to my computer. Do I 'import' it using the url?

I so appreciate your patience with me. Thank you. You've been a great help.

you should be able to download the batchgeo KML file to your desktop so you can use the same method as the cdol files - see below, if you've saved the map, scroll all the way to the bottom
As for the pdf file, if you can't find a way to do it specifically, you might just need to convert it to an image - you might need to capture it in a screen capture, or if Adobe had the ability to open the pdf and save it as gif or jpeg, unfortunately, I'm not at a computer that has either adobe or google earth.

sumrhome1,

If you want precise school district boundaries as KML files, you might need to do some file conversion. Although KML/KMZ is becoming a popular format for map display, the most common format for disseminating map-layer files is still the shapefile (an industry standard that includes several files typically grouped into a single zip archive.) Shapefiles can be imported by almost any Geographic Information System (GIS), which Google Earth is not.

Shapefiles for school districts can be downloaded here from theU.S. Census website.

There a lots of ways to convert shapefiles to KML. You might try this free utility, Shp2kml. (I have never tried it myself. I usually just use my GIS application for such conversions.)

As for the membership layer, I am not sure what you gain by uploading your unit's private data to the BatchGeo website, whose geocoding is not even very accurate. You might be almost as well off by downloading the geocoded files from maps.lds.org as a CSV for the lat/lon values, as aebrown suggested above. Then you could use one of several common utilities to convert those files to KML format. (There are several free utilities for that, often named CSV2KML. Just Google that search term.) Of course, the geocoded membership files downloaded from maps.lds.org will only be fully accurate and complete if your ward clerks have done the intensive manual work to make them so. But with the exception of some glaring problems, such as members who move within their ward and don't get their locations updated, even the rough geocoding the clerks start with on the Church site may be almost as good as that at BatchGeo.com, and much more private.

It is not clear to me what your purpose is. After you get all these KML files into Google Earth, you can eyeball the overlays together. But Google Earth will not do any automated boundary analysis for you. It is basically just a visualizing tool.

Actually, I now recall that Google Earth is supposed to have school districts built in. You have to enable them to see the borders and labels because by default they are toggled off.

Within Google Earth's pane on the left of the screen called Layers, browse the hierarchy to More --> US Government --> US School Districts and enable the layer(s) you want. Your choice ares Unified School Districts, Secondary School Districts and Elementary School Districts. In my state, only the Unified School Districts layer seems to be populated.

With your help, we now have the maps we need to begin our analysis.

You mention "automated boundary analysis" in a previous post. Is there a software package (other than that offered by the Church which requires the Clerk to manually enter codes to families) that provides such an analysis?

Thank you again.

sumrhome1 wrote: You mention "automated boundary analysis" in a previous post. Is there a software package (other than that offered by the Church which requires the Clerk to manually enter codes to families) that provides such an analysis?

Sure there is. There is an entire family of software known as Geographic Information Systems -- GIS for short. This has been discussed on this forum several times over the years, for example in this long thread and this one. (Unfortunately, you can't just search for "GIS" here because the forum does not index three-character words.) GIS is essentially the marriage of mapping technology with a generic database application.

I do not mean that this is easy. The principal barriers to the use of GIS by local units are 1) cost and 2) learning curve. GIS is historically expensive and complex, but is becoming less so. (At least the free options such as QuantumGIS are improving). But if one is fortunate to possess such software and the related skills, automating boundary analysis or similar tasks is eminently feasible.

With the advances the Church has made in online mapping, perhaps someday there will be tailored GIS functionality built into the tools rolled out to local units for such tasks as boundary analysis. (There is plenty of GIS technology being used at headquarters.) But for now, you would mostly be on your own.

With the CSV exports from MLS, and some solution to the address-geocoding problem, it is quite possible to do automated analysis within a GIS application, benefiting from much greater flexibility than MLS offers. Even within our ward, we do a lot. (For example, we routinely gerrymander the ward into multiple fast-offering districts, balanced according to the demographics specified by the bishopric. And although unit boundaries are a stake responsibility, the task is essentially the same. Our bishop at least can anticipate the full demographic impact of various what-if scenarios assuming the stake were to redraw ward boundaries at X or Y.)

Can you recommend any specific software titles (in case our Stake President elects to purchase for the Stake for future - and this - boundary realignment)? I've searched but nothing seems to come up.

But for now, what we have will get us started with the analysis phase. When we provide the Clerk with options, he will have to enter codes in MLS for the 'new unit' membership for final analysis.

sumrhome1,

The GEOCode part is particularly interesting when you want to compare multiple scenarios, If GEO Codes are not already arranged nicely already by stakes and wards, which we find very difficult in our stake, I would make some suggestions... add to your member spreadsheet the default geo codes (in my case, I went through each unit in consideration and labled them as their own ward using abbreviations. That was my default position for everyone. Then as we created 'options' by using Google Earth to see the member groupings and how they could be divided, we create lists of people... for instance I would then create another GEOCODE for those lists... and a new column in the spreadsheet for each new option - base, option 1, option 2... and created meaningful names for geocodes for each option for example Ward1toWard2, or Ward1toWard3 Ward2toWard3 etc....Ward2toWard1 - once you have pre-defined each boundary option, then created your geocodes you must go in and change the geocodes for the members... change from default position to the geocode under the first option - then go into the software and get your data - you have to be careful with the software and remember to create one option, then take a snapshot of all the data, and then do a second option and take a snapshot of all the data because after you put in the 2nd option it typically messes up the 1st option because some of the same people are part of both options. This is the best way to really see how MP, Primary, RS, Youth will be divided up, etc...

I hope this is making sense, when you start using the boundary re-alignment tool you'll understand, and again, this all hinges on whether you already have good geocode data or not. The only good geocode data our stake had was my former ward where we put that data in when I was a head clerk, and in fact, was used successfully to change a recent boundary.

sumrhome1 wrote:Can you recommend any specific software titles (in case our Stake President elects to purchase for the Stake for future - and this - boundary realignment)? I've searched but nothing seems to come up.

Well, I happen to use a commercial product called Manifold, including its Business Tools optional addin that has a nice interactive districting tool. Including that option, it cost me about $400 several years ago. One qualm I have about recommending its purchase today is that the small Manifold company has not had a major new release for a couple of years, although the current version is well supported. See main Manifold site here, online user manual here, and support forum here.

I considered that $400 a bargain at the time, considering that the market leader GIS system, ArcGIS from ESRI, is many times as expensive -- far beyond what any stake budget could bear. Although ESRI products are the "industry leader," I have never worked in an organization that could afford the licenses. My workplace uses MapInfo, which is not as expensive but still pricy. I consider MapInfo inferior to Manifold for most of the desktop tasks I perform, so I have desktop license for Manifold at work and own a personal license at home just because I'm a map geek.

If I were starting out today, I would definitely explore several free and open-source alternatives, notably Quantum GIS, gvSIG, and MapWindow GIS. At least the price is right.

Learning curve is probably the biggest barrier. It does require developing some specialized skills. So one consideration is whether the stake has the talent that can be called or assigned, and keeping that calling stable.

sumrhome1 wrote:But for now, what we have will get us started with the analysis phase. When we provide the Clerk with options, he will have to enter codes in MLS for the 'new unit' membership for final analysis.

The method you are using is a pretty decent middle ground. It's just that MLS requires a lot of manual work. We did something similar in our unit. Our stake, which as far as I know does not have GIS software, determined that it wanted ward clerks to code all households by a certain scheme for the Stake Geo Code field in MLS. What I did for our ward clerk, within Manifold, was twofold: First I build a layer matching the stake's specification. With that resource, I exported a household spreadsheet with all codes added for each record. Then became a matter of copy-paste for our clerk using MLS. For subsequent updates, such as new move-ins, I built him a KML overlay file of these Stake Geo Code areas. Then, one address at a time, he can paste the new address into Google Earth with the overlay loaded, then click next to the mapped marker to pop up the requisite code in Google Earth for copy-pasting back into MLS. It would have been hard to create this overlay outside of a GIS application, but using the manual copy-paste method only requires Google Earth for the clerk's machine.

Just as a footnote to my comment above about BatchGeo.com, after testing a few hundred random addresses I must say that this geocoding site is much more accurate than I expected for my area, and better than it used to be. (I was familiar with BatchGeo's performance when it used to employ Yahoo's geocoding API, but it now uses Google's) At least in my city, almost all well-formatted addresses seem to be geocoded with rooftop accuracy, particularly those for single-family homes.

Like just about any geocoding process, there are some errors, though. Maddeningly, such errors are unreported by BatchGeo. I suspect that these are cases where the Google API is guessing and returning something fuzzier than a true address-based lat/lon, and that status is reported under the hood to BatchGeo, but the accuracy status is kept from the BatchGeo.com user. So the service still must be used with a grain of salt.

Accuracy aside, however, for reasons of privacy I still would choose not to upload membership name-address data to BatchGeo.com.

In the long run, capturing accurate lat/lon coordinates on the Church's maps.lds.org site seems the better alternative to satisfy privacy requirements. In the short run, I confess, I still do not use this data for serious tasks because our ward has not invested the clerk time to manually verify (and reverify as needed) all our addresses online. Surely this would have to change if performing that now-optional task were mandated by our stake. So if stakes do want to use this securely private data for boundary work or other purposes, they would be well advised to instruct their ward clerks to undertake and maintain the work. Stake clerks lack the privilege to do it themselves. Meanwhile, the gross processing errors that remain when addresses change still need to be fixed upstream by CHQ.

RossEvans,

I agree with your assessment, when I used batchgeo it was prior to the downloads being available in the maps tools, or prior to my knowledge that they contained them --> so I endorse the method of downloading the maps.lds.org and running the csv through a conversion utility to KML

JohnShaw wrote: I agree with your assessment, when I used batchgeo it was prior to the downloads being available in the maps tools, or prior to my knowledge that they contained them --> so I endorse the method of downloading the maps.lds.org and running the csv through a conversion utility to KML

The geocoded downloads from maps.lds.org have been there for some time, but the content has varied. Notably, the confidential fields available to leaders used to include the MRN; then that key data was taken out; now it is back again. The inclusion of that unique key is very important for doing any local database work to slice and dice the demographic analysis because the geocoded download can be linked to the MLS export tables. (BTW, that has enabled me to compare the Church's geocoding with my own locally generated "gold standard," so I can generate an objective measure of accuracy within my GIS application.)

I have noticed that the quality of the automatic geocoding done for maps.lds.org has improved over time, although the complete process still assumes manual verification by ward clerks. The most egregious problem remaining is not in the geocoding itself, but in the logic in the backend workflow determining when addresses are geocoded (or not). When an address changes in the household record, even within the unit, that should trigger a reset, with fresh geocoding and fresh verification. Right now, when a household moves, its new address will be displayed but the location will still be plotted at the old coordinates. Even worse, if the old location had been marked as "Verified," that bogus location will still show as "Verified" even though it may be miles away from the new address.