Add new field to Location model

[nixilla]

Option 1

We need additional unique and indexed field in Location model - called "external_id". This field will store the node id from OSM. It'll allow to update locations rather than creating new ones, every time the import script runs.

I'd like to be able to call model method like this:

Location.objects.get(external_id="123456789")

Option 2

Because we may need to import data from more than one source (currently OSM), we may need to store ids for each system we import separately. Therefore I'd suggest adding new table called LocationReferences with fields (id, location_id, provider, provider_id) with sample data:

record1:
  id: 1
  location_id: 1
  provider: osm
  provider_id: 123456789
record2:
  id: 2
  location_id: 1
  provider: OpenData
  provider_id: 321432543

provider_id in option 2 is the same field as external_id in option 1.

Using this new table/model should be transparent for project, there for I would expect the following method in the Location model:

Location.objects.findBy(provider="osm",provider_id="123456789")

The method above should return unique object.

[konektaz]

do we need to be able merge the records? maybe a click to merge?

[paparent]

What about an option 3:

Add the two fields on the Location model directly? provider and provider_id, or maybe better source and source_id. Both optional field.

I guess the best question here is: Why add a new LocationReference table?

[nixilla]

I think we'll be working only with OpenStreetMap for now so external_id (or osm_id) will do for now.

Currently option 2 - LocationReference table is dismissed, so don't worry about it.

We're only thinking to implement Option 1 as a way to make OSM import to update possible. Right now it only adds new records without checking if the record exists or not.

And to answer your question "Why add a new LocationReference table?":

To keep db structure in 3NF - as much as possible.

Regards Janusz

On 2 April 2013 18:07, PA Parent notifications@github.com wrote:

What about an option 3:

Add the two fields on the Location model directly? provider and provider_id, or maybe better source and source_id. Both optional field.

I guess the best question here is: Why add a new LocationReference table?

— Reply to this email directly or view it on GitHubhttps://github.com/konekta/where-is-when-is/issues/71#issuecomment-15788684 .

Janusz Slota

Founder and System Architect Nixilla Limited

http://www.nixilla.com http://www.linkedin.com/in/januszslota

Please don't print this e-mail unless you really need to

This message is confidential, you may use and apply the information only for the intended purpose. Internet communications are not secure and therefore Nixilla does not accept legal responsibility for the contents of this message. Any views or opinions expressed are solely those of the author and may not represent those of Nixilla. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.

[konektaz]

nice!

The third normal form (3NF) is a normal formhttp://en.wikipedia.org/wiki/Database_normalization#Normal_forms used in database normalizationhttp://en.wikipedia.org/wiki/Database_normalization .

On 2 April 2013 19:06, Janusz Slota notifications@github.com wrote:

I think we'll be working only with OpenStreetMap for now so external_id (or osm_id) will do for now.

Currently option 2 - LocationReference table is dismissed, so don't worry about it.

We're only thinking to implement Option 1 as a way to make OSM import to update possible. Right now it only adds new records without checking if the record exists or not.

And to answer your question "Why add a new LocationReference table?":

To keep db structure in 3NF - as much as possible.

Regards Janusz

On 2 April 2013 18:07, PA Parent notifications@github.com wrote:

What about an option 3:

Add the two fields on the Location model directly? provider and provider_id, or maybe better source and source_id. Both optional field.

I guess the best question here is: Why add a new LocationReference table?

— Reply to this email directly or view it on GitHub< https://github.com/konekta/where-is-when-is/issues/71#issuecomment-15788684>

.

Janusz Slota

Founder and System Architect Nixilla Limited

http://www.nixilla.com http://www.linkedin.com/in/januszslota

Please don't print this e-mail unless you really need to

This message is confidential, you may use and apply the information only for the intended purpose. Internet communications are not secure and therefore Nixilla does not accept legal responsibility for the contents of this message. Any views or opinions expressed are solely those of the author and may not represent those of Nixilla. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.

— Reply to this email directly or view it on GitHubhttps://github.com/konekta/where-is-when-is/issues/71#issuecomment-15792119 .

Kind regards Mark Herringer http://www.linkedin.com/in/markherringer Konektaz Open Development Initiative www.konekta.info

[konektaz]

Hi Guys, great that we've made some progress.

Janusz, Pier has updated our social sign on process. In addition we now have a a services and contact page.

Pier, please let us know if you have any other concerns/questions about the new field in the DB. If not please could you proceed with option 1 so janusz can do the next.

Step 1: Add new field to Location model (option 1) - Pier https://github.com/konekta/where-is-when-is/issues/71

Step 2: Reverse geocode the OSM locations - Janusz https://github.com/konekta/where-is-when-is/issues/114

On 2 April 2013 19:06, Janusz Slota notifications@github.com wrote:

I think we'll be working only with OpenStreetMap for now so external_id (or osm_id) will do for now.

Currently option 2 - LocationReference table is dismissed, so don't worry about it.

We're only thinking to implement Option 1 as a way to make OSM import to update possible. Right now it only adds new records without checking if the record exists or not.

And to answer your question "Why add a new LocationReference table?":

To keep db structure in 3NF - as much as possible.

Regards Janusz

On 2 April 2013 18:07, PA Parent notifications@github.com wrote:

What about an option 3:

Add the two fields on the Location model directly? provider and provider_id, or maybe better source and source_id. Both optional field.

I guess the best question here is: Why add a new LocationReference table?

— Reply to this email directly or view it on GitHub< https://github.com/konekta/where-is-when-is/issues/71#issuecomment-15788684>

.

Janusz Slota

Founder and System Architect Nixilla Limited

http://www.nixilla.com http://www.linkedin.com/in/januszslota

Please don't print this e-mail unless you really need to

This message is confidential, you may use and apply the information only for the intended purpose. Internet communications are not secure and therefore Nixilla does not accept legal responsibility for the contents of this message. Any views or opinions expressed are solely those of the author and may not represent those of Nixilla. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.

— Reply to this email directly or view it on GitHubhttps://github.com/konekta/where-is-when-is/issues/71#issuecomment-15792119 .

Kind regards Mark Herringer http://www.linkedin.com/in/markherringer Konektaz Open Development Initiative www.konekta.info

[nixilla]

Hi Pier,

Can you make external_id as string rather than integer, please? Also can you make it unique please?

Thanks

[paparent]

Hi,

The field is now a string. I can't really make it unique because it can be null ! I guess you'll be to make the import location smarter.

[nixilla]

You can have null and unique in the same time:

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases may not follow this rule. So be careful when developing applications that are intended to be portable.

http://www.postgresql.org/docs/8.2/interactive/ddl-constraints.html#AEN2058

Regards Janusz

On 7 April 2013 17:43, PA Parent notifications@github.com wrote:

Hi,

The field is now a string. I can't really make it unique because it can be null ! I guess you'll be to make the import location smarter.

— Reply to this email directly or view it on GitHubhttps://github.com/konekta/where-is-when-is/issues/71#issuecomment-16018360 .

Janusz Slota

Founder and System Architect Nixilla Limited

http://www.nixilla.com http://www.linkedin.com/in/januszslota

Please don't print this e-mail unless you really need to

This message is confidential, you may use and apply the information only for the intended purpose. Internet communications are not secure and therefore Nixilla does not accept legal responsibility for the contents of this message. Any views or opinions expressed are solely those of the author and may not represent those of Nixilla. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.