Hi Kent, Seems to be that you should attempt to generate a dataset with non-overlapping intervals, i.e. duplicate "St. Paul" into (at least) two intervals, 55101-55112 and 55114-55195 (as 5 digit zips).
The way to proceed is to build some helper tables, but as MIPro's SQL syntax is somewhat limited, it may prove to be more work than is reasonable. An mbx would most likely be a better choice. I've made an attempt at basic MIPro scripting nevertheless. The idea is to split intervals whenever another interval lies within an outer interval. It even works if several levels of intervals are embedded within each other, but most likely will turn out garbage if interval partially overlaps. It didn't work at first because of the way MIPro handles selections. The only way to circumvent this was to save the selections STARTS and ENDS as temporary base tables. The input consists of a single table ORIG with three columns: NAME (text) and ZIP1 and ZIP2 (integers). The NAME contains the zip area name, while the ZIP1-ZIP2 defines the interval. You should convert your zips values to either 5 or 9 digit zip integers. The column DUMMY is added to enable faking outer/cross joins, which normally isn't supported in MIPro. The part between LOOP BEGINS and LOOP ENDS are run as many times as is necessary (probably only a few times), stopping when the selection SPLIT turns up empty. The statements before the loop are to be run once before running loops, the statements after are run once after running loops. Just copy'n'paste into the MapBasic window to run the relevant statements, replacing names as needed. Hope it works on (a copy of) your data. ' -- PROCEDURE BEGINS -- Alter Table ORIG (Add DUMMY Integer) 'leave the value to be zero at all times ' -- LOOP BEGINS -- Commit Table ORIG as "orig2.tab" Open Table "orig2.tab" As ORIG2 SELECT ORIG.NAME FROM ORIG, ORIG2 WHERE (ORIG2.DUMMY = ORIG.DUMMY) AND (ORIG2.ZIP1 BETWEEN ORIG.ZIP1 AND ORIG.ZIP2) AND (ORIG2.NAME <> ORIG.NAME) INTO SPLIT BROWSE * from SPLIT ' -- STOP if no rows in SPLIT !!! SELECT ORIG.NAME, ORIG.ZIP1, ORIG2.ZIP1-1 "ZIP2" FROM ORIG, ORIG2 WHERE (ORIG.DUMMY = ORIG2.DUMMY) AND (ORIG2.ZIP1 BETWEEN ORIG.ZIP1 AND ORIG.ZIP2) AND (ORIG.NAME <> ORIG2.NAME) AND (ORIG.ZIP1 < ORIG2.ZIP1) INTO STARTS SELECT ORIG.NAME, ORIG2.ZIP2+1 "ZIP1", ORIG.ZIP2 FROM ORIG, ORIG2 WHERE (ORIG.DUMMY = ORIG2.DUMMY) AND (ORIG2.ZIP2 BETWEEN ORIG.ZIP1 AND ORIG.ZIP2) AND (ORIG.NAME <> ORIG2.NAME) AND (ORIG2.ZIP2 < ORIG.ZIP2) INTO ENDS ' -- work-around: save and re-open selections as permanent base tables to prevent clearing Commit Table STARTS as "starts.tab" Commit Table ENDS as "ends.tab" Close Table STARTS Close Table ENDS Open Table "starts.tab" as STARTS Open Table "ends.tab" as ENDS SELECT * FROM ORIG WHERE NAME IN (SELECT NAME FROM STARTS) DELETE FROM SELECTION SELECT * FROM ORIG WHERE NAME IN (SELECT NAME FROM ENDS) DELETE FROM SELECTION Insert Into ORIG ( NAME, ZIP1, ZIP2) Select NAME, ZIP1, ZIP2 From STARTS Insert Into ORIG ( NAME, ZIP1, ZIP2) Select NAME, ZIP1, ZIP2 From ENDS Close Table SPLIT Close Table STARTS Close Table ENDS Close Table ORIG2 Commit Table ORIG ' -- LOOP ENDS -- Alter Table ORIG (Remove DUMMY) ' -- PROCEDURE ENDS -- Best regards/Med venlig hilsen Lars V. Nielsen GisPro, Denmark http://www.gispro.dk/ ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[email protected]> Sent: Friday, June 24, 2005 9:24 PM Subject: MI-L Compressing numerically sequential rows I am attempting to compress 1.2 million ZIP plus 4 rows into fewer rows based on the city with which they are associated. I have an mbx that will provide the range for each city, i.e. St. Paul, 55101-0001 to 55195-9998, however the range is inclusive of ZIP+4's that are not in St. Paul, 55113-0001. I think I have an idea of how this can work, however, the spirit is willing but the coding is weak. I sorted the file by ZIP+4 and then updated recnum with rowid, which means when St. Paul ZIP4's are selected, those not contiguous breaks in the data will show up as non-sequential recnums. That is as far as I've gotten. I am not looking for a completely written mbx, maybe a few pointers or even snippets of code I can run in the MapBasic window. Thank you. ____________ Kent Treichel Analyst-GIS Tax Research Minnesota Department of Revenue MS 2230 600 North Robert St St. Paul, MN 55146-2230 651-556-6150 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 16944
