I have thousands of points and I am trying to intersect them with US states, sort them by date and then create a linestring from those points grouped by date, and state name. I have accomplished this by performing two separate queries, but I want to combine the process into one larger query. I am fairly certain this requires a subquery. here is what I have so far
create table mypoints as select us.name,ralphpoints.* from us,ralphpoints where st_intersects(us.geom,ralphpoints.geom) order by ralphpoints.datereal asc; create table lines as select name as state,datetosort as date,st_makeline(geom) as geom from mypoints group by name,date order by date;
this query produces the below picture which is accurate.
Now when my subquery:
create table lines as select t.name as state,t.datetosort as date,st_makeline(t.geom) as geom from( select us.name,ralphpoints.* from us,ralphpoints where st_intersects(us.geom,ralphpoints.geom) order by ralphpoints.datereal asc)t group by state,date order by date
the result from the subquery is off. i tried to just follow the same logic as the separate two queries, i am not sure why the results are different.
*us refers to US shapefile of states *there are two date fields from the original ralphpoints table. 1.datereal = date day/month/year with detailed time - used for subquery order by to 2.datetosort = just day/month/year used in the group by