lucene - Hibernate Search and Spatial - Database Design -

i'm looking use spatial locate vehicles within x miles of given zip code. i'd use 2 tables, vehicle_listing , zip_code_detail vehicle_listing has manytoone relationship zip_code_detail. address table made of entire zip code database contains long/lat etc.

1. will spatial work join, or should include long/lat within vehicle_listing?
2. if used @indexembedded on manytoone relationship , @indexed zip_code_detail, entire zip_code_detail table indexed, or zip_code_detail records being joined?

i'm looking database design best performance while minimizing memory consumption , ideally reducing data duplication.

entity design using mysql database.

@entity public class zipdetail implements serializable {      @id      @column(length = 5)     private string zip;       private string city;      @manytoone     @joincolumn(name = "state_id")     private state state;      @manytoone     @joincolumn(name = "county_id")     private county county;      @nonvisual     private string areacodes;      @nonvisual     private double latitude;      @nonvisual     private double longitude;      private string country; 

vehiclelisting.class

@indexed @spatial(spatialmode = spatialmode.grid) public class vehiclelisting extends baseentity {       @nonvisual     @latitude     private double latitude;      @nonvisual     @longitude     private double longitude;      @indexedembedded     @manytoone     @joincolumn(name = "year_id", nullable = false)     private vehicleyear vehicleyear;      @indexedembedded     @manytoone     @joincolumn(name = "make_id", nullable = false)     private vehiclemake vehiclemake;      @manytoone     @joincolumn(name = "zip_detail_id", nullable = false)     private zipdetail zipdetail; 

i've provided sql solution (i'm not versed in mysql) hope - i.e. can reverse engineer similar solution.

will spatial work join, or should include long/lat within vehicle_listing?

in short, yes work fine. when join tables, queries using information both tables use appropriate indexes on either table , produce necessary filters keep performance maximum - without duplication (which should minimised in data model).

naturally, you'd expect see small improvement in performance if store latitude / longitude coordinates @ vehicle level because there not overhead of making join in query, you're going have update lat / longs @ vehicle level (rather association) , going force far more work onto spatial index (assuming have more vehicles zip codes) expect degrade performance. assume, unless know fact never will, have more vehicles zip codes given zip codes not change often.

so assuming following (ultra simplified example), (these wrote before posted classes still relevant):

create table [vehicles] ( int [id], int [zipcodedetailid] -- foreign key on [zip_code_detail].[id] (also create index here) );  create table [zip_code_detail] ( int [id], geography [location] -- ensure spatial index on here ); 

you write following:

declare @searchdistance float = 1000; -- distance in metres declare @searchfrom geography = geography::stpointfromtext('point(12.3456 56.7890)', 4326);  select count(v.*) [vehicles] v join [zip_code_detail] zip on zip.[id] = v.[zipcodedetailid] zip.[location].stdistance(@searchfrom) <= @searchdistance; 

in sql on point database of on 2m records , random search distance sub 2s responses on 1,000 results. you'll far better times smaller database , index geared multiple geometry types, not points.

i've answer based on several assumptions here:

1. you representing zip codes 5-digit means table has approx 40,000 records.
2. you representing zip codes central points rather polygon boundary?
3. the vehicles assumed static (for example @ home address purpose of query) , not in motion (which require spatial data "timestamps" on separate table altogether).

hope helps in way.