About

TerraQ - Backend test. This test was created to assess your skills in developing a backend application that integrates geospatial functionalities. The goal is to create an API that interacts with a PostgreSQL database with the PostGIS extension, providing endpoints for the manipulation and querying of geospatial data.

Objectives:

1. Install and Configure PostGIS on PostgreSQL;
2. Set up the PostgreSQL environment with PostGIS in Symfony migrations. This should be done to prepare the database for geospatial operations;
3. Create Geospatial Feature Tables

• Table municipal_geometry: To store the geometries of municipalities in São Paulo (SP) and Minas Gerais (MG). Municipalities should be created from the provided GeoJSONs. This table should have the fields "id," "name" (municipality name), and "geom" (geometry).
• Table state_geometry: To store the geometries of states São Paulo (SP) and Minas Gerais (MG). This table should be created by processing the data from each state's municipalities and performing a join/dissolve to form the state's geometry. This table should have the fields "id," "name" (state name), and "geom." Create the state_geometry and municipal_geometry tables using the GeoJSONs of São Paulo municipalities and the GeoJSONs of Minas Gerais municipalities as inputs.
• Table pontos_usuario: To store points posted by users, with fields for "id," "latitude," "longitude," "municipal_id" (municipality ID), and "geom."
• PS: The geometry columns of the created tables should be of type Geometry, have the name "geom," and be in SRID 4326.

4. Develop Specific Endpoints:

• Location Query by Latitude and Longitude (/api/municipal/find): Endpoint to receive latitude and longitude and return the corresponding municipality or an error if not found.
• CRUD for Points (/api/user-point): Implement a complete CRUD to manipulate the "user_points" table, with the methods POST, GET, PUT, DELETE.

Solution

Made using the Symfony 7 framework, PostgreSQL database with PostGIS(PostGIS extends the capabilities of the PostgreSQL relational database by adding support for storing, indexing, and querying geospatial data.) and Docker for application containerization.

How to run the project

After downloading the geo-polygons-symfony repository, being in its main folder, go up the structure composed of the following containers:

• geo-polygons-symfony-back: Composed with nginx and PHP, being exposed to port 9000;
• geo-polygons-postgres: With the PostgreSQL database.

1. Through the following commands:

docker-compose build --no-cache

2. After building the containers, execute the command below to start it.

docker-compose up -d

After finishing the creation of the containers, we must execute the commands below so that the environment is ready to be used:

1. Execute the migrations with the command below to populate the database with the necessary tables of the solution:

docker exec -it geo-polygons-symfony-back php bin/console doctrine:migrations:migrate --no-interaction

2. Used to populate the tables (Required):

docker exec -it geo-polygons-symfony-back php bin/console doctrine:fixtures:load --no-interaction

After importing the files, we will have this result below, in other words, was created the state maps.

Now we can use the application through the address "http://127.0.0.1:9000" If you want to run the application later, check if the geo-polygons-symfony-back container is active executing the following command (Remembering that the same can/must be applied to the container from the database):

docker container ls -a

If you want to start the geo-polygons-symfony-back container, run the following command:

docker container start geo-polygons-symfony-back

If you want to stop the geo-polygons-symfony-back container, run the following command:

docker container stop geo-polygons-symfony-back

Endpoints

MUNICIPAL GEOMETRY
Find municipal by latitude and longitude	POST	http://127.0.0.1:9000/municipal/find

JSON structure for the following endpoint

Find municipal by latitude and longitude:

{
	"latitude": "-21.398883548",
	"longitude": "-51.057868674"
}

USER POINT
Find all user points	GET	http://127.0.0.1:9000/user-point/all
Buscar a user point by id	GET	http://127.0.0.1:9000/user-point/find/{id}
Save a user point	POST	http://127.0.0.1:9000/user-point/store
Update a user point	PUT	http://127.0.0.1:9000/user-point/update/{id}
Delete a user point	DELETE	http://127.0.0.1:9000/user-point/delete/{id}

JSON structure for the following endpoints

Save a user point(all attributes are required):

{
	"latitude": "-21.398883548",
	"longitude": "-51.057868674",
	"municipal_id": 631,
	"geom":"-50.7937920319 -21.1802260969, -50.7945216833 -21.1864952881, -50.7881337997 -21.1950409069, -50.8089869263 -21.2017423474, -50.8073567674 -21.204440552, -50.8100856765 -21.2152464404, -50.8035491235 -21.2303022787, -50.811856151 -21.2313857247, -50.8162171008 -21.2374762179, -50.8224617668 -21.2398572863, -50.8379237836 -21.2353462052, -50.8531581064 -21.2595515627, -50.9018021914 -21.3367986023, -50.9041490302 -21.3266497574, -50.9146786187 -21.3175423304, -50.9407818056 -21.3146722801, -50.9472545742 -21.3279341481, -50.9485692091 -21.3406233144, -50.9453193887 -21.3479949793, -50.9501232257 -21.3597757907, -50.9560337152 -21.3672747988, -50.9633697611 -21.3703541693, -50.9683945113 -21.3836760418, -50.9756872982 -21.3912518136, -50.9744583563 -21.3965997404, -50.984236372 -21.4065468725, -50.9824222661 -21.4122100559, -50.9860319048 -21.4287046154, -50.9835672416 -21.4322547857, -50.9925874583 -21.4367999015, -51.0033347502 -21.4385388237, -51.0245845013 -21.4263462599, -51.0247100808 -21.4208663742, -51.0330880762 -21.408863112, -51.0397224364 -21.4067760488, -51.045439432 -21.4063589008, -51.0450043489 -21.4037238276, -51.0536454977 -21.4046228386, -51.0578686748 -21.3988835488, -51.0747601327 -21.3881230191, -51.0808068853 -21.3875704745, -51.0805752068 -21.3848604689, -51.085748182 -21.3846390217, -51.0921726281 -21.3712158666, -51.0998372696 -21.3703467748, -51.1210992938 -21.3814066826, -51.1349780479 -21.3742530474, -51.1440548096 -21.3739104081, -51.1428245942 -21.3687768829, -51.1338818041 -21.3643358323, -51.134145139 -21.3582387887, -51.1152117707 -21.338962687, -51.1010693287 -21.3160616694, -51.0908030611 -21.3076239982, -51.0811917678 -21.2826281971, -51.0817505691 -21.2736049184, -51.0750369697 -21.2682534758, -51.0735679284 -21.2595009612, -51.0612257755 -21.2462348055, -51.0597838575 -21.2376640884, -51.0533159049 -21.2290833753, -51.0467320838 -21.2290622487, -51.0441320056 -21.2234911876, -51.0377655175 -21.2237433198, -51.0357201313 -21.2176730047, -51.003373532 -21.1920089213, -50.9917520162 -21.1953512502, -50.9804764327 -21.1943978878, -50.9687343175 -21.1847829382, -50.9673699877 -21.1795209878, -50.966999588 -21.1791705976, -50.9616586514 -21.1835201385, -50.9556162407 -21.1788886587, -50.9544866691 -21.1714998004, -50.9493676675 -21.1701941511, -50.9516658396 -21.158670724, -50.9464983919 -21.1549615248, -50.9457511997 -21.1453853238, -50.9477708008 -21.1367250931, -50.9521612955 -21.1333927237, -50.9376425942 -21.128004699, -50.9366924246 -21.1173656315, -50.9272562298 -21.1059831098, -50.9255848062 -21.0931927353, -50.9185608434 -21.0882945167, -50.9223423702 -21.0782699349, -50.9223177532 -21.0781567115, -50.9147536562 -21.0432652467, -50.9079100326 -21.0393986438, -50.9032977539 -21.0289898721, -50.8964316403 -21.02374392, -50.8977277138 -21.009147229, -50.8846753436 -20.9997589557, -50.8849716685 -20.990259662, -50.8741196338 -20.9771190572, -50.8775078958 -20.9698245664, -50.8751430097 -20.9641281012, -50.8785559215 -20.9598401323, -50.8756937838 -20.945030487, -50.857629789 -20.9407746181, -50.8497099284 -20.9354466198, -50.79742573 -20.9630801551, -50.8000325691 -20.9761524756, -50.79682499 -20.9847547031, -50.7967287923 -20.9852057741, -50.7954416272 -20.9912034164, -50.8041502711 -21.0120071467, -50.7999205952 -21.0220714928, -50.8023783441 -21.0268286667, -50.7961806601 -21.0399736725, -50.8012824084 -21.0460129633, -50.7976413038 -21.0521160351, -50.8021821735 -21.0603252668, -50.7947317449 -21.0627308501, -50.7743683803 -21.0839859973, -50.7440819733 -21.0951889411, -50.7528792361 -21.1016973799, -50.766493421 -21.1241492642, -50.7546754333 -21.1306695785, -50.7631298939 -21.1470727788, -50.780531983 -21.1716514645, -50.7937920319 -21.1802260969"
}

Update a user point(all attributes are optional):

{
	"latitude": "-21.398883548",
	"longitude": "-51.057868674",
	"municipal_id": 631,
	"geom":"-50.7937920319 -21.1802260969, -50.7945216833 -21.1864952881, -50.7881337997 -21.1950409069, -50.8089869263 -21.2017423474, -50.8073567674 -21.204440552, -50.8100856765 -21.2152464404, -50.8035491235 -21.2303022787, -50.811856151 -21.2313857247, -50.8162171008 -21.2374762179, -50.8224617668 -21.2398572863, -50.8379237836 -21.2353462052, -50.8531581064 -21.2595515627, -50.9018021914 -21.3367986023, -50.9041490302 -21.3266497574, -50.9146786187 -21.3175423304, -50.9407818056 -21.3146722801, -50.9472545742 -21.3279341481, -50.9485692091 -21.3406233144, -50.9453193887 -21.3479949793, -50.9501232257 -21.3597757907, -50.9560337152 -21.3672747988, -50.9633697611 -21.3703541693, -50.9683945113 -21.3836760418, -50.9756872982 -21.3912518136, -50.9744583563 -21.3965997404, -50.984236372 -21.4065468725, -50.9824222661 -21.4122100559, -50.9860319048 -21.4287046154, -50.9835672416 -21.4322547857, -50.9925874583 -21.4367999015, -51.0033347502 -21.4385388237, -51.0245845013 -21.4263462599, -51.0247100808 -21.4208663742, -51.0330880762 -21.408863112, -51.0397224364 -21.4067760488, -51.045439432 -21.4063589008, -51.0450043489 -21.4037238276, -51.0536454977 -21.4046228386, -51.0578686748 -21.3988835488, -51.0747601327 -21.3881230191, -51.0808068853 -21.3875704745, -51.0805752068 -21.3848604689, -51.085748182 -21.3846390217, -51.0921726281 -21.3712158666, -51.0998372696 -21.3703467748, -51.1210992938 -21.3814066826, -51.1349780479 -21.3742530474, -51.1440548096 -21.3739104081, -51.1428245942 -21.3687768829, -51.1338818041 -21.3643358323, -51.134145139 -21.3582387887, -51.1152117707 -21.338962687, -51.1010693287 -21.3160616694, -51.0908030611 -21.3076239982, -51.0811917678 -21.2826281971, -51.0817505691 -21.2736049184, -51.0750369697 -21.2682534758, -51.0735679284 -21.2595009612, -51.0612257755 -21.2462348055, -51.0597838575 -21.2376640884, -51.0533159049 -21.2290833753, -51.0467320838 -21.2290622487, -51.0441320056 -21.2234911876, -51.0377655175 -21.2237433198, -51.0357201313 -21.2176730047, -51.003373532 -21.1920089213, -50.9917520162 -21.1953512502, -50.9804764327 -21.1943978878, -50.9687343175 -21.1847829382, -50.9673699877 -21.1795209878, -50.966999588 -21.1791705976, -50.9616586514 -21.1835201385, -50.9556162407 -21.1788886587, -50.9544866691 -21.1714998004, -50.9493676675 -21.1701941511, -50.9516658396 -21.158670724, -50.9464983919 -21.1549615248, -50.9457511997 -21.1453853238, -50.9477708008 -21.1367250931, -50.9521612955 -21.1333927237, -50.9376425942 -21.128004699, -50.9366924246 -21.1173656315, -50.9272562298 -21.1059831098, -50.9255848062 -21.0931927353, -50.9185608434 -21.0882945167, -50.9223423702 -21.0782699349, -50.9223177532 -21.0781567115, -50.9147536562 -21.0432652467, -50.9079100326 -21.0393986438, -50.9032977539 -21.0289898721, -50.8964316403 -21.02374392, -50.8977277138 -21.009147229, -50.8846753436 -20.9997589557, -50.8849716685 -20.990259662, -50.8741196338 -20.9771190572, -50.8775078958 -20.9698245664, -50.8751430097 -20.9641281012, -50.8785559215 -20.9598401323, -50.8756937838 -20.945030487, -50.857629789 -20.9407746181, -50.8497099284 -20.9354466198, -50.79742573 -20.9630801551, -50.8000325691 -20.9761524756, -50.79682499 -20.9847547031, -50.7967287923 -20.9852057741, -50.7954416272 -20.9912034164, -50.8041502711 -21.0120071467, -50.7999205952 -21.0220714928, -50.8023783441 -21.0268286667, -50.7961806601 -21.0399736725, -50.8012824084 -21.0460129633, -50.7976413038 -21.0521160351, -50.8021821735 -21.0603252668, -50.7947317449 -21.0627308501, -50.7743683803 -21.0839859973, -50.7440819733 -21.0951889411, -50.7528792361 -21.1016973799, -50.766493421 -21.1241492642, -50.7546754333 -21.1306695785, -50.7631298939 -21.1470727788, -50.780531983 -21.1716514645, -50.7937920319 -21.1802260969"
}