Spatial Aggregation of attributes from line structures in polygons

Question

maybe someone has an idea how to deal with it (or directly knows the equivalent functions in PostGIS) for the following problem:

I have a transport network (something like railways) and the attributes of this lines are mostly the transport goods, their corresponding weights and the transportatino dates. In a big railway station lots of these lines come together (all lines are connected and "covered" with a polygon "railway_station" which has not attributes). Now I would like to aggregate the weights of some goods in a defined period based on the polygons area.

Do any function like "Spatial Aggregation" or "Spatial Join" exist which is able to give me the expected results?

Hopefully someone has an idea! :)

Best regards, Jann

Answer 1

One way would be to use the PostGIS function ST_Intersects()

SELECT r.goods, SUM(r.weights) as total_weight, s.station_name
  FROM railways r, railway_station s
  WHERE r.date > '12/5/2005 AND r.date <= '3/15/2006'
  AND ST_Intersects(r.the_geom, s.the_geom)
  GROUP BY s.station_name, r.goods;