topic Re: When location data is missing in CSV file in Data Management Questions

When location data is missing in CSV file

EninnaLuli1 — Tue, 22 Dec 2015 05:31:21 GMT

I have a CSV file with points and their location (in longitude and latitude).

I notice that some longitude and latitude cells have -99 or -999. I am guessing they are null values?

I made it into a shapefile but the location is way off because ArcGIS is still reading these -99 and -999 values.

If I replace these values with <NULL> in the CSV file, would the location of the other values (which are correct) be showing in the right place on the map?

I don't want to delete the records that have these -99 and -999 values although I realize that when a point shapefile is made there is a point (thus, a location) for every record.

I think maybe I could just keep the original CSV file with the -99 and -999 values, make a copy of it, delete these records and make a shapefile out of it (though I have to double check with the person who is also working with me).

Is there another solution though?

Re: When location data is missing in CSV file

NeilAyres — Tue, 22 Dec 2015 06:19:03 GMT

I have a strong aversion to using csv (text), or excel etc as a primary source of spatial data.

It is obviously useless to have points created at some arbitrary "no data" location such as -99, -99. Unless you really need them like that. That will depend or workflow and your need for this data.

What I would do is create a personal geodatabase (Access mdb) in ArcCatalog. Then use the import tools in Access to import the csv to a db table. These tools have much more control on the type (double, text etc) of fields to be created.

Once you have the table you can create queries to either update your "no data" values to some standard, rather than having some at -99 and others at -999, or filter them out altogether.

Then create a new table with only the data you want to use.

Then you can use the Create Feature class from XY table tools in Catalog or ArcMap.

Don't forget to properly define the coordinate system of the XY columns.

Re: When location data is missing in CSV file

RebeccaStrauch__GISP — Tue, 22 Dec 2015 16:16:48 GMT

Depending on the version you are using, I would recommend a file gdb over a personal GDB...you will most likely have fewer issue down the line, especially if you start reaching the limits of Access.

I had written these instructions up a couple days ago for another thread...I'll include them here.

''''

ake a look at this help topic Make XY Event Layer—Help | ArcGIS for Desktop

the python syntax is

MakeXYEventLayer_management (table, in_x_field, in_y_field, out_layer, {spatial_reference}, {in_z_field})

And the Spatial_reference refers to the coordinates of you XY coordinates, not what you want them to be. You can also do this thru the toolbox. Make sure to choose the correct coordinate system for the input.

Also remember, that an Event table is not permanent, so you will want to save it as a shape of feature-class. If you want, you can use the projection tool from the Event table in geographic coordinates to a new projection,

Re the -99 values. I see a purpose for having records in a database with a set "no location" value (based on work I've done in the past). I suggest picking a coordinate that is within your extent, but a location that isn't needed (in the ocean, or lake....someplace obvious) then add a field with a flag each record as usable or not when doing calculations based on location. Reselect to eliminate these records from and of those type of calculations. Just a thought.

Many ways to do get to the same end game.

Re: When location data is missing in CSV file

NeilAyres — Tue, 22 Dec 2015 18:12:09 GMT

Indeed, there are always many ways to skin the cat (apologies to cat lovers...)

And, of course I do use fgdb's extensively. My only point was, when importing a text file, the access importer gives you much more control as to which field get defined as what.

And, as long as you have < 1million (?) records, space is not a problem.

Re: When location data is missing in CSV file

RebeccaStrauch__GISP — Tue, 22 Dec 2015 18:32:57 GMT

Thought you would be a FGDB guy...was surprised that you suggested the other.