Skip to content

Latest commit

 

History

History
164 lines (112 loc) · 4.7 KB

File metadata and controls

164 lines (112 loc) · 4.7 KB

Cities API

Calculate distance between cities and find cities by radius

Requirements

  • Linux
  • Git
  • Java 8
  • Docker
  • IntelliJ Community
  • Heroku CLI
  • Travis CLI

DataBase

Postgres

docker run --name cities-db -d -p 5432:5432 -e POSTGRES_USER=postgres_user_city -e POSTGRES_PASSWORD=super_password -e POSTGRES_DB=cities postgres
  • earth_distance(earth, earth) return float8 Returns the great circle distance between two points on the surface of the Earth.
  • ll_to_earth(float8, float8) return earth Returns the location of a point on the surface of the Earth given its latitude (argument 1) and longitude (argument 2) in degrees.
  • earth_box(earth, float8) return cube Returns a box suitable for an indexed search using the cube @> operator for points within a given great circle distance of a location. Some points in this box are further than the specified great circle distance from the location, so a second check using earth_distance should be included in the query.

Access

docker exec -it cities-db /bin/bash

psql -U postgres_user_city cities

Query Earth Distance

lat_lon as Point data type

select ((select lat_lon from cidade where id = 4929) <@> (select lat_lon from cidade where id=5254)) as distance;

Cube

select earth_distance(
    ll_to_earth(-21.95840072631836,-47.98820114135742), 
    ll_to_earth(-22.01740074157715,-47.88600158691406)
) as distance;

Query Earth Box

  • 30000 is the radius

Get a Cube

SELECT earth_box(ll_to_earth(-21.95840072631836, -47.98820114135742), 30000) as cube;

The Cube contains? (ll_to_earth)

SELECT earth_box(ll_to_earth(-21.95840072631836, -47.98820114135742), 30000) @> ll_to_earth(-21.95840072631836, -47.98820114135742) as contains;

All Cities Where The Cube contains ll_to_earth

SELECT cidade.id, cidade.nome, cidade.lat_lon 
FROM cidade 
WHERE earth_box(ll_to_earth(-21.95840072631836, -47.98820114135742), 30000) @> ll_to_earth(cidade.lat_lon[0],cidade.lat_lon[1]);

Query Earth Box by Radius

SELECT cidade.id, cidade.nome, cidade.lat_lon 
FROM cidade 
WHERE earth_box(ll_to_earth(-21.95840072631836, -47.98820114135742), 30000) @> ll_to_earth(cidade.lat_lon[0],cidade.lat_lon[1]) 
AND earth_distance(ll_to_earth(-21.95840072631836, -47.98820114135742), ll_to_earth(cidade.lat_lon[0],cidade.lat_lon[1])) < 30000;

Spring Boot

  • Java 8
  • Gradle Project
  • Jar
  • Spring Web
  • Spring Data JPA
  • PostgreSQL Driver

Spring Data

Properties

Types

Heroku

heroku create dio-cities-api --addons=heroku-postgresql

Code Quality

PMD

Checkstyle

wget https://raw.githubusercontent.com/checkstyle/checkstyle/master/src/main/resources/google_checks.xml

InMemory Database Testing

Migrations

CI

Travis

extra