finally its working. saving lat and lng as float and running ur query works.
i have also being trying to use "glength" function and it works too. ------------------------------------------------------ db table - "geo_location" id (auto) vim -- name of a location e.g. p1 or p2 lat (float) lng (float) loc (datatype POINT) ------------------------------------------------------ source data - here p1 will be the base point. The distances (p1 to p5 182.3km )were calculated manually using http://www.movable-type.co.uk/scripts/latlong.html thus vim | lat/lng | Distance from p1-> ------------------------ p1 | 50.0/2.0 | 0K p2 | 50.0/3.0 | 71.47k p3 | 50.0/2.5 | 35.74k p4 | 49.0/2.0 | 111.2 km p5 | 49.0/4.0 | 182.3 km Now I converted the google lat and lng coordinates to UTM site http://home.hiwaay.net/~taylorc/toolbox/geography/geoutm.html 50.0/2 translates to 5539109.815010837/428333.5524949407 INSERT INTO `geo_location` (vim,lat,lng,loc) VALUES ('p1', 5539109.815010837,428333.5524949407,GEOMFROMTEXT('POINT (5539109.815010837 428333.5524949407)')); INSERT INTO `geo_location` (vim,lat,lng,loc) VALUES ('p2', 5538630.702579503,500000,GEOMFROMTEXT('POINT(5538630.702579503 500000)')); INSERT INTO `geo_location` (vim,lat,lng,loc) VALUES ('p3', 5538750.477291074,464166.5408016819,GEOMFROMTEXT('POINT (5538750.477291074 464166.5408016819)')); INSERT INTO `geo_location` (vim,lat,lng,loc) VALUES ('p4', 5427937.523178127,426857.9877156724,GEOMFROMTEXT('POINT (5427937.523178127 426857.9877156724)')); INSERT INTO `geo_location` (vim,lat,lng,loc) VALUES ('p5', 5427937.523178127,573142.0122843275,GEOMFROMTEXT('POINT (5427937.523178127 573142.0122843275)')); running the query distance p1 to p5 ----------------- SELECT GLENGTH(GEOMFROMTEXT( 'LINESTRING( 5539109.815010837 428333.5524949407, 5427937.523178127 573142.0122843275)' )) result - 182561.68409037 or 182.56 km have got the radial search working. thank you for your time Bjorn. my next mission hibernate spatial and cometd ;-) bye px On Apr 10, 3:27 pm, Björn Brala <[email protected]> wrote: > I always use lat/long, i dont convert. You must know, not all functions in > MySQL have been implemented. For distance we implemented out own SQL > statement. > > SELECT id, ( 6371 * acos( cos( radians(".$fLat.") ) * cos( radians( > X(geo) ) ) * cos( radians( Y(geo) ) - radians(".$fLng.") ) + sin( > radians(".$fLat.") ) * sin( radians( X(geo) ) ) ) ) AS distance > FROM verkooppunt > > In this query, geo is a binary geometry field. $fLat and $fLng are the lat > and long of the point we want to compare to. Perhaps this helps. > > 2009/4/10 px <[email protected]> > > > > > > > ur info on geom datatypes is helpful. > > > specific to thepart "how do we store the lat/lng decimals (google > > map api) to ... " > > > i will state is more clearly cause i am grappling with this for the > > last few days > > > i click on Innsbruck - the latlng returned by google map api is > > lat: 47.2626455432058 > > lng: 11.394656195068347 > > > For Munchen its > > lat: 48.138900363549034 > > lng: 11.577990545654284 > > > i preserve all the decimal and query the length from Innsbruck to > > Munchen > > > SELECT GLENGTH(GEOMFROMTEXT('LINESTRING(47.2626455432058 > > 11.394656195068347, 48.138900363549034 11.577990545654284)')) > > > results in - 0.89522845926586 > > > is it as simple as this? > > > just read this point "Well-Known Text is a format for the textual > > representation of geometric objects. The UTM coordinates > > of "Innsbruck" in WKT format look like this: POINT(681547.32 > > 5237595.88)" > > > should i be translating the lat lng to UTM coordinates? > > thus saving Innsbruck to a point datatype as POINT(681547.32 > > 5237595.88) rather than POINT(47.2626455432058 11.394656195068347) > > and then apply the geom function on the data. > > > i am bordering on desperation now. thanks for ur time anyway. > > > cheers, > > px > > > On Apr 10, 12:22 pm, Björn Brala <[email protected]> wrote: > > > If you want to implement the functions from MySQL you need to store them > > in > > > a geometry column. To make sure its fast create an index on that column; > > > > ALTER TABLE markers ADD SPATIAL INDEX(geo_field); > > > > This will make it a lot faster. > > > > When cerating Polygons or lines, be sure the decimal character is a > > point. I > > > use for polyline; > > > > LINESTRING(52.4 4.6, 53.3 4.5) etc. > > > > And polygon; > > > > POLYGON((52.6 4.7, 43.3 4.6, 50.7 4.8)) > > > > Double the (()) because polygon can consist of inner and outer. > > > > Hopefully this helps. > > > > 2009/4/10 px <[email protected]> > > > > > hi, > > > > > i am working on a loc based concept. stuck on a few issues. > > > > > how do we store the lat/lng decimals (google map api) to a "point" > > > > datatype in mysql. > > > > > example for paris > > > > lat - 48.8372 > > > > lng - 2.33 > > > > > do we store it as GEOMFROMTEXT('POINT(48.8372 2.33234)') or do > > > > i need to convert to any specific format. > > > > > storing it as float column will not suffice as all the spatial quering > > > > fu > > > > > dont i do need to store them as point/linestring/polygon to use data > > > > query functions like glenght/exteriorring ? > > > > > eg given "n" mile radius from a base point finding matching locations > > > > > thanks, > > > > px > > > > -- > > > Bjorn Brala > > > ---------------- > > > GeoStart.nl- Google maps - Swis Webdesign > > -- > Bjorn Brala > ---------------- > GeoStart.nl- Google maps - Swis Webdesign --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Google Maps API" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Google-Maps-API?hl=en -~----------~----~----~----~------~----~------~--~---
