|
I recently acquired data describing the location of several hundred fire hydrants in my hometown of Homer, Alaska. When I requested the data, I was under the impression it would be on the order of a few dozen, maybe even 100, hydrants and that I would add them manually. However, it turns out the full list contains about 450 fire_hydrants which is way too many to add manually. The data is in three columns of an Excel spreadsheet, namely Ref#, latitude, longitude. What is the best way to add this data? |
|
Skipping over the nearly obligatory comments about compatible copyrights. . . Excel data is easy to export as csv (comma separated value) files. From there, with a bit of munging with a reasonably capable text editor you can convert it to a GPX file (a flavor of XML). Or if you have the tool set I have on my Mac (may be an equivalent one on Windows or Linux): I have a program called MacGPS Pro that I've used for decades for setting up my GPS for hiking, plotting my hikes on topo maps, etc. It's native format for storing waypoints happens to be tab separated values with a specific set of headers. It only takes me a couple of minute to covert a CSV file exported from Excel into the format needed by that program. Then it is an easy one, two, three process: 1) Load the waypoints into Mac GPS Pro. 2) Export waypoints as GPX file. 3) Load GPX file into JOSM. I happen to be debugging an Android app that collects some geospatial data while I am out and about. I like to get the raw data into a map presentation of some sort to give me a quick visual check to see if it makes sense. Getting the data into a mapping program like MacGPS Pro or JOSM uses the above with only a couple of additional preliminary steps: Pull Sqlite database file from phone, using Sqlite3 on my laptop query the data to convert I want to text, then follow the same as above with a text editor. From the time I plug my phone in to my laptop to the point I am looking at thousands of lat/lon points overlay on top of an older USGS topo map is maybe two or three minutes. If I want to see what they look like with current building outlines, etc., then a quick export as GPX from GpsPro and import to JOSM allows that. If you don't have the tool set needed to covert the file or the confidence to edit a CSV file into a GPX file, contact me off line and I can probably do the conversion in a couple of minutes and email you back the results. EDIT: I think current US government data is NAD83. You may want to verify the datum used by the provider, I think states, counties and cities are likely to follow the national standard. NAD83 and WGS84 are not too different (continental plate movement in the last 30 years hasn't been that much) but you may still need to do a conversion on the data. Also, an OSM file is also just a XML flavor and can be created with a text editor too. Thanks so much, stf, I will contact you separately about the data conversion. I had hoped perhaps GPSBabel could perform the conversion but a quick check suggests it cannot. I know I could create a GPX file in a text editor but that's a lot of work when other mapping chores in Alaska are more pressing. I checked several hydrant positions in person and believe the city's data to be more accurate than I can attain using my usual positioning methods which involve a GPS and camera. Dave
(07 Sep '17, 03:09)
AlaskaDave
FWIW, if I want to position something fairly accurately I use the waypoint averaging feature of my low end Garmin eTrex. If you average the waypoint at different times so the satellite constellation is different you can get a fairly decent position recorded. Takes about 30 seconds for my unit to do an averaged waypoint (or to update one). I use this when hiking to collect the position of signs and trail junctions. If it is a "out and back" type of hike my return is usually at least a couple hours after my hike in so the GPS satellite positions will be different. Trail signs are not usually visible in satellite imagery and often trail junctions are hidden in the trees. If junctions are clearly visible in the imagery then I can use the junction waypoints to verify image alignment. Anyway, a fire hydrant is a bit like a trail sign: Not usually visible on the satellite imagery but something you can set a GPS unit on top of before telling the unit to create or update an averaged waypoint. All that said, I think I can convert your Excel data. I assume you are the same AlaskaDave that I see on the mail lists. . . If so, I'll send you an email so you can send me the data.
(07 Sep '17, 04:00)
n76
Thanks escada! I did not know that. Just tried it and my last data from my app testing just came in perfectly. A far better workflow for me to use. Seems like that will be the easiest way for AlaskaDave to import his fire hydrants.
(07 Sep '17, 04:34)
n76
2
Another handy free conversion tool for csv to gpx is GPSVisualizer
(07 Sep '17, 04:36)
nevw
2
I tried the Opendata plugin but it failed complaining that it was "unable to find any valid coordinates" My CSV file is simple, three comma-separated columns, a ref column, latitude column and longitude column containing coordinates in decimal degrees. (???) But the GPS visualizer convert page worked like a charm. So that part of the job is now behind me. @stf, I have used the averaging feature of my Garmin before. Time consuming so I don't use it often. And thanks for your offer of conversion but gpsvisualizer did the trick. Many thanks to all contributors. Dave
(07 Sep '17, 06:08)
AlaskaDave
Interesting, the header on my cvs file that loaded perfectly was: "id","type","guard","lat","lon","radius" Maybe the lat/lon have to be abbreviated. Or maybe the quotes need to be there even if there is no particular csv reason for them in this case.
(07 Sep '17, 15:36)
n76
1
I considered adding quotes around the values too but gpsvisualizer saved me the trouble. Thanks again stf
(07 Sep '17, 23:31)
AlaskaDave
showing 5 of 8
show 3 more comments
|
|
One technical way to approach this is to write a script that generates an OSM file with 450 nodes, or if you cannot do that, perhaps save as .csv then use ogr2ogr (from the GDAL utilities suite) to convert into a shape file and then open that in JOSM. What you're talking about here straddles the boundary between manual mapping and a data import. If you get a file with hydrants from your local fire dept. and you use that to aid your mapping, perhaps even adding them to OSM one by one and visually verifying they're in the right place then, at a couple 100 nodes, we could still say it's not a wholesale import, it's just a mapper exploring all available sources to map his home town. If however you plan to upload the lot to OSM in one go then you're clearly importing a data set and you should be clearing that with the imports mailing list and following the import guidelines. Whichever way you do it, be sure to attribute your source correctly, and check that the license is actually suitable for using the data in OSM. Just because you got something under some FOI request doesn't necessarily mean you have permission to distribute it further. They need to explicitly say it's public domain or it's ok for use in OSM. But what prevents from saying, that someone just walked through the city and put all data manually using GPS?
(06 Sep '17, 11:38)
Sergey Karavay
The data in OSM MUST be based on freely available data (e.g. surveys, open source data), otherwise all of the users of OSM data would be in violation of the copyright of the original owner of the data (e.g. the Homer city council, for example). If the city council could prove that we copied their data in violation of their copyright they can sue OSM. One way to determine this is if they put intentional errors (e.g. a fictional fire hydrant) into their data (that's what mapping companies do) - if the same error then shows up on our map, it's fairly likely that we copied their data.
(06 Sep '17, 13:14)
Lightsider
2
There has been a case where exactly this happened: Person takes location of hydrants from copyrighted provider's map, provider complains, we say "but the mapper has simply recorded what he saw" and provider says "well, hydrants A, K, and M are under ground and don't have signage so how exactly did you 'see' them"? - Busted! Don't do it. Don't talk about how data "could have been" recorded when it hasn't been.
(06 Sep '17, 13:38)
Frederik Ramm ♦
Thaks for the replies - I will check with the city administration again. I did explain it fully to my contact person but I will get a document that will more firmly outline their permission. Frederick mentioned "straddling the boundary" re: imports and I knew that when I asked for help. But I can tell you right now that if I have to write a full proposal to do this work, I'm going to forget about it. I have too much other stuff to do in Alaska. I'll add the hydrants that I can visually check as time allows.
(06 Sep '17, 14:10)
AlaskaDave
Is it allowed to use the third-party list as the basis of a survey? I.e., to use that list as a checklist of places to physically visit? Or would that just be an expensive way of creating a dataset that's still a copyright violation? (E.g., because hydrants missing in the original data would also be missing in the OSM data)
(07 Sep '17, 07:40)
dsh4
@dsh: That should be fine. Missing data is not a problem for copyright - after all, our map is permanently incomplete. :)
(07 Sep '17, 09:09)
Lightsider
showing 5 of 6
show 1 more comments
|
