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: 11794
---------------------------------------------------------------------
List hosting provided by Directions Magazine | www.directionsmag.com |
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Message number: 11806
