You can get lat and long from a geometry field using X(geo) or Y(geo) no real need to store the lat long (tho i do it also in my database ;)
2009/4/10 px <[email protected]> > > 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<http://home.hiwaay.net/%7Etaylorc/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 > > > -- 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 -~----------~----~----~----~------~----~------~--~---
