• Home
  • Forums
  • FME Form
  • Data
  • SoQL using medicare.gov to retrieve number of hospitals filtered by state within a 50 mile radius of a location parameters.
Question

SoQL using medicare.gov to retrieve number of hospitals filtered by state within a 50 mile radius of a location parameters.

salvaleonrp
Rank
Badge +10

My question is more on the SoQL than FME. I have a JSON reader with a filter on hospitals located in GA and AL.

In my workspace, the user enters a location parameter (Lat, long) which is then buffered by 50 miles. The 50mi buffer is then used as a clippee on the hospital points and this results to the number of hospitals in the two states that are within 50 mi of the user location entered during run time.

I wanted the reader to be more efficient by using the SoQL function  DISTANCE_IN_PARAMATERS on the filtered list. 

 

(Reference: https://dev.socrata.com/docs/functions/distance_in_meters.html)

I tried several things but none of my attempts has worked so far.

Once I get the results this will be published to FME Server where the workspace will be used in another app.

<a href="https://data.medicare.gov/resource/xubh-q36u?$where=%28%28state ='AL'%29 OR %28state = 'GA'%29%29">https://data.medicare.gov/resource/xubh-q36u?$where=((state ='AL') OR (state = 'GA'))</a>

7 replies

geosander
Badge

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:

  1. Does it need authentication (e.g. a token)? And if so, are you using it correctly?
  2. 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.
  3. 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!

salvaleonrp
Rank
Badge +10

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!

geosander
Badge

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!

 

salvaleonrp
Rank
Badge +10

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')
geosander
Badge

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!

 

screen-shot-2017-06-21-at-213044

geosander
Badge

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!

 

0684Q00000ArMDBQA3.png

salvaleonrp
Rank
Badge +10

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!

 

screen-shot-2017-06-21-at-213044

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.

 

Reply


Community Stats

28,670
Posts
109,365
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.