3

I have a MS Access 2010 database with about 80 000 records in it. It is point data for animal locations. I would like to bring this into GIS and project the data into lambert conformal conic. I am experiencing some difficulties.

First I tried exporting the database in MS Access 2010 as a .csv before importing to ArcGIS 10.1, but there is apparently a known bug where Access will cut off your double fields to two decimal places. This doesn't work for me: I need those lat longs to be precise!

Then I tried exporting the data as an Excel .xlsx file type. Here all of my long integer fields from Access were converted to doubles (no good - for future processing I need those fields to be longs or integers in GIS).

Then I tried to directly import the x,y data using an OLE DB Connection. So far so good... the events file from the x,y data is imported as NAD 83 and the integer fields from Access are shown as being the long data type in Arc GIS.

Crossing my fingers I tried to then export this events file (NAD 83) as a shapefile (using the background data frame projection, lambert conformal conic, as to properly project the data for visualization). All of my fields that were previously classed as "long" in GIS have become doubles - and now they don't work in any of my post processing programs (namely, GME the geospatial modeling environment).

This is my first question - not sure if I can upload some sample data but I'm happy to provide it if I can figure out how to do it!

I have considered just bulldozing through them and making all new fields in GIS using the field calculator to change the field types, but I will be performing these kinds of operations regularly and since my data has about 50 fields I'd rather get this solved!

I am trying to export these data to a shapefile so that UDID is in long format. Table showing offending field: UDID (never has Null values)

  • I have encountered similar problems when bringing in animal location data, usually from excel, which has been responsible. one issue that I found often resulted in the field change is if there is a null or zero value in the first row, it can screw up the field designation. I have never had that happen in ArcGIS though. can you post a sample of your table with a few records? – TDavis Jan 15 '14 at 14:21
  • Thanks for your reply! There are no null or zero values (that I'm aware of). How can I post a sample? – Nova Jan 15 '14 at 14:26
  • please tell us the format desired in GIS. Note there are limitations to field types in the gis format. i.e. "If you're specifying numeric fields for a table in a file or personal geodatabase, you need only specify the data type. If you're specifying numeric fields for a database or ArcSDE geodatabase, you additionally specify the precision." full text @ resources.arcgis.com/en/help/main/10.2/index.html#//… – Brad Nesom Jan 15 '14 at 14:27
  • you can always edit your original question. you can use block quotes or upload a screen shot. gis.stackexchange.com/help – Brad Nesom Jan 15 '14 at 14:30
1

When adding table information from any source to arcmap, selecting the add xy option,
Arcmap programmatically converts the data (internally) to a spatial type and displays it on your map with the xy coordinates in the projection you choose.
When you select any data source and right click and choose export data there are no options to modify the output field type/s.
In order to accomplish this task so that you have EXACTLY the field type you want. Create an empty shapefile (utilizing steps 1-3 below).
Steps 1 and 2 keep you from having to manually add all your fields.
Import brings them in from your mdb.
This will provide a container with the correct field types in a shape file.
Then steps 4 and 5 will export the data with the wrong field types and you load that data into the container with the right field types.

Use these esri resources help pages to understand limitations and uses.
Data Type in the DBMS
ArcGIS Field Data Types

1.) create a new file geodatabase in arccatalog and on the field tab use the import feature.

new

import

2.) select the original file. (your accdb)
3.) modify the field type to exactly what you need.

modify

4.) export the data to a scratch shapefile. (or better yet file gdb.

5.) Load the data using simple loader to the file gdb you created with the specific fields.

loader

  • Which file do you output to dbms (and what is that)? Please excuse my lack of knowledge! – Nova Jan 15 '14 at 14:50
  • I modified my answer. use your access mdb file to import the field types. But make sure to modify them to exactly what you need. – Brad Nesom Jan 15 '14 at 15:05
  • Do you mean I should create a new field in ArcCatalog for each field in my .accdb database? I don't see an option in ArcCatalog to import an entire table to a new shapefile I just created. – Nova Jan 15 '14 at 16:19
  • there are many ways to accomplish this. If you understand the output limitations and the supported field types for your output it will make a difference of how you go about it. creating double fields in shape file you have no option to express the scale. but in sql you do. this is all outlined in the resouces help page. – Brad Nesom Jan 15 '14 at 18:13
  • 1
    Thanks for the further explanation - I don't mean to be obtuse here, but when I create a new shapefile, I have no option for "New Table". I clicked properties, and clicked on the fields tab, but "import" is greyed out and the title of that pane is "Shapefile Properties", not "New Table". Am I missing a step somewhere? I am not working with .mdbs, I have an .accdb file. Thanks for your input Brad - if I can't figure this out today I think I will just leave it. But I find it hard to believe this is such a pain! – Nova Jan 15 '14 at 18:37
0

If some of the other suggestions posted don't work, another option could be using your initial export to CSV idea.

However, before exporting, simply add an extra X and Y field and calculate the current XY values * whatever the number would be to remove the decimal (ex: 32.123456 * 1000000 = 32123456) you can even store that as a text field if it's easier for formatting (something like replace(field1,".","") depending on what system/language you're using for the calculate). Then export to CSV.

Then, import the CSV into ArcGIS (if you have an issue with field types changing or not being what you expect during a CSV import all you have to do is use a schema.ini file (see support for that at the very bottom of this page http://resources.arcgis.com/en/help/main/10.1/index.html#//005s00000010000000 and at this page http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

Then when you get it into ArcGIS as a table you can simply calculate the original XY fields from the new ones you stored either by dividing by the appropriate number (32123456 / 1000000 = 32.123456) or placing the decimal back at the appropriate character position if storing as a text value (ex: place "." after the 2nd character in 32123456.). Then you could use this to create a feature class/shapefile from a table with XY values.

Yes this involves probably a few more steps than you would ideally like, but I figured I'd throw it out there as an option if you weren't able to get the others to work.

0

I had problems setting fields to Long Integer in Access only to have them converted by ArcGIS to Double when exporting them to their final format using either ArcCatalog or ArcToolbox.

The solution I found was to load the feature set, or table, into ArcMap while it is still in Microsoft Access and then right-clicking on the feature set/table and choosing Data - Export Data. The resulting exports save the feature set/table with the Long field type I set in Access.

My guess is that ArcCatalog and ArcToolbox exports process the data in terms of what will best define it for use by ArcGIS, while ArcMap just accepts the data as it is presented to it, and it either works or it doesn't. The result being exports from ArcMap forward what it is told the data is, not what it thinks it should be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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