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 :
Partition.updatePartition = async (organisationId, params = {}, options = {}) => {
const currentTx = options.transaction;
const {
description,
color,
partition_id: partitionId,
polygon_data: polygonData = [],
partition_code: partitionCode,
tags: tags = [],
is_active: isActive,
bounds: bounds,
hub_id: hubId,
} = params;
assert(currentTx, 'currentTx is required');
assert(organisationId, 'organisationId is required');
assert(partitionId, 'partitionId is required');
const partitionAttributesToUpdate = {};
const requiredPartition = await Partition.findOne({
where: {
id: partitionId,
}
}, {transaction: currentTx});
if (!requiredPartition) {
throw helper.notFoundError('Partition not found');
};
if (helper.hasKey(params, 'polygon_data', { blankIsFalse: true })) {
if (Array.isArray(polygonData) && polygonData.length) {
const polygon = postgisHelper.geojsonToWkb(postgisHelper.createPolyGeoJson([polygonData]));
partitionAttributesToUpdate.polygon = polygon;
};
};
if (helper.hasKey(params, 'bounds', { blankIsFalse: true })) {
if (Array.isArray(bounds) && bounds.length) {
const bounds = postgisHelper.geojsonToWkb(postgisHelper.createPolyGeoJson([bounds]));
partitionAttributesToUpdate.bounds = bounds;
};
};
if (helper.hasKey(params, 'partition_code', { blankIsFalse: true })) {
partitionAttributesToUpdate.partition_code = helper.sanitizeStringCode(partitionCode);
};
if (helper.hasKey(params, 'hub_id', { blankIsFalse: true })) {
partitionAttributesToUpdate.hubId = helper.sanitizeStringCode(hubId);
};
if (helper.hasKey(params, 'description', { blankIsFalse: true })) {
partitionAttributesToUpdate.description = description;
};
if (helper.hasKey(params, 'tags', { blankIsFalse: true }) && Array.isArray(tags)) {
partitionAttributesToUpdate.tags = tags;
};
if (helper.hasKey(params, 'is_active', { blankIsFalse: true })) {
partitionAttributesToUpdate.is_active = helper.parseBoolean(isActive);
};
if (helper.hasKey(params, 'color', { blankIsFalse: true })) {
partitionAttributesToUpdate.color = color;
};
await requiredPartition.updateAttributes(partitionAttributesToUpdate, { transaction: currentTx });
};
CreatePartition :
Partition.createPartition = async (organisationId, params = {}, options = {}) => {
const currentTx = options.transaction;
const {
description,
color,
polygon_data: polygonData,
tags: tags = [],
hub_id: hubId
} = params;
assert(currentTx, 'currentTx is required');
assert(organisationId, 'organisationId is required');
assert(params.partition_code, 'partitionCode is required');
assert(params.hub_id, 'hubId is required');
assert(Array.isArray(tags), 'tags should be array');
if (!(Array.isArray(polygonData) && polygonData.length)) {
throw helper.wrongInputError('partition data should be array');
};
if (!(Array.isArray(bounds) && bounds.length)) {
throw helper.wrongInputError('bounds data should be array');
};
const partitionCode = helper.sanitizeStringCode(params.partition_code);
const existingPartition = await extendedModels.Partition.findOne({
where: {
partition_code: partitionCode
},
fields: { id: true }
}, { transaction: currentTx });
if (existingPartition) {
throw helper.wrongInputError('', {
message: 'Partition code already exists',
reason: 'PARTITION_ALREADY_EXISTS'
});
};
if (helper.hasKey(params, 'hub_id', { blankIsFalse: true })) {
partitionAttributesToUpdate.hubId = helper.sanitizeStringCode(hubId);
};
const polygon = postgisHelper.geojsonToWkb(postgisHelper.createPolyGeoJson([polygonData]));
const bounds = postgisHelper.geojsonToWkb(postgisHelper.createPolyGeoJson([bounds]));
await Partition.create({
organisation_id: organisationId,
partition_code: partitionCode,
description: description,
tags: tags,
polygon: polygon,
color: color,
bounds: bounds,
hub_id: hubId
}, { transaction: currentTx });
};
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
else if(orgConfig.partition_based_hub_allocation) {
const lat = get(params, 'origin_address.lat', null);
const lng = get(params, 'origin_address.lng', null);
if (!lat || !lng){
throw helper.wrongInputError('', {
message: 'latitude and latitude should be present for partition based hub allocation'
});
}
const partition = await extendedModels.Partition.findValidPartition( organisationId,
{
lat: inputParams.lat,
lng: inputParams.lng,
}, options);
if( partition && partition.hub_id){
hubId = partition.hub_id;
}
}
Similarly for destination hub allocation following logic will be added in getMultipleConsignmentDestinationHub fn:
else if(orgConfig.partition_based_hub_allocation) {
const lat = get(consignment, 'destination_address.lat', null);
const lng = get(consignment, 'destination_address.lng', null);
let plannedDestinationHubId = null, plannedDestinationHubCode = null, plannedDestinationHubActive = null;
if (!lat || !lng){
throw helper.wrongInputError('', {
message: `${consignment.reference_number}:latitude and latitude should be present for partition based hub allocation`
});
}
const partition = await extendedModels.Partition.findValidPartition( organisationId,
{
lat,
lng,
}, options);
if(partition && partition.hub_id){
const requiredHub = await extendedModels.Hub.findOne({
where: {
id: hubId
}
});
if(!requiredHub){
throw helper.wrongInputError('Hub not found!');
}
plannedDestinationHubId = requiredHub.id;
plannedDestinationHubCode = requiredHub.code;
plannedDestinationHubActive = requiredHub.is_active;
}
toRet[consignment.reference_number] = {
plannedDestinationHubId, plannedDestinationHubCode, plannedDestinationHubActive
};
}
Add Comment