yeah MySQL, you are my BITCH - GoFuckYourself.com - Adult Webmaster Forum

X

borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#1

yeah MySQL, you are my BITCH

12-14-2008, 01: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

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
Tags: None
seeandsee

Check SIG!

Join Date: Mar 2006

Posts: 50945
Share

Post
#2

12-14-2008, 01:31 PM

I see your geo sig working

BUY MY SIG - 50$/Year

Contact here
Comment
fris

I have to go potty

Join Date: Aug 2002

Posts: 55789
Share

Post
#3

12-14-2008, 01:36 PM

thats one big sql statement

Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence.

My Newest Theme
Comment
Deej

I make pixels work

Join Date: Jun 2005

Posts: 24386
Share

Post
#4

12-14-2008, 01:41 PM

Sweet! Fight the Machine!

Deej's Designs n' What Not
Hit me up for Design, CSS & Photo Retouching

Icq#30096880
Comment
rowan

Too lazy to set a custom title

Join Date: Mar 2002

Posts: 17393
Share

Post
#5

12-14-2008, 01:53 PM

I tried that statement on my server and it melted
Comment
CaptainHowdy

Too lazy to set a custom title

Join Date: Dec 2004

Posts: 94102
Share

Post
#6

12-14-2008, 02:05 PM

Yeah, baibiiiiiiiiiii!

PIGGY SIGGY FOR SALE
Comment
fallenmuffin

Confirmed User

Join Date: Nov 2005

Posts: 8170
Share

Post
#7

12-14-2008, 02:10 PM

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

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#8

12-14-2008, 02:11 PM

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

Last edited by borked; 12-14-2008, 02:12 PM.

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#9

12-14-2008, 02: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

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#10

12-14-2008, 02: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

.

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
Comment
Bro Media - BANNED FOR LIFE

MOBILE PORN: IMOBILEPORN

Join Date: Jan 2004

Posts: 16502
Share

Post
#11

12-14-2008, 04: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
Comment
mrkris

Confirmed User

Join Date: May 2005

Posts: 2737
Share

Post
#12

12-14-2008, 08:25 PM

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

PHP-MySQL-Rails | ICQ: 342500546
Comment
Mutt

Too lazy to set a custom title

Join Date: Sep 2002

Posts: 34431
Share

Post
#13

12-14-2008, 08:36 PM

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!
Comment
Antonio

Too lazy to set a custom title

Join Date: Oct 2001

Posts: 14136
Share

Post
#14

12-14-2008, 08: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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#15

12-14-2008, 10:07 PM

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

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#16

12-14-2008, 10:16 PM

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

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
Comment
Antonio

Too lazy to set a custom title

Join Date: Oct 2001

Posts: 14136
Share

Post
#17

12-14-2008, 10:19 PM

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#18

12-14-2008, 10:22 PM

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!

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
Comment
fuzebox

making it rain

Join Date: Oct 2003

Posts: 22363
Share

Post
#19

12-14-2008, 10:26 PM

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

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#20

12-14-2008, 10:28 PM

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

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#21

12-14-2008, 10:31 PM

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

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
Comment
thehand

235 Pound Gorilla

Join Date: Apr 2003

Posts: 3470
Share

Post
#22

12-14-2008, 11:32 PM

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

Last edited by thehand; 12-14-2008, 11:33 PM.

Theers no more money in porn - I got the last of it.
Comment
V_RocKs

Damn Right I Kiss Ass!

Join Date: Nov 2003

Posts: 32451
Share

Post
#23

12-14-2008, 11:50 PM

I fucked her in her arse.
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#24

12-15-2008, 07:28 AM

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...

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#25

12-15-2008, 07:28 AM

Originally posted by Mutt

borked are you freelancing these days?

In the new year hopefully

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
Comment
alias

aliasx

Join Date: Apr 2001

Posts: 19010
Share

Post
#26

12-15-2008, 07:30 AM

borked is a hacker

https://porncorporation.com
Comment
HorseShit

Too lazy to set a custom title

Join Date: Dec 2004

Posts: 17513
Share

Post
#27

12-15-2008, 07:36 AM

i love mysql
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#28

12-15-2008, 07:38 AM

Originally posted by alias

borked is a hacker

say what now

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
Comment
gooddomains

Too lazy to set a custom title

Join Date: Jul 2003

Posts: 10127
Share

Post
#29

12-15-2008, 07:38 AM

job well done
Comment
alias

aliasx

Join Date: Apr 2001

Posts: 19010
Share

Post
#30

12-15-2008, 07:39 AM

Used as a term of endearment.

https://porncorporation.com
Comment
bloggingseo

Confirmed User

Join Date: May 2007

Posts: 1793
Share

Post
#31

12-15-2008, 08:58 AM

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
Comment
tranza

ICQ: 197-556-237

Join Date: Jun 2003

Posts: 57559
Share

Post
#32

12-15-2008, 09:21 AM

Looks like Query analyzer error.

I'm just a newbie.
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#33

12-15-2008, 09:24 AM

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.

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
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#34

12-15-2008, 09:31 AM

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

Last edited by borked; 12-15-2008, 09:33 AM.

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
Comment

Previous template Next

Working...