Hi Karl, I do this with the ST_ExtractToRaster() function from the PostGIS Add-ons:
https://github.com/pedrogit/postgisaddons/releases/tag/1.21 I just added a SUM_OF_LENGTHS method to ST_ExtractToRaster(). Look at the ST_ExtractToRaster() function in the postgis_addons.sql file for an example. In your case it should look like this: SELECT ST_ExtractToRaster(ST_AddBand(ST_MakeEmptyRaster(rast), '32BF'), 'public', 'geomtable', 'geom', null, 'SUM_OF_LENGTHS') rast FROM refrastertable; You should have a reference raster already existing in a table and your lines should be indexed. Pierre > -----Original Message----- > From: [email protected] [mailto:postgis-users- > [email protected]] On Behalf Of Karl Zinglersen > Sent: Wednesday, November 27, 2013 11:30 AM > To: [email protected] > Cc: PostGIS Users Discussion > Subject: Re: [postgis-users] Simple Line Density > > Hi, > I am dealing with the same types of data and issues as Jeff. > My procedure for data clean up is: > 1) CSV files into PostGIS as points > 1.1) ST_TRANSFORM to projected spatial reference (here EPSG:32621 / > UTM zone 21 N WGS84) > 2) ST_MAKELINE to "sub"-lines via PARTITION BY > 3) Create a gridded polygon layer in same srid > 4) Run equivalent to select cell_id, line_id from cells, lines where > intersects(lines.geom, cells.geom > - but my postgis runs out of memory after a while. And I am sorry to say > Spatial Analyst doesn't. > Is there more memory cheap way to do it in PostGIS (e.g. via the raster > functions - although I haven't found i yet). > > Karl > > Den onsdag den 26. december 2012 09.58.43 UTC-3 skrev Jeff Adams - > NOAA Affiliate: > > Hi Brent, > > Thanks for the response. I don't think your original response made > it into my inbox via the list, but now I see it on the website thread. That > seems like it might be a viable alternative, I just worry about those vessel > tracks that would be clearly outside of a particular grid cell, but whose > buffer would bleed over. How did you handle this type of situation? > > Jeff > > > On Mon, Dec 24, 2012 at 7:22 PM, <[email protected] > <javascript:> > wrote: > > > Jeff, > > Did you see my reply using vessel tracklines as vectors & a grid in Postgis to > do exactly what you describe? > > Given we were looking at benthic impact, we buffered the tracklines to > create polygons representing the swept area of the deployed fishing gear. > These were clipped by the cells, & we could generate statistics suca as the > cumulative swept area of all tracks with each cell, number of times each cell > was crossed, & given the tracklines have a timestamp associated with them, > we could also look at the temporal pattern of tracks crossing cells, for > things like seasonal impacts & variation between seasons. > > Cheers, > > Brent Wood > _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
