Using geospatial technologies to generate actionable insights from more than 7 million records: a case study with Laravel and MySQL.

This article details how Laravel and MySQL were used to create efficient interactive maps from a database with more than 7 million records. The main challenge was to turn gross data into useful information, scalably and without compromising performance.

The initial challenge: dealing with massive data

The project began with the need to extract value from a MySQL table containing more than 7 million records. The first concern was the database's ability to support the demand. The analysis focused on optimizing consultations, identifying the relevant attributes for filtering.

The table had many attributes, but few were crucial to the solution. After validation, restrictions were defined to refine the search. As the goal was to create a map, the initial filtering was based on location (state, city and neighborhood). A select2 component was used to allow controlled neighborhood selection after choosing the state and the city. Additional filters such as name, category and evaluation have been implemented for more accurate search. The combination of dynamic filters and appropriate indexes guaranteed the optimization of consultations.

The next challenge was the implementation of polygon design functionality on the map.

The application: Laravel, React and Optimizations

Considering the amount of data, the application was designed for high efficiency. The chosen stack was Laravel 11 (Back End) and React (Front End), using Laravel Breeze to accelerate development. Back-end employed an MVC architecture with layers of service and repository for organization and maintenance. The front end has been modularized with react, ensuring component reuse and efficient communication with back-end via axios.

Architecture was designed for future scalability, allowing integration with AWS services such as Fargate (API) and Cloudfront (Front-end). The absence of state on the server facilitates the separation of responsibilities.

Tests and Quality of Code

A robust test suite using PestpHP was implemented, covering 22 endpoints with approximately 500 tests. This approach ensured stability and maintenance efficiency.

The application core: Interactive maps

Leaflet was the library chosen for map manipulation. To optimize performance with a large number of markers, were used:

• react-leaflet-markercluster : Dynamic marker grouping to reduce rendering overload and improve user experience.
• react-leaflet-draw : Allows users to draw polygons on the map, capturing coordinates for data filtering in the database.

The integration of filters (state, city, neighborhood) with the map ensured an intuitive experience. Custom layers were implemented in the leaflet to differentiate records and attributes, and the lazy loading was used to load only visible data.

The table and geospatial indices

The table uses a POINT column to store the coordinates with a geospatial index (R-GTE) to optimize queries. MySQL space functions, such as ST_Contains, ST_Within and ST_Intersects, were used to filter records based on the intersection with the designed polygon.

Consultation Example:

SELECT id, name, address
FROM users
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((...))'),
    coordinates
);

FINAL CONSIDERATIONS: Learning and improvements

Some important lessons were learned during development:

1. Coordinate migration: A script was created to migrate the separate columns coordinates (latitude and longitude) to a POINT column, allowing the use of the geospatial index.
2. JavaScript Efficiency: The choice of iteration method (e.g., array.map vs. for...in) impacts performance and should be evaluated on a case by case.
3. Additional optimizations: Lazy loading and clustering were crucial to optimizing performance.
4. Treatments and Validations: Located updates in the database and front end avoid unnecessary rework.

This project demonstrates the importance of specific optimizations and good development practices to create scalable and efficient applications. Focus on delivery and continuous iteration are fundamental to success.

The above is the detailed content of Geospatial in Laravel: Optimizations for interactive maps and large volumes of data. For more information, please follow other related articles on the PHP Chinese website!