July 12, 2005
Well, if you want to build a geospatial search, whether it is for google maps mashups/hacks or not, here is a quick walkthrough…and, by the way, thanks to open source software, it won’t cost you a dime!
I basically do it using MySQL GIS functions which should be good enough for these applications (although you may consider more accurate and performance-oriented custom application in a very high performance environment).
You can create a column called “coordinates” and define it like so:
CREATE TABLE mytable (
my_id mediumint(9) NOT NULL auto_increment,
coordinates POINT NOT NULL,
PRIMARY KEY (my_id),
Note that this is a myisam table, not innodb, since myisams are the only ones that support GIS functions currently.
UPDATE: This bug seems to be resolved in the latest versions of mysql but you may want to wait until the kinks are worked out…if you’d like to add it in now, however, you can specify innodb tables by replacing the close parentheses in the SQL above with this:
To insert into the table, you will need the latitudes and longitudes of each location. That means, if they are not already available, you will need to geocode your locations. This is the process of taking an address and translating it into a latitude & longitude. There are a few free services out there you can use including Geocoder.us (which already has a perl API), Geo::PostalCode (which will geocode zipcodes…don’t use this if you have full addresses and want to be more accurate), or even the Google Geocoder Contest Winner (which, since it won’t be a service but will store the data on your machine, can be very cumbersome and time consuming…but was an interesting project nonetheless).
Update: I have a free geocoder you might like (give it a try and give me some feedback for improvement).
Once you have a latitude and longitude associated with an item (i.e. a business location, etc), you can insert the data into the table like so:
INSERT INTO mytable (… , coordinates) VALUES (… , GeomFromText(’POINT($latitude $longitude)’))
In the above example, $latitude and $longitude represent the geocoded coordinates of the individual location you are inserting. Note that there is no comma between your latitude and longitude and also note the quotes required around the POINT definition. Also note that this is a geospatial search walkthrough and I won’t be discussing the benefits of proper coding/DB techniques such as binding values.
Now that you have your data geocoded and inserted into the DB, you’ll need to extract the data. To do this, here is how you select your data out:
SELECT my_id,…,x(coordinates) as latitude, y(coordinates) as longitude, (ROUND(GLength(LineStringFromWKB(LineString(AsBinary(coordinates), AsBinary(GeomFromText(’POINT($latitude $longitude)’))))))) AS distance FROM mytable ORDER BY distance LIMIT 20
In the above example, $latitude and $longitude represent the center point from which to begin searching outwards. You could also use a HAVING clause (i.e. “HAVING distance < $miles”) to limit the distance…but you should understand the performance implications of using HAVING.
The above requires the use of a mysql version that supports GIS functions, in this case, mysql version 4.1 or later.
However, all hope is not lost if you are in an environment where you cannot upgrade your mysql installation (even though you could have a second instance of mysql running on an unprivileged port)…
You could, instead, insert your locations with their individual coordinates into their own columns. So, “latitude” would be its own column within the table and “longitude” would be its own as well. Then, when given a lat/long combo, to search geospatially, you could covert the great-circle algorithm into SQL sytax. Here is an example.
A few other notes/tips (optional):
- you may want to enter data with POINT datatypes as integers to ensure better accuracy/precision (rounding, etc) but make sure to take that into account when SELECTing the data since the distance should be divided by whatever factor you used to convert your numbers into integers
- there are other GIS datatypes that mysql uses and many functions that support them (including polygones, polylines, etc and the ability to tell if they touch/overlap, etc). You may want to research them and see what other cool things you can do with them.
- Rather than figuring out your user’s starting point (from which to begin searching outwards from), you can try to guess it based on their IP (beware of proxies). Maxmind provides good, cheap data/service to do this while hostip.info provides a free community-built version with downloadable data and source code.
- Some phones provide GPS/geotagged data you can extract and use their geocoded data to put onto maps
- Coordinate conversion tool
Good luck! I hope this was clear. If not, please feel free to post your questions or comments here and I’ll be glad to respond to you.
Update: You can also try GeoSpatial Web Service which will do all the work for you…just supply it with the records and it will geocode and create a webservice for you to query.