topic Re: Export data out of SDE in consistent order each time in Data Management Questions

Export data out of SDE in consistent order each time

DanSereno — Fri, 27 Apr 2012 22:14:56 GMT

Hello All,

My problem is that I need a unique, maintained attribute field to perform SQL on later in my workflow in order to maintain database linkages in a computer aided dispatching system between rebuilds.

Is there a way to have the records, being exported from an SDE using Feature Class to Feature Class, populate the newly created feature class in the same order each time?
In other words, when exporting data, from the SDE, I would like FID 1 to be the same record each time I export the same feature class. Currently, each time I export data FID 1 ends up being a different record.

Thanks,
Dan Sereno
GIS DBA

Re: Export data out of SDE in consistent order each time

VinceAngelo — Sat, 28 Apr 2012 15:42:31 GMT

The only way to be certain of a consistent order is to apply an ORDER BY on the query.

It's probably not particularly safe to rely on the objectid for this purpose.

- V

Re: Export data out of SDE in consistent order each time

DanSereno — Mon, 30 Apr 2012 14:57:09 GMT

Thank you for the reply Vangelo.
However, unless I am doing something wrong, placing "order by OBJECTID" into the "Expression" portion of the Feature Class to Feature Class tool produces the message: Invalid SQL syntx[Microsoft SQL Native Client: Deferred prepare could not be completed.]

Thank you,
Dan Sereno
GIS DBA

Re: Export data out of SDE in consistent order each time

VinceAngelo — Mon, 30 Apr 2012 16:28:00 GMT

The order_by clause is a separate component in the ArcSDE SE_QUERY_INFO object used
to query the stream. I haven't looked to see if it's available from ArcObjects, but it's unlikely
to be available though the where clause in that tool. You might be able to use an ORDER BY
in a subquery, but there's still no guarantee that a query (especially a spatial query) would
honor the subquery order. It is very likely that the resulting query would be much slower.

Even if the export order is reliable, there's still no guarantee that the objectid insert order
will always be increasing (generally, it is, but it's not requred), so you could still have subtle
errors in alignment. I suggest you use a column other than the registered rowid column for
this purpose. You'll most probably need to code your own export tool to achieve this goal.

- V

Re: Export data out of SDE in consistent order each time

DanSereno — Mon, 30 Apr 2012 20:39:07 GMT

Vangelo,
I have no write permissions to the SDE from which I am looking to extract data.
So, unless there is some type of "order", on which any of the many tools that can extract a data set from an SDE operate, it seems I have no way of even writing my own tool to extract records in the same order each time from an SDE.

Thanks,
Dan Sereno
GIS DBA