Hibernate搜索与空间数据库设计

Question

我希望使用空间定位车辆在x英里内给定的邮政编码。我想使用两个表，vehicle_listing和zip_code_detail，其中vehicle_listing与zip_code_detail有ManyToOne关系。我的地址表是由包含long/lat等的整个邮政编码数据库组成的。

1. 空间是否与联接一起正常工作，还是应该在vehicle_listing中包括long/lat？
2. 如果我在我的ManyToOne关系中使用@IndexEmbedded和@Indexed zip_code_detail，那么整个zip_code_detail表是被索引的，还是仅仅是加入了zip_code_detail记录？

我正在寻找一个性能最好的数据库设计，同时最大限度地减少内存消耗并理想地减少数据重复。

使用MySql作为数据库的实体设计。

@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;

Answer 1

我提供了一个SQL解决方案(我不太精通MySQL)，但我希望它能对您有所帮助--也就是说，您可以将它逆向工程成类似的解决方案。

空间是否与联接一起正常工作，还是应该在vehicle_listing中包括long/lat？

总之，是的，它会工作得很好。当您加入这些表时，任何使用来自两个表的信息的查询都将在两个表上使用适当的索引，并产生必要的过滤器，以使性能保持在最大限度--而不会重复(在任何好的数据模型中，这都应该被最小化)。

当然，如果您将纬度/经度坐标存储在车辆级别上，您可能会看到性能上的小改进，因为在查询中没有加入的开销，但是您将不得不在车辆级别(而不仅仅是关联)更新lat / longs，然后在空间索引上强制进行更多的工作(假设您有比ZIP代码多的车辆)，这最终会降低性能。我会假设，除非你知道你永远不会，最终你会有比邮政编码更多的车辆，因为邮编不会经常改变。

因此，假设如下(对示例进行了超简化)，我会这样做(这些都是在您发布类之前编写的，但仍然相关)：

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
);

然后，您可以编写以下内容：

DECLARE @searchDistance FLOAT = 1000; -- Distance in metres
DECLARE @searchFrom GEOGRAPHY = GEOGRAPHY::STPointFromText('POINT(12.3456 56.7890)', 4326);

SELECT
COUNT(V.*)
FROM [Vehicles] V
JOIN [Zip_Code_Detail] ZIP ON ZIP.[Id] = V.[ZipCodeDetailId]
WHERE
ZIP.[Location].STDistance(@searchFrom) <= @searchDistance;

在SQL中，在一个记录超过200万条的点数据库和随机搜索距离上，我得到了1千多个结果的子2s响应。使用一个较小的数据库，您会得到更好的结果，我的索引适合多种几何类型，而不仅仅是点。

我的答案是基于以下几个假设：

1. 您将邮政编码表示为5位数，这意味着您的表有大约40,000条记录.
2. 您将邮政编码表示为中心点而不是多边形边界？
3. 假设车辆是静态的(例如，在查询的家庭地址)，而不是在运行中(这需要在单独的表上带有“时间戳”的空间数据)。

希望能在某种程度上有所帮助。