Assuming you've done the following (which it sounds like you've done): 1. Update the lon and lat columns with centroidX and centroidY 2. Make the group-by query (group by lat and lon. 3. Make the query where count>1
You should then: 3. Save the result of query 2 to its own table (we'll call it DUPLOCS.TAB). 4. Close the query tables generated in steps 1 and 2, then open the table you saved in step 3 (DUPLOCS). 5. If the original table has 10000 records or more, index its lat column, and the lat column of DUPLOCS. 6. Perform a SQL Select: select * from ORIGTAB, DUPLOCS where (origtab.lon=duplocs.lon) and (origtab.lat=duplocs.lat) Viola'! Hope this helps Spencer -----Original Message----- From: Robert DeRubeis [mailto:[EMAIL PROTECTED] Sent: Monday, May 17, 2004 12:02 PM To: [EMAIL PROTECTED] Cc: Kir Luong; Peter Horsb�ll M�ller Subject: Re: MI-L points with same lat/lon I've done this, and found that I have 100's of duplications of 2 or more, but I need all of the records that are duplicates. When a count query is done it does not select all records with matching lat/lon's. It returns, for example, Count Lat Lon 6 49.715709 -123.156264 5 49.709248 -124.916418 4 43.745795 -79.526276 etc, etc.. There are 15 records above with the same lat/lon. I need to be able to select all 15 records? -Bob "Kir Luong" <[EMAIL PROTECTED]> writes: >If the lat or long is the same then a count and group by function on >those variables should give you the records that are identical (group by >clause) and how many duplication (count function): >I'm not sure the mapinfo SqL...but something like: > >Select count(*),lat >from table1 >group by lat >having count(*) > 1; >execute; ===> returns a table of all lats that are duplicated and the >number of duplication > >likewise for the longs. Hope this helps. > >kir. > >-----Original Message----- >From: Robert DeRubeis [mailto:[EMAIL PROTECTED] >Sent: Friday, May 14, 2004 3:03 PM >To: [EMAIL PROTECTED] >Subject: MI-L points with same lat/lon > > >List- >Is there a way to select all records from one table where the values in 2 >different columns are identical? I wanted to select all records where the >lat of 1st record = lat of 2nd record AND lon of 1st record = lon of 2nd >record. I'm trying to identify where and how many points are right on top >of each other and if they need to be dispersed or better geocoding. > >The table has about 30,000 points and I'm using MIPro 6.5. >Thanks, >-Bob > > >--------------------------------------------------------------------- >List hosting provided by Directions Magazine | www.directionsmag.com | >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >Message number: 11767 > > >This communication is intended for the use of the recipient to which it >is addressed, and may contain confidential, personal and or privileged >information. Please contact us immediately if you are not the intended >recipient of this communication, and do not copy, distribute, or take >action relying on it. Any communication received in error, or subsequent >reply, should be deleted or destroyed --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11790 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11792
