SoCal Utility Pole Management Application Upgrade

SCJPC sought to modernize its data management processes by integrating cloud-based solutions with its existing on-premises infrastructure. The objectives included:

The Scope of Work

The project involved a series of well-defined tasks to achieve SCJPC’s goals:

Data Export and Upload Created scripts to extract, compress, and securely upload data from the MS-SQL server to AWS S3 buckets.
API Integration and Data Insertion Created scripts to extract, compress, and securely upload data from the MS-SQL server to AWS S3 buckets.Developed scalable APIs for data download, extraction, and insertion into temporary tables.
Ensured seamless synchronization and updates between databases.
Data Processing and Cleanup Built optimized Postgres database views for efficient data handling. Automated routines for file cleanup and server optimization.
Client-Side Functionality Developed an intuitive WordPress-based interface for searches.
Added advanced search options such as partial matches, geographic coordinates, and owner codes.
Enabled export capabilities in multiple formats.
Automated Processes Implemented automated export mechanisms to streamline periodic updates.
Conducted rigorous testing to ensure system reliability.
Deployment and Maintenance Deployed the solution on AWS EC2 instances, optimizing configurations for performance.
Established monitoring protocols for long-term stability.

About the Client

The Southern California Joint Pole Committee (SCJPC) has been a cornerstone of utility pole management since 1906. Initially formed by telephone, electricity, and railroad companies to optimize pole usage and standardize ownership records, SCJPC now includes utilities, communication providers, and municipalities across Southern California. The organization currently comprises 32 members from these diverse sectors, all sharing joint equity interests in utility poles to foster collaboration and accountability. SCJPC manages utility poles across Southern California, supported by an administrative team responsible for maintaining ownership records and facilitating joint pole equity transactions. Through monthly Bills of Sale, SCJPC ensures equitable financial settlements among its 32 member organizations.

TECHNOLOGY STACK

This technology stack was meticulously selected to meet SCJPC’s requirements for scalability, performance, and user satisfaction, enabling a seamless and efficient data management system.

Elastic Beanstalk

Simplifies application deployment and management.

AWS EC2

Virtual servers to host the API and backend services

AWS S3

Scalable storage for data files and export results.

Elasticsearch

For building WordPress plugins and enhancing client-side integration.

PHP

For building WordPress plugins and enhancing client-side integration.

Python

For scripting and automation tasks, including data migration scripts.

Node JS

Utilized for specific backend processes requiring scalability and event-driven architecture.

CloudFormation

Automated setup and deployment of AWS resources.

Route 53

DNS web services for efficient routing.

CDN

Accelerates content delivery.

FastAPI

High-performance API framework for data import/export processes.

Postgres

Relational database for structured data storage and querying.

Redis

Caching solution to enhance data retrieval performance.

RabbitMQ

Message broker for asynchronous data synchronization and reliable processing.

WordPress

Customized interface for managing search functionalities and content interaction.

AWS CloudWatch

For monitoring system performance and identifying issues.