# [postgis-users] Simple Line Density

pcreso at pcreso.com pcreso at pcreso.com
Sat Dec 22 17:44:22 PST 2012

```Hi Jeff,

I tend to use a vector based solution for this sort of analysis. I am not up to speed with the raster capability of Postgis, but this sort of operation can certainly be done with vectors.

I do this pretty often for gridded analyses of commercial fishing data from start/finish positions, eg:

http://www.deepwater.co.nz/f2111,103778/103778_20_Baird_et_al_2011_AEBR_73.pdf

Appendix F has some of the maps, with pixels analysed by species. From a FOSS
perspective, if anyone is interested, the data were managed in Postgis,
analysed in Postgis & R, previewed in Quantum GIS, with many of the
maps generated automatically from the data using GMT. Excel & Arcmap
were also used, but it was very much a FOSS based project.

Back on topic :-)

A raster is a set of cells. Create a table with the cells as (indexed) polygons squares)  & do a normal Postgis vector intersection query.

There are a few examples of how to create such a grid, such as:
http://gis.stackexchange.com/questions/16374/how-to-create-a-regular-polygon-grid-in-postgis

Then:

create table hits as
select cell_id, line_id from cells, lines where intersects(lines.geom, cells.geom);

You can then query the values in the hits table, joined to the lines to do all sorts of summary stats around the lines/cell values.

An alternative approach, which gives very similar results, perhaps more robustly in some ways, is to use the points as you mentioned, but return lines which intersect a buffer of the point, using something like:

create table hits as
select point_id, line_id from point, line
where intersects(line.geom, ST_Buffer(point.geom, N));

So rather than gridding your data, you are identifying the points that each line was with distance (N) of. Subtly different result, because the distance between the centre of the cell is not the same to all points on the cell boundary, & the buffered circles will either overlap, or leave gaps in the "grid" to intersect with the lines. So N could be 1/2 the diagonal or perpendicular distance between neighbouring points...

& to get a cell where the diagonal distance issue is reduced (all cell boundary vertices are equidistant from the cell centroid), you can use hexagonal cells instead of square. Lots of refinements possible with vectors that are not easy with rasters!

HTH,

Brent Wood

--- On Sun, 12/23/12, Jeff Adams - NOAA Affiliate <jeff.adams at noaa.gov> wrote:

Subject: [postgis-users] Simple Line Density
To: postgis-users at lists.osgeo.org
Date: Sunday, December 23, 2012, 9:07 AM

Hi,

I was wondering if anybody knew of a way to create a very
simple line density raster. I am not interested in interpolation or
clustering, simply how many line features pass through each pixel in the
raster. I know how to do this with points, but am having difficulty
figuring out how to do it with lines. Any help would be greatly
appreciated...

J

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...