| Home | Forums | Reviews | Guides | Newsgroups | Register | Search |
 |
| Thread Tools |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
--HAv5+T9jbwMPl6Kw
Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Mark Ericson () wrote: > Excellent! The only thing remaining is an efficient algorithm for a sear= ch > for all zipcodes within a given radius. Using Ruby and SQLite3: pabs@halcyon:~/proj/zip> ./import.rb zipcode.{csv,db} pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3 "city","state","zip","distance (mi)" "Annandale","VA","22003","0.0" "Springfield","VA","22161","1.62363604423677" "Springfield","VA","22151","1.87190097838136" "Falls Church","VA","22042","2.97362028549975" Here's the code for each piece (also available at the URL=20 http://pablotron.org/files/zipfind.tar.gz): ---- import.rb ---- #!/usr/bin/env ruby =20 # load libraries require 'rubygems' rescue nil require 'sqlite3' =20 # constants SCAN_RE =3D /"(\d{5})","([^"]+)","(..)","([\d.-]+)","([\d.-]+)","([\d-]+)= ","(\d)"/ SQL =3D "INSERT INTO zips(zip, city, state, lat, long, timezone, dst)=20 VALUES (?, ?, ?, ?, ?, ?, ?)" TABLE_SCHEMA =3D "CREATE TABLE zips ( id INTEGER NOT NULL PRIMARY KEY, =20 zip VARCHAR(5) NOT NULL, city TEXT NOT NULL, state VARCHAR(2) NOT NULL, lat FLOAT NOT NULL, long FLOAT NOT NULL, timezone INTEGER NOT NULL, dst BOOLEAN NOT NULL );" =20 =20 # handle command-line arguments unless ARGV.size =3D=3D 2 $stderr.puts "Usage: #$0 <csv> <db>" exit -1 end csv_path, db_path =3D ARGV =20 # load database, create zip table and prepared statement db =3D SQLite3:  atabase.new(db_path)db.query(TABLE_SCHEMA) st =3D db.prepare(SQL) =20 # parse CSV and add each line to the database db.transaction { File.read(csv_path).scan(SCAN_RE).each { |row| st.execute(*row) } } ---------- ---- find.rb ---- #!/usr/bin/env ruby =20 require 'rubygems' require 'sqlite3' =20 MI_R =3D 1.15 =20 # grab base zip code unless ARGV.size > 1 $stderr.puts "Usage: #$0 <db> <zipcode> [radius]" exit -1 end db_path, src_zip, radius =3D ARGV radius =3D (radius || 50).to_i =20 # open database db =3D SQLite3: atabase.new(db_path)=20 # get lat/long for specified zip code sql =3D "SELECT lat, long FROM zips WHERE zip =3D ?" src_lat, src_long =3D db.get_first_row(sql, src_zip).map { |v| v.to_f } =20 unless src_lat && src_long $stderr.puts "Unknown zip code '#{src_zip}'" exit -1 end =20 # calculate min/max lat/long ret, range =3D [], radius / 69.0 =20 # get all codes within given rectangle sql =3D "SELECT lat, long, city, state, zip FROM zips=20 WHERE lat > ? AND lat < ? AND long > ? AND long < ?" args =3D [src_lat - range, src_lat + range,=20 src_long - range, src_long + range] =20 db.prepare(sql).execute(*args).each do |row| # get row values, convert lat/long to floats dst_lat, dst_long, dst_zip, dst_city, dist_st =3D row dst_lat, dst_long =3D dst_lat.to_f, dst_long.to_f =20 # calculate distance between zip codes. if dst_zip is within the # specified radius, then add it to the list of results d =3D Math.sqrt((dst_lat - src_lat) ** 2 + (dst_long - src_long) ** 2) ret << [dst_zip, dst_city, dist_st, d * 69.0] if d <=3D range end =20 # sort results by distance ret =3D ret.sort { |a, b| a[-1] <=3D> b[-1] } =20 # print out results as a CSV puts '"city","state","zip","distance (mi)"',=20 ret.map { |row| '"' << row.join('","') << '"' } ---- > I suppose one technique might be to first narrow the databse search withi= n a > given a given square latitude/longitude range and then filter those resul= ts > by testing that they are within the given circle radius That's all the code above does. There's some room for optimization there; for example, you could create a region field, then calculate list of regions that intersect with the search radius. If you index on the region field, then the query becomes essentially an index lookup instead of a lat/long comparison (you still have to do the second distance calculation, of course). Anyway, I didn't do that because the code above runs pretty quickly on my machine. =20 --=20 Paul Duncan <> pabs in #ruby-lang (OPN IRC) http://www.pablotron.org/ OpenPGP Key ID: 0x82C29562 --HAv5+T9jbwMPl6Kw Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFDoOcjzdlT34LClWIRAh74AJ0Y3trUYm6otAH8s5uAoP 6W37Uq7ACbB2cJ YiyjVF67Q3A5eo/bPnwL5cI= =q2s1 -----END PGP SIGNATURE----- --HAv5+T9jbwMPl6Kw-- |
|
|
|
|
|||
|
Guest
Posts: n/a
|
------=_Part_673_2012695.1134619192078
Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Excellent! You beat me to it. My approach to import was somewhat different, your probably has the advantage of a transaction per row. require 'csv' require 'dbi' DBI.connect("DBI:ADO  rovider=3DSQLOLEDB;Data Source=3Dlocalhost;InitialCatalog=3DUSZipCodes;User Id=3Dtest;Password=3Dtest") do | dbh | sql =3D "INSERT INTO ZipData (zipcode, city, state, latitude, longitude= , timezone, dst) VALUES (?, ?, ?, ?, ?, ?, ?)" dbh.prepare(sql) do | sth | begin rdr =3D CSV.open("zipcode.csv", "r") header =3D rdr.shift # skip header row rdr.each do |row| sth.execute(row[0], row[1], row[2], row[3], row[4], row[5], row[6]) end ensure CSV.close unless CSV.nil? end end end On 12/14/05, Paul Duncan <> wrote: > > Using Ruby and SQLite3: > > pabs@halcyon:~/proj/zip> ./import.rb zipcode.{csv,db} > pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3 > "city","state","zip","distance (mi)" > "Annandale","VA","22003","0.0" > "Springfield","VA","22161","1.62363604423677" > "Springfield","VA","22151","1.87190097838136" > "Falls Church","VA","22042","2.97362028549975" > > Here's the code for each piece (also available at the URL > http://pablotron.org/files/zipfind.tar.gz): > > ---- import.rb ---- > #!/usr/bin/env ruby > > # load libraries > require 'rubygems' rescue nil > require 'sqlite3' > > # constants > SCAN_RE =3D > /"(\d{5})","([^"]+)","(..)","([\d.-]+)","([\d.-]+)","([\d-]+)","(\d)"/ > SQL =3D "INSERT INTO zips(zip, city, state, lat, long, timezone, dst) > VALUES (?, ?, ?, ?, ?, ?, ?)" > TABLE_SCHEMA =3D "CREATE TABLE zips ( > id INTEGER NOT NULL PRIMARY KEY, > > zip VARCHAR(5) NOT NULL, > city TEXT NOT NULL, > state VARCHAR(2) NOT NULL, > lat FLOAT NOT NULL, > long FLOAT NOT NULL, > timezone INTEGER NOT NULL, > dst BOOLEAN NOT NULL > );" > > > # handle command-line arguments > unless ARGV.size =3D=3D 2 > $stderr.puts "Usage: #$0 <csv> <db>" > exit -1 > end > csv_path, db_path =3D ARGV > > # load database, create zip table and prepared statement > db =3D SQLite3: atabase.new(db_path)> db.query(TABLE_SCHEMA) > st =3D db.prepare(SQL) > > # parse CSV and add each line to the database > db.transaction { > File.read(csv_path).scan(SCAN_RE).each { |row| st.execute(*row) } > } > ------=_Part_673_2012695.1134619192078-- |
|
|
|
|
|||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
|
| |
|
| Thread Tools | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Need to Format a zipcode into xxxxx-xxxx. | Jeff Thur | ASP .Net Datagrid Control | 1 | 02-18-2005 04:14 AM |
| How can I display a 9 digit zipcode in a Datagrid with a hypen | Jeff Thur | ASP .Net Web Controls | 1 | 02-17-2005 01:02 PM |
| [YO] Free Swingers Search, See Pics, Read Stories, Search Via Zipcode! | Swin4Real Mon | Computer Support | 0 | 11-23-2004 03:20 AM |
| [OT] Free Swingers Search, See Pics, Read Stories, Search Via Zipcode! | Joe Deluga | Computer Support | 0 | 11-17-2004 05:19 AM |
| ZIPCODE DB ACCESS TO SQL | DaveF | ASP .Net | 6 | 09-29-2004 11:44 PM |
