Calculate distance between cities and find cities by radius
- Linux
- Git
- Java 8
- Docker
- IntelliJ Community
- Heroku CLI
- Travis CLI
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
- Postgres Earth distance
- earthdistance--1.0--1.1.sql
- OPERATOR <@>
- postgrescheatsheet
- datatype-geometric
- Cube @>
- Cube Tutorial
- 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.
docker exec -it cities-db /bin/bash
psql -U postgres_user_city cities
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;
- 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]);
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;
- Java 8
- Gradle Project
- Jar
- Spring Web
- Spring Data JPA
- PostgreSQL Driver
heroku create dio-cities-api --addons=heroku-postgresql
wget https://raw.githubusercontent.com/checkstyle/checkstyle/master/src/main/resources/google_checks.xml