yeah MySQL, you are my BITCH - GoFuckYourself.com

borked · 12-14-2008, 03:29 PM

well, she was winning for a while, but she can be tamed...

SELECT *, AsText(geometry)
FROM geo_data
WHERE

Intersects( GeomFromText('POLYGON((43.3723651907 5.4,43.2999559362 5.49943509666,43.2276328093 5.40000000532,43.2999559284 5.30056490336,43.3723651907 5.4))'),geometry )

ORDER BY

SQRT(POW( ABS( X(geometry) - X( GeomFromText('POINT(43.3723651907 5.4)') )), 2) + POW( ABS(Y(geometry) - Y( GeomFromText('POINT(43.3723651907 5.4)') )), 2 ))

OK, sorry you had to read this, but sometimes when you fight for so long, winning is sooooo sweeeet.

.

As you were

seeandsee · 12-14-2008, 03:31 PM

I see your geo sig working

fris · 12-14-2008, 03:36 PM

thats one big sql statement

Deej · 12-14-2008, 03:41 PM

Sweet! Fight the Machine!

rowan · 12-14-2008, 03:53 PM

I tried that statement on my server and it melted

CaptainHowdy · 12-14-2008, 04:05 PM

Yeah, baibiiiiiiiiiii!

fallenmuffin · 12-14-2008, 04:10 PM

Your sig is looking good. By far the closest GeoIP locations then I've seen. Very good work.

borked · 12-14-2008, 04:11 PM

Quote:

Originally Posted by fris

thats one big sql statement

yaar,
6668045 rows in total
the sql query:
Showing rows 0 - 13 (14 total, Query took 0.0042 sec)

mysql spatial indices kick frikken ass!

Sad think is, I'm just amusing myself, trying to tweak 'nearby' searches to be faster and more accurate. This did it but some more coding before it goes live.

Sad geeks

borked · 12-14-2008, 04:15 PM

and those paying attention would point out to me my error:

SQRT(POW( ABS( X(geometry) - X( GeomFromText('POINT(43.3723651907 5.4)') )), 2) + POW( ABS(Y(geometry) - Y( GeomFromText('POINT(43.3723651907 5.4)') )), 2 ))

those POINT() values should be the center of the polygon, not the top left corner, as shown there

borked · 12-14-2008, 04:54 PM

http://www.geoplugin.net/extras/near...xml&radius=100

All the nearest populated places to you within a 100 mile radius
XML output

Mission accomplished

.

Bro Media - BANNED FOR LIFE · 12-14-2008, 06:41 PM

Nice job man! keep it up and I may replace using my own geo database from maxmind with your solution in my products :D

mrkris · 12-14-2008, 10:25 PM

The real question is, how long does it take to run.

Mutt · 12-14-2008, 10:36 PM

borked are you freelancing these days?

Antonio · 12-14-2008, 10:53 PM

I was more impressed with your sig when it gave me my location in Cyrillic (which is what we use), now it's all Latin

borked · 12-15-2008, 12:07 AM

Quote:

Originally Posted by Antonio

I was more impressed with your sig when it gave me my location in Cyrillic (which is what we use), now it's all Latin

Antonio - can you give me the ascii/latin name and the cyrillic name of an example? I changed the place name column encoding to be utf8, which may have screwed things up in your case.

That way I can search the database for your example and find out what changed during he modificaitons.

Cheers

borked · 12-15-2008, 12:16 AM

Quote:

Originally Posted by mrkris

The real question is, how long does it take to run.

Table info:
geo_data 6,668,045 INNODB latin1_swedish_ci 1.5 GiB

On a query with a polygon covering 100 miles around my location
query:

SELECT name, country, region, latitude, longitude
FROM geo_data
WHERE place_type = 'P'
AND place_code = 'PPL'
AND alternate_names != ''
AND INTERSECTS( GEOMFROMTEXT( 'POLYGON((44.7473228148 5.4,43.2827760916 7.38832741803,41.8526751852 5.40000010412,43.2827759366 3.41167258704,44.7473228148 5.4))' ) , GEOMETRY )
ORDER BY SQRT( POW( ABS( X( GEOMETRY ) - X( GEOMFROMTEXT( 'POINT(43.299999 5.400000)' ) ) ) , 2 ) + POW( ABS( Y( GEOMETRY ) - Y( GEOMFROMTEXT( 'POINT(43.299999 5.400000)' ) ) ) , 2 ) )
LIMIT 0 , 30

Run time:
Showing rows 0 - 29 (2,468 total, Query took 0.0826 sec)

ooooh yeah

Antonio · 12-15-2008, 12:19 AM

Quote:

Originally Posted by borked

Antonio - can you give me the ascii/latin name and the cyrillic name of an example? I changed the place name column encoding to be utf8, which may have screwed things up in your case.

That way I can search the database for your example and find out what changed during he modificaitons.

Cheers

here's one

borked · 12-15-2008, 12:22 AM

Quote:

Originally Posted by Retox Josh

Nice job man! keep it up and I may replace using my own geo database from maxmind with your solution in my products :D

I have a table that converts the region in maxmind's geoip db to a proper region name - maxmind's works fine for the US, but outside that, it's just codes (mine is B8). The nearby search replaces these codes (ie FR.B8 for me) with the real name (ie Provence-Alpes-C'ôte-d'Azur)

Now that the code lookup/replace works on the nearby stuff, I'll be swapping them out in the geoip lookups next.

And fixing this cyrillic/latin swap that happened. That's the problem with these databases - if you aren't in that locality, it's a bitch to try and squash bugs cos you never see them!

fuzebox · 12-15-2008, 12:26 AM

I'm sure it'll run great when you toss it on a site doing 300k/day...

borked · 12-15-2008, 12:28 AM

Quote:

Originally Posted by Antonio

here's one

grrr damn - ok I see it

eg:
Sarafovo Sarafovo Antim I,Paparos,Papure,Sarafovo,Sarefovo,Сарафово
Saraevo Saraevo 3333,Saraevo,Saraewo,Сараево
Sanur Dere Sanur Dere Sanur Dere,Санър Дере

the names got moved over to alternate names column. Damn damn - not easy to reverse, but I'll try

borked · 12-15-2008, 12:31 AM

Quote:

Originally Posted by fuzebox

I'm sure it'll run great when you toss it on a site doing 300k/day...

geoplugin.net is already getting >million/day hits. I honestly shit you not
top - 23:29:53 up 12 days, 10:55, 1 user, load average: 0.24, 0.30, 0.27

have a look at the bottom of the html source on any of evilangel's sites if you don't believe it is getting hammered.

load-balanced server with a powerful beast running mysql

thehand · 12-15-2008, 01:32 AM

Quote:

Originally Posted by borked

geoplugin.net is already getting >million/day hits. I honestly shit you notl

only one million?

jk - your hamster style is impressive

V_RocKs · 12-15-2008, 01:50 AM

I fucked her in her arse.

borked · 12-15-2008, 09:28 AM

Quote:

Originally Posted by Antonio

I was more impressed with your sig when it gave me my location in Cyrillic (which is what we use), now it's all Latin

How's it working for you now Antonio? Should be back to Cyrillic...

borked · 12-15-2008, 09:28 AM

Quote:

Originally Posted by Mutt

borked are you freelancing these days?

In the new year hopefully

alias · 12-15-2008, 09:30 AM

borked is a hacker

HorseShit · 12-15-2008, 09:36 AM

i love mysql

borked · 12-15-2008, 09:38 AM

Quote:

Originally Posted by alias

borked is a hacker

say what now

gooddomains · 12-15-2008, 09:38 AM

job well done

alias · 12-15-2008, 09:39 AM

Used as a term of endearment.

bloggingseo · 12-15-2008, 10:58 AM

Your sig is damn impressive! Some of the smallest no people having places around on there

tranza · 12-15-2008, 11:21 AM

Looks like Query analyzer error.

borked · 12-15-2008, 11:24 AM

Quote:

Originally Posted by bloggingseo

Your sig is damn impressive! Some of the smallest no people having places around on there

Thanks

Follow the link in the sig - the script is available for free. GPL license.

borked · 12-15-2008, 11:31 AM

Quote:

Originally Posted by tranza

Looks like Query analyzer error.

The thing is throwing some error? or you mean the statemtnt looks like an error?!!

I'm interested in knowing how the nearby stuff works for you, from brazil...
Thanks for the feedback

12-14-2008, 03:29 PM	#1
borked Totally Borked Industry Role: Join Date: Feb 2005 Posts: 6,284	yeah MySQL, you are my BITCH well, she was winning for a while, but she can be tamed... SELECT , AsText(geometry) FROM geo_data WHERE Intersects( GeomFromText('POLYGON((43.3723651907 5.4,43.2999559362 5.49943509666,43.2276328093 5.40000000532,43.2999559284 5.30056490336,43.3723651907 5.4))'),geometry ) ORDER BY SQRT(POW( ABS( X(geometry) - X( GeomFromText('POINT(43.3723651907 5.4)') )), 2) + POW( ABS(Y(geometry) - Y( GeomFromText('POINT(43.3723651907 5.4)') )), 2 )) OK, sorry you had to read this, but sometimes when you fight for so long, winning is sooooo sweeeet. . As you were __________________ For coding work - hit me up on andy // borkedcoder // com (consider figuring out the email as test #1) All models are wrong, but some are useful.* George E.P. Box. p202

12-14-2008, 03:31 PM	#2
seeandsee Check SIG! Industry Role: Join Date: Mar 2006 Location: Europe (Skype: gojkoas) Posts: 50,945	I see your geo sig working __________________ BUY MY SIG - 50$/Year Contact here

12-14-2008, 03:36 PM	#3
fris Too lazy to set a custom title Industry Role: Join Date: Aug 2002 Posts: 55,289	thats one big sql statement __________________ Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence. WP Stuff

12-14-2008, 03:41 PM	#4
Deej I make pixels work Industry Role: Join Date: Jun 2005 Location: I live here... Posts: 24,386	Sweet! Fight the Machine! __________________ Deej's Designs n' What Not Hit me up for Design, CSS & Photo Retouching Icq#30096880

12-14-2008, 04:05 PM	#6
CaptainHowdy Too lazy to set a custom title Industry Role: Join Date: Dec 2004 Location: Happy in the dark. Posts: 93,082	Yeah, baibiiiiiiiiiii! __________________ FLASH SALE INSANITY! deal with a 100% Trusted Seller Buy Traffic Spots on a High-Quality Network 1 Year or Lifetime — That’s Right, Until the Internet Explodes!

12-14-2008, 03:53 PM	#5
rowan Too lazy to set a custom title Join Date: Mar 2002 Location: Australia Posts: 17,393	I tried that statement on my server and it melted

12-14-2008, 04:10 PM	#7
fallenmuffin Confirmed User Industry Role: Join Date: Nov 2005 Posts: 8,170	Your sig is looking good. By far the closest GeoIP locations then I've seen. Very good work.

12-14-2008, 04:15 PM	#9
borked Totally Borked Industry Role: Join Date: Feb 2005 Posts: 6,284	and those paying attention would point out to me my error: SQRT(POW( ABS( X(geometry) - X( GeomFromText('POINT(43.3723651907 5.4)') )), 2) + POW( ABS(Y(geometry) - Y( GeomFromText('POINT(43.3723651907 5.4)') )), 2 )) those POINT() values should be the center of the polygon, not the top left corner, as shown there __________________ For coding work - hit me up on andy // borkedcoder // com (consider figuring out the email as test #1) All models are wrong, but some are useful. George E.P. Box. p202

12-14-2008, 04:54 PM	#10
borked Totally Borked Industry Role: Join Date: Feb 2005 Posts: 6,284	http://www.geoplugin.net/extras/near...xml&radius=100 All the nearest populated places to you within a 100 mile radius XML output Mission accomplished . __________________ For coding work - hit me up on andy // borkedcoder // com (consider figuring out the email as test #1) All models are wrong, but some are useful. George E.P. Box. p202

12-14-2008, 06:41 PM	#11
Bro Media - BANNED FOR LIFE MOBILE PORN: IMOBILEPORN Join Date: Jan 2004 Location: Tinseltown NL Posts: 16,502	Nice job man! keep it up and I may replace using my own geo database from maxmind with your solution in my products :D

12-14-2008, 10:25 PM	#12
mrkris Confirmed User Join Date: May 2005 Posts: 2,737	The real question is, how long does it take to run. __________________ PHP-MySQL-Rails \| ICQ: 342500546

12-14-2008, 10:36 PM	#13
Mutt Too lazy to set a custom title Industry Role: Join Date: Sep 2002 Posts: 34,431	borked are you freelancing these days? __________________ I moved my sites to Vacares Hosting. I've saved money, my hair is thicker, lost some weight too! Thanks Sly!

12-14-2008, 10:53 PM	#14
Antonio Too lazy to set a custom title Join Date: Oct 2001 Location: Spartaaaaaaaaa Posts: 14,136	I was more impressed with your sig when it gave me my location in Cyrillic (which is what we use), now it's all Latin

12-15-2008, 12:26 AM	#19
fuzebox making it rain Industry Role: Join Date: Oct 2003 Location: seattle Posts: 22,021	I'm sure it'll run great when you toss it on a site doing 300k/day...

12-15-2008, 01:50 AM	#23
V_RocKs Damn Right I Kiss Ass! Industry Role: Join Date: Dec 2003 Location: Cowtown, USA Posts: 32,392	I fucked her in her arse.

12-15-2008, 09:30 AM	#26
alias aliasx Join Date: Apr 2001 Posts: 19,010	borked is a hacker __________________ https://porncorporation.com

12-15-2008, 09:36 AM	#27
HorseShit Too lazy to set a custom title Join Date: Dec 2004 Posts: 17,513	i love mysql

12-15-2008, 09:38 AM	#29
gooddomains Too lazy to set a custom title Join Date: Jul 2003 Location: Netherlands Posts: 10,127	job well done

12-15-2008, 09:39 AM	#30
alias aliasx Join Date: Apr 2001 Posts: 19,010	Used as a term of endearment. __________________ https://porncorporation.com

12-15-2008, 10:58 AM	#31
bloggingseo Confirmed User Join Date: May 2007 Location: Austin Posts: 1,793	Your sig is damn impressive! Some of the smallest no people having places around on there __________________ Writing mainstream and adult since 2003 Hit me up ICQ 375089597

12-15-2008, 11:21 AM	#32
tranza ICQ: 197-556-237 Join Date: Jun 2003 Location: BRASIL !!! Posts: 57,559	Looks like Query analyzer error. __________________ I'm just a newbie.