Comments (16)
Can you propose structure of sqlite db?
What tables and columns do you want?
Should scheme be configurable or not?
from gazetteer.
What are all the values that can be exported so I can think to a scheme
from gazetteer.
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?
from gazetteer.
why do you need array? you can save fields from array like values in a table with same structure like array
from gazetteer.
I dont want to create explicit table for Tags and associations between features
from gazetteer.
ok, tomorrow I will send how I think is ok schema for sqlite
from gazetteer.
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
from gazetteer.
As first step - ok. I should think about it for a few days.
from gazetteer.
@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?
from gazetteer.
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
from gazetteer.
Or maybe you have a better idea for storing shapes
from gazetteer.
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.
from gazetteer.
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
from gazetteer.
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.
from gazetteer.
ok, then store them like json if is easy for you
from gazetteer.
Anyway, I'll wait for few days for more comments, I have rather simmilar task (export for oracle).
from gazetteer.
Related Issues (20)
- Failed to compile HOT 6
- Неточности в поиски адресов по схеме addrN:*
- split and slice succeed,but block at join using Gazetteer-1.3 HOT 5
- Netherlands - Out of memory HOT 10
- Добавить уровень подробности для обратного геокода
- Геокодирование внутри дома, внутри osm границы HOT 11
- Использовать названия из Wikidata для wikipedia-затегированных объектов HOT 11
- Data extracts for Romania HOT 3
- fix object namings to follow GeoJSON standards HOT 15
- add osm node ids to Gazetteer objects HOT 4
- Gazetteer hangs on JoinSliceRunable HOT 1
- Add ref for highways
- Output to CSV - Syntax HOT 3
- Extract only addresses? HOT 2
- Падает если в osm файле нет атрибута timestamp
- Путает номер дома со номером "микрорайона" (web) HOT 5
- Complain about unrecognized handlers options
- support parsing postcode from fullText address with libpostal HOT 2
- POI станции метро
- corrupted output file when processing the whole planet
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from gazetteer.