1

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>
4
  • The solution though in the questions referenced mention http and https, so that's probably not the issue.
    – nmtoken
    Commented Apr 24, 2019 at 12:51
  • Can you edit the question to show the VRT you created to do this?
    – nmtoken
    Commented Apr 24, 2019 at 12:53
  • @nmtoken I have edited the question to show the VRT file
    – oglj
    Commented Apr 24, 2019 at 19:25
  • 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.
    – user30184
    Commented Apr 24, 2019 at 19:45

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.