topic Re: Can a SQL expression be created to select the five ( 5 ) greatest and five ( 5 ) least values of a parameter? in Geoprocessing Questions

Can a SQL expression be created to select the five ( 5 ) greatest and five ( 5 ) least values of a parameter?

Michael_StanleyGallisdorfer — Thu, 23 Oct 2014 16:43:45 GMT

I wish to select only the five ( 5 ) greatest and five ( 5 ) least values of a parameter ( elevation ) for several sets of over 1000 points. Batch selecting requires that SQL expressions for the desired greatest and least unique values be identified manually, which is very time consuming.

Background:

Five ( 5 ) greatest and least elevation values should be selected from a very large set of elevation points. Setting-up a batch selection with SQL expressions conforming to unique point elevation values consumes much time, slowing data.

Goal:

A SQL expression or tool that allows points with the specified attributes to be selected and exported to a new shapefile. Conceptually, the process might be considered according to the following steps.

1. Sort point elevations, ascending or descending values

2. Identify the greatest and least elevation values

3. Select the greatest, and second-fifth greatest, and the least through fifth-least elevations

4. Export the selected points to a new point shapefile

Logically, the query could be expressed in the following way.

1. The greatest point elevation is greater than all other point elevation values

2. The second greatest point elevation value is less than the point identified in 1, above, but greater than all other values, and so on.

Re: Can a SQL expression be created to select the five ( 5 ) greatest and five ( 5 ) least values of a parameter?

TedKowal — Thu, 23 Oct 2014 17:30:33 GMT

The SQL expression to be used would be dependant upon your database.

A SQL Server way of doing would follow this type of logic:

Select Top 5 Min(ElevationFieldName) Elevation, ElevationID from TablewithElevation

Union All

Select Top 5 Max(ElevationFieldName) Elevation, ElevationID from TablewithElevation

(Note that the number of fields and basic data types must be the same in both select statements.)

Oracle does not have a Top function and would have to use embedded select statments with a where clause limiting the rows along with the Union. (A bit more complicated -- plenty of examples on google)

Access would be similar to SQL Sever

This is what comes immediately to mind!

Re: Can a SQL expression be created to select the five ( 5 ) greatest and five ( 5 ) least values of a parameter?

Michael_StanleyGallisdorfer — Thu, 23 Oct 2014 18:04:37 GMT

Thanks, Ted.

I am using ArcGIS, although I could probably figure out how to implement your solution using Access.

Re: Can a SQL expression be created to select the five ( 5 ) greatest and five ( 5 ) least values of a parameter?

TedKowal — Fri, 24 Oct 2014 14:25:02 GMT

That was my intent ... so give you a starting point.

One more tip... when using access to develop your queries I would do it in multiple phases so that any errors can be isolated quickly...

Problem set up... if there is no unique unchangeable ID in your GIS data I would create a field in the elevation data (GIS) that is unique and unchangeable. This field will be used to link the developed sql queries back to the GIS so they may be seen in ArcGIS.

MS Access:

Create a query selecting the Top 5 (individual query) from the GIS data -- save it in access
Create a query selecting the Bottom 5 (individual query) from the GIS data -- save it in access
Make sure the above query includes all the same fields including the unchangeable ID field.
Create a Union query combining #1 and #2 -- save it in access

To see the results in ArcGIS, Add the Union Query to the ArcGIS map as a datatable (If you did not know, you can add a named access query to the ArcGIS Map). Join the elevation gis data with the unique unchangeable ID with that same ID in the union query with matching ID's only. The result map should now only show the Top 5 and Bottom 5 results.

Hope this is helpful,

Ted