Edit - Confluence Trial Project

/

Tech doc for Hub Allocation

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.

1CREATE INDEX idx_partition_bounds ON partition USING gist(bounds);
2//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.

1Partition.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');
17
const partitionAttributesToUpdate = {};

const requiredPartition = await Partition.findOne({
	where: {
		id: partitionId,
	}
}, {transaction: currentTx});
25
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;
	};
};
42
if (helper.hasKey(params, 'partition_code', { blankIsFalse: true })) {
	partitionAttributesToUpdate.partition_code = helper.sanitizeStringCode(partitionCode);
};
46
if (helper.hasKey(params, 'hub_id', { blankIsFalse: true })) {
	partitionAttributesToUpdate.hubId = helper.sanitizeStringCode(hubId);
};

if (helper.hasKey(params, 'description', { blankIsFalse: true })) {
	partitionAttributesToUpdate.description = description;
};
54
if (helper.hasKey(params, 'tags', { blankIsFalse: true }) && Array.isArray(tags)) {
	partitionAttributesToUpdate.tags = tags;
};
58
if (helper.hasKey(params, 'is_active', { blankIsFalse: true })) {
	partitionAttributesToUpdate.is_active = helper.parseBoolean(isActive);
};
62
if (helper.hasKey(params, 'color', { blankIsFalse: true })) {
	partitionAttributesToUpdate.color = color;
};
66
await requiredPartition.updateAttributes(partitionAttributesToUpdate, { transaction: currentTx });
};

CreatePartition :

1Partition.createPartition = async (organisationId, params = {}, options = {}) => {
2
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');
};
24
const partitionCode = helper.sanitizeStringCode(params.partition_code);
const existingPartition = await extendedModels.Partition.findOne({
	where: {
		partition_code: partitionCode
	},
	fields: { id: true }
}, { transaction: currentTx });
32
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

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

16
if (partition && partition.hub_id) {
	const requiredHub = await extendedModels.Hub.findOne({
		where: {
			id: hubId
		}
	});
	if (!requiredHub) {
		throw helper.wrongInputError('Hub not found!');
	}
26
	plannedDestinationHubId = requiredHub.id;
	plannedDestinationHubCode = requiredHub.code;
	plannedDestinationHubActive = requiredHub.is_active;
}
31
toRet[consignment.reference_number] = {
	plannedDestinationHubId, plannedDestinationHubCode, plannedDestinationHubActive
};
  } 
36