Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#1181 closed defect (fixed)

topology.TopologySummary is broken

Reported by: robe Owned by: strk
Priority: critical Milestone: PostGIS 2.0.0
Component: topology Version: master
Keywords: Cc:

Description

I tried this on both my 8.4 and 9.1 installs and get this message: When I do this:

SELECT topology.topologysummary('boston_topo');

I get this

 Got column reference "topology_id" is ambiguous (42702)

Change History (7)

comment:1 by robe, 13 years ago

Resolution: fixed
Status: newclosed

fixed at r7823

We should probably add a regress test for this function.

comment:2 by robe, 13 years ago

correction was testing on 9.0 and 9.1 (not sure how it was on 8.4). The rules might have gotten stricter in 9.0.

comment:3 by strk, 13 years ago

You could also drop the variable and reference rec.id directly. That's how I've dealt with those errors in other recent cases. 8.4 was likely fine as that's where I develop against. I'm glad you're enjoying the function, post some tiger stats ! :)

comment:4 by robe, 13 years ago

Well stats. I got it loaded with this command

SELECT tiger.topology_load_tiger('topo_boston', 'place', '2507000'); 

and summary for my clipped region returns

Topology topo_boston (6), SRID 2249, precision 1
19894 nodes, 30449 edges, 11109 faces, 0 topogeoms in 0 layers

ran ValidateTopology and got 484 errors. I suspect my left right edge logic is wrong because that was the only part not directly available in tiger edges structure. Also was trying to avoid having to bring the whole thing in so just set the left_ … to - of the current edge where I clipped the topology.

Scary — will have to investigate where I went wrong or if its a flaw in tiger data. Some edges were really multilinestrings for one so don't fit in linestring restricted edge model we have. I also assume we are not taking into consideration the tolerance yet so some might be stupid rounding errors since as Leo noted some nodes in tiger data that claim to be the same are slightly different.

edge crosses edge;85695808;85695811
edge crosses edge;85696752;85723278
edge crosses edge;85696942;85727753
edge crosses edge;85696943;85697487
edge crosses edge;85697284;608979919
edge crosses edge;85697537;85720011
edge crosses edge;85697565;85728034
edge crosses edge;85697565;634869800
edge crosses edge;85698971;85698973
edge crosses edge;85699835;85699836
edge crosses edge;85701496;85701508
edge crosses edge;85701858;85701866
edge crosses edge;85702123;85702124
edge crosses edge;85702130;85702137
edge crosses edge;85703528;85735045
edge crosses edge;85705725;85705726
edge crosses edge;85705774;85705778
edge crosses edge;85709730;85709731
edge crosses edge;85710150;85717526
edge crosses edge;85711232;85711348
edge crosses edge;85712407;635346015
edge crosses edge;85714383;85738889
edge crosses edge;85715529;85715540
edge crosses edge;85715539;85726410
edge crosses edge;85717140;85717141
edge crosses edge;85720254;85728172
edge crosses edge;85721292;85724170
edge crosses edge;85724590;612867218
edge crosses edge;85725069;87127392
edge crosses edge;85725854;85740032
edge crosses edge;85726772;85731250
edge crosses edge;85726993;85735538
edge crosses edge;85728950;85736318
edge crosses edge;85729124;85736365
edge crosses edge;85730252;85736516
edge crosses edge;85730337;613367291
edge crosses edge;85730443;633317065
edge crosses edge;85731427;85740038
edge crosses edge;85732492;85741808
edge crosses edge;85735634;85738066
edge crosses edge;85737844;85738898
edge crosses edge;85738677;85741619
edge crosses edge;85739088;85740434
edge crosses edge;631741617;631741619
edge crosses edge;85695087;85722281
edge crosses edge;85695810;85695818
edge crosses edge;85696515;85696532
edge crosses edge;85696621;85727524
edge crosses edge;85698973;613369831
edge crosses edge;85699648;85718350
edge crosses edge;85701508;633012752
edge crosses edge;85703839;85735329
edge crosses edge;85704328;85729179
edge crosses edge;85710752;85710757
edge crosses edge;85714384;85738889
edge crosses edge;85720331;624424009
edge crosses edge;85724003;85724754
edge crosses edge;85724874;85726920
edge crosses edge;85725751;85729053
edge crosses edge;85727043;85727045
edge crosses edge;85728949;85736319
edge crosses edge;85730130;85735780
edge crosses edge;85730565;85739433
edge crosses edge;85738144;618744419
edge crosses edge;85739678;618752688
edge crosses edge;85695240;85695246
edge crosses edge;85701507;85724455
edge crosses edge;85701546;85701571
edge crosses edge;85705789;85705796
edge crosses edge;85711231;85711232
edge crosses edge;85720338;85720341
edge crosses edge;85724203;85724204
edge crosses edge;85724879;85734340
edge crosses edge;85726349;85736034
edge crosses edge;85726350;85736028
edge crosses edge;85728468;85734999
edge crosses edge;85728522;630520049
edge crosses edge;85731554;613368912
edge crosses edge;85731723;85739367
edge crosses edge;85733494;85738647
edge crosses edge;85738258;85739094
edge crosses edge;85738272;85739118
edge crosses edge;85740434;85740436
edge crosses edge;85740435;85740534
edge crosses edge;85741113;85741185
edge crosses edge;85695646;85695812
edge crosses edge;85697267;85697284
edge crosses edge;85698487;85698490
edge crosses edge;85699972;85699984
edge crosses edge;85700763;85700772
edge crosses edge;85701180;85701287
edge crosses edge;85701931;85701933
edge crosses edge;85703834;85703838
edge crosses edge;85704135;85729112
edge crosses edge;85711349;85711351
edge crosses edge;85711349;85720047
edge crosses edge;85711850;85741780
edge crosses edge;85714385;618752688
edge crosses edge;85722640;635115101
edge crosses edge;85724167;85724170
edge crosses edge;85724881;87108047
edge crosses edge;85726745;85732549
edge crosses edge;85728011;85728034
edge crosses edge;85728011;634869800
edge crosses edge;85730121;85730131
edge crosses edge;85730129;85735781
edge crosses edge;85731949;85737081
edge crosses edge;85733521;85741619
edge crosses edge;85736233;85738509
edge crosses edge;85737969;618740625
edge crosses edge;85737976;618744422
edge crosses edge;85739033;85739034
edge crosses edge;85740365;85740415
edge start node geometry mis-match;85695126;81043829
edge start node geometry mis-match;85697284;81045293
edge start node geometry mis-match;85697822;81045664
edge start node geometry mis-match;85698131;81045849
edge start node geometry mis-match;85699865;81047037
edge start node geometry mis-match;85701128;81047583
edge start node geometry mis-match;85701288;81047925
edge start node geometry mis-match;85701570;81048188
edge start node geometry mis-match;85702061;81048515
edge start node geometry mis-match;85702123;81048561
edge start node geometry mis-match;85704129;81049907
edge start node geometry mis-match;85705725;81050973
edge start node geometry mis-match;85705774;81051012
edge start node geometry mis-match;85705859;81050993
edge start node geometry mis-match;85709065;81053207
edge start node geometry mis-match;85710749;81054288
edge start node geometry mis-match;85712407;81055326
edge start node geometry mis-match;85713266;81055869
edge start node geometry mis-match;85715529;81057337
edge start node geometry mis-match;85721989;81048426
edge start node geometry mis-match;85722851;81053599
edge start node geometry mis-match;85723043;81045141
edge start node geometry mis-match;85725068;81058937
edge start node geometry mis-match;85725583;81046095
edge start node geometry mis-match;85725854;81059767
edge start node geometry mis-match;85728950;81060978
edge start node geometry mis-match;85729053;81049496
edge start node geometry mis-match;85729124;81061052
edge start node geometry mis-match;85729179;81050053
edge start node geometry mis-match;85730568;81061702
edge start node geometry mis-match;85731314;81062048
edge start node geometry mis-match;85732845;81062708
edge start node geometry mis-match;85734419;81046081
edge start node geometry mis-match;85735624;81055047
edge start node geometry mis-match;85735780;81061502
edge start node geometry mis-match;85738066;81063862
edge start node geometry mis-match;85738290;81064151
edge start node geometry mis-match;85738325;81064183
edge start node geometry mis-match;85739088;81065128
edge start node geometry mis-match;85739193;81065198
edge start node geometry mis-match;85739400;81061999
edge start node geometry mis-match;85740266;81061854
edge start node geometry mis-match;85740981;81065734
edge start node geometry mis-match;85741756;81063806
edge start node geometry mis-match;85742397;81062097
edge start node geometry mis-match;613368912;81057477
edge start node geometry mis-match;618740625;81063718
edge start node geometry mis-match;618752688;81052885
edge start node geometry mis-match;624007766;81047153
edge start node geometry mis-match;625298336;81061148
edge start node geometry mis-match;630520384;81062018
edge start node geometry mis-match;631741618;421382413
edge start node geometry mis-match;633012950;81063434
edge start node geometry mis-match;85694292;81043233
edge start node geometry mis-match;85695087;81043812
edge start node geometry mis-match;85695449;81044062
edge start node geometry mis-match;85695481;81044062
edge start node geometry mis-match;85695810;81044203
edge start node geometry mis-match;85695811;81044298
edge start node geometry mis-match;85698504;81046081
edge start node geometry mis-match;85699648;81046897
edge start node geometry mis-match;85700372;81047379
edge start node geometry mis-match;85702060;81048515
edge start node geometry mis-match;85702067;81048514
edge start node geometry mis-match;85702137;81048565
edge start node geometry mis-match;85702649;81048909
edge start node geometry mis-match;85703839;81049708
edge start node geometry mis-match;85709064;81053207
edge start node geometry mis-match;85711076;81054488
edge start node geometry mis-match;85715470;81057303
edge start node geometry mis-match;85720331;81045910
edge start node geometry mis-match;85720341;81047452
edge start node geometry mis-match;85720506;81057672
edge start node geometry mis-match;85723278;81044944
edge start node geometry mis-match;85723281;81044949
edge start node geometry mis-match;85724702;81054487
edge start node geometry mis-match;85727010;81059369
edge start node geometry mis-match;85727043;81043889
edge start node geometry mis-match;85727067;81043916
edge start node geometry mis-match;85729059;81049555
edge start node geometry mis-match;85729471;81051101
edge start node geometry mis-match;85731204;81061999
edge start node geometry mis-match;85731538;81062145
edge start node geometry mis-match;85735622;81062048
edge start node geometry mis-match;85737281;81060570
edge start node geometry mis-match;85739433;81061701
edge start node geometry mis-match;85740200;81065369
edge start node geometry mis-match;612867218;81047653
edge start node geometry mis-match;613367291;81061598
edge start node geometry mis-match;85696532;81044777
edge start node geometry mis-match;85697269;81045295
edge start node geometry mis-match;85698970;81046418
edge start node geometry mis-match;85699984;81047097
edge start node geometry mis-match;85700577;81047350
edge start node geometry mis-match;85701546;81048173
edge start node geometry mis-match;85702066;81048514
edge start node geometry mis-match;85702264;81048647
edge start node geometry mis-match;85703137;81049214
edge start node geometry mis-match;85704284;81050020
edge start node geometry mis-match;85715384;81056104
edge start node geometry mis-match;85724203;81046432
edge start node geometry mis-match;85724879;81058940
edge start node geometry mis-match;85728522;81060777
edge start node geometry mis-match;85728904;81060958
edge start node geometry mis-match;85732478;81062562
edge start node geometry mis-match;85734022;81063229
edge start node geometry mis-match;85735258;81061020
edge start node geometry mis-match;85736266;81062455
edge start node geometry mis-match;85737829;81064475
edge start node geometry mis-match;85738258;81064798
edge start node geometry mis-match;85738272;81064806
edge start node geometry mis-match;85741106;81065728
edge start node geometry mis-match;85742297;81062226
edge start node geometry mis-match;613367157;81059590
edge start node geometry mis-match;613367266;81060570
edge start node geometry mis-match;613370813;81062488
edge start node geometry mis-match;631741632;421382413
edge start node geometry mis-match;85695116;81043829
edge start node geometry mis-match;85695646;81044084
edge start node geometry mis-match;85695649;81044197
edge start node geometry mis-match;85698487;81046093
edge start node geometry mis-match;85704135;81049907
edge start node geometry mis-match;85705724;81050973
edge start node geometry mis-match;85711349;81054592
edge start node geometry mis-match;85712254;81055207
edge start node geometry mis-match;85713267;81055869
edge start node geometry mis-match;85714026;81056389
edge start node geometry mis-match;85721237;81058706
edge start node geometry mis-match;85721250;81049907
edge start node geometry mis-match;85722640;81055208
edge start node geometry mis-match;85726745;81056455
edge start node geometry mis-match;85727184;81059272
edge start node geometry mis-match;85727641;81059590
edge start node geometry mis-match;85730121;81053157
edge start node geometry mis-match;85731724;81062226
edge start node geometry mis-match;85732234;81062455
edge start node geometry mis-match;85734999;81060750
edge start node geometry mis-match;85735050;81061005
edge start node geometry mis-match;85735538;81059087
edge start node geometry mis-match;85736319;81060978
edge start node geometry mis-match;85737557;81063806
edge start node geometry mis-match;85737976;81064384
edge start node geometry mis-match;85738136;81064718
edge start node geometry mis-match;85738138;81064718
edge start node geometry mis-match;85738648;81063211
edge start node geometry mis-match;85739033;81065091
edge start node geometry mis-match;609020740;81054487
edge start node geometry mis-match;613367909;81054327
edge start node geometry mis-match;633012840;81060699
edge start node geometry mis-match;633012692;81048515
edge start node geometry mis-match;85741619;81056606
edge start node geometry mis-match;87318464;64728602
edge start node geometry mis-match;87327504;64716202
edge end node geometry mis-match;85695207;81043889
edge end node geometry mis-match;85696942;81045064
edge end node geometry mis-match;85697537;81045473
edge end node geometry mis-match;85698115;81045844
edge end node geometry mis-match;85700327;81047350
edge end node geometry mis-match;85700373;81047379
edge end node geometry mis-match;85700772;81047653
edge end node geometry mis-match;85701287;81047925
edge end node geometry mis-match;85701496;81048143
edge end node geometry mis-match;85701545;81048173
edge end node geometry mis-match;85701858;81048379
edge end node geometry mis-match;85701933;81048426
edge end node geometry mis-match;85702291;81048665
edge end node geometry mis-match;85703125;81049223
edge end node geometry mis-match;85703355;64690758
edge end node geometry mis-match;85703528;81049496
edge end node geometry mis-match;85704963;81050442
edge end node geometry mis-match;85705426;81050744
edge end node geometry mis-match;85705713;81050973
edge end node geometry mis-match;85709332;81053371
edge end node geometry mis-match;85709730;81053629
edge end node geometry mis-match;85713204;81055829
edge end node geometry mis-match;85715469;81057303
edge end node geometry mis-match;85715858;81057583
edge end node geometry mis-match;85718442;81044306
edge end node geometry mis-match;85720254;81045664
edge end node geometry mis-match;85720510;81057681
edge end node geometry mis-match;85721236;81058706
edge end node geometry mis-match;85721292;81049107
edge end node geometry mis-match;85722701;81048971
edge end node geometry mis-match;85726772;81056455
edge end node geometry mis-match;85726920;81043329
edge end node geometry mis-match;85727624;81044944
edge end node geometry mis-match;85728111;81060578
edge end node geometry mis-match;85728521;81060777
edge end node geometry mis-match;85729474;81051101
edge end node geometry mis-match;85731427;81062092
edge end node geometry mis-match;85731544;81043233
edge end node geometry mis-match;85732319;81062488
edge end node geometry mis-match;85732477;81062562
edge end node geometry mis-match;85733411;81062977
edge end node geometry mis-match;85734339;81058937
edge end node geometry mis-match;85734419;81060699
edge end node geometry mis-match;85736028;81059993
edge end node geometry mis-match;85736175;81064151
edge end node geometry mis-match;85736516;81061558
edge end node geometry mis-match;85738647;81063018
edge end node geometry mis-match;85739157;81064828
edge end node geometry mis-match;85739367;81062226
edge end node geometry mis-match;85740415;81065117
edge end node geometry mis-match;85741780;81054969
edge end node geometry mis-match;85741808;81062567
edge end node geometry mis-match;87324609;64716202
edge end node geometry mis-match;630520506;419525479
edge end node geometry mis-match;85695247;81043916
edge end node geometry mis-match;85697022;81045106
edge end node geometry mis-match;85698973;81046419
edge end node geometry mis-match;85699107;81046513
edge end node geometry mis-match;85700774;81047655
edge end node geometry mis-match;85701566;81048188
edge end node geometry mis-match;85703838;81049708
edge end node geometry mis-match;85710757;81054288
edge end node geometry mis-match;85710809;81054327
edge end node geometry mis-match;85711076;81054487
edge end node geometry mis-match;85711348;81054592
edge end node geometry mis-match;85720337;81047452
edge end node geometry mis-match;85724003;81058399
edge end node geometry mis-match;85726269;81059955
edge end node geometry mis-match;85727122;81044062
edge end node geometry mis-match;85727209;81059303
edge end node geometry mis-match;85733981;81063211
edge end node geometry mis-match;85735981;81063229
edge end node geometry mis-match;85737281;81063882
edge end node geometry mis-match;85738144;81064384
edge end node geometry mis-match;85738679;81063030
edge end node geometry mis-match;85739317;81059999
edge end node geometry mis-match;85739383;81059767
edge end node geometry mis-match;85740436;81065128
edge end node geometry mis-match;85741667;81045013
edge end node geometry mis-match;85742022;81061994
edge end node geometry mis-match;85742399;81062097
edge end node geometry mis-match;85697487;81045064
edge end node geometry mis-match;85699836;81047017
edge end node geometry mis-match;85700662;81047583
edge end node geometry mis-match;85701507;81048143
edge end node geometry mis-match;85704190;81049952
edge end node geometry mis-match;85705789;81051021
edge end node geometry mis-match;85711231;81054590
edge end node geometry mis-match;85716504;81057868
edge end node geometry mis-match;85716978;81048647
edge end node geometry mis-match;85717141;81058088
edge end node geometry mis-match;85717526;81053915
edge end node geometry mis-match;85718867;81053538
edge end node geometry mis-match;85724202;81046432
edge end node geometry mis-match;85726410;81057337
edge end node geometry mis-match;85727524;81044716
edge end node geometry mis-match;85727712;81045013
edge end node geometry mis-match;85728034;81045473
edge end node geometry mis-match;85729168;81050020
edge end node geometry mis-match;85729404;64691256
edge end node geometry mis-match;85732491;81062567
edge end node geometry mis-match;85734520;81063400
edge end node geometry mis-match;85735332;81062977
edge end node geometry mis-match;85735909;81059407
edge end node geometry mis-match;85737695;81060570
edge end node geometry mis-match;85738080;81063882
edge end node geometry mis-match;85738889;81052885
edge end node geometry mis-match;85738898;81064563
edge end node geometry mis-match;85739093;81064798
edge end node geometry mis-match;85739120;81064806
edge end node geometry mis-match;85740534;81065128
edge end node geometry mis-match;85741113;81065799
edge end node geometry mis-match;85742215;81045653
edge end node geometry mis-match;87127392;81058940
edge end node geometry mis-match;631741619;421382413
edge end node geometry mis-match;630520503;81062097
edge end node geometry mis-match;632936158;64716202
edge end node geometry mis-match;633012752;81048143
edge end node geometry mis-match;87294738;64689930
edge end node geometry mis-match;85695246;81043916
edge end node geometry mis-match;85701567;81048188
edge end node geometry mis-match;85702284;81048665
edge end node geometry mis-match;85703128;81049223
edge end node geometry mis-match;85705743;81050993
edge end node geometry mis-match;85710750;81054288
edge end node geometry mis-match;85711552;81054801
edge end node geometry mis-match;85711976;81055047
edge end node geometry mis-match;85720631;81051102
edge end node geometry mis-match;85724167;81049107
edge end node geometry mis-match;85724881;81043334
edge end node geometry mis-match;85725469;81059588
edge end node geometry mis-match;85730129;81061502
edge end node geometry mis-match;85731949;81062329
edge end node geometry mis-match;85733521;81063030
edge end node geometry mis-match;85734563;81063412
edge end node geometry mis-match;85736034;81059993
edge end node geometry mis-match;85736233;81064183
edge end node geometry mis-match;85737560;81061702
edge end node geometry mis-match;85738067;81063862
edge end node geometry mis-match;85738649;81063211
edge end node geometry mis-match;85738876;81059999
edge end node geometry mis-match;85739069;81064602
edge end node geometry mis-match;85741256;81065880
edge end node geometry mis-match;633317065;81061646
edge end node geometry mis-match;634869800;81045473
edge end node geometry mis-match;635513685;81054690
face without edges;0;
face within face;205862783;205862095
face within face;205862799;205862095
face within face;205865351;205862095
face within face;205874035;205862095
face overlaps face;205862471;227168671
face within face;205862735;250903961
face within face;250903028;250903961
face within face;205862896;250903961
face within face;205862789;250883064
face within face;205862817;250903961
face within face;205862819;250883064
face within face;205862823;250903961
face within face;205862881;257319799
face within face;257319799;205862881
face within face;205862882;250903961
face within face;205862887;250903961
face overlaps face;205863302;257413188
face overlaps face;205863376;229835545
face overlaps face;205864246;250883108
face overlaps face;205864660;227167582
face within face;205864956;250883064
face within face;205864957;250883064
face within face;205865321;250903961
face within face;205865345;227169008
face within face;227169008;205865345
face within face;205865352;205873116
face within face;205873116;205865352
face within face;205865363;205872319
face within face;205872319;205865363
face within face;205865373;205871833
face within face;205871833;205865373
face within face;205865395;205868775
face within face;205868775;205865395
face within face;205865958;250883064
face overlaps face;205866227;257444605
face overlaps face;205867277;229835545
face overlaps face;205867238;229835545
face overlaps face;205868159;258319348
face within face;205869028;250903961
face within face;227168330;250883064
face overlaps face;205869512;227168349
face within face;205869559;250883064
face within face;205869560;250883064
face within face;205869563;250904452
face overlaps face;205869736;250882878
face overlaps face;205870367;258108715
face overlaps face;205870367;258362605
face within face;205870672;205872411
face within face;205872411;205870672
face overlaps face;205871034;227168389
face overlaps face;205871127;227168774
face overlaps face;205871321;258108715
face overlaps face;205871321;258362605
face overlaps face;205871462;226888629
face overlaps face;205871550;227167502
face overlaps face;205872480;257444534
face overlaps face;205872480;257444535
face within face;205872795;205872797
face within face;205872797;205872795
face within face;205873814;250882844
face within face;250882844;205873814
face within face;227168331;250883064
face within face;250904453;250904452
face within face;250903962;250903961
face within face;250903029;250903961

As far as speed — it varied a lot on which box I was running it on (and was way faster on my 9.1 install compared to 9.0) so will have to revisit that. Right now just focusing on correctness - making sure generated face geometries match original face geometries of tiger and so forth.

comment:5 by aperi2007, 13 years ago

Hi,

Robe, I don't know your tiger dataset. You are using the topology ISO functions ? Or try to load directly in the tables ?

If you use the ISO function you should not see happened that validatetopology error. The ISO function should directly lock any try to insert a wrong edge.

comment:6 by robe, 13 years ago

Andrea,

Inserting directly into the tables. The TIGER structure is very much like our postgis topology structure. It has faces, nodes, edges and ids for them. I figured anyone who uses (particularly if they are migrating from some other topology databases that uses TIGER) will want to maintain the same ids as TIGER does since those ids are essentially universal across the US so they have some significance in meaning that we would lose if I used our functions to load the data.

The code I have is here in case you are interested in looking at it.

http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010/topology

comment:7 by robe, 13 years ago

Here is the document that describes the structure — http://www.census.gov/geo/www/tiger/tgrshp2010/TGRSHP10SF1AA.pdf

You'll notice that the edges, faces tables are very similar to our edge and face except diffeernt naming of columns. The connecting nodes can be inferred from the node ids of tnidf, tnidt columns in the edges table and really all the features they provide are denomalized topogeoms from these tables I believe for normal GIS consumption.

Note: See TracTickets for help on using tickets.