Hello,
Thank you for this input. At a first glance I did not understand it fully but Ill 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 : [email protected] [mailto:[email protected]] 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.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
