Read a .csv file from a URL with an API call in QGIS 3?

Question

How do I read a .csv file from a URL with an API call, including key, in QGIS 3?

I've tried creating a .vrt file as outlined here

I've tried the solutions presented here

If I'm doing everything correctly, then the differences between these examples and my case are that my URL is https and that my URL has an API call following the .csv extension.

eg: https://developer.nrel.gov/api/alt-fuel-stations/v1.csv?fuel_type=HY&state=CA&api_key=G3tUr0wNk33&format=csv

The .vrt file I assembled:

<OGRVRTDataSource>
<OGRVRTLayer name="test">
 <SrcDataSource relativeToVRT="0">/vsicurl/https://developer.nrel.gov/api/alt-fuel-stations/v1.csv?fuel_type=HY&state=CA&api_key=G3tUr0wNk33&format=csv</SrcDataSource>
 <GeometryType>wkbPoint</GeometryType>
 <LayerSRS>WGS84</LayerSRS>
 <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>
</OGRVRTLayer></OGRVRTDataSource>

• nmtoken: The solution though in the questions referenced mention http and https, so that's probably not the issue.
• nmtoken: Can you edit the question to show the VRT you created to do this?
• oglj: @nmtoken I have edited the question to show the VRT file
• user30184: One thing that you must correct is to use XML escaped special characters in the VRT file. Thus this is at least more correct as data source <SrcDataSource relativeToVRT="0">/vsicurl/https://developer.nrel.gov/api/alt-fuel-stations/v1.csv?fuel_type=HY&amp;state=CA&amp;api_key=G3tUr0wNk33&amp;format=csv</SrcDataSource>. Unfortunately that did not make the vrt to work for me. Continue from this and use ogrinfo for testing ogrinfo apitest.vrt --debug on.

Answer 1

I'm accessing the same resource, but for CNG station locations, and ran into the same issue.

Using @user30184 's advice I saw in the CLI that the URL was returning a forbidden error. I went to download the CSV from the site again, and realized it worked but using a different API key in the query string. So it seems they rotate the API key, and thus you might want to sign up for your own API key if you haven't already.

Even with an API key that works and gets ogrinfo to process the file correctly, it doesn't load the CSV in QGIS for me. I looked at the layer I had loaded from the local copy, and realized that QGIS appends its own query string with the rules for how the CSV driver should parse the file:

file:///home/me/Downloads/alt_fuel_stations%20(May%2030%202024).csv?type=csv&maxFields=10000&detectTypes=yes&xField=Longitude&yField=Latitude&crs=EPSG:4326&spatialIndex=no&subsetIndex=no&watchFile=no

I imagine that's needed for proper parsing, so you have to include it as well, and hopefully the server will ignore the irrelevant query args... But in my testing, combining the queries didn't work. Which makes sense if the CSV driver is expecting a query string to parse, it probably hijacks it meaning the query is never sent to the server. Perhaps there's a way to specify the driver's rules in the VRT file instead...

But I found another solution to the original issue. That API doesn't have to export CSV. It can even export GeoJSON! Check it out:

https://developer.nrel.gov/docs/transportation/alt-fuel-stations-v1/all/

All you have to do is change the URL's file extension to .geojson - it correctly parsed. This is cool because you can enter this URL directly into the new Vector Layer box in the Data Source Manager, and no need to tell it how to parse it! No VRT file needed either.

https://opengislab.com/blog/2018/11/8/adding-and-viewing-geojson-in-qgis-and-arcgis

So that ended up being a better way to pull from the same data source; hope that helps