add a gui and sql / sqlite export

[rakucmr]

Adding a GUI for gazetter will improve usability, specialy when exporting. Also adding an option for export sql or sqlite will make it easy and faster when using on android.

[kiselev-dv]

Can you propose structure of sqlite db? What tables and columns do you want? Should scheme be configurable or not?

[rakucmr]

What are all the values that can be exported so I can think to a scheme

[kiselev-dv]

This is JSON scheme https://github.com/kiselev-dv/gazetteer/blob/develop/GazetteerWeb/src/main/resources/gazetteer_schema.json

In a nut shell - this is data I have. In cvs I can't save all data what I have. (In first place I can't save neighbor streets and places).

Does sqlite supports arrays or associative arrays?

[rakucmr]

why do you need array? you can save fields from array like values in a table with same structure like array

[kiselev-dv]

I dont want to create explicit table for Tags and associations between features

[rakucmr]

ok, tomorrow I will send how I think is ok schema for sqlite

[rakucmr]

What you say about this structure?

POI

• id - openstreetmap id
• name
• countrycode - iso3166-2 country code (2 letters)
• alternatenames
• location - middle location of the POI (lat/long)
• amenity - POI type

Street

• id - openstreetmap Id
• name
• location - middle location of the POI (lat/long)
• countrycode - iso3166-2 country code (2 letters)
• city_id
• shape - The delimitation of the street in HEXEWKB
• alternatenames

Cities

• id - openstreetmap id
• name
• countrycode - iso3166-2 country code (2 letters)
• postcode
• location - middle location of the POI (lat/long)
• shape - The delimitation of the city in HEXEWKB
• type - the type of city ('city', 'village', 'town', 'hamlet', ...)
• administrative_division_id - where the cities is located (generally the fully qualified administrative division)
• alternatenames

House Number

• id - openstreetmap id
• housenumber
• postcode
• countrycode - iso3166-2 country code (2 letters)
• location - middle location of the POI (lat/long)
• street_id
• city_id
• country_id

Administrative Division

• id - openstreetmap id
• name
• type / level
• administrative_division_id - where is located
• location - middle location of the POI (lat/long)
• shape - The delimitation of the city in HEXEWKB
• alternatenames

[kiselev-dv]

As first step - ok. I should think about it for a few days.

[kiselev-dv]

@raku I'm not familiar with sqlite, so first question about alternatenames, is there some kind of string array type in sqlite? Or how alternatenames should looks like?

Next thing about alternatenames - all they actualy have particular language. I've dropped language for some reasons in json alternate_names but I think it will be helpfull in sqlite dumps.

Next shape HEXEWKB - is it buildedin data type in sqlite? Maybe there is native data types for geometry in sqlite or in spatiallite?

[rakucmr]

alternatenames - will be a string text or varchar, here depends on how long are alternatenames, and is the name in other language separated by coma I think HEXEWKB is not present in SQLite, but I think you can store it in a text field

[rakucmr]

Or maybe you have a better idea for storing shapes

[kiselev-dv]

For shapes, I think to use https://en.wikipedia.org/wiki/SpatiaLite (it's sqlite geospatial extension).

But still, I'm not shure about storing alternate names as json encoded string (or any oteher kind of string encode). Also I want to store original object tags which are also key-value pairs. Ofcourse I can keep those pairs in separate table or add them as columns, but it's not easy to use.

[rakucmr]

why to store them in json format? if you store like that then will need to parse json and get values, another operation that is not needed. simple text just a query and you have the values

[kiselev-dv]

But you still should parse them. Arrays:

['string 1', 'string2', 'string3']
string1; string2; string3

Tags

{'key1':'value1', 'key2':'value2', 'key3':'value3' }
'key1'=>'value1';'key2'=>'value2';'key3'=>'value3'

As for me there is no difference between those two cases, expet I should write my own encoder and decoder for custom format.

Maybe I'll use separate table for tags and string arrays. Something like this:

pk, key:string, value:string

key=null for array members, in such case it's not an arrays - it's sets.

[rakucmr]

ok, then store them like json if is easy for you

[kiselev-dv]

Anyway, I'll wait for few days for more comments, I have rather simmilar task (export for oracle).