Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am importing data from excel into ArcGIS 10. The data are columns of GPS data, associated date and time, and a unique point identifier. For some of the rows the GPS data is missing and so the Lat and Long columns are empty but the other columns retain dates and times etc. When I import this data into ArcGIS 10 a 0 is placed in these cells and so Arc plots the points as 0,0.

Previously I have just removed the empty rows from the imported data table to get around this problem. However, I now need to import the full data table including the empty rows. How can I do this without Arc entering the empty cells as 0?

share|improve this question
add comment

2 Answers 2

In a shapefile, numeric zeros are stored as text "0", while empty cells will be stored as empty strings "". However, ArcGIS interprets an empty string as a 0.

Because you cannot do anything about this, you need to use a work-around. A standard method, which is quite resistant to corruption from the GIS (or any other software), is to put an extremely negative value in empty cells. The value should be so extreme that if you mistakenly try to map it or use it in a statistical summary, it will skew the results so badly something will obviously be wrong. Within ArcGIS itself, you can always cause such values to be ignored by means of a definition query or an explicit selection operation.

(In Excel, just select an entire column or group of columns, from the top data row to the bottom data row, and use Edit|Replace to search for all empty cells and fill them with your NoData code. It may look ugly in the end, but this is a reliable way to indicate missing data. Even Excel sometimes treats empty cells as zeros: such unreliable behavior has caused many subtle errors and erroneous results.)

Typically, a NoData code for an attribute intended to be stored as a float is equal to or close to the most negative float the computer can handle: around -10^38 for single precision or -10^304 for double precision. For a decimal attribute, use the most negative possible value. For instance, if your lat,lon values need 10 digits (including sign and decimal point) and you want five decimal places of precision, the most negative value you can store is -999.99999 (which obviously is an invalid latitude or longitude). Using such conventions makes it easy to recognize and remember these codes solely from knowledge of the field type in the database.

Incidentally, even using zeros for missing (lat,lon) is not such a bad idea: those points will plot in the ocean west of Africa, which is rarely within anybody's region of interest! Thus, they clearly show up as problem data, which is consistent with the underlying philosophy that errors, when they occur, should be so obvious and prominent that they will be identified and corrected. The silent, subtle errors are the deadly ones...

share|improve this answer
add comment

If you're working with a shapefile the empty cells will always be filled with '0'. The only way to keep the cells 'empty' (in means of NULL) is to work with a geo- or filedatabase.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.