Exporting Objects Geoposition into CSV / xlsx / whatever

[jakob.streit]

Good afternoon.

I'm a newbie to AutoCAD, only using it for the fire dept to get the gps positions from the fire hydrants in the area. 

I've got every hydrant as its own object in autocad, aligned it with the geoposition command but I have no Idea how to get the GPS Coordinates from the points. Using the Dataextract command I only get the original coordinates in meters, without any reference to the geo-coordinates.

Is there any way to to this? 

For reference I added a screenshot of some of the hydrant points, I can't really upload them here as they are protected as sensitive data in my country.

Thank you for your time

[Pointdump]

Hi Jakob,
There's no way to do what you want with Vanilla AutoCAD. You need Map 3D. If you have a current subscription to AutoCAD, then getting Map 3D is no extra charge.
More details please on what you are going to do with the coordinate list of points. Are they going into another program?
Dave

[jakob.streit]

I'll download the Map 3D in the evening (at my place its currently 5:12 pm) and try that, is there anything I should watch out for?

The Coordinate List of Points is going into another - sadly proprietary program to have them available on the tablets on our rigs

[ChicagoLooper]

Hi @jakob.streit 

<<...I've got every hydrant as its own object in autocad, aligned it with the geoposition command...>>>

Verify the geospatial accuracy before proceeding any further. I don't know what the 'geoposition command' is but if it's what I think it is, then be careful, very very careful. That command is tricky to use, and if not used correctly will fake you out and return inaccurate results. Unless you have the savvy to spot coordinates that appear to be right when they're not, you should verify geospatial accuracy sooner (like now) rather than later. And yes, you'll need the Map3D toolset to make your drawing accurate. 

If you, or someone else, determines your coordinates to be incorrect, you'd end up having wasting your time and your team's time.

BTW, I've never heard of fire hydrants, the kind used with fire hoses, that are considered sensitive data. Do you have TOP SECRET fires in your country?

[Pointdump]

Jakob,
From the mobile tablets to Map 3D, and then back out again, you'll probably want to use shapefile format instead of CSV. That way you can add data fields, like color of hydrant or flow capacity, etc.
Dave

[ChicagoLooper]

The workflow I'd use would be like this.

1. Using the actual drawing that's represented in your image in Post #1, assign the appropriate coordinate system (it will have units of meters). Then name and save that map (the drawing file) in your project folder, then close it. You'll perform a query and the query won't work if the drawing is still open. <<Assigning a coordinate system to a drawing is a basic function within the Map3D Toolset.>> 
2. Open a brand new, clean drawing and assign a Geographic Coordinate System e.g. LL84 (EPSG 4326). Next, attach the map you saved in step #1. By attach I mean drag-and-drop it onto the Map Explorer Tab of the Map Task Pane. <<The Map Task Pane is a Palette that's exclusive to the Map3D Toolset.>>
3. Run a QUERY. Location=All. (No others queries are necessary.) Perform Data Extraction to get the Lat/Longs which you may save in xlsx, txt, or csv format. 

Note: If you didn't assign an appropriate coordinate system in step #1 (or didn't assign one at all) then your output in step #3 won't be accurate.

[jakob.streit]

Will definitely check before, the hydrants themselves are not secret, but the in the plans included water lines are seen as critical infrastructure and therefore information about it shall not be published

[ChicagoLooper]

@jakob.streit 

Understood.

The water lines are not needed. If you can share a sample of around ten (10) points representing the fire hydrants, a step-by-step workflow, up to and including a spreadsheet, can be provided.

Simple AutoCAD points (or small circles centered on those points) in the same geolocation as the image in Post #1 will be sufficient. You want to upload the actual point locations as they are in your drawing so their locations can be confirmed. Without confirmation, the output of Lat/Longs tabulated in a spreadsheet will be meaningless.

[jakob.streit]

Thanks for the time you're investing into this, I've uploaded the map with the georeference and a few hydrants

[ChicagoLooper]

Hi @jakob.streit 

Mollersdorf, correct?

Your map is not accurate. The GEOLOCATION command is tricky to use, very tricky.

You should fix it..

Point 58856 located at Schulgasse and Wolfgang Amadeous Mozart has Lat/Long 48.027370, 16.300343 which is fine. Your Cad drawing is using the projection (coordinate system) named UTM84-33N which is also fine, it's the correct UTM Zone. Bravo!

The issue is HOW you establish the drawing's projection. It's wrong. How do I know this? Here's how I know.

Continuing with fire hydrant 58856, it should have

Example 1:

Lat 48.027370 degrees

Long 46.300343 degrees

and the Lat/Long should have UTM84 coordinates (zone 33 north) like this:

596947.56 meters E

5320160.26 meters N

Both the green Lat/Long in degrees AND the blue UTM84 in meters represent the same spot on Earth.

Your drawing has this:

Example 2:

Lat 48.027370 degrees

Long 46.300343 degrees (The green Lat/Longs are exactly the same in both Example 1 and Example 2.)

but your Cad drawing with UTM84 zone 33 have coordinates like this: 

-2373.1365 meters E

320984.7174 meters N

What does this mean? It means the your coordinates in your map will not transform from UTM=>>Lat/Long correctly. Neither will they transform correctly from Lat/Long=>>to UTM. Whether transforming forward or reverse, your drawing isn't accurate. 

Here's another way to explain it in US-Dollars (sorry I don't know Euros so I'm using US dollars).

Bart Simpson has two $50 bills.

Homer Simpson has five $20 bills.

Marge Simpson has one $100 bill.

Who is the richest? Who is the poorest?

Answer. They are ALL rich!! No one is poorer than the other. Why? Because all three have the same purchasing power. It doesn't matter how each individual acquires $100, that amount of money can purchase the same amount of goods. Whether two 50's or five 20's or one 100, the total value is exactly $100.

In YOUR map the Lat/Long degrees must equal the UTM meters but it doesn't. The green coordinates above must equal the blue. In your map, however, your Lat/Long coordinates equal the red. The easting and northing are off, way, waaay off. And that's not good.

<<Your map might be $100 in Lat/Longs but it's only $74 in UTM, so sorry, so very sorry, but you're $26 short.>>

Your map can be fixed. How easy it will be depends on the DATA you used to insert the fire hydrants in the first place. What did you use to insert the fire hydrants into AutoCAD? Were you provided with a spreadsheet? A PDF doc? An image such as another map? An AutoCAD dwg file? Can you explain/describe your source for the fire hydrant locations?

Are you running Map3D OR Civil3D? Or are you running vanilla AutoCAD? If you are using plain vanilla, you'll need to download and install the Map3D Toolset. Plain vanilla Cad doesn't have the horsepower to perform transformations in coordinate geometry.

[vladimir_michl]

You can place temporary GPS blocks in your geopositioned drawing and then extract the GPS coordinates with DATAEXTRACTION. See https://www.cadforum.cz/en/geo2gps-web-map-from-any-coordinate-of-an-autocad-dwg-tip12914

Vladimir Michl, www.arkance-systems.cz  -  www.cadforum.cz

[jakob.streit]

I've got the dwg file with the points representing the hydrants directly from the company commisioned from the state. So (at least I hope so) they would be correct. I added the georeference with more or less precise eyeballing on the satellite image, as they don't give out geo-coordinates for the maps.

I checked the other hydrants randomly if they are on the right place and they are (at least precisely enough for our fd. It's not a big city with round about 6km north / south and 3,5 east to west)

I was initially running AutoCad Vanilla, but due to the recommendations in this post I also installed Map 3D. I'm using both of it on the 30 day trial period as we only need the data from that (insert beep) file to get the positions once.

Thanks again for all your time trying to explain it to me, I am somewhat familiar with Inventor, but losing my mind with AutoCad

[ChicagoLooper]

@jakob.streit 

According to your opening post, you cannot get GPS (the coordinates in Lat/Long format) in units of degrees.  

Unfortunately, you won't be able to get those Lat/Longs from your current drawing. Never. Never ever. That drawing is not an accurate geospatial drawing so you'll need to create a brand new one!!

As already stated in Post #4, the geolocation command is tricky to use. It confuses many users, both experienced and inexperienced users.. As you've already mentioned in your first post, when using DataExtraction command, you can only get the individual fire hydrant coordinates in meters and not in Lat/Long Degrees. You'll need to create an accurate geospatial drawing first because your UTM84 coordinates are wrong.

Why are the UTM84 coordinates wrong? They're wrong because you have negative Eastings. Despite Bing Maps in the background, negative eastings won't put your fire hydrants in Mollersdorf. To get the Lat/Longs, you need to get the UTMs right right first. Do this:

1. Open a clean, brand new metric template. You can use an out-of-the-box template or your standard office template. Make sure your office template doesn't contain any linework.
2. Assign a coordinate system to this drawing. You may use UTM84-33N meters. (Although your original drawing uses UTM84-33N, you didn't ASSIGN it correctly. This is why you are starting a new one.) 
3. Turn on Bing Hybrid aerial imagery. Zoom-in on Bing imagery when it appears in modelspace. Insert at least three (3) fire hydrants using Bing Hybrid as a guide. The Easting coordinates will be in 590,000's and Northings will be in 532,000's (the coordinates are estimates). Both Easting (y) and Northing (n) will be positive numbers when using the UTM84 coordinate system.   

If you can get your UTMs right, then you're halfway to your goal. If you are unable or unwilling, then you won't be able to use DATAEXTRACTION to get your Lat/Longs.

Using the Map3D Toolset, you can obtain the Lat/Longs using a query. If you run the query correctly, then you can use DATAEXTRACTION command to pull out the Lat/Longs. AutoCAD can easily generate output of Lat/Longs that will be tabulated in either xlsx or csv format after you have run the query.  

Note: Depending on your fire hydrant information, this procedure, may or may not be fast, taking only a few minutes to complete (it all depends on your data and willingness to share). Can you upload the original fire hydrant information? If you share a video can be made showing you how this is done.