[postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX
Birgit Laggner
birgit.laggner at vti.bund.de
Wed Dec 16 04:38:47 PST 2009
Hi Regina, Kevin and Leo,
I just solved the problem. For testing purposes, I deleted the rows in
the data table which were causing problems. Then the function ran
without problems, but, unfortunately, the result was not as I expected.
After some research, I discovered that I used the data type "record" in
a wrong way. I thought this data type would store more than one row
which it doesn't... Now, I changed my function and it works properly,
also including the rows which were causing problems in the former version.
Thanks for your help!
Birgit.
On 15.12.2009 08:59, Paragon Corporation wrote:
> Birgit,
>
> Never gotten that error before, but I have had success at least with 8.4
> doing a vacuum analyze on the corrupted table. When I get erros like
> page_header not found.
>
> You can try doing that and see if it makes a difference. Though not sure if
> you can put that in a stored proc, since I've never tried.
>
> Hope that helps,
> Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Birgit
> Laggner
> Sent: Monday, December 14, 2009 5:05 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] problem with plpgsql function - ERROR: could
> not open relation with OID XXX
>
> Hi Leo,
>
> with your TRUNCATE and ALTER SEQUENCE method, I still get an error at
> polygon 451, but a new one:
>
> ERROR: missing chunk number 0 for toast value 27366 in pg_toast_26963 SQL
> Status:XX000 Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 63
> at RAISE
>
> I think, this describes the problem better then the oid referencing error.
> As a result of a Google search, I got the impression that there might be a
> corrupted row (polygon 451) in the database table. Any suggestions how to
> solve the problem?
>
> Thanks,
>
> Birgit.
>
>
>
> Birgit Laggner wrote:
>
>> Hi Kevin,
>>
>> to define the table name in question as a variable did not work,
>> unfortunately. I get the same error message as before. Now, I will try
>> the TRUNCATE and ALTER SEQUENCE method of Leo...
>>
>> Regards, Birgit.
>>
>>
>> Birgit Laggner wrote:
>>
>>> Thanks, Kevin and Leo! I will try your suggestions today.
>>>
>>> Birgit.
>>>
>>> Kevin Neufeld wrote:
>>>
>>>> Yeah, I agree. What I've done to get around the caching problem
>>>> that seems to work is to define all table names as variables at the
>>>> top of the function. All the sql statements used throughout the
>>>> function then reference a variable instead of an actual table. The
>>>> planner can't cache the query plan since the query is adhoc ... no
>>>> OID referencing problem.
>>>> -- Kevin
>>>>
>>>> Paragon Corporation wrote:
>>>> Birgit,
>>>>
>>>> I suspect as you alluded to that you are a victim of the dreaded
>>>> cached plan and your OID issue is because the new table doesn't have
>>>> the same OID as the old table. 8.4 is supposed to be smart enough to
>>>> invalidate plans in these situations, thought maybe not.
>>>>
>>>> One possible work around is instead of creating and dropping the
>>>> table, why don't you just TRUNCATE the table and reset the sequence
>>>>
>>>> So something like
>>>>
>>>>
>>>> TRUNCATE TABLE birgit.test_diff_dlm07_tmp; ALTER SEQUENCE
>>>> birgit.test_diff_dlm07_tmp.gid RESTART WITH 1;
>>>>
>>>>
>>>> You could also use CREATE TEMP TABLE instead of CREATE TABLE. I
>>>> suspect temp table oids may not be cached.
>>>>
>>>> Leo
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: postgis-users-bounces at postgis.refractions.net
>>>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>>>> Birgit Laggner
>>>> Sent: Thursday, December 03, 2009 12:26 PM
>>>> To: PostGIS Users Discussion
>>>> Subject: [postgis-users] problem with plpgsql function - ERROR:
>>>> could not open relation with OID XXX
>>>>
>>>> Dear list,
>>>>
>>>> I have written a pl/pgsql function (see below) for st_difference
>>>> which in short should sequentially scan a geometric table (a) if
>>>> there are intersections with geometric table (b) and if there are,
>>>> it writes the intersecting polygons of table (b) into an extra table
>>>> and then executes the st_difference for the actual polygon of table
>>>> (a) and all polygons of table (b ) written in the extra table as a
>>>> sequence always using the product of the last difference as the
>>>> input (instead of the table (a)
>>>> polygon) of the next difference. I hope everybody understands my way
>>>> of thinking ;-)
>>>>
>>>> My problem is now, that at polygon 451 of table (a), the function
>>>> stops with the following error message:
>>>>
>>>> ERROR: could not open relation with OID 25736 SQL Status:XX000
>>>> Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at
>>>> RAISE
>>>>
>>>> Strange is, that the function did run successfully for more than 100
>>>> difference-loops. In an older PostGres version (8.1...), I have had
>>>> a similar problem, but then always in the 2nd loop, because of the
>>>> cashing-problem of the query planner.
>>>>
>>>> This are the PostGIS/PostgreSQL versions I am using:
>>>> PostgreSQL: 8.4.1-2.1
>>>> PostGIS: 1.4.0-10.1
>>>>
>>>> Here, the last few message rows of the running function, perhaps
>>>> this helps with understanding the problem (sorry because it's partly
>>>> in German, I hope it doesn't matter):
>>>>
>>>> NOTICE: Beginn Difference für dlm07-Polygon 450
>>>> NOTICE: Anzahl Intersection-Polygone: 1
>>>> NOTICE: CREATE TABLE erstellt implizit eine Sequenz
>>>> »test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte
>>>> »test_diff_dlm07_tmp.gid«
>>>> CONTEXT: SQL-Anweisung »create table birgit.test_diff_dlm07_tmp
>>>> (gid serial, inv07_id integer, the_geom geometry);« PL/pgSQL
>>>> function "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung
>>>> NOTICE: recordset_object2a:
>>>> (309108,0103000020EB7A000001000000220000009B8A50B33D3D4A410CFC875191
>>>> 41564121
>>>>
>>>> B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6
>>>> AD773D4A
>>>>
>>>> 41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD
>>>> 0E943870
>>>>
>>>> 4156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F0504156
>>>> 41D2D792
>>>>
>>>> 9CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD
>>>> 3C4A4142
>>>>
>>>> 690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A41704938
>>>> 51304156
>>>>
>>>> 41D1ABC0367F3C4A41000000002B4156415FC001E8EB3B4A41000000002B41564163
>>>> BDF058E9
>>>>
>>>> 3B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A
>>>> 419F2015
>>>>
>>>> 233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49
>>>> 415641EE
>>>>
>>>> 6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F64
>>>> 2C703C4A
>>>>
>>>> 41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111
>>>> FD983366
>>>>
>>>> 4156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F7465059744156
>>>> 41125009
>>>>
>>>> 96C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB
>>>> 3C4A418B
>>>>
>>>> 99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C
>>>> 8F914156
>>>>
>>>> 41579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,1
>>>> )
>>>> NOTICE: recordset_object1:
>>>> (450,0103000020EB7A00000100000022000000CC72F1149A3C4A41000000002B415
>>>> 641295C8
>>>>
>>>> F229A3C4A4185EB51582B4156413E0AD7A3983C4A41000000002B415641351D7C688
>>>> 33C4A410
>>>>
>>>> 00000002B415641F6285C2F7E3C4A410000001031415641B81E856B913C4A41A4703
>>>> D6A35415
>>>>
>>>> 6413E0AD723AD3C4A41AE47E17A3B415641B81E850BAE3C4A4114AE47E13E415641E
>>>> 17A144EA
>>>>
>>>> F3C4A417B14AEE74341564152B81EA5B03C4A410AD7A3B04741564114AE4721BB3C4
>>>> A41EC51B
>>>>
>>>> 82E474156417B14AE67CC3C4A41AE47E17A46415641B81E852BD83C4A411F85EB014
>>>> 64156416
>>>>
>>>> 6666646D73C4A4185EB51B8454156413E0AD783D63C4A41666666664541564114AE4
>>>> 761D53C4
>>>>
>>>> A41B81E85DB44415641295C8F02D53C4A418FC2F548444156413E0AD703D53C4A416
>>>> 66666C64
>>>>
>>>> 34156419A999979D53C4A41AE47E1CA424156410AD7A330D63C4A41713D0AA741415
>>>> 641A4703
>>>>
>>>> D8AD73C4A41B81E859B40415641A4703DEAD93C4A41E17A14CE3E415641713D0A17D
>>>> B3C4A41B
>>>>
>>>> 81E85DB3D415641CDCCCC8CDC3C4A41F6285CAF3C415641E17A148EDF3C4A41AE47E
>>>> 10A3B415
>>>>
>>>> 6419A999959E13C4A410AD7A3103A4156413E0AD7E3E23C4A4148E17AC438415641A
>>>> 4703D0AE
>>>>
>>>> 43C4A41AE47E13A38415641A4703DCAEB3C4A4114AE47213841564100000060EC3C4
>>>> A413E0AD
>>>>
>>>> 7D33241564114AE47E1E03C4A41D7A370AD324156419A999979E33C4A417B14AE772
>>>> E4156418
>>>>
>>>> 82F554CE43C4A41000000002B415641CC72F1149A3C4A41000000002B415641)
>>>> NOTICE: recordset_object2a:
>>>> (309108,0103000020EB7A000001000000220000009B8A50B33D3D4A410CFC875191
>>>> 41564121
>>>>
>>>> B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6
>>>> AD773D4A
>>>>
>>>> 41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD
>>>> 0E943870
>>>>
>>>> 4156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F0504156
>>>> 41D2D792
>>>>
>>>> 9CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD
>>>> 3C4A4142
>>>>
>>>> 690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A41704938
>>>> 51304156
>>>>
>>>> 41D1ABC0367F3C4A41000000002B4156415FC001E8EB3B4A41000000002B41564163
>>>> BDF058E9
>>>>
>>>> 3B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A
>>>> 419F2015
>>>>
>>>> 233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49
>>>> 415641EE
>>>>
>>>> 6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F64
>>>> 2C703C4A
>>>>
>>>> 41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111
>>>> FD983366
>>>>
>>>> 4156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F7465059744156
>>>> 41125009
>>>>
>>>> 96C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB
>>>> 3C4A418B
>>>>
>>>> 99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C
>>>> 8F914156
>>>>
>>>> 41579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,1
>>>> )
>>>> NOTICE: Intersection-Polygon 1 verarbeitet.
>>>> NOTICE: Difference-Polygon dlm07 450 ist fertig.
>>>> NOTICE: Beginn Difference für dlm07-Polygon 451
>>>> NOTICE: Anzahl Intersection-Polygone: 1
>>>> NOTICE: CREATE TABLE erstellt implizit eine Sequenz
>>>> »test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte
>>>> »test_diff_dlm07_tmp.gid«
>>>> CONTEXT: SQL-Anweisung »create table birgit.test_diff_dlm07_tmp
>>>> (gid serial, inv07_id integer, the_geom geometry);« PL/pgSQL
>>>> function "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung
>>>> NOTICE: recordset_object2a:
>>>> (242405,0103000020EB7A00000100000093000000A5D6BC7F113B4A41DA9C414B8C
>>>> 44564150
>>>>
>>>> FAAEDD133B4A419F49202D8B4456412FF0D7391B3B4A41168EC19286445641E5C111
>>>> 791C3B4A
>>>>
>>>> 412DEE6FD88D44564193716BA71F3B4A41527950589B4456413706B5B4203B4A411C
>>>> A7E1639F
>>>>
>>>> 4456417F264CED223B4A410BDF111DA544564195B45007273B4A410C80CF17AE4456
>>>> 41233940
>>>>
>>>> 05293B4A41D9F53FC8B34456417B6AB9082D3B4A415F147142B4445641B5FC2FAC30
>>>> 3B4A4129
>>>>
>>>> 35E853B4445641C323EFF1373B4A41C18C5DAAB34456412A8D25813F3B4A41C33BF4
>>>> E5B24456
>>>>
>>>> 41022A8ED84C3B4A4118E6FF5DB2445641F237A3BB5D3B4A414C7E863FB244564191
>>>> C9735265
>>>>
>>>> 3B4A410E9B8A5CB1445641529A25D6633B4A41C47E2DB7AD445641B5D7BDEA5E3B4A
>>>> 4160BF3D
>>>>
>>>> D7A4445641064DA2335A3B4A41B397823D9B445641468081F5693B4A412AAF71059B
>>>> 44564174
>>>>
>>>> 5B60647B3B4A4188667A919A4456411E7579487A3B4A41BA466E559944564131F2D4
>>>> F87E3B4A
>>>>
>>>> 41C9563A27994456414A570C2C893B4A41CC1B91F797445641FAD2C8BCA33B4A419E
>>>> EF661595
>>>>
>>>> 44564194F74682CE3B4A41C8A7A28090445641F677AA51E53B4A41C3AC804A8E4456
>>>> 419D16B3
>>>>
>>>> 33023C4A4145B4DB368B4456412CD4AE17083C4A4172F829968A4456418E660AAB22
>>>> 3C4A4142
>>>>
>>>> 4103B9874456414F5B3AF4233C4A4100517DEF92445641F472B68E233C4A41C56C4F
>>>> 98934456
>>>>
>>>> 4102FCF859223C4A415E932D9A95445641EFCC195F113C4A41DB4F155BA644564154
>>>> 57E1A40E
>>>>
>>>> 3C4A4151BCC460A9445641115545C6083C4A41DC02CB19B14456418064136EF33B4A
>>>> 413F76BC
>>>>
>>>> D1AC445641DB6389DCF13B4A41BFE689EFB344564126807354EB3B4A4183EC2AACBD
>>>> 445641C5
>>>>
>>>> F5579DFA3B4A4121A624F3BF445641D717C6EBF73B4A41ABBB2195C744564161A21A
>>>> 04F33B4A
>>>>
>>>> 41FB77D15FD5445641D0D839DB053C4A41B7831098D84456410A907C33073C4A4142
>>>> 44DACED0
>>>>
>>>> 44564148A334A9163C4A41547CEC36D1445641BF7B60D5363C4A41BCF33468D14456
>>>> 41F68B9D
>>>>
>>>> FE393C4A411283312ADA445641B45CC31A4C3C4A41F6A929D1D744564185C3D2CE56
>>>> 3C4A4146
>>>>
>>>> BC02D9D64456410E266F8E503C4A41256BD10AC544564178A04D1C4E3C4A416E5030
>>>> 6BBD4456
>>>>
>>>> 417ED898E54C3C4A41A3F1EA24B6445641C11890CB4D3C4A4185584DDFAE44564110
>>>> 0E678C50
>>>>
>>>> 3C4A41447D8CC1A8445641C8EA7258533C4A41D025F919A4445641F45E60645B3C4A
>>>> 41F11D11
>>>>
>>>> 5B9B445641769EB3136B3C4A41DD06E5858D44564175B35E1A703C4A41CBE002D887
>>>> 4456418E
>>>>
>>>> E0827A743C4A41A399728D80445641C2DE7203773C4A4116C1F4C579445641BF3B19
>>>> 2A783C4A
>>>>
>>>> 41250884A571445641B465A72D773C4A41317A276E5C4456414FFAB66E6C3C4A41EE
>>>> 3D4D3554
>>>>
>>>> 445641D8E3F8AF613C4A41CC142A074A445641C4DD74D9493C4A41E18223504A4456
>>>> 4149E72F
>>>>
>>>> 02203C4A416083AE4C4B445641BB020316163C4A417AD8A6804D44564115A108B702
>>>> 3C4A4133
>>>>
>>>> B9970352445641667D32D4FA3B4A413B9DE3AB52445641206FE7D0EF3B4A410F1221
>>>> 7D524456
>>>>
>>>> 416EAA545EE83B4A41E4AAF88E51445641C242D969DD3B4A41BC9873F84F44564159
>>>> 773A65CF
>>>>
>>>> 3B4A410D0595C04B445641B4F85B10CF3B4A4187C6CA374A445641883CC8C0C73B4A
>>>> 419A306D
>>>>
>>>> 07464456419595327EBB3B4A41A5F9E0843F445641A4113DFEA83B4A4120D2EB1E36
>>>> 44564157
>>>>
>>>> 6BBF8A9D3B4A41334EA22D30445641F52DD12E9C3B4A411BEEF45232445641A4E92D
>>>> F3973B4A
>>>>
>>>> 413F90FFD13444564143331E7A943B4A41F761391136445641A69D4CB1903B4A414D
>>>> C4100837
>>>>
>>>> 445641267AE7258C3B4A41A46AA98B37445641AFD254247C3B4A41E986946E384456
>>>> 417541D6
>>>>
>>>> 286D3B4A416D1F52DB3944564158B4E7076B3B4A412A3AF20F3A4456419F2AFEC462
>>>> 3B4A4145
>>>>
>>>> 0828DC3A44564109C6A7D5613B4A416D1F52DB39445641E9BFB312583B4A413FC197
>>>> 913B4456
>>>>
>>>> 4137F356324D3B4A4145E438BF3E445641A69714F4243B4A41FF1C9DC249445641F8
>>>> 194AC213
>>>>
>>>> 3B4A4129F0221B4F445641FA42FB2D133B4A41A2CCF75B54445641616D3114153B4A
>>>> 419CB61B
>>>>
>>>> 4C58445641BF8B0E1D183B4A418E3130FA5B4456415BD1C57B1F3B4A41AE6C11205E
>>>> 445641EB
>>>>
>>>> C43854353B4A4112AAE30063445641F4DE50CD423B4A41B562C8B96C4456417E0F5D
>>>> 25413B4A
>>>>
>>>> 41BA301B6C6D445641CBDFD71D343B4A41E66F8A9663445641805C16E81C3B4A41F8
>>>> 72CAAB5E
>>>>
>>>> 445641433BF7F3173B4A41BAB368095D4456413F29FF2F143B4A41D805CA755A4456
>>>> 41C55526
>>>>
>>>> D1103B4A4158A7F41B554456415BF92622113B4A41885AA7994F4456416402759FF5
>>>> 3A4A4116
>>>>
>>>> 623BE750445641F6FFBDB0F23A4A415EB061555044564131A5403BE93A4A418CC63C
>>>> FA4C4456
>>>>
>>>> 412FFBA344E43A4A418D36F0724C4456415DB86386B33A4A41BFA5FE494D445641A5
>>>> FD702CAD
>>>>
>>>> 3A4A417D8DBFBC4D445641A2967FC9A33A4A41E508260650445641FAC39BD16C3A4A
>>>> 419ABBC1
>>>>
>>>> 1F5F445641E2C7B9E9683A4A413EFFA1685F44564170F2B498613A4A41F872CAAB5E
>>>> 44564192
>>>>
>>>> C7B713423A4A41D76C65915B445641F5A23B242A3A4A41F4F96F2D5A445641DAD3B5
>>>> 921B3A4A
>>>>
>>>> 4168628F5858445641AF0DF4EC133A4A41F038A8CC56445641807AF58D0D3A4A41F8
>>>> 0F4C3D56
>>>>
>>>> 445641641AB90A073A4A412046A47557445641764EC9D5013A4A417192BBD7584456
>>>> 41C1CD4D
>>>>
>>>> 37FE394A418E16E7775B445641E07DDC1AFA394A418AE5C1E55E445641783329A1F1
>>>> 394A411E
>>>>
>>>> EC95816444564171FF88C2E8394A41BB6F1AAA694456411A500F6DD9394A414D4590
>>>> 52724456
>>>>
>>>> 4160B99CAEE8394A41D4C6E9F3744456411E15301DF4394A41E6F51C60794456416C
>>>> D799DEF7
>>>>
>>>> 394A41A3D453B37A4456411D105594103A4A41CC5FFA667E44564136C98BE8183A4A
>>>> 4147B127
>>>>
>>>> 89804456414A49D33C213A4A41B381F8C8814456414633A250283A4A41AA794B4182
>>>> 44564122
>>>>
>>>> 3F70313C3A4A415F233FEA82445641B5899402483A4A410904F70B8344564117DDEF
>>>> EC5C3A4A
>>>>
>>>> 41E541706483445641E6247DF6713A4A417E1B589683445641A219CD66933A4A4137
>>>> 2C3C3784
>>>>
>>>> 44564146FD76AECD3A4A41F5171BC28644564129A670D9D23A4A41BB8934F9874456
>>>> 4136C565
>>>>
>>>> B1D63A4A4104824C3A89445641265EE8E6E03A4A41B0E45ABC8D44564141F89B25EA
>>>> 3A4A416D
>>>>
>>>> 9A8BC792445641E1B6AC97EC3A4A41EE52A9E19444564164801FAAFA3A4A414E05B8
>>>> 289A4456
>>>>
>>>> 41A5D6BC7F113B4A41DA9C414B8C445641,1)
>>>> NOTICE: recordset_object1:
>>>> (451,0103000020EB7A00000100000007000000F6285CEF5D3C4A413E0AD7134A445
>>>> 641D7A37
>>>>
>>>> 0DD733C4A411F85EB0159445641C2F5283C7C3C4A413E0AD72359445641EC51B8FE7
>>>> A3C4A41D
>>>>
>>>> 7A370FD47445641EC51B8FE733C4A413E0AD76348445641713D0A57663C4A418FC2F
>>>> 54849445
>>>>
>>>> 641F6285CEF5D3C4A413E0AD7134A445641)
>>>>
>>>> ERROR: could not open relation with OID 25736
>>>> CONTEXT: PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at
>>>> RAISE
>>>>
>>>> If anybody has suggestions, I would be very happy. If you need more
>>>> information or the two tables in question, please tell me.
>>>>
>>>> Many thanks,
>>>>
>>>> Birgit.
>>>>
>>>> CREATE OR REPLACE FUNCTION _laggner_b_pgdifference_a()
>>>> RETURNS void AS
>>>> $BODY$
>>>>
>>>> DECLARE
>>>> counter integer;
>>>> recordset_object1 RECORD;
>>>> recordset_object2 RECORD;
>>>> recordset_object2a RECORD;
>>>> recordset_object3 RECORD;
>>>> i integer;
>>>> n integer;
>>>> j integer;
>>>> m integer;
>>>>
>>>> BEGIN
>>>> --4. Difference a (dlm07):
>>>>
>>>> counter := 0;
>>>> i := 0;
>>>> n := count(dlm07_id) from birgit.ni_dlm07_clip2;
>>>>
>>>> FOR i in 1..n LOOP --LOOP 1
>>>>
>>>> RAISE NOTICE 'Beginn Difference für dlm07-Polygon % ', i;
>>>>
>>>> SELECT dlm07_id, the_geom INTO recordset_object1 from
>>>> birgit.ni_dlm07_clip2 where dlm07_id=i;
>>>>
>>>> SELECT b.inv07_id as inv07_id, b.the_geom as the_geom INTO
>>>> recordset_object2
>>>> from birgit.ni_dlm07_clip2 a,
>>>> birgit.ni_inv07_clip2 b
>>>> where a.dlm07_id=i and
>>>> st_relate(a.the_geom, b.the_geom, '2********');
>>>>
>>>> m := count(recordset_object2.inv07_id);
>>>>
>>>> RAISE NOTICE 'Anzahl Intersection-Polygone: % ', m;
>>>>
>>>> IF m > 0 THEN
>>>>
>>>> execute
>>>> 'create table birgit.test_diff_dlm07_tmp (gid serial, inv07_id
>>>> integer, the_geom geometry);';
>>>>
>>>> insert into birgit.test_diff_dlm07_tmp (inv07_id, the_geom)
>>>> select recordset_object2.inv07_id, recordset_object2.the_geom;
>>>>
>>>> SELECT a.inv07_id as inv07_id,
>>>> a.the_geom as the_geom,
>>>> a.gid as gid
>>>> INTO recordset_object2a
>>>> FROM birgit.test_diff_dlm07_tmp a;
>>>>
>>>> RAISE NOTICE 'recordset_object2a: %', recordset_object2a;
>>>>
>>>> execute
>>>> 'drop table birgit.test_diff_dlm07_tmp;';
>>>>
>>>> j := 0;
>>>>
>>>> FOR j in 1..m LOOP --LOOP 2
>>>>
>>>> RAISE NOTICE 'recordset_object1: %', recordset_object1; RAISE
>>>> NOTICE
>>>> 'recordset_object2a: %', recordset_object2a;
>>>>
>>>> SELECT recordset_object1.dlm07_id as dlm07_id,
>>>> st_difference(recordset_object1.the_geom,
>>>> recordset_object2a.the_geom) as
>>>> the_geom
>>>> INTO recordset_object3
>>>> WHERE recordset_object2a.gid=j;
>>>>
>>>> SELECT recordset_object3.dlm07_id as dlm07_id,
>>>> recordset_object3.the_geom as the_geom INTO recordset_object1;
>>>>
>>>> RAISE NOTICE 'Intersection-Polygon % verarbeitet. ', j;
>>>>
>>>> END LOOP; --END LOOP 2
>>>>
>>>> IF st_isempty(recordset_object1.the_geom)='f' then
>>>>
>>>> INSERT INTO birgit.test_diff_dlm07 (dlm07_id, inv07_id, the_geom)
>>>> VALUES (
>>>> recordset_object1.dlm07_id,
>>>> NULL,
>>>> recordset_object1.the_geom);
>>>>
>>>> END IF;
>>>>
>>>> RAISE NOTICE 'Difference-Polygon dlm07 % ist fertig. ', i ;
>>>>
>>>> ELSE RAISE NOTICE 'Kein Difference berechnet. ';
>>>>
>>>> END IF;
>>>>
>>>> counter := counter + 1;
>>>>
>>>> END LOOP; --END LOOP 1
>>>>
>>>> END;
>>>> $BODY$
>>>> LANGUAGE 'plpgsql' VOLATILE;
>>>> ALTER FUNCTION _laggner_b_pgdifference_a() OWNER TO postgres;
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
More information about the postgis-users
mailing list