[WIP] Add `wd_id` + `properties` column

ImreSamu commented 6 years ago

Work in progess

Extending PG data with 2 new columns

becasuse my use case is very special, probably we need to add this columns via special optional command line parameters like:

go-whosonfirst-pgis --add_wd_id ... ( add wd_id::text columns : default NOT add )
go-whosonfirst-pgis --add_properties ... ( add properties JSON/JSONB columns : default NOT add )
or add both --add_wd_id --add_properties

thisisaaronland commented 6 years ago

A few comments:

It's probably worth add a generic -add COLUMN#COLUMN_DEFINITION flag that can be used beyond -add_wd_id and --add_properties. I agree that those shouldn't be part of core but I can imagine that there be other similar use cases in time. Rather than any ad hoc columns we can specify a fixed list of known/possible columns in a flags/column.go package.
It seems like client.go needs to be updated to account for additional properties (see above) which might be a little fiddly since Go is fiddly that way...
I'm not sure I understand what the dwindex target is doing; either all the dependencies should be grouped under the deps target.
Are the c.sh and t.sh scripts just helper tools or are they meant to be general usage applications? If the latter we should find some more descriptive names for them :-)

ImreSamu commented 6 years ago

c.sh and t.sh scripts just helper tools

yes, ( and sorry for the noise ) I will remove this (helper) files

It's probably worth add a generic -add COLUMN#COLUMN_DEFINITION

good idea,

my use case is not typical

I need at least 2 new columns ("wd:id","properties" )
and I am not using meta columns ( so it would better if I can drop )
and an optimal for me if I can create 1 unified geo field like coalesce(geom, centroid)

my long time vision:

it would be optimal for me if I can configure via yml file ( like in imposm3 / osm2pgsql tools )

with a carefully planned framework, i can implement, register and use any mapping functions I can dream of:

example - for the current columns

sql variable	mapping func	array of mapping parameters
id	gjson.bigint.primarykey	["id"]
parent_id	gjson.bigint	["properties.wof:parent_id"]
placetype_id	placetype_id
wd_id	gjson.text	["properties.wof:concordances.wd:id"]
is_superseded	is_superseded
is_deprecated	is_deprecated
meta	meta
properties	gjson.rawjson	["properties"]
geom_hash	geom_hash.text
lastmod	gjson.text	["properties.wof:lastmodified"]
geom	geography.multipolygon
centroid	geography.centroid

-add COLUMN#COLUMN_DEFINITION

The # char as a separator not ideal, it can be used as a gjson parameter

Rather than any ad hoc columns we can specify a fixed list of known/possible columns in a flags/column.go package.

I have collected some functions, as I see we need

my favorite example:

mapping function id: gjson.sprintf

params:

example parameters:

["SRID=4326;POINT(%v %v)", "properties.mps:longitude" , "properties.mps:longitude" ]

example output - perfect Postgis Point for bulk loading:

this is my long-term vision based on using this tool
I have a database mindset, so most of the mapping can be implemented as an SQL postprocessing ( but this takes more times, io, disk space )
for the short time - I need only 2 new columns.
I need more time to re-think what will be optimal for short time - and long time: this draft spec only for brainstorming. :)

ImreSamu commented 3 years ago

closing .. reason: I have started using a simple bash + postgres scripts for importing the geojson files ..