Hi @salvaleonrp,
You say that you "tried several things but none of [your] attempts has worked so far". Can you be a bit more specific maybe? Are you using the HTTPCaller in FME? What error response do you get?
I'm not familiar with Socrata, but there's a few things you need to be aware of when using these kind of API's:
- Does it need authentication (e.g. a token)? And if so, are you using it correctly?
- When a parameter takes a point as input type, always read the documentation carefully. Sometimes, you need to feed it a latitude/longitude, but it can also be the other way around. Is it a GeoJSON point, does it use a comma or a space as a delimiter, etc.
- You might be calling the wrong endpoint. If you click on the Export tab on the Medicare page you provided above, you will see the SODA API endpoint for that same page, which is https://data.medicare.gov/resource/rbry-mqwu.json.
Furthermore, I don't really understand what you want to achieve. Wouldn't it be easier to just use this function? That will simply give you all the medical centers in a radius of n meters (so 50 000 in your case) around a lat/lon coordinate. Seems to me that is exactly what you need, right? Have a look at this request example on Hurl.it. Click on the "Launch Request" button to see the response.
If you want, I can post an FME workspace how to do this with an HTTPCaller? With the Hurl example above though, it should be pretty straightforward. You can simply copy-paste the field values into the HTTPCaller. Be aware that you might need your own token though!
I'm sorry I missed mentioning where I failed.... I created an xval and Yval parameter that I'm passing to my SoQL that is the http call on my JSON reader. What I have on my JSON reader that works today is the filter for hospitals in the state of GA and AL. So to achieve what I wanted I added the function distance_in_meters and passing the XVAL and YVAL parameters. Just to check the distance of each feature read by JSON and from there use the distance as a filter or another where clause or transformer in run time. So that's where I failed.
As to your suggestion, using the circle that's probabaly what I need . Let me test this out and send feedback later... thanks!
I'm sorry I missed mentioning where I failed.... I created an xval and Yval parameter that I'm passing to my SoQL that is the http call on my JSON reader. What I have on my JSON reader that works today is the filter for hospitals in the state of GA and AL. So to achieve what I wanted I added the function distance_in_meters and passing the XVAL and YVAL parameters. Just to check the distance of each feature read by JSON and from there use the distance as a filter or another where clause or transformer in run time. So that's where I failed.
As to your suggestion, using the circle that's probabaly what I need . Let me test this out and send feedback later... thanks!
Ah, so you are using a JSONReader directly with a URL as your data source.
With the circle method, this can be your URL:
https://data.medicare.gov/resource/rbry-mqwu.json?$where=(state = 'GA' OR state = 'AL') AND within_circle(location, 33.741373, -84.389716, 50000)
Where 33.741373 is your YVAL and -84.389716 is your XVAL. Note that both the circle function and the state filter are part of the $where clause.
I have included an
example workspace which uses the circle method.
You can also add the result of your distance_in_meters method to the output. This can be done like so:
https://data.medicare.gov/resource/rbry-mqwu.json?$select=*, DISTANCE_IN_METERS(location, 'POINT(-84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')
Note that the XVAL and YVAL are flipped here and that the values are separated by a space now. Also, be sure to include a "*," in front of your distance_in_meters function as part of the $select clause. Otherwise, the API will only return a range for each hospital, but drop all the other data. The * ensures that all other columns are also returned.
You can even fetch all data within the circle AND have the distance added to the result!
Thanks for the answer and the workspace @sander_s. I prefer the one with distance but the two workspaces seem to be the same. I wrote the SoQL as you suggested below but I got a fatal error.
Do you have any suggestions?
https://data.medicare.gov/resource/rbry-mqwu.json?$select=*, DISTANCE_IN_METERS(location, 'POINT( -84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')
There is a space directly after "POINT(", but that is not the problem. If I paste this in as the JSONReader URL, it works for me.
Once again, can you be more specific about your error? Does it perhaps say the following?
The file ' 'POINT( -84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')' does not exist
A fatal error has occurred. Check the logfile above for details
If so, you should add double quotation marks before and after the URL, like this:
"https://data.medicare.gov/resource/rbry-mqwu.json?$select=*, DISTANCE_IN_METERS(location, 'POINT(-84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')"
It is similar to working with Windows paths in a command prompt: if it contains whitespace characters, you need to wrap the path in quotes, else it gets misinterpreted.
I've updated my example workspace so that it uses the distance and circle function together in 1 query. Please note that my query (in the JSONReader) contains Published Parameters already, which I believed is what you need when you wish to run it in FME Server. You can simply fill in the lat/lon and maximum search range for that coordinate in the Published Parameters section and hit run!
Thanks for the answer and the workspace @sander_s. I prefer the one with distance but the two workspaces seem to be the same. I wrote the SoQL as you suggested below but I got a fatal error.
Do you have any suggestions?
https://data.medicare.gov/resource/rbry-mqwu.json?$select=*, DISTANCE_IN_METERS(location, 'POINT( -84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')
There is a space directly after "POINT(", but that is not the problem. If I paste this in as the JSONReader URL, it works for me.
Once again, can you be more specific about your error? Does it perhaps say the following?
The file ' 'POINT( -84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')' does not exist
A fatal error has occurred. Check the logfile above for details
If so, you should add double quotation marks before and after the URL, like this:
<strong>"</strong>https://data.medicare.gov/resource/rbry-mqwu.json?$select=*, DISTANCE_IN_METERS(location, 'POINT(-84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')<strong>"<br></strong>
It is similar to working with Windows paths in a command prompt: if it contains whitespace characters, you need to wrap the path in quotes, else it gets misinterpreted.
I've updated my
example workspace so that it uses the distance and circle function together in 1 query. Please note that my query (in the JSONReader) contains Published Parameters already, which I believed is what you need when you wish to run it in FME Server. You can simply fill in the lat/lon and maximum search range for that coordinate in the Published Parameters section and hit run!
There is a space directly after "POINT(", but that is not the problem. If I paste this in as the JSONReader URL, it works for me.
Once again, can you be more specific about your error? Does it perhaps say the following?
The file ' 'POINT( -84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')' does not exist
A fatal error has occurred. Check the logfile above for details
If so, you should add double quotation marks before and after the URL, like this:
"https://data.medicare.gov/resource/rbry-mqwu.json?$select=*, DISTANCE_IN_METERS(location, 'POINT(-84.389716 33.741373)') AS range&$where=(state = 'GA' OR state = 'AL')"
It is similar to working with Windows paths in a command prompt: if it contains whitespace characters, you need to wrap the path in quotes, else it gets misinterpreted.
I've updated my example workspace so that it uses the distance and circle function together in 1 query. Please note that my query (in the JSONReader) contains Published Parameters already, which I believed is what you need when you wish to run it in FME Server. You can simply fill in the lat/lon and maximum search range for that coordinate in the Published Parameters section and hit run!
My use case is back and I just wanted to change the answer and thank you @sander for your help. BTW, I think the Search Range seems to be more like Meters than Km.
