[GIS] PostGIS subquery make linestring from points

linestringpostgispostgresql

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

Best Answer

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;
Related Question