Most of the sqls come from [https://tapoueh.org/blog/2018/05/postgresql-data-types-point/](https://tapoueh.org/blog/2018/05/postgresql-data-types-point/)

## Setup
```shell
docker pull kartoza/postgis:9.6-2.4
wget http://download.geonames.org/export/dump/allCountries.zip
unzip allCountries.zip
wget http://download.geonames.org/export/dump/admin2Codes.txt
wget http://download.geonames.org/export/dump/shapes_simplified_low.json.zip
```

Some other data files are at the end of this gist.
To execute the script `01.csv.load` you will need `pgloader`. On Ubuntu, it is part of the distribution so you can install it via `sudo apt install pgloader`.
To install ogr2ogr go to the [download](https://trac.osgeo.org/gdal/wiki/DownloadingGdalBinaries) page of GDal.

## Goal
The goal was to be able to retrieve information based on some GPS information. At the end I could execute the following queries:

Get some information about using the GPS coordinate of Neuilly-sur-Seine.

```sql
select 
    round((point(2.2728913,48.8821175) <-> geoname.location)::numeric, 3) as dist,
    country.iso,
    region.name as region,
    district.name as district
from  geoname.geoname
        left join geoname.country using(isocode)
        left join geoname.region using(isocode, regcode)
        left join geoname.district using(isocode, regcode, discode)
order by location <-> point(2.2728913,48.8821175)
limit 1;

 dist  | iso |    region     |    district    
-------+-----+---------------+----------------
 0.001 | FR  | Île-de-France | Hauts-de-Seine
```

Using the country boriders:

```sql
select iso, name from geoname.country_shape
left join geoname.country using(geonameid)
WHERE ST_Contains(shape, ST_MakePoint(2.2728913,48.8821175,4326));

 iso |  name  
-----+--------
 FR  | France
```