2

I am working on getting American Community Survey(ACS) data into SDE table format. I have managed to import all the text files downloaded from ACS into excel and then imported to Access for further extracting.

Anyway, so all the tables have an ID field. When I try to preview the data in ArcCatalog it would not show the data in proper order shown below. The problem is when I register this tables as a geodatabase tables it is not using an ID field to create OID field but it assigns OID to the order that it was displayed in preview tab. I want to understand how it creates and assigns the OID to each row. I know I can right click on the field and sort by ascending order and it would display how I want it but it is a temporary solution. This does not solve the problem when I convert the access table to a SDE table.

enter image description here

Improve this question
3
  • What DBMS is your SDE running (Oracle, SQL Server, etc.)?
    – blah238
    Commented Feb 6, 2012 at 19:15
  • @ blah238, It is Oracle.
    – Raj
    Commented Feb 6, 2012 at 19:22
  • Then see this explanation: asktom.oracle.com/pls/apex/…
    – blah238
    Commented Feb 6, 2012 at 19:25

2 Answers 2

Reset to default
2

I have been told that it is not possible to permanently sort a table in most DBMSes because of the way they are optimized, they can return rows in an arbitrary order. I am not sure but I would guess this also applies to Access databases. You could create a view in your DBMS that applies an ORDER BY clause if this is that important to you.

Improve this answer
3
  • Thanks for your reply. The issue I have is when I register an access table it assigns ObjectID to each row randomly. Thus, when user displays the an end product SDE table it is not displayed in order I want.
    – Raj
    Commented Feb 6, 2012 at 19:12
  • See the last sentence in my answer for the only solution I am aware of to resolve that in a semi-permanent manner. Otherwise you have to rely on the sorting capabilities of whatever tool you are using to view the data.
    – blah238
    Commented Feb 6, 2012 at 19:14
  • I concur, sorting is not enforced by a DBMS. The GUI, in this case, ArcGIS will generally present records sorted according to the primary key by default. In this case ArcGIS is assigning the OID as the primary key. Short of that the records are generally present in the order they are received from the disk/server. In some file formats, dbf, etc. you can enforce sorting by rearranging the records on the disk, but this is a hack and prone to disaster. The only way to enforce sorting is as @blah238 says, use a view or query table.
    – tharen
    Commented Feb 7, 2012 at 0:18
1

You might want to check out the Sort tool.

This tool reorders, in ascending or descending order, records in a feature class or table based on field values. The reordered result is copied to a new dataset.

Improve this answer
3
  • Interesting that they mention that SDE datasets are supported; can anyone test that it actually works on Oracle or SQL Server?
    – blah238
    Commented Feb 7, 2012 at 1:12
  • Unfortunately, I do not have access to an SDE server. We'd just have to wait for the OP or someone else to test it.
    – R.K.
    Commented Feb 7, 2012 at 1:36
  • Thanks for your answer. I created a new file based geodatabase and used this tool to sort the data by the field that I wanted and it worked just find. The only downside is it can do one only one table at a time but it works! Thanks again!
    – Raj
    Commented Feb 7, 2012 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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