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


.

As you were

I see your geo sig working :)

thats one big sql statement

Sweet! Fight the Machine!

I tried that statement on my server and it melted

Yeah, baibiiiiiiiiiii!

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

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 :thumbsup

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 :winkwink:

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

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

Mission accomplished

.

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

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

borked are you freelancing these days?

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

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 :pimp

here's one

http://www.6dicksunder.com/hl/c.jpg

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!

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

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

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

only one million?:1orglaugh:1orglaugh:1orglaugh




































































































































































































































jk - your hamster style is impressive :thumbsup

http://www.nanarland.com/Chroniques/.../orgazmo03.jpg

I fucked her in her arse.

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

In the new year hopefully :thumbsup

borked is a hacker

i love mysql

say what now :disgust

job well done

Used as a term of endearment.

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

Looks like Query analyzer error.

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

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