How to query all records (>1000) and export to csv?

Question

I posted in arcgis forum but never got any answer. Can anyone tell me how to solve this?

Assume you have a vehicle tracking records from Oct.1 to Dec. 31 with wind speed information. All data is saved in geodatabase (sde-sqlserver) and up in ArcGIS Server 10.1 as feature layer. A time slider show the locations of a car with wind speed.

When a user change a time-range (eg. Oct.2 - Oct.4), first query task (for count) calculate the number of features within the range. There are usually more than 1000 results even two days (eg. 1750) (I don't want to change this limit though).

I used another query task (executeforIds) to keep all records in hand but reduce the amount with modulus (1/10) which is still enough number for making a nice chart for an overall trend of wind speed. However, I also want to provide an option to download a whole dataset in csv (in this case 1750 rows)

Here, I used findtask to retrieve attribute dataset within the time-range.

try {
    //console.log(app.objIDs);
    var attribs;
    csvdata = "";

    var find = new esri.tasks.FindTask("http://xyz.com/arcgis/rest/services/Realtime/Car_Wind_WM/MapServer");
    var params = new esri.tasks.FindParameters();
    params.layerIds = [0];
    params.searchFields = ["OBJECTID"];

    for (var i = 0; i < app.objIDs.length; i++) {
        params.searchText = app.objIDs[i];
        find.execute(params, function (results) {
            attribs = results[0].feature.attributes;
            csvdata += attribs.Date_Central + ',' + attribs.Longitude + "," + attribs.Latitude + "," + attribs.windspeed + "\n";
            console.log(csvdata); //1
        }, function (error) {
            alert("Error");
        });
    }
} catch (error) {
    alert("Change the time range first");
}
console.log(csvdata); //2
setTimeout(function () {
    formatData(app.csvdata);
}, (3 * 1000));

csvdata was undefined at console.log2, although all csvdata (in console.log1) show data in console. The issue seems timing of find.execute complete, so I added setTimeout.

This seems work but when i increase the time-range, it won't obviously.

Is there anyway to keep all records (1000 - 200,000) in a specific time range and export to csv? Any ideas/help appreciated!

Thanks

Answer 1

1. You could certainly increase the service record limit to 10k or more if you are dealing with points and couple attributes -- particularly if you are not generating graphics.

2. You could make an asynchronous geoprocessing task and pick up the data after generated in an output folder on server.

3. If you throw if an order by clause and the time in your query. You might be able to read the time in the last record and get the next set of records greater than that time. Keep going until you reach the end of your time range.

Little helper for ArcGIS Server time that I wrote, http://www.spatialexception.org/posts/arcgis-server-and-javascript-dates

Answer 2

One of the options is to have the system return the records to you. It will give you the record numbers for all 1750 rows even though it will only return the data for 1000.

When you get the list of records (I am not sure if they are sorted) pull out the first 1000, then add the first and last OBJECTIDs to the whereclause

OBJECTID>=firstobjectid and  OBJECTID<=lastobjectid

Repeat as many times as necessary.

Answer 3

I was going to suggest using the QueryTask and setting returnIdsOnly=true.

Note that while there is a limit on the number of features included in the feature set response, there is no limit on the number of object IDs returned in the ID array response. Clients can exploit this to get all the query conforming object IDs by specifying returnIdsOnly=true and subsequently requesting feature sets for subsets of object IDs.

However I don't see support for this in the Javascript api's Query.

It is supported in the Silverlight API's Query.

If you're using native SQL server spatial datatypes, and not using versioning, you may consider just writing a Microsoft REST service using the template as a starting point.