Traffic Volume Counts API

Request for data
1

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,

2

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

3

Hi,

Thank you so much. Do you know how to correct errors in the examples in the manual (User account | TfNSW Open Data Hub and Developer Portal)?

Regards,

1 Like
4

Hi,

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

5

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
6

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
7

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

Regards,

Hyeuk

8

Hi i noticed the traffic counts stop at Jun2021, will this be updated more frequently given the COV19

9

Hi everyone, this has now been updated as of August 2021 (it updates monthly).

10

Hi,
I have structured the requests exactly as listed out in the example given, but continue to get an ‘error in request data’ error.
Could you let me know how to fix this?
Regards,

11

Hi otj899,

Could you be more specific about which example you are referring to?

Thanks,
Julie