OT: Wordhjälp

[geologen]

Supertramp skrev:
-------------------------------------------------------
> JT skrev:
> --------------------------------------------------
> -----
> > Supertramp skrev:
> >
> --------------------------------------------------
>
> > -----
> > > Genom funktionen
> > > MIN((A1:A1000-X)^2 + (B1:B1000-Y)^2 )
> > > kan jag räkna ut avståndet till närmaste
> > > plats.
> >
> > Den funktionen räknar ju dock inte ut rätt
> > avstånd eftersom koordinaterna är på en
> sfär.
>
> Exakt avstånd är inte så viktigt. Jag vill
> identifiera platser som är samma men rapporterade
> med viss osäkerhet i GPS-data.

Hmm, men hur åtskilda är platserna då? För en enkel lösning skulle kunna vara att dela positionsvärdena med t ex 10, avrunda till heltal och sen se vilka platser som får samma värden. Om det inte är särskiljande nog kanske du kan börja med att dela med 100, och göra en grovsortering den vägen. Det bör hitta alla platser som teoretiskt kan vara identiska (+/- 50 m). I den mindre gruppen kan du sen göra en mer detaljerad sortering.

Ett annat alternativ är att, efter en grovsortering, plotta punkterna grafiskt och med ögat se vilka som överlappar. Om det bara förväntas vara ett fåtal, kanske hundra eller färre, lär det gå snabbt att hitta de som är samma position med hänsyn till mätfel.

 

[perzzon]

Då låter det helt plötsligt som om man skulle kunna använda någon sorts statistik-funktion.
Regression heter det väl?

Sen tog fragmenten av utbildningen slut :)

 

[Fliken]

JT skrev:
-------------------------------------------------------
> Fliken skrev:
> --------------------------------------------------
> -----
> > Excel är ett förenklat och guifierat SQL ;-)
>
> Nä det är det inte.

Det riktiga svaret är "det beror på". Det beror på betraktaren. Är det användaren eller utvecklaren?

 

[JockeP]

Ett ganska simpelt makro fixade biffen. Tyvärr kraschar Excel under tunga körningar så det är inte speciellt praktiskt då TS hade +20000 unika ställen i listan.
En lite intelligent grovrensning enligt ovan är nog ingen dum idé.

Här är makrot:

Sub PopulateClosestSite_ID()
'
' PopulateClosestSite_ID Macro
' Locates the closest Site_ID for each other Site_ID.
' Run for 10 rows at a time, Excel VBA crashes if too much to execute

'


Dim X, Y, minDistance, minPlace, placeX, placeY, dist, inner_row, outer_row, start_row
start_row = ActiveCell.row

outer_row = start_row
'Loop through all rows that have SITE_ID value

Do Until IsEmpty(Cells(outer_row, 1)) Or (outer_row > start_row + 10)

X = Cells(outer_row, 2).Value
Y = Cells(outer_row, 3).Value

'Loop through all rows to find the closest
inner_row = 2
minDistance = 0
minPlace = Empty

Do Until IsEmpty(Cells(inner_row, 1))

placeX = Cells(inner_row, 2).Value
placeY = Cells(inner_row, 3).Value

dist = Math.Sqr((placeX - X) ^ 2 + (placeY - Y) ^ 2)

If (minPlace = Empty) And (dist > 0) Then
'First row, asign new values
minDistance = dist
minPlace = Cells(inner_row, 1).Value

End If

If (dist < minDistance) And (dist > 0) Then
minDistance = dist
minPlace = Cells(inner_row, 1).Value
End If

inner_row = inner_row + 1
Loop

Cells(outer_row, 6).Value = minDistance
Cells(outer_row, 5).Value = minPlace

outer_row = outer_row + 1
Loop
Cells(outer_row, 1).Select

End Sub



Känner man för att strunta i 10-åt-gången så fimpa bara vilkoret "Or (outer_row > start_row + 10)".

 

[TBone]

Supertramp skrev:
-------------------------------------------------------
> Exakt avstånd är inte så viktigt. Jag vill
> identifiera platser som är samma men rapporterade
> med viss osäkerhet i GPS-data.

Aha, så problemet var alltså inte att hitta kortaste avståndet mellan en plats och alla platser i en annan lista? Det var att hitta "dubletter" i inexakt mätdata, eller?

Om det är att hitta dubletter kan "plats A" bara förekomma en gång "jämföreslelistan" eller kan den förekomma flera gånger? Har varje plats i "lista A" (minst) en motsvarande plats i "lista B"?

 

[Supertramp]

geologen skrev:
-------------------------------------------------------
> Hmm, men hur åtskilda är platserna då? För en
> enkel lösning skulle kunna vara att dela
> positionsvärdena med t ex 10, avrunda till heltal
> och sen se vilka platser som får samma värden.
> Om det inte är särskiljande nog kanske du kan
> börja med att dela med 100, och göra en
> grovsortering den vägen. Det bör hitta alla
> platser som teoretiskt kan vara identiska (+/- 50
> m). I den mindre gruppen kan du sen göra en mer
> detaljerad sortering.


Jag har gjort typ så. Funkar hyfsat men krångligt och tidskrävande.
Med tusentals punkter över hela landet kan det vara många punkter som har nästan samma X men ligger i helt olika landsdelar (eller tvärt om).

> Ett annat alternativ är att, efter en
> grovsortering, plotta punkterna grafiskt och med
> ögat se vilka som överlappar. Om det bara
> förväntas vara ett fåtal, kanske hundra eller
> färre, lär det gå snabbt att hitta de som är
> samma position med hänsyn till mätfel.

Skulle också funka men jag skulle vilja hitta en snabbare lösning då det är väldigt mycket data.

 

[Supertramp]

TBone skrev:
-------------------------------------------------------
> Aha, så problemet var alltså inte att hitta
> kortaste avståndet mellan en plats och alla
> platser i en annan lista? Det var att hitta
> "dubletter" i inexakt mätdata, eller?

Japp.
Jag vill indentifiera platser som ligger nära varandra, för att sedan vidare kontrollera om det kan vara samma plats.

> Om det är att hitta dubletter kan "plats A" bara
> förekomma en gång "jämföreslelistan" eller kan
> den förekomma flera gånger? Har varje plats i
> "lista A" (minst) en motsvarande plats i "lista
> B"?

Det beror lite på. Ibland är det bara en stor lista jag vill leta dubbletter i (kan finnas fler än en). I andra fall vill jag kolla en lista mot en annan för att se om några platser kan vara samma.

 

[TBone]

Stoppa in repektive lista i två tabeller i någon sql-databas:
SELECT * FROM
ListaA join
ListaB
on ABS(ListaA.x - ListaB.x) < @Felmarginal AND ABS(ListaA.y - ListaB.y) < @Felmarginal

Där @Felmarginal är din tolerans. Sen kan du stoppa in resultatet i excel och formatera sjuukt snyggt...

 

[fhloston]

Ovan borde kanske funka i access, som beroende på licens ingår i office.

 

[JockeP]

Jag testade att implementera samma algoritm i Visual Studio, C# och då tog beräkningen ca 2 minuter för alla +20000 rader eller ca 100 ggr snabbare. Excel är inte helt optimalt för sådana här övningar.

 

[Fliken]

Excel är kanon för att betatesta en idé. Transparant. Intuitivt. Sen skall den implementeras i stabilare miljö för produktionsversionen. Det är inte Excel det.

 

[JockeP]

Fast det hade gräsligt coola påskägg i versionerna åren runt y2k. Värsta 3D äventyret i bästa Wolfenstein-stil.
Finns det fortfarande påskägg i office-sviten?

 

[Kupa]

Det var visst många X och Y som skulle processas... Med den här koden får jag svar (10000 X-punkter, 20000 Y-punkter) på ungefär 1.5 sekunder i Matlab på en skapligt modern dator. Fult med en loop, men förhoppningsvis är det snabbt nog...

D(1:nX) blir avstånd från varje X-punkt X(1:nX,:) till närmaste punkt i Y, och J(1:nX) berättar vilken rad i Y som var närmast.

rng(0) % samma seed till slumptalsgeneratorn varje gång

% Indata, slumpade X och Y
nX = 10000;
nY = 20000;
X = rand(nX,2);   
Y = rand(nY,2);   

% Utdata (pre-allokeras)
D = zeros(nX,1);
J = zeros(nX,1);

tic
for k=1:nX
  [D(k), J(k)] = min( sum( bsxfun(@minus, Y, X(k,:)).^2 , 2) ); 
end
t_elapsed = toc

 

[Kupa]

Det tar längre tid (tiotals sekunder) om man vill plocka ut t.ex. n st närmaste eller alla som är inom någon speciell tolerans, men det går det också... (om jag får posta det här... det är inte spam).