3

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.

enter image description here

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

enter image description here

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

4

The key is that for PostgreSQL 9.0+ all aggregates support an ORDER BY clause so no need for a subquery.

Try this:

CREATE TABLE lines as
SELECT us.name as state, r.datetosort as date,
  ST_MakeLine(r.geom ORDER BY r.datereal) as geom 
FROM ralphpoints AS r INNER JOIN us ON st_intersects(us.geom,r.geom) 
GROUP BY us.state, date 
ORDER BY date;
| improve this answer | |
  • awesome that worked perfect, i had no idea you can do an order by within an aggregate function – ziggy Nov 18 '16 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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