I have an Excel spreadsheet with about 200,000 data points (lat/long coordinates plus species name). Any recommendations for a tool that displays these points on a map, with the possibility to comfortably filter for a subset of these species?
Could be either an Excel add-in or an external program.
Thanks in advance!
Have you tried EarthPoint? It can convert excel files to kml that can be viewed in Google Earth. I donāt think it has filtering capabilities but you could add each species as its own folder. https://www.earthpoint.us/exceltokml.aspx
QGIS will definitely handle 200,000 points and allow you to filter. (Iāve used it with millions of points.) Itās free to download and use.
Iāve used ArcGIS Online to map 200,000-ish points, but Iām not sure what its maximum limit is. It handles filtering, too. (see my example a few posts down from here.) Youāll have to save your source data in a public shared location (ex. a publicly-shared Google Sheet, a csv saved on OneDrive made available to the public, a file saved in ArcGIS Online and shared with the public, etc.), and then you can reference it from ArcGIS Onlineās map editor. (Youāll need an ArcGIS account ā the Public account is free ā and youāll want to use the new map editor as opposed to the āclassicā editor.)
EDIT: And of course, I totally forgot that Excel itself does have a 3D Maps feature that allows you to map stuff. (It can map on a 2D map, too, even though itās called 3D maps.) Hereās an example of a timeseries animation I created using 3D maps: https://youtu.be/wB_kI4cDgSU. The Microsoft Support link I referenced above says 3D Maps can āplot more than a million rows of dataā, and I just tried with 50,000-ish rows, and it handled the task no sweat.
A point map with so many data will be hard to handle, and maybe also to read; anyway, my favorite tool is the Simplemapper site: https://www.simplemappr.net/
Thatās not very many points to handle - whatās your level of computer fluency? There are solutions with R, Python, leaflet, Tableau, Q, Grass etc that all might meet your needs. If you are comfortable writing or modifying code, I could help you set up a Binder or CoLabs notebook to visualize things and exert substantial control over the look and feel of the visualization. If you want less overall control, Tableau has a free trial and does a great job mapping things from Excel but offers some options for controlling look and feel. Online tools are going to struggle with that many points and I have no real recommendations there.
One important thing to think about is how you want the data visualized - overlaying the species name over a dot will look bad with so many points - if you have relatively few total species, coloring points by species will allow quick visual parsing. With many species, coloring higher taxonomic ranks similarly will prove a better result - for example, all species in one genus or family could be different shades of red. Best case scenario in that case would be to plot points and have species names appear on mouse hover.
Iāve had some good luck using Google MyMaps, not sure how many points it will accept. Here is a random one Iāve made of public lands in Ohio. https://www.google.com/maps/d/edit?mid=1rIpf1r6Jxz2YOrFpubLa3ie_lHkmMDdA&usp=sharing
G MyMaps is limited to 2000 points, if i remember correctly.
My experience doing this sort of thing has always been by importing this into ArcGIS versions 8-10 or so. I have some experience with QGIS, but havenāt done this particular task there. Basically, your x-coordinate goes in one column and your y-coordinate goes in another column at a bare minimum. extra columns for more data fields that might help you visualize the data more efficiently.
with thousands of points, Iām going to bet that youāll need to clean it up to ensure that everything is typed exactly the same (with case, typos, spaces, etc cleaned up).
I think QGIS would be your best bet. It takes some time to learn, but I think it probably gives you the best payoff in capability for that learning effort for general purpose geographic visualization and analysis.
Google Earth can import data from tables, and might work as well. I just tested it with a csv exported from Excel that includes Ā± 7000 points. It gave an āoh my thatās a lot of pointsā message, but otherwise seems to work fine. Point data is pretty simple, so thereās no reason 200,000 should be a particularly problematic number. But maybe it is, I donāt know. Google Earthās limitations become pretty severe if you want to do much more than put something on a map and look at it, so while itās easy to get started with Google Earth I wouldnāt recommend it if thereās much chance youāll want to move on to any other work with geographic data.
Google Earth chokes pretty quickly with larger datasets. Even if it doesnāt freeze, it often takes a very long time to load and to change display if youāre trying to view all of of them.
Thanks so far for the answers.
To specify the use case: there is a register of most of the planted trees in Vienna publicly available:
https://data.wien.gv.at/daten/geo?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:BAUMKATOGD&srsName=EPSG:4326&outputFormat=csv
On the city government website, clicking every single ātree dotā reveals the species and further details (stem diameter, age etc.) but it is not possible to use a filter. Iād like to have a map display where I can select certain species or genus
I tried Excel 3D Maps, but the points/squares are too large (donāt know if I can change the size somewhere). No experience with ArcGIS or R or any other tools that require programming, and as this would be just a ānice to haveā feature, and as I am very time restricted at the moment, I will not invest time into learning a new application right now. But everyone - feel free too play around with the file, if there would be a practicle outcome that I could use, this would be very much appreciated 
yes, this is possible. in the Layer Options, thereās a Size slider that will allow you to adjust the size of the markers. you can also change other marker properties, like color, opacity, etc.
i didnāt add a genus column to the data, but below is an example Vienna Street Trees map in ArcGIS Online (https://www.arcgis.com/apps/mapviewer/index.html?webmap=b1683a8638dc42499645a093dff708aa). to filter by a particular species, you could select Layers > Vienna Street Trees > Options > Show Propertiesā¦ and then on the properties bar on the right, use the Filter tool to filter by species, as shown in the screenshot below:
the source data is an abridged version of the government data, stored here: https://www.arcgis.com/sharing/rest/content/items/afd55b6a4c7745419e8b753914e61dc2/data.
note that iām just providing the map and data here as an example for now, and i may delete them at a future point in time. if you want to to reference this in the future, itās probably best for you to save the underlying data to your own shared location and create your own map. (itās only a 2-layer map. so it shouldnāt be much work to replicate.) itās worth noting that i tried pointing the map directly to the Vienna government link you provided, but for me, it responds too slowly or else the file is too large, and ArcGIS Online times out when trying to access that data. (thatās why i created my own abridged set of the data. it retains all the rows but eliminates a lot of columns just for space savings. on the map, you can click the points, and it will show only species and lat/long because those are the only fields that iāve retained in my data set.)
Relatedly, i recently downloaded all of my data to do this but could not get the lat lon to come with the query, it just didnāt appear in the CSV. Anyone else run into this? Also that the lat lon downloaded only displays the fake obscured location if the location is obscuredā¦ it is possible to get the 'real location of the obscured observations, but only for those and there is no field for all of the ārealā points. Maybe I am not using the correct query?
Once downloaded onto a CSV one can convert the lat lons to points on a map, as this was for a work project I was using ArcGIS but it should work fine on QGIS also. The projections get confusing, but i always manage to figure it out, i think itās necessary to use a āgeographicā projection?
That is beautiful! Thanks for your service 
Yeah, I wondered if Google Earth might not like a number that large. Nonetheless, there is no reason 200,000 should be a particularly problematic number for point data. Meaning if it is problematic, that indicates a deficiency in the software.
Are you using the export tool at https://www.inaturalist.org/observations/export? If so, so long as you have the latitude and longitude fields under āGeoā checked, that information should be in the csv. If an observationās location is obscured, the obscured latitude & longitude shown publicly will be in ālatitudeā and ālongitudeā, while the actual latitude and longitude will be in āprivate_latitudeā and āprivate_longitudeā.
Pretty much any data that you import into ArcGIS that doesnāt already have an assigned projected coordinate system with it first needs to be reprojected and given a geographic projection. After that you can fine tune it via the same tool, but using the projected coordinates option instead of the geographic projection option.
Never run into the lack of coordinates problem, but for a while there was a problem with common names being in random languages. I made a bug report about it and after a bit of work that appears to have been sorted out.
I did use that and for some reason it didnāt export properly. I may just need to try again. Edit: i looked at the more recent one and it looks like it worked. maybe it was a bug the first time.
I always want the real coordinates to display not the obscured ones, so i wonder if inat could automatically populate the real ones in the āprivate coordinatesā when the location is open. Otherwise itās a bit tricky to do in excel with a huge file.
For what itās worth, my preference would be that iNaturalist always give the accurate coordinates in the ālatitudeā and ālongitudeā fields, and leave those fields blank if accurate coordinates are not available to me. Mixing accurate and intentionally inaccurate data in the same field is bad data management. Better to put the fake coordinates somewhere else where I would have to specifically decide to include them.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
