[postgis-users] Simple Line Density

[Pierre Racine]

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: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Karl Zinglersen
> Sent: Wednesday, November 27, 2013 11:30 AM
> To: postgis-users at googlegroups.com
> 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, <pcr... at pcreso.com
> <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
>