topic Re: Help understanding a query in Geodatabase Questions

Help understanding a query

MarieCline_Delgado — Thu, 23 Jan 2020 15:24:29 GMT

Looking for a little help understanding what the Where clause of this SQL query is doing. This is used to define a query layer in an ArcPro map. DBMS is postgresql

SELECT

a.id, a.valid_date, a.id, a.name, a.net, a.elevation, a.mph, a.local_time, a.shape
FROM

obs.gusts AS a
WHERE

::r:DateTime

I understand that the last line is a cast function, but I do not understand what the "r" represents and how it's filtering the data.

Thanks in advance

Re: Help understanding a query

JoshuaBixby — Thu, 23 Jan 2020 16:18:03 GMT

Most of the SQL statement is straightforward, but there are a couple of things about the WHERE clause that are odd to me. How are you extracting this query? Are you copying and pasting it or typing it from what you seen on a screen?

Re: Help understanding a query

MarieCline_Delgado — Thu, 23 Jan 2020 16:30:31 GMT

The query is a property of an existing Query Layer in a map within ArcPro. I copied/pasted from the properties into the message above.

Re: Help understanding a query

JoshuaBixby — Thu, 23 Jan 2020 19:17:43 GMT

I am stumped, then. You are correct that double colons are historical Postgres syntax for type casting, and type casting can be chained using the old syntax:

"1"::real::integer‍‍

Postgres isn't my primary DBMS, but 2 things are throwing me: 1) type casting requires an expression to the left of the double colon, which appears to not exist; and 2) single quotes are usually used for slicing arrays but that doesn't make sense given the syntax.

This may be some clever use of Postgres operators, but clever is the death of readability and maintainability.

Does the query layer give the same results if you remove the WHERE clause?

Re: Help understanding a query

MarieCline_Delgado — Thu, 23 Jan 2020 19:50:09 GMT

I share all of these thoughts and feelings with you!

I did try removing the WHERE clause and it DOES change the results.

I would expect that it is filtering a time field, but I haven't been able to find a pattern yet.

Re: Help understanding a query

MarieCline_Delgado — Thu, 23 Jan 2020 20:21:25 GMT

I finally found something.
Define parameters in a query layer—Query layers | ArcGIS Desktop

Looks like it's a range parameter.

Re: Help understanding a query

JoshuaBixby — Thu, 23 Jan 2020 23:00:12 GMT

Nice catch. I was so focused on the Postgres part that I forgot about the possibility of it being an ArcGIS issue.

Re: Help understanding a query

MichaelVolz — Fri, 24 Jan 2020 06:28:26 GMT

The range parameters are a powerful feature of a query layer as per this thread which goes to the same link as you:

https://community.esri.com/thread/205904-dynamic-date-value-in-filter-widget-using-arcgis-online

This might be of use to you if you want to use range parameters in a web application.

Re: Help understanding a query

MarieCline_Delgado — Fri, 24 Jan 2020 14:28:15 GMT

Same here! Thanks for helping me explore to understand.