Tech doc for Hub Allocation
- Sahib Singh
Problem: searching whether a given point is contained within a partition using complete poygen( columns which is of type geometry made by converting geoJson data to WKB)
Solution: Implementing indexing on a new Column bounds which will be of data type geometry by converting { lat_min , lat_max , lng_min , lng_max } for a partition.
Faster initial filtering: Can quickly eliminate partitions that don't intersect the query point's bounding box.
Reduced index size: Smaller than an index on the full polygon.
CREATE INDEX idx_partition_bounds ON partition USING gist(bounds);
//using GiST as it is better than GiN index for spatial data.
Implementation:
→ Adding one more column as hub_id to store to which hub a partition belongs
FindAllPartitions :
We will be fetching all partitions which contains given point(lat,lng) using ST_Contains which will check whether boundary of a partition contains given point geom or not.
Partition.findAllRequiredPartitions = async (organisationId, params = {}, options = {}) => {
assert(organisationId, 'organisationId is required');
const { lat, lng } = params;
if (!lat || !lng) {
throw helper.wrongInputError('Invalid location details');;
}
const query = `
SELECT *
FROM partition
WHERE
organisation_id = $1
AND ST_Contains(bounds,ST_SetSRID (ST_MakePoint ($2, $3), 4326))
AND is_active = $4
`;
//ST_Contains checks whether first geometry contains second geometry
//ST_MakePoint : to create a point geometry. It takes coordinate values as input and returns a point object.
const queryResult = await helper.executeQueryAsync(Partition, query, [organisationId, lat, lng, true], options);
if (Array.isArray(queryResult) && queryResult.length) {
return queryResult;
}
return [];
};Doubt: should we also include (ST_Intersects(bounds,ST_SetSRID (ST_MakePoint ($2, $3), 4326)) this in where condition
FindValidParition:
Fallback as of now is to throw err if no partition is found or if more than 1 is found.
Partition.findValidPartition = async (organisationId, params = {}, options = {}) => {
assert(organisationId, 'organisationId is required');
const {lat, lng} = params;
const currentTx = options.transaction;
if (!lat || !lng) {
return {};
}
const query = `
SELECT *
FROM partition
WHERE
organisation_id = $1
AND ST_Contains(bounds,ST_SetSRID (ST_MakePoint ($2, $3), 4326))
AND is_active = $4
`;
const queryResult = await helper.executeQueryAsync(Partition, query, [organisationId, lat, lng, true], {transaction: currentTx});
if (Array.isArray(queryResult) && queryResult.length === 1) {
return queryResult[0];
} else {
return helper.wrongInputError('Invalid location details');
}
};
UpdatePartition :
CreatePartition :
For hub Allocation using partition based method, a new IDB key will be used partition_based_hub_allocation while creating softadata and calling fn getConsignmentOriginHub
Similarly for destination hub allocation following logic will be added in getMultipleConsignmentDestinationHub fn: