#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 , 13 years ago
| Resolution: | → fixed |
|---|---|
| Status: | new → closed |
comment:2 by , 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 , 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 , 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 , 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 , 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 , 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.
fixed at r7823
We should probably add a regress test for this function.