[postgis-users] Huge query, out of memory

[Nicolas Gillet - MARKET-IP]

Hello,

 

Thank you for this input.

 

At a first glance I did not understand it fully but I’ll take time to go
deeper.

 

Indeed a tutorial might be useful for several users as I think merging road
elements is a redundant issue for GIS users.

 

Nicolas.

 

De : postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] De la part de Nicklas
Avén
Envoyé : jeudi 14 janvier 2010 14:38
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Huge query, out of memory

 

Hallo 

 

 

I have been struggling with the same task. I don't know if I have solved it
because I ran out of time a few months ago. But I attach the sql as a file
with some descriptive texts. I intend to check it more properly and write
some tutorial, but that will take some time before I can do.

 

So, no guarantees :-)

 

/Nicklas

2010-01-14 Nicolas Gillet - MARKET-IP wrote:

>

Hello,

 

 

 

I have a big shape file to process (about 300 Mo,  1.5 million records)

 

I have to aggregate the touching linestrings having the same attributes
therefore the best way I could find (with help of this mailing list) was to
aggregate all the linestrings with the same attribute and then splitting the
ones that dont touch.

 

 

 

Here is the query :

 

SELECT subQ.id, subQ.feattyp, subQ.frc, subQ.shieldnum, subQ.name, null,
(st_dump(merged_geom)).geom AS the_geom

 

FROM (

 

                SELECT MAX(id) AS id, feattyp, frc, MAX(shieldnum) AS
shieldnum, nw.name, ST_LineMerge(ST_Collect(the_geom)) AS merged_geom

 

                FROM nw

 

                WHERE nw.frc >= 0

 

                GROUP BY nw.name, nw.feattyp, nw.frc

 

) AS subQ;

 

 

 

This has worked fine on smaller shapes  but on this one I get an out of
memory error.

 

 

 

I used pgTune to change database default settings and the result is as
follow :

 

 

 

#custom_variable_classes = ''                   # list of custom variable
class names

 

default_statistics_target = 50 # pgtune wizard 2010-01-13

 

maintenance_work_mem = 120MB # pgtune wizard 2010-01-13

 

constraint_exclusion = on # pgtune wizard 2010-01-13

 

effective_cache_size = 1408MB # pgtune wizard 2010-01-13

 

work_mem = 12MB # pgtune wizard 2010-01-13

 

wal_buffers = 8MB # pgtune wizard 2010-01-13

 

checkpoint_segments = 16 # pgtune wizard 2010-01-13

 

shared_buffers = 480MB # pgtune wizard 2010-01-13

 

max_connections = 80 # pgtune wizard 2010-01-13

 

 

 

All the other parameters have their default values.

 

 

 

What can I do to run my script through this heavy shape files ?

 

 

 

Thank you

 

 

 

Nicolas.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100115/6ad6102b/attachment.html>