Koop Outputs Empty Array For Point Data

[jmburns]

Hello, I'm implementing a custom provider that queries data from our PostgreSQL databases. Our provider connects to 2 separate database instances. One with operational data and one with geospatial reference data. Our provider also accounts for custom row-level security.

I'm still in the proof-of-concept stage and am stuck on an issue. When my provider returns geojson to Koop from a table containing polygons Koop outputs the features fine. However, when I make calls to another table containing points (with altitude) Koop always outputs an empty feature array. I have verified that the geojson created by my provider is valid. I have also tried modifying the query to return the points in 2D, with the same result. I'm looking at the Koop code to try to determine why this might be happening, but if someone is able to provide guidance it would be much appreciated.

[jmburns]

One thing I've noticed is that if you provide a where parameter you get no features returned unless the field(s) used in the where clause are also included in the list of outFields. Maybe that makes sense, but I can think of a scenario where you don't necessarily want to return all fields used in the where.

I was able to finally get a response for the point data by changing my where clause to use objectid. I am trying to query by a timestamp field though and even when I include it in the list of outFields I still get an empty array of features back. I don't see anything obvious in the Koop code to explain this.

@rgwozdz Do you have any thoughts?

[rgwozdz]

@jmburns - thanks for reaching out. Can you send me the request URL that is giving you the empty array and the geojson returned by the provider? Feel free to truncate the geojson's feature array, but try to leave all root level properties intact.

[rgwozdz]

I am trying to query by a timestamp field though and even when I include it in the list of outFields I still get an empty array of features back. I don't see anything obvious in the Koop code to explain this.

My guess is that Koop doesn't understand this field is a Date type. Is your timestamp field a number or ISO-string? You might have to use "field definitions", otherwise Koop doesn't know how to handle. See notes about "fields" array in geojson.metadata.

[jmburns]

Thank you for your quick response @rgwozdz! Here is the local URL I am testing with. I have stripped the parameters down from the standard Esri Javascript API tiled request to focus on the issue: http://localhost:9080/services/rest/services/{schema}/{table}/FeatureServer/0/query?f=json&outFields=objectid,timestamp&where=timestamp > '2023-04-06 12:30:00'

Here is an example of the geojson returned by my provider: { type: 'FeatureCollection', features: [ { type: 'Feature', geometry: { type: 'Point', coordinates: [ -83.0496479872618, 42.3404466037335, 71.5 ] }, properties: { objectid: 4831511, timestamp: 2023-04-06T20:52:01.120Z } } { type: 'Feature', geometry: { type: 'Point', coordinates: [ -83.0496479872618, 42.3404408741555, 71.5 ] }, properties: { objectid: 4831512, timestamp: 2023-04-06T20:52:03.860Z } } { type: 'Feature', geometry: { type: 'Point', coordinates: [ -83.0496479872618, 42.3404466037335, 71.5 ] }, properties: { objectid: 4831513, timestamp: 2023-04-06T20:52:07.637Z } } ], metadata: { idField: 'objectid', limitExceeded: false, hasZ: true } }

Here is the response I get from Postman: { "objectIdFieldName": "objectid", "uniqueIdField": { "name": "objectid", "isSystemMaintained": true }, "globalIdFieldName": "", "hasZ": true, "hasM": false, "spatialReference": { "wkid": 4326, "latestWkid": 4326 }, "fields": [ { "name": "objectid", "type": "esriFieldTypeOID", "alias": "objectid", "sqlType": "sqlTypeInteger", "domain": null, "defaultValue": null }, { "name": "timestamp", "type": "esriFieldTypeDate", "alias": "timestamp", "sqlType": "sqlTypeOther", "domain": null, "defaultValue": null, "length": 36 } ], "features": [], "exceededTransferLimit": false, "geometryType": "esriGeometryPoint" }

The timestampe field is a PostgreSQL timestamp type. I will take a look at the link you sent.

[jmburns]

@rgwozdz I ran a test with specifying "field definitions" and still get an empty array of features from the FeatureServer endpoint. Here are the details:

URL: http://localhost:9080/services/rest/services/{schema}/{table}/FeatureServer/0/query?f=json&outFields=objectid,timestamp&where=timestamp > TIMESTAMP '2023-04-10 12:15'

GeoJSON returned by my provider (notice the fields array in the metadata): { type: 'FeatureCollection', features: [ { type: 'Feature', geometry: { type: 'Point', coordinates: [ -83.187532392, 42.407467173, 200 ] }, properties: { objectid: 4959364, timestamp: 2023-04-10T16:15:30.000Z } }, { type: 'Feature', geometry: { type: 'Point', coordinates: [ -83.187858767, 42.407338269, 200 ] }, properties: { objectid: 4959363, timestamp: 2023-04-10T16:15:30.000Z } } ], metadata: { idField: 'objectid', maxRecordCount: 2000, limitExceeded: false, hasZ: true, fields: [ { name: 'objectid', type: 'Integer', }, { name: 'timestamp', type: 'Date', } ] } }

Response from Koop: { "objectIdFieldName": "objectid", "uniqueIdField": { "name": "objectid", "isSystemMaintained": true }, "globalIdFieldName": "", "hasZ": true, "hasM": false, "spatialReference": { "wkid": 4326, "latestWkid": 4326 }, "fields": [ { "name": "objectid", "type": "esriFieldTypeOID", "alias": "objectid", "sqlType": "sqlTypeInteger", "domain": null, "defaultValue": null }, { "name": "timestamp", "type": "esriFieldTypeDate", "alias": "timestamp", "sqlType": "sqlTypeOther", "domain": null, "defaultValue": null, "length": 36 } ], "features": [], "exceededTransferLimit": false, "geometryType": "esriGeometryPoint" }

I am trying to prove out that Koop can handle this, but there seems to be an issue with querying by dates. I'd appreciate your additional thoughts on this. Thank you!

[rgwozdz]

@jmburns - thanks for the additional info; looks like some kind of issue with how Koop interprets the time fragment of the WHERE query value. Looking into it.

[rgwozdz]

Ok, I see what is happening here. Koop takes the time from the string value in the WHERE and converts it to a JavaScript Date object. The Date constructor converts it to local time (time where your koop server is running). So for example, your query > TIMESTAMP '2023-04-10 12:15' turns into a Date object of Mon Apr 10 2023 12:15:00 GMT-0700 (Pacific Daylight Time)" for me. But then it's converted to UTC time for the actual comparison, so it becomes 2023-04-10T19:15:00.000Z. This time occurs after all timestamps in your data, so you're not getting any features back.

If you do your WHERE like this instead, I think you will find you get data back: > TIMESTAMP '2023-04-10T12:15:00.000Z'.

So this is a "bug" in Koop, in that it should treat all the incoming timestamp values in the WHERE as UTC time, not local time. Should be a quick PR.

[jmburns]

Okay, thanks for the explaination. So Koop is additionally filtering the data by the where clause on top of the provider? Could there be a performance improvement if there was an option to skip that filter?

[jmburns]

I just tried > TIMESTAMP '2023-04-10T12:15:00.000Z' and still get an empty feature array.

[rgwozdz]

I just tried > TIMESTAMP '2023-04-10T12:15:00.000Z' and still get an empty feature array.

ok, that surprises me. At this point, I'm not able to reproduce and I've copied your data over and tested it here. I'll take another look later tonight/tomorrow.

But as for your other question, yeah, Koop has to be told not filter as it assumes the provider doesn't do anything on its own. But you can add a filtersApplied property to your metadata. See this section for an example on how to tell Koop the provider has already applied the where filter.

[jmburns]

Okay, I was able to get by this issue by using the filtersApplied property. Apologies for missing that before. Thank you for your help!

[rgwozdz]

Even though you worked around this bug by using filtersApplied, there was still the bug with timestamps getting converting to local timezones. Fixed in @koopjs/winnow@4.0.2