Using ArcGIS Desktop 10.5.1 I have a point featureclass in an Microsoft SQL Server 2012 Enterprise Geodatabase (ArcGIS Server 10.5) that holds MDS (mobility ride share scooter) data. The feature class has two fields we are interested in, [vehicle_id] and [route_timestamp]. The [vehicle_id] field contains a unique ID for each scooter and [the route_timestamp] contains the timestamp of the scooter point.

Each [vehicle_id] contains multiple timestamps (making a breadcrumb trail the scooter traveled)

I only want to know the scooters last location (not interested in any of the previous breadcrumbs) To only see the latest timestamp I figured I would use a TOP 1 SQL query and group them by [vehicle_id].

My definition query on the layer is:

route_timestamp IN ( 
   SELECT MAX ( route_timestamp ) 
   FROM ENG_GDB.ENGGISEDITOR.MDS_trips_breadcrumbs 
   GROUP BY [vehicle_id])

This query takes the records from around 20,000 records to only 318, but it is returning multiple timestamps for a single vehicle_id, when in fact it should only be returning the top 1.

I have tested a similar query on a different dataset and it works, so I am wondering what is going on here, and why I am getting the results I am getting.

enter image description here

With further testing I got this query to work and return the wanted results directly in SQL but not in a layer definition query.

SELECT MAX (route_timestamp),vehicle_id FROM ENG_GDB.ENGGISEDITOR.MDS_trips_breadcrumbs where vehicle_id in (SELECT distinct (vehicle_id) FROM ENG_GDB.ENGGISEDITOR.MDS_trips_breadcrumbs) group by vehicle_id

Still trying to figure out how to put a definition query directly on the layer.

  • 2
    Data is never "in an SDE". What RDBMS are you using? – Vince Jul 26 '19 at 13:59
  • That was a simplification, all server geodatabases are SDE. but anyways, the server is SQL. – ed.hank Jul 26 '19 at 14:01
  • 1
    Actually, I use PostgreSQL as a geodatabase all the time, without an enterprise geodatabase enabled. Please Edit the question to specify the exact release of Microsoft SQL Server. – Vince Jul 26 '19 at 14:03
  • 1
    I've been working a project with millions to hundreds of millions of features, comparing them back and forth, processing them, and mapping the results, using exclusively QueryLayers and ArcSDESQLExecute cursors, It's not the Query Layers which are abysmal. If you're not willing to use the right tool, it will be more difficult to get assistance. – Vince Jul 26 '19 at 14:14
  • 1
    Does case sensitivity matter? I see your column is named Route_timestamp. Are your vehicle IDs the same even though they look like it? Maybe different languages for some letters? – vagvaf Jul 26 '19 at 14:32

Your Answer

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

Browse other questions tagged or ask your own question.