Traffic Volume Counts API

Hi,

I tried two examples in the Traffic Volume Counts API, but neither of them worked.
SELECT FROM road_traffic_counts_station_reference ORDER BY station_id LIMIT 50
SELECT FROM road_traffic_counts_hourly_permanent LIMIT 10

Request URL

https://api.transport.nsw.gov.au/v1/roads/spatial?format=geojson&q=SELECT%20FROM%20road_traffic_counts_station_reference%20ORDER%20BY%20station_id%20LIMIT%2050

Response Body

{
  "ErrorDetails": {
    "TransactionId": "000001747c7415a0-28295b87",
    "ErrorDateTime": "2021-02-01T20:15:34.449+11:00",
    "Message": "Error in the request Data",
    "RequestedUrl": "/v1/roads/spatial",
    "RequestedMethod": "GET"
  }
}

Could you let me know how to fix?

Regards,

Hi hyeuk,

please use the following.
SELECT * FROM road_traffic_counts_station_reference ORDER BY station_id LIMIT 50
SELECT * FROM road_traffic_counts_hourly_permanent LIMIT 10

Request URL

https://api.transport.nsw.gov.au/v1/roads/spatial?format=geojson&q=SELECT%20*%20FROM%20road_traffic_counts_station_reference%20ORDER%20BY%20station_id%20LIMIT%2050

Hi,

Thank you so much. Do you know how to correct errors in the examples in the manual (https://opendata.transport.nsw.gov.au/dataset/nsw-roads-traffic-volume-counts-api/resource/13d061b1-1606-49b5-b182-d36ce0801f14)?

Regards,

1 Like

Hi,

May I know which errors in the examples do you wish to correct?

Hi,

Basically what I did was to copy the query text from the manual and to paste into q value in the API web page.

Example 1:

I made the query simpler, but it did not work.

SELECT * FROM road_traffic_counts_hourly_permanent LEFT JOIN road_traffic_counts_station_reference ON road_traffic_counts_station_reference.station_key = road_traffic_counts_hourly_permanent.station_key WHERE station_id='SHB' AND year = '2016' AND public_holiday is true AND day_of_week BETWEEN 1 AND 5 AND classification_seq = 2 and month in (1,2,3) AND cardinal_direction_seq = 5 ORDER BY date

Example 2:

It ran but the result is empty.

SELECT * FROM road_traffic_counts_station_reference WHERE ST_DWithin(the_geom, ST_MakePoint(150.999919,-33.816228)::geography,10000)

Example 3.

I made the query simpler, but it did not work.

SELECT * FROM road_traffic_counts_station_reference REF JOIN road_traffic_counts_yearly_summary SUMM ON REF.STATION_KEY = SUMM.STATION_KEY WHERE SUBURB = 'Parramatta' AND PERIOD = 'PM PEAK' AND TRAFFIC_DIRECTION_SEQ IN ('1','2')

Example 4

I made the query simpler, but it did not work.

SELECT * FROM (SELECT PERM.DATE, REF.STATION_ID, PERM.TRAFFIC_DIRECTION_SEQ,(HOUR_07%2bHOUR_08) AS AM_VOL
FROM road_traffic_counts_station_reference REF JOIN road_traffic_counts_hourly_permanent PERM ON PERM.STATION_KEY = REF.STATION_KEY
WHERE public_holiday is false
AND SCHOOL_HOLIDAY = '0'
AND DAY_OF_WEEK BETWEEN 1 AND 5 AND YEAR = '2016'
AND MONTH = 5) AMV
GROUP BY STATION_ID,TRAFFIC_DIRECTION_SEQ
ORDER BY STATION_ID, TRAFFIC_DIRECTION_SEQ

Thank you for your time.

Regards,

1 Like

Hi,

We will update the swagger to include json format.

Example 1: Changed format to json

https://api.transport.nsw.gov.au/v1/roads/spatial?format=json&q=SELECT%20station_id,%20traffic_direction_seq,date,(hour_06%2Bhour_07%2Bhour_08)%20as%20%22am_peak_total%22%20%0AFROM%20road_traffic_counts_hourly_permanent%20%0ALEFT%20JOIN%20road_traffic_counts_station_reference%20%0AON%20road_traffic_counts_station_reference.station_key%20%3D%20road_traffic_counts_hourly_permanent.station_key%0AWHERE%20station_id%3D'SHB'%20AND%20year%20%3D%20'2016'%20AND%20%20public_holiday%20is%20true%20AND%20day_of_week%20BETWEEN%201%20AND%205%20AND%20classification_seq%20%3D%202%20and%20month%20in%20(1,2,3)%20AND%20cardinal_direction_seq%20%3D%205%0AORDER%20BY%20date%0A

Example 2: We will get back on this.

Example 3: Please ensure that you select csv as the format.

https://api.transport.nsw.gov.au/v1/roads/spatial?format=csv&q=SELECT%20NAME,LGA,SUBURB,ROAD_FUNCTIONAL_HIERARCHY,WGS84_LATITUDE,WGS84_LONGITUDE,SUMM.*%20%0AFROM%20road_traffic_counts_station_reference%20REF%20%0AJOIN%20road_traffic_counts_yearly_summary%20SUMM%20ON%20REF.STATION_KEY%20%3D%20SUMM.STATION_KEY%20WHERE%20SUBURB%20%3D%20'Parramatta'%20%0AAND%20PERIOD%20%3D%20'PM%20PEAK'%20%0AAND%20TRAFFIC_DIRECTION_SEQ%20IN%20('1','2')

Example 4: Changed format to json. Do note that you have to change the %2b to + when you are using our swagger in the API page. I will change the documentation to reflect that.

https://api.transport.nsw.gov.au/v1/roads/spatial?format=json&q=SELECT%20STATION_ID,%20TRAFFIC_DIRECTION_SEQ,%20%0AAVG(AMV.AM_VOL)%20AM_VOL%20%0AFROM%20(SELECT%20PERM.DATE,%20REF.STATION_ID,%20PERM.TRAFFIC_DIRECTION_SEQ,(HOUR_07%2BHOUR_08)%20AS%20AM_VOL%20%0AFROM%20road_traffic_counts_station_reference%20REF%20JOIN%20road_traffic_counts_hourly_permanent%20PERM%20ON%20PERM.STATION_KEY%20%3D%20REF.STATION_KEY%20%0AWHERE%20public_holiday%20is%20false%20%0AAND%20SCHOOL_HOLIDAY%20%3D%20'0'%20%0AAND%20DAY_OF_WEEK%20BETWEEN%201%20AND%205%20%0AAND%20YEAR%20%3D%20'2016'%20%0AAND%20MONTH%20%3D%205)%20AMV%20%0AGROUP%20BY%20STATION_ID,TRAFFIC_DIRECTION_SEQ%20%0AORDER%20BY%20STATION_ID,%20TRAFFIC_DIRECTION_SEQ%0A%0A
1 Like

Thank you so much for your help. I hope you could update the manual accordingly.

Regards,

Hyeuk

© Transport for NSW