Thanks Peter. That is more along the lines of what I was expecting. It works great. Very cool. -Bob
Peter Horsb�ll M�ller <[EMAIL PROTECTED]> writes: >Hi Robert, > >I'm not sure whether you saw this solution I sent before. I just checked >and found some errors in it, but it works, without saving any queries ! > >Grouping all reocrds by LAT and long, and counting the number of records >at the same location: >Select LAT, LONG, LAT + "#" + LONG "LAT_LONG", Count(*) "NUMPOINTS" > From <MYTABLE> > Group By LAT, LONG > Order By NUMPOINTS Desc > ><MYTABLE> is the name of the table with the recoords > >To select the points where more than 1 point is found in the same >location, enter the following in SQL Select > >Select * > From <MYTABLE> > Where (LAT + "#" + LONG) In (Select LAT_LONG From Selection Where >NUMPOINTS > 1) > >Now you should have a selection of all points that were located were at >least one other point also was located > >Peter Horsb�ll M�ller >GIS Developer >Geographical Information & IT > >COWI A/S >Odensevej 95 >DK-5260 Odense S. >Denmark > >Tel +45 6311 4900 >Direct +45 6311 4908 >Mob +45 5156 1045 >Fax +45 6311 4949 >E-mail [EMAIL PROTECTED] >http://www.cowi.dk > > >-----Original Message----- >From: Robert DeRubeis [mailto:[EMAIL PROTECTED] >Sent: Monday, May 17, 2004 6:47 PM >To: [EMAIL PROTECTED] >Subject: Re: MI-L points with same lat/lon > > >Saving the count query and the last step was what I was missing and it >worked fine. I thought it wouldn't take so many steps and having to save >query results, etc., but better more steps with it being able to be done, >than it can't do it. Thanks to all -Bob > > >"Spencer Simpson" <[EMAIL PROTECTED]> writes: >>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: 11819
