FAQ |
Kalender |
2011-12-01, 12:07 | #11 | |||
|
||||
Klarade millennium-buggen
|
andreasp har svarat samma sak kom jag på
Senast redigerad av Westman den 2011-12-01 klockan 12:09 |
|||
Svara med citat |
2011-12-01, 15:51 | #12 | |||
|
||||
Har WN som tidsfördriv
|
Testa att byta datatyp till FLOAT.
Dina index blir väl som sagt inte så effektiva eftersom queryn ändå måste söka igenom allt för att göra beräkningar. Tyvärr är det ju även svårt att cahcea den typen av query då det är sällan man får exakt samma koordinater som indata vilket gör att det blir få cahce-träffar. Har du en valtrafikerad site kanske du kan lösa det på något sätt med någon sökmotorsmjukvara som t.ex. Solr eller ElasticSearch. Det kommer dock innebära en hel del konfigurerade. Edit igen: Det verkar finnas en del om man söker lite: http://www.google.se/search?q=Elasti...h+geo+distance http://www.elasticsearch.org/guide/r...nce-facet.html Senast redigerad av pelmered den 2011-12-01 klockan 16:01 |
|||
Svara med citat |
2011-12-01, 19:59 | #13 | ||
|
|||
Klarade millennium-buggen
|
Citat:
Sqlite har också en spatial extension men det är knappast praktiskt möjligt med de antal rader du har. |
||
Svara med citat |
2011-12-01, 21:53 | #14 | ||
|
|||
Mycket flitig postare
|
Tack alla för feedback!
Jag har kommit fram till att trimma min befintliga lösning. Ändrade datatypen till float och gjorde en avgränsning av koordinaterna i sql-frågan (med hjälp av BETWEEN och +- en viss marginal på utgångskoordinaten) för att sen finjustera med php. Resultatet blev att laddningstiden sjönk nästan med hälften. En laddningstid på drygt en sekund på de tunga sidorna känns som att jag får acceptera. De flesta sidorna har en laddningstid på 0,1 sekunder numera (mot ca 0,4 innan). Laddningstid = tid för att generera färdig html. |
||
Svara med citat |
2011-12-01, 23:41 | #15 | |||
|
||||
Bara ett inlägg till!
|
Citat:
Vore intressant om ville dela med dig av följande: * hur skapar du den rektangeln som du selekterar SQL-en med - utifrån från centrumpunkten och utvidgar den till en fyrkant med (cirkelradien * 2) som sidlängd? * hur arbetar du med finjusteringen i PHP Jag har gjort liknande saker, och en något som snabbade upp för mig var att bryta ut long + lat + "POI-id" till en egen tabell och lägga på index på long och lat. Men det beror ju lite på databasdesign & mängd data om det lönar sig. Vissa värden i sql-cirkelformeln går även att förkalkylera utanför databaslagret. Mvh Robert |
|||
Svara med citat |
2011-12-02, 08:06 | #16 | |||
|
||||
Har WN som tidsfördriv
|
Citat:
Jag skulle också tycka att det skulle vara väldigt intressant att höra lite mer om hur du gjort eller till och med få se lite kod. Gör du beräkningen och avståndet och sorteringen i (My)SQL eller PHP? Det där fungerar nog bra så länge du inte har allt för många besökare, men det är ju en sån sak som kan sänka servern vid mycket trafik. |
|||
Svara med citat |
2011-12-02, 08:09 | #17 | ||
|
|||
Mycket flitig postare
|
Absolut!
I grova drag handlar problemet om att jag vill ha ut närliggande koordinater utifrån en fast punkt. Jag började med att räkna ut hur stor en kvadrat blir som precis omger cirkeln med samma radie som jag är ute efter (ex 20 miles). En av sidorna på kvadraten är därmed 40 miles. Det längsta avståndet inom kvadraten mätt ifrån mittpunkten är till ett av hörnen och blir i detta fallet ca 28,3 miles (pythagoras sats). Andra delen i lösningen är att hitta rätt felmarginal i koordinaterna som motsvarar max 28,3 miles (jag landade på ca 29 miles enl. nedanstående exempel). Exempel (koordinaten är tagen ur luften): $lat: 30.1234 $long: -63.5552 $p: 0.35 (kom jag fram till via trial & error) ($p är "felmarginalskoordinat") Så jag körde en SELECT med dom data jag ville ha ut och körde med villkoren $sql = "SELECT LAT,LONG FROM tabellen WHERE (LAT BETWEEN ".($lat-$p)." AND ".($lat+$p).") AND (LONG BETWEEN ".($long-$p)." AND ".($long+$p).")"; Därefter kör jag en avståndsberäkning mellan svaren och min fasta punkt och sorterar med PHP enl. tex: http://www.web-max.ca/PHP/misc_2.php Efter sorteringen kan jag enkelt sortera bort dom som ligger utanför cirkelradien. Ungefär så... Citat:
Senast redigerad av gregoff den 2011-12-02 klockan 08:16 |
||
Svara med citat |
2011-12-02, 11:45 | #18 | ||
|
|||
Klarade millennium-buggen
|
Jag la upp en test i SQL-server 2008 R2 för att jämföra prestanda (och lära mig någt nytt om Global positionering). Jag upplever dock inget problem med prestandan. De skillnader jag har är att LONG och LAT är float som datatyper, satte upp två sammansatta index (IDX_LONGLAT(LONG,LAT) och IDX_LATLONG(LAT,LONG)) och att jag la upp SQL-frågorna som Stored Procedures, det går blixtsnabbt när jag kör frågan.
Först script för att skapa tabellen: Kod:
USE [wntest] GO /****** Object: Table [dbo].[geographic] Script Date: 12/02/2011 11:38:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[geographic]( [GEOGRAPHIC_ID] [int] NOT NULL, [LONG] [float] NOT NULL, [LAT] [float] NOT NULL, CONSTRAINT [PK_geographic] PRIMARY KEY CLUSTERED ( [GEOGRAPHIC_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO Kod:
/****** Object: StoredProcedure [dbo].[CreateRandomPositions] Script Date: 12/02/2011 11:38:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CreateRandomPositions] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @start INT DECLARE @stop INT DECLARE @count INT DECLARE @seed float SET @count = 0 SET @start = 1 SET @seed = RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ); set @stop=@start+400000 WHILE (@count <= @stop) BEGIN INSERT INTO [dbo].[geographic] ([GEOGRAPHIC_ID],[LONG], [LAT]) VALUES ((SELECT ISnull(MAX([GEOGRAPHIC_ID]),1) as c from geographic)+1,RAND()*180.0-90.0, RAND()*180.0-90.0) set @count = @count + 1 END END GO Stored procedure för att få fram det du ville ha: Kod:
/****** Object: StoredProcedure [dbo].[GetDist] Script Date: 12/02/2011 11:38:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetDist] -- Add the parameters for the stored procedure here @pLong float, @pLat float, @pDist float AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Justera FLOAT set @Justera = 0.35 SELECT * FROM ( SELECT *, ( 3959 * ACOS( COS( RADIANS(@pLong) ) * COS( RADIANS( LAT ) ) * COS( RADIANS( LONG ) - RADIANS(@pLat) ) + SIN( RADIANS(@pLong) ) * SIN( RADIANS( LAT ) ) ) ) AS distance FROM geographic -- Extra urvalsvillkor för att snabba upp frågan WHERE LAT > (LAT-@Justera) AND LAT < (LAT+@Justera) AND LONG > (LONG-@Justera) AND LONG < (LONG+@Justera) ) AS geo WHERE distance < @pDist ORDER BY distance END GO Jag var tvungen att först skapa testdata med min sp för detta: Kod:
EXEC CreateRamdomPositions; Kod:
EXEC GetDist -84.093177, 35.922934, 50; Kod:
GEOGRAPHIC_ID LONG LAT distance 231748 36,2256386076705 -84,0477825755256 3,80884213288603 20005 35,7445105631688 -84,033263190999 4,3318317124107 186701 35,424433416423 -84,0558823440571 4,39150040757033 108005 36,5425297929339 -84,0921525164496 4,40681230355622 78602 35,5652975894876 -84,1533625394946 4,86788851297135 . . . 164845 29,1025556118211 -84,297305469497 49,6733354458806 169569 33,57303607886 -83,4206588728788 49,7015643727222 249101 42,920426204891 -84,214341976377 49,9240068355831 116678 31,319839796601 -83,5653389912852 49,9637363476681 352070 42,7163354066425 -84,3256493787583 49,9748637448209 En klar rekommendation är att du kör så mycket som möjligt av urvalet i SQL så att systemet behöver skyffla så lite data som möjligt. SQL-motorn är mycket effektiv och har en hel del automatiska optimeringar som man slipper tänka på som utvecklare. Jag har tidigare kört MySQL med den riktiga SQL-motorn (dvs ej ISAM som är en extremt gammal teknik) och nu går det ju från version 5 att köra SP i MySQL. När jag testar performancetiderna med följande test: Kod:
declare @stop Datetime declare @start Datetime set @start = GetDate() EXEC GetDist -84.093177, 35.922934, 50; set @stop = GetDate() SELECT DateDiff(ms,@start,@stop) AS PerformanceTime Efter att ha lagt till det extra urvalsvillkoret med en kvadrat innan den noggranna avståndskalkylen så ÖKADE körningstider till tider som överstiger 2200 millisekunder. Med 50 miles avståndskrav. Det tog m a o längre tid. Jag vet inte vad det beror på ännu. Senast redigerad av Conny Westh den 2011-12-02 klockan 16:47 |
||
Svara med citat |
2011-12-02, 11:55 | #19 | |||
|
||||
Har WN som tidsfördriv
|
Okej, tack!
Om man vill variera radien borde konstanten vara någonstans runt 0,009 per km om man kollar här: http://en.wikipedia.org/wiki/Latitude#Degree_length 1/112(för att vara säker på att få med allt) ~ 0,008 per km Konstanten kan man ju anpassa en del och ha olika för latituden och longituden efter vart man är för longituden varierar ju väldigt mycket beroende på vart man är. |
|||
Svara med citat |
2011-12-03, 12:12 | #20 | ||
|
|||
Mycket flitig postare
|
Kan nämna att jag fick ner laddningstiden till i snitt 0.15 sekunder. Lösningen var att fixa ett nybörjarmisstag...
Det var inte databassökningarna som var problemet. Problemet var PHP. Jag tänkte inte på att avgränsa mina sql-frågor, så vissa frågor gav 30.000 rader som svar, och det tar en stund för PHP att bearbeta (att skicka från databasen till en variabel i php antar jag). I och med att jag aldrig vill ha mer än 20 svar i min lista, så körde jag en LIMIT 0,20 på sql-frågan och vips var laddningstiden ner till en tiondel på de tyngsta sidladdningarna. Ifall att någon ville veta ;-) |
||
Svara med citat |
Svara |
|
|