Reverse country GeoCoder has some flaws near 'Monaco'

[GGGuerin]

Hello,
As you can see, I have three visits for this GK which are located in France. However, the site displays an other flag (polish one I suppose)

https://geokrety.org/konkret.php?id=78523&page=3

[kumy]

Thanks @GGGuerin for your report. This flag is the Monaco one ;) (yes it looks like Polish, but in reverse).
I've seen that before, it's our internal reverse country geocoder that has some "rounded values" in the polylines. It affects bother website version, actual prod and new-theme branch.

I'll keep this issue open to not forget that.

(BTW Welcome on GitHub!!!)

[GGGuerin]

Remark : These three caches are more than 200km far away from Monaco. I have got some caches which are in the area of Monaco (but in France) and are well situated in France.
I hope it helps ;-)

[kumy]

To be noted reverse country geocoder methods are completly different in both GK implementation GKv1 (master) vs GKv2 (new-theme)

GKv1:

1. check using an external API we host
2. fallback to external free geocoding service
3. it's a frequently scheduled task

GKv2:

1. Countries geometries (btw, also timezones, and altitudes) are in database
2. All logic about countries ans altitudes are directly handled by the database on CRUD triggers

This issue has been opened targeted to master branch (GKv1). Effectively there is a bug, but it's located in the simplified polygons used in geokrety/country-reverse-geocoder, I will not fix it, sorry.

Here is the result: https://geo.geokrety.org/api/getCountry?lat=43.74667&lon=5.20602

On GKv2, problem does not exists in normal use case:

I'll check what's the problem with daily imports it should have been automatic.

Edit: During daily imports, triggers are disabled to prevent foreign key errors :/

geokrety-website/website/db/database-migrator.php

Line 47 in 8d8cffb

$pgsql->query('SET session_replication_role = replica;');

[okainov]

Countries geometries (btw, also timezones, and altitudes) are in database

Not really related to the issue itself, but could you please provide some insights, what was the data source here (I don't believe you handcrafter coordinates->countries mapping yourself), is this part reusable (read as "we're in Geocaching.su would be interested in using some smartness based on coordinates") and so on and so forth?

[GGGuerin]

Great !

[kumy]

Here some insight about Gis features

General

• I'm using postgres with spacial feature enabled (PostGis)
• Triggers in database update the related fields before insert/update
• database schema https://github.com/geokrety/geokrety-website/blob/feature/new-theme/website/db/dumps/10_public-schema.sql
• data sources
  https://github.com/geokrety/geokrety-website/blob/feature/new-theme/website/db/README.md#datasources

Altitude profiles

• Database has been seeded with publicly available tiles from Nasa srtm https://github.com/geokrety/geokrety-website/blob/feature/new-theme/website/db/README.md#initialize-srtm
• We host a complete data private use mirror - that's about 14GiB

geokrety-website/website/db/dumps/20_geokrety-schema.sql

Lines 1005 to 1008 in 8d8cffb

	SELECT public.ST_Value(rast, NEW.position::public.geometry) As elevation
	FROM public.srtm
	WHERE public.ST_Intersects(rast, NEW.position::public.geometry)
	INTO elevation;

reverse country geocoder

• are polygons stored in database
• taken from https://github.com/kumy/srtm-server#licence
• here again PostGis functions magic happen

geokrety-website/website/db/dumps/20_geokrety-schema.sql

Lines 998 to 1001 in 8d8cffb

	SELECT iso_a2
	FROM public.countries
	WHERE public.ST_Intersects(geom, NEW.position::public.geography)
	INTO country;

timezones

• Not handled right now, didn't found a nice datasource similar to the reverse country geocoder polygons
• The feature behind is to propose using dynamic localtime in logging form, based on provided move coordinates - but nothing implemented yet.