postgresql - Buffering multiple linestrings -

postgresql - Buffering multiple linestrings -

i'm pretty new postgresql, there might pretty simple reply question, @ to the lowest degree hope so.

i have imported table thousands of single linestrings represent main roads of country. i'd buffer every single 1 of them , intersect results polygon (basically circle, thing is, position of circle dynamic, depending on preferences of user).

however, don't know how buffer linestrings @ once. works fine when buffer , intersect 1 linestring, it's kinda crucial buffer of them. , importing roads multilinestring spit doesn't work @ all.

so ... how create happen? hints? i'd appreciate help.

the best approach add together column represents buffers of roads , add together spatial index, ie,

alter table roads add together column road_buffer geometry(polygon, srid); update table roads set road_buffer = st_buffer(roads, distance); create index ix_spatial_road_buffer on roads using gist(road_buffer);

where polygon , srid indicate type , spatial reference id of column. can omit this, although practice utilize specific type , srid. utilize addgeometrycolumn same end.

you can run query against buffered roads, fast, indexed, homecoming actual roads, eg,

select road_id, road roads st_intersects(road_buffer, circle);

now, if wanted other way, without having pre-buffered linestring, somthing like,

select road_id, road, road_buffer (select st_buffer(road, dist) road_buffer, road, road_id roads st_intersects(st_expand(st_envelope(road), dist), circle) ) road_buff st_intersects(road_buffer, circle);

the trick here compute buffer in subquery, linestrings/roads envelope (ie, minimum bounding rectangle) intersects circle -- much faster calculation buffering linestrings. note, also, utilize of st_expand, same amount buffer distance, expands mbr , ensures don't miss potential candidates. did quick test on half 1000000 random lines, , approach much faster (10x) checking circle intersections against buffer of points.

postgresql gis postgis