• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Find geographical records based on zip code and radius

 
M Burke
Ranch Hand
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to find geo records based on zip code and a radius. It does not have to be very accurate, so curvature of the earth based on lat and long are not necessary. But I do need some general calculation to do this. Also, I am using Hibernate, so it would be nice to shoehorn the calculation into HSQL

Here is and example of the records.

2511 6491 Stevenson CT 41.3866460000 -73.1872070000
2512 6492 Wallingford CT 41.4577110000 -72.8170400000
2513 6493 Wallingford CT 41.3657090000 -72.9275070000
2514 6494 Wallingford CT 41.3657090000 -72.9275070000
2515 6497 Stratford CT 41.2071460000 -73.1305030000
2516 6498 Westbrook CT 41.2894990000 -72.4608700000
2517 6501 New Haven CT 41.3657090000 -72.9275070000
2518 6502 New Haven CT 41.3657090000 -72.9275070000
2519 6503 New Haven CT 41.3657090000 -72.9275070000
2520 6504 New Haven CT 41.3046970000 -72.9356100000
2521 6505 New Haven CT 41.3057210000 -72.7799010000
2522 6506 New Haven CT 41.3657090000 -72.9275070000
2523 6507 New Haven CT 41.3657090000 -72.9275070000
2524 6508 New Haven CT 41.3657090000 -72.9275070000
2525 6509 New Haven CT 41.3657090000 -72.9275070000

Create Table Statement
CREATE TABLE [dbo].[GEO_CODES](
[ID] [int] NOT NULL,
[CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LATITUDE] [decimal](18, 10) NULL,
[LONGITUDE] [decimal](18, 10) NULL,
CONSTRAINT [PK_GEO_CODES] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

 
Paul Clapham
Sheriff
Posts: 21551
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's the same question from just last year: http://www.coderanch.com/t/521934/JDBC/java/closest-values-query
 
M Burke
Ranch Hand
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Here's the same question from just last year: http://www.coderanch.com/t/521934/JDBC/java/closest-values-query


That is usefull , Paul. I do have a question. What units is distance in? It can't be miles from the results I am seeing from my table.

select ZipCode,sqrt( square(:latitudeValue - Latitude) + square(:longitudeValue - Longitude)) as distance from LOCATIONS order by distance

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic