I am writing a query that would give me the geojson element contains the outline of a US State along with some additional information. The problem I am running into is the function ST_AsGeoJSON from PostGIS is seem to cause the query to run very slowly. Here is the entire query:
select count(distinct datatable.radiogroupid), datatable.stateabbr, ST_AsGeoJSON(statePoly.geom) as geom from( select radiogroupid, amountpaidbydriver, concat(substring(starttime,1,4), '', substring(starttime,6,2)) as yearmonth, ziptable.state as stateabbr from mytable inner join zipcodetable ziptable on ziptable.zip = mytable.stationzipcode ) as datatable left join cb_2013_us_state_5m statePoly on statePoly.stusps = datatable.stateabbr where cast(yearmonth as int) >=201001 and cast(yearmonth as int) =201001 and cast(yearmonth as int)
select count(distinct datatable.radiogroupid), datatable.stateabbr, ST_AsGeoJSON(statePoly.geom) as geom from( select radiogroupid, amountpaidbydriver, concat(substring(starttime,1,4), '', substring(starttime,6,2)) as yearmonth, ziptable.state as stateabbr from mytable inner join zipcodetable ziptable on ziptable.zip = mytable.stationzipcode ) as datatable left join cb_2013_us_state_5m statePoly on statePoly.stusps = datatable.stateabbr where cast(yearmonth as int) >=201001 and cast(yearmonth as int) =201001 and cast(yearmonth as int)